welcome: please sign in

Diff for "MemberManual/Databases"

Differences between revisions 27 and 38 (spanning 11 versions)
Revision 27 as of 2007-12-18 00:35:18
Size: 5952
Editor: DavorOcelic
Comment:
Revision 38 as of 2009-09-09 20:08:33
Size: 6794
Editor: ClintonEbadi
Comment:
Deletions are marked like this. Additions are marked like this.
Line 2: Line 2:
Line 5: Line 4:
[[TableOfContents]] <<TableOfContents>>
Line 8: Line 7:
Line 12: Line 10:
Line 14: Line 11:
Creating a MySQL user account is an easy task with {{{dbtool}}}. It is available for use when you login to mire.hcoop.net.   Creating a MySQL user account is an easy task with {{{dbtool}}}. It is available for use when you log in to `ssh.hcoop.net`.
Line 21: Line 18:
Line 30: Line 26:
Line 39: Line 34:
Line 43: Line 37:
For technical reasons, users at first do not have the privilege to drop their tables. You need to create database tables and then run '''mysql-fixperms'''. Upon command completion, you will have the privilege to drop all tables that existed at the time of running the command.
Line 44: Line 39:
For technical reasons, users at first do not have the privilege to drop their tables. You need to create database
tables and then run '''mysql-fixperms'''. Upon command completion, you will have the privilege to drop all tables
that existed at the time of running the command.

If you are trying to import a database structure into MySQL and your script does an unconditional "drop and re-create"
on database tables, the process will of course fail on first attempt. The way to go about it is to either remove
unconditional DROP statements from your SQL script, or to create same-named empty tables ("CREATE TABLE ''NAME'' (a INT)") and
run '''mysql-fixperms'''.
If you are trying to import a database structure into MySQL and your script does an unconditional "drop and re-create" on database tables, the process will of course fail on first attempt. The way to go about it is to either remove unconditional DROP statements from your SQL script, or to create same-named empty tables ("CREATE TABLE ''NAME'' (a INT)") and run '''mysql-fixperms'''.
Line 59: Line 47:
Line 70: Line 57:
To learn more about the MySQL shell, review the [[http://dev.mysql.com/doc/refman/5.0/en/|MySQL manual]].
Line 71: Line 59:
To learn more about the MySQL shell, review the [http://dev.mysql.com/doc/refman/5.0/en/ MySQL manual]. There is a web interface available for managing your databases at https://phpmyadmin.hcoop.net/. Use your database password when logging into it.
Line 74: Line 62:
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. 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 o as stated above and do the following.
Line 77: Line 65:

The "-h" option is for the hostname, which is "mysql" in our case. (The full hostname is `mysql.hcoop.net`.)
Line 78: Line 68:
mysqldump --skip-add-drop-table -p USER_DATABASE > mydatabase.sql mysqldump --skip-add-drop-table -p USER_DATABASE -h mysql> mydatabase.sql
Line 81: Line 71:
Line 85: Line 74:
Line 88: Line 78:
Line 94: Line 83:

(Assuming, of course, that you don't have any jerk users in your database named "[http://xkcd.com/327/ Robert'); DROP TABLE Students;--]".)
(Assuming, of course, that you don't have any jerk users in your database named "[[http://xkcd.com/327/|Robert'); DROP TABLE Students;--]]".)
Line 100: Line 88:
Line 106: Line 93:
Line 108: Line 94:
Line 112: Line 97:
{{{dbtool postgres adduser}}}
{{{
dbtool postgres adduser
}}}
Line 122: Line 108:
The database created will be called USER_DATABASE (where USER is your username and DATABASE is the name you provided {{{dbtool}}}).
Line 123: Line 110:
The database created will be called USER_DATABASE (where USER is your username and DATABASE is the name you provided {{{dbtool}}}). If you would like to specify an alternate text encoding for your database (e.g., UTF8), you can run `dbtool` like this:
{{{
dbtool postgres createdb DATABASE UTF8
}}}
Line 131: Line 121:
Line 142: Line 131:

To learn more about the {{{psql}}} shell, take a look at the [http://www.postgresql.org/docs/8.1/interactive/index.html PostgreSQL manual].
To learn more about the {{{psql}}} shell, take a look at the [[http://www.postgresql.org/docs/8.1/interactive/index.html|PostgreSQL manual]].
Line 146: Line 134:
If you're using PostgreSQL, then you probably already know how to do this. If not, please wait a little while for instructions to be posted here. If you're using PostgreSQL, then you probably already know how to do this. Let us know if that's not the case and you need instructions.

= Database quotas =

Each user is granted a default 400 MB database quota that applies to all SQL databases together. If you are planing to store more data, please submit a quota increase request on the Members Portal page.

Some users submitted quota reduce requests for partitions they didn't use. If you did that back in time when you did not have any database usage in mind, please check your current quota through the Members Portal.

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 log in to ssh.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

For technical reasons, you cannot drop a database using the mysql shell (using "DROP DATABASE...").

Delete a Table

For technical reasons, users at first do not have the privilege to drop their tables. You need to create database tables and then run mysql-fixperms. Upon command completion, you will have the privilege to drop all tables that existed at the time of running the command.

If you are trying to import a database structure into MySQL and your script does an unconditional "drop and re-create" on database tables, the process will of course fail on first attempt. The way to go about it is to either remove unconditional DROP statements from your SQL script, or to create same-named empty tables ("CREATE TABLE NAME (a INT)") and run mysql-fixperms.

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

To learn more about the MySQL shell, review the MySQL manual.

There is a web interface available for managing your databases at https://phpmyadmin.hcoop.net/. Use your database password when logging into it.

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 o as stated above and do the following.

On the system with the original database (remember to replace USER with your username and DATABASE with the name of your database):

The "-h" option is for the hostname, which is "mysql" in our case. (The full hostname is mysql.hcoop.net.)

mysqldump --skip-add-drop-table -p USER_DATABASE -h mysql> mydatabase.sql
scp ./mydatabase.sql USER@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 "Robert'); DROP TABLE Students;--".)

Then import the dumpfile as written above.

Resetting Permissions

Sometimes we change our policy on which permissions users are granted on their databases. If your database has different permissions set than our current defaults, you can run this command line to update the permissions:

dbtool mysql grant DATABASE

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

If you would like to specify an alternate text encoding for your database (e.g., UTF8), you can run dbtool like this:

dbtool postgres createdb DATABASE UTF8

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 PostgreSQL manual.

Transferring Databases

If you're using PostgreSQL, then you probably already know how to do this. Let us know if that's not the case and you need instructions.

Database quotas

Each user is granted a default 400 MB database quota that applies to all SQL databases together. If you are planing to store more data, please submit a quota increase request on the Members Portal page.

Some users submitted quota reduce requests for partitions they didn't use. If you did that back in time when you did not have any database usage in mind, please check your current quota through the Members Portal.

MemberManual/Databases (last edited 2021-11-06 19:46:06 by ClintonEbadi)