#pragma section-numbers off This is the chapter of the MemberManual that describes how to use PostgreSQL and MySQL databases on HCoop servers. [[TableOfContents]] = 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 --skip-add-drop-table -p USER_DATABASE > mydatabase.sql scp ./mydatabase.sql mire.hcoop.net:~/ }}} `--skip-add-drop-table` is important since by default no tables are granted the `DROP` command. Finally, import the database using `mire`: {{{ mysql -h mysql -p USER_DATABASE < ~/mydatabase.sql }}} Now that your database has tables, you may grant `DROP` permissions to them using `mysql-fixperms` without any arguments. Now you will be able to `DROP` tables when needed. If for some reason you have a MySQL dump that includes the `DROP` command, then you may run this script on your dumpfile to remove them: {{{ sed 's/DROP TABLE/-- DROP TABLE/g' ~/mydatabase.sql > ~/mydatabase_nodrop.sql }}} (Assuming, of course, that you don't have any jerk users in your database named "[http://xkcd.com/327/ Robert'); DROP TABLE Students;--]".) Then import the dumpfile as written above. = 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].