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:~/
If you are importing a mysqldump for the first time on mire, you won't have permissions to DROP the nonexistant tables. You can filter out the offending "DROP TABLE" commands with the following command:
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 TABLES Students;--]".)
Finally, import the database using mire:
mysql -h mysql -p USER_DATABASE < ~/mydatabase.sql
Now that you have created tables within a database, you can grant DROP permissions to them using mysql-fixperms without any arguments.
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].