welcome: please sign in

Upload page content

You can upload content for the page named below. If you change the page name, you can also upload content for another page. If the page name is empty, we derive the page name from the file name.

File to load page content from
Page name
Comment
First name of the author of the GNU Manifesto

Revision 20 as of 2007-12-09 19:13:52

Edit

MemberManual / Databases

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 -p USER_DATABASE > mydatabase.sql
scp ./mydatabase.sql mire.hcoop.net:~/

On mire, run mysql-fixperms without any arguments. This will grant DROP table permissions on your databases so that importing the dumped file should not error. You may have to wait a few moments for this to be accomplished.

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

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].