2685
Comment: add content... maybe I've created too many sections?
|
4163
add instructions about mysql database transfer
|
Deletions are marked like this. | Additions are marked like this. |
Line 7: | Line 7: |
== MySQL == | = Introduction = We use a custom tool called {{{dbtool}}} to handle many of the aspects of creating and deleting databases for MySQL and PostgreSQL. = MySQL = == Create an Account == |
Line 10: | Line 16: |
Run the following command:{{{dbtool mysql adduser}}}. You will be asked for a password. The user created will be the same as your log-in username. | Run the following command: |
Line 12: | Line 18: |
=== Creating Databases === {{{dbtool}}} is also used to create new MySQL databases. Replace DATABASE with your desired database name: {{{dbtool mysql createdb DATABASE}}}. The database created will be USER_DATABASE (where USER is your username and database is the name you provided {{{dbtool}}}). |
{{{ dbtool mysql adduser }}} |
Line 15: | Line 22: |
=== Deleting Databases === Using {{{dbtool}}}, you may drop a specified database. Replace DATABASE with your desired database: {{{dbtool mysql dropdb DATABASE}}}. |
You will be asked for a password. The user created will be the same as your log-in username. |
Line 18: | Line 24: |
=== Changing Your Password === If you need to change your MySQL password for security purposes or you have forgotten it, you may do so with this command: {{{dbtool mysql passwd}}}. You will be prompted to input a new password. |
== Create a Database == {{{dbtool}}} is also used to create new MySQL databases. Replace DATABASE with your desired database name: |
Line 21: | Line 27: |
=== Accessing Databases === For web applications, set the MySQL host/server to {{{mysql}}}. Default ports apply. Follow your software package's instructions. |
{{{ dbtool mysql createdb DATABASE }}} |
Line 24: | Line 31: |
In addition, you can easily use the {{{mysql}}} shell to manipulate or analyze your databases. Replace USER with your username and DATABASE with the name you gave {{{dbtool}}} to create it: {{{ mysql -p -h mysql USER_DATABASE }}} | The database created will be USER_DATABASE (where USER is your username and DATABASE is the name you provided {{{dbtool}}}). |
Line 26: | Line 33: |
== PostgreSQL == To create a PostgreSQL user account, enter the following command on mire.hcoop.net: {{{dbtool postgres adduser}}}. You will not be prompted for a password since PostgreSQL utilizes a security model that is quite different from MySQL. |
== Delete a Database == Delete a database with the following: |
Line 29: | Line 36: |
=== Creating Databases === Additionally, {{{dbtool}}} is used to create new MySQL databases. Replace DATABASE with your desired database name: {{{dbtool postgres createdb DATABASE}}}. The database created will be called USER_DATABASE (where USER is your username and database is the name you provided {{{dbtool}}}. |
{{{ dbtool mysql dropdb DATABASE }}} |
Line 32: | Line 40: |
== Changing Your Password == If you need to change your MySQL password for security purposes or you have forgotten it, you may do so with this command: |
|
Line 33: | Line 43: |
=== Deleting Databases === Using {{{dbtool}}}, you may also drop specified databases. Replace DATABASE with your desired database: {{{dbtool postgres dropdb DATABASE}}}. |
{{{ dbtool mysql passwd }}} |
Line 36: | Line 47: |
=== Accessing Databases === For web applications, set the PostgreSQL host/server to {{{postgres}}}. Default ports apply. Follow your software package's instructions. |
You will be prompted to input a new password. |
Line 39: | Line 49: |
It is very easy to access your database using a PostgreSQL shell. Replace USER with your username and DATABASE with the database name you gave {{{dbtool}}}: psql -h postgres USER_DATABASE | == Accessing Databases == For web applications, set the MySQL host/server to {{{mysql}}}. Default ports apply. Use your HCoop username. Your password will be required. Please safeguard it. Remember that your database is really named USER_DATABASE, where DATABASE is the name you originally gave {{{dbtool}}}. Follow your software package's instructions. In addition, you can easily use the {{{mysql}}} shell to manipulate or analyze your databases: {{{ mysql -p -h mysql USER_DATABASE }}} For security reasons, you cannot drop a database using the {{{mysql}}} shell. To learn more about the MySQL shell, review the [http://dev.mysql.com/doc/refman/5.0/en/ MySQL manual]. == Transferring Databases == If you have a MySQL database on another system and you wish to put that database onto our systems, you'll have to create the database on `mire` as stated above and do the following: On the system with the original database: {{{ mysqldump -p USER_DATABASE > mydatabase.sql scp ./mydatabase.sql mire.hcoop.net:~/ }}} On the system that you wish to transfer the database to (i.e., mire): {{{ mysql -h mysql -p USER_DATABASE < mydatabase.sql }}} If you receive errors that the `DROP` command cannot be used, you will have to comment out all DROP commands. There is also another way, using `mysql-fixperms` but I have no idea how that works. = PostgreSQL = == Create an Account == To create a PostgreSQL user account, enter the following command on mire.hcoop.net: {{{dbtool postgres adduser}}} You will not be prompted for a password since PostgreSQL utilizes a security model that is quite different from MySQL. The user created will be the same as your log-in username. == Create a Database == Additionally, {{{dbtool}}} is used to create new Postgres databases. Replace DATABASE with your desired database name: {{{ dbtool postgres createdb DATABASE }}} The database created will be called USER_DATABASE (where USER is your username and DATABASE is the name you provided {{{dbtool}}}). == Delete a Database == Delete a database with the following command: {{{ dbtool postgres dropdb DATABASE }}} For security reasons, you cannot drop a database using the {{{psql}}} shell. == Accessing Databases == For web applications, set the PostgreSQL host/server to {{{postgres}}}. Default ports apply. Use your HCoop username. A password is not required. Remember that your database is really named USER_DATABASE, where DATABASE is the name you originally gave {{{dbtool}}}. Follow your software package's instructions. It is very easy to access your database using a PostgreSQL shell: {{{ psql -h postgres USER_DATABASE }}} To learn more about the {{{psql}}} shell, take a look at the [http://www.postgresql.org/docs/8.1/interactive/index.html PostgreSQL manual]. |
This is the chapter of the MemberManual that describes how to use PostgreSQL and MySQL databases on HCoop servers.
Introduction
We use a custom tool called dbtool to handle many of the aspects of creating and deleting databases for MySQL and PostgreSQL.
MySQL
Create an Account
Creating a MySQL user account is an easy task with dbtool. It is available for use when you login to mire.hcoop.net.
Run the following command:
dbtool mysql adduser
You will be asked for a password. The user created will be the same as your log-in username.
Create a Database
dbtool is also used to create new MySQL databases. Replace DATABASE with your desired database name:
dbtool mysql createdb DATABASE
The database created will be USER_DATABASE (where USER is your username and DATABASE is the name you provided dbtool).
Delete a Database
Delete a database with the following:
dbtool mysql dropdb DATABASE
Changing Your Password
If you need to change your MySQL password for security purposes or you have forgotten it, you may do so with this command:
dbtool mysql passwd
You will be prompted to input a new password.
Accessing Databases
For web applications, set the MySQL host/server to mysql. Default ports apply. Use your HCoop username. Your password will be required. Please safeguard it. Remember that your database is really named USER_DATABASE, where DATABASE is the name you originally gave dbtool. Follow your software package's instructions.
In addition, you can easily use the mysql shell to manipulate or analyze your databases:
mysql -p -h mysql USER_DATABASE
For security reasons, you cannot drop a database using the mysql shell.
To learn more about the MySQL shell, review the [http://dev.mysql.com/doc/refman/5.0/en/ MySQL manual].
Transferring Databases
If you have a MySQL database on another system and you wish to put that database onto our systems, you'll have to create the database on mire as stated above and do the following:
On the system with the original database:
mysqldump -p USER_DATABASE > mydatabase.sql scp ./mydatabase.sql mire.hcoop.net:~/
On the system that you wish to transfer the database to (i.e., mire):
mysql -h mysql -p USER_DATABASE < mydatabase.sql
If you receive errors that the DROP command cannot be used, you will have to comment out all DROP commands. There is also another way, using mysql-fixperms but I have no idea how that works.
PostgreSQL
Create an Account
To create a PostgreSQL user account, enter the following command on mire.hcoop.net:
dbtool postgres adduser
You will not be prompted for a password since PostgreSQL utilizes a security model that is quite different from MySQL. The user created will be the same as your log-in username.
Create a Database
Additionally, dbtool is used to create new Postgres databases. Replace DATABASE with your desired database name:
dbtool postgres createdb DATABASE
The database created will be called USER_DATABASE (where USER is your username and DATABASE is the name you provided dbtool).
Delete a Database
Delete a database with the following command:
dbtool postgres dropdb DATABASE
For security reasons, you cannot drop a database using the psql shell.
Accessing Databases
For web applications, set the PostgreSQL host/server to postgres. Default ports apply. Use your HCoop username. A password is not required. Remember that your database is really named USER_DATABASE, where DATABASE is the name you originally gave dbtool. Follow your software package's instructions.
It is very easy to access your database using a PostgreSQL shell:
psql -h postgres USER_DATABASE
To learn more about the psql shell, take a look at the [http://www.postgresql.org/docs/8.1/interactive/index.html PostgreSQL manual].