welcome: please sign in

Diff for "MemberManual/Databases"

Differences between revisions 49 and 50
Revision 49 as of 2020-01-02 18:16:34
Size: 7759
Editor: 98
Comment: remove outdated references to bog
Revision 50 as of 2021-11-06 19:46:06
Size: 6539
Editor: ClintonEbadi
Comment: mysql-fixperms is dead and members can DROP tables at will, update docs to remove workarounds for not having DROP by default
Deletions are marked like this. Additions are marked like this.
Line 24: Line 24:
{{{dbtool}}} is also used to create new MySQL databases. Replace DATABASE with your desired database name: {{{dbtool}}} is also used to create new MySQL databases. Replace `DATABASE` with your desired database name:
Line 29: Line 29:
The database created will be USER_DATABASE (where USER is your username and DATABASE is the name you provided {{{dbtool}}}). The database created will be USER_DATABASE (where USER is your username and `DATABASE` is the name you provided {{{dbtool}}}).
Line 37: Line 37:
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'''.
Although you could drop a database using the `mysql` shell (using "`DROP DATABASE...`"), you should avoid doing so as it will leave stale grants behind and `dbtool` ensures everything is cleaned up appropriately.
Line 53: Line 48:
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. For web applications, set the MySQL host/server to `mysql.hcoop.net`. 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.
Line 55: Line 50:
In addition, you can easily use the {{{mysql}}} shell to manipulate or analyze your databases: In addition, you can easily use the `mysql` shell to manipulate or analyze your databases:
Line 58: Line 53:
mysql -p -h mysql USER_DATABASE mysql -p -h mysql.hcoop.net USER_DATABASE
Line 60: Line 55:
To learn more about the MySQL shell, review the [[http://dev.mysql.com/doc/refman/5.5/en/|MySQL manual]]. To learn more about the MySQL shell, review the [[https://dev.mysql.com/doc/refman/5.7/en/|MySQL manual]].
Line 65: Line 60:
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. 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 as stated above and do the following.
Line 67: Line 62:
On the system with the original database (remember to replace USER with your username and DATABASE with the name of your database): On the system with the original database (remember to replace `USER` with your username and `DATABASE` with the name of your database):
Line 69: Line 64:
The "-h" option is for the hostname, which is "mysql" in our case. (The full hostname is `mysql.hcoop.net`.) The "-h" option is for the hostname, which is `mysql.hcoop.net` in our case.
Line 71: Line 66:
mysqldump --skip-add-drop-table -p USER_DATABASE -h mysql> mydatabase.sql mysqldump --single-transaction --set-gtid-purged=OFF -p USER_DATABASE -h mysql> mydatabase.sql
Line 74: Line 69:
`--skip-add-drop-table` is important since by default no tables are granted the `DROP` command.
Line 81: Line 75:
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:
Line 83: Line 76:
{{{
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.
Line 91: Line 78:
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: 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, or you need to regain full database permissions, you can run this command to update the permissions:

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

We are running Percona MySQL 5.6

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

Although you could drop a database using the mysql shell (using "DROP DATABASE..."), you should avoid doing so as it will leave stale grants behind and dbtool ensures everything is cleaned up appropriately.

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.hcoop.net. 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.hcoop.net 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 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.hcoop.net in our case.

mysqldump --single-transaction --set-gtid-purged=OFF -p USER_DATABASE -h mysql> mydatabase.sql
scp ./mydatabase.sql USER@ssh.hcoop.net:~/

Finally, import the database on the shell server:

mysql -h mysql -p USER_DATABASE < ~/mydatabase.sql

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, or you need to regain full database permissions, you can run this command to update the permissions:

dbtool mysql grant DATABASE

PostgreSQL

The following instructions are for PostgreSQL 9.1, supported by shelob and marsh. Postgres 9.1 listens on port 5433 instead of 5432, as an artifact of when we supported 8.1 and 9.1 simultaneously. You will therefore need to supply the port when connecting to the database.

Create an Account

To create a PostgreSQL user account, enter the following command on ssh.hcoop.net:

dbtool postgres-9 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-9 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-9 createdb DATABASE UTF8

Delete a Database

Delete a database with the following command:

dbtool postgres-9 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. Currently version 8.1 is running on port 5432 and version 9.1 is running on port 5433. 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 -p PORT 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

We currently are not enforcing any quotas on database volumes because the amount of data stored is so small, and we've never had issues with users filling up file systems. This may change in the future, but would be like all other quota requests, only used to prevent accidental overcommitting of storage.

Database Backups

All databases are backed up once per day, and retained for seven days. Backups are not directly accessible by members at this time, but if you need a copy of one of your database backups open a Bugzilla ticket under the SQL DBs component, making sure to note which date you want a backup from, and an admin will copy the database to a secure directory in your afs home directory.

MySQL backups are stored as sql dumps, and PostgreSQL backups are stored in the native PostgreSQL dump format.


CategoryMemberManual

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