502021-11-06 19:46:06ClintonEbadimysql-fixperms is dead and members can DROP tables at will, update docs to remove workarounds for not having DROP by default492020-01-02 18:16:3498.26.10.3remove outdated references to bog482019-03-31 19:31:06ClintonEbadidatabases are backed up, note retention and how members can gain access to a backup if they need one472018-10-14 23:14:44ClintonEbadidbms postgres-9.1 is now just postgres-9, 8.1 was disabled quite a while ago462017-01-29 18:14:23ClintonEbadiswitched on over to percona 5.6 from dotdeb 5.5452015-03-20 06:28:17ClintonEbadiwhat is a mire442015-03-20 06:19:47ClintonEbadilink to current manuals432015-03-20 02:22:44tor-exit.server7.tvdw.euAdd information about what is running on which port.422015-01-17 01:33:47ClintonEbadiseems like a reasonable place to document which version of mysql we are using412013-01-14 08:44:37ClintonEbadicat402012-12-25 21:47:35ClintonEbadicorrection392012-12-10 02:42:01ClintonEbadiUpdate for 9.1 / We haven't had quotas in years!382009-09-09 20:08:33ClintonEbadiRevert to revision 36.372009-09-09 18:20:3393.174.94.592PhUof design and policy problems that require joint attention to noise, air quality and climate, [[http://technetnepal.net/members/malaysian_2D00_airline/default.aspx|malaysian airline]]safe landings362008-07-07 04:28:03localhostconverted to 1.6 markup352008-05-11 14:18:0378.134.204.64342008-04-30 14:43:09AdamChlipalamire -> ssh332008-04-20 23:52:02DavorOcelic322008-04-09 14:44:17AdamChlipalaEncodings for Postgres312008-03-16 16:07:58ABTS-NCR-Dynamic-244.69.162.122.airtelbroadband.in302008-02-19 22:29:44MichaelOlsonUse db password for logging into phpmyadmin292008-02-14 06:58:53MichaelOlsonMention phpmyadmin282008-02-09 07:32:00MichaelOlson272007-12-18 00:35:18DavorOcelic262007-12-15 19:10:09AdamChlipaladbtool grant252007-12-11 19:24:23RyanMikulovskyStart a PostgreSQL transferring database section242007-12-11 18:25:24RyanMikulovsky232007-12-11 18:20:10RyanMikulovsky222007-12-11 18:18:20RyanMikulovskyadd --skip-add-drop-table to mysql dump command212007-12-09 19:25:50RyanMikulovskymove mysql-fixperms to bottom202007-12-09 19:13:52c-24-16-159-88.hsd1.mn.comcast.net192007-12-08 21:53:23RyanMikulovsky182007-12-08 21:52:05RyanMikulovskymysql-fixperms172007-12-07 04:14:07RyanMikulovsky162007-12-07 04:13:14RyanMikulovskystyle.. and.. how does mysql-fixperms work?152007-12-07 04:08:23RyanMikulovskyadd instructions about mysql database transfer142007-11-01 19:33:45MichaelOlsonGive quick explanation of what dbtool is132007-10-27 19:07:31AdamChlipalaRemove PostgreSQL password change section; dbtool doesn't support passwords for Postgres.122007-10-27 18:19:52MichaelOlsonAdd change password section for postgres112007-10-27 18:18:21MichaelOlsonSplit create/delete into separate sections102007-10-27 18:15:10MichaelOlsonPut example commands on their own line for readability92007-10-27 17:01:52AdamChlipalaTypo82007-10-27 16:48:00AdamChlipalaPromote all headings to avoid silly small text72007-10-27 05:35:47RyanMikulovskyuniformity correction62007-10-27 05:32:48RyanMikulovskytypo52007-10-27 05:31:57RyanMikulovskymore more clarifications42007-10-27 05:29:49RyanMikulovskymore clarifications32007-10-27 05:26:20RyanMikulovskyget rid of some redundancy, reduce sections a bit... clarify a little22007-10-27 05:17:03RyanMikulovskyadd content... maybe I've created too many sections?12007-10-26 14:58:32MichaelOlsonInitial contents; needs someone else to write itThis is the chapter of the MemberManual that describes how to use PostgreSQL and MySQL databases on HCoop servers.

We use a custom tool called dbtool to handle many of the aspects of creating and deleting databases for MySQL and PostgreSQL.

We are running Percona MySQL 5.6

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: You will be asked for a password. The user created will be the same as your log-in username.

dbtool is also used to create new MySQL databases. Replace DATABASE with your desired database name: The database created will be USER_DATABASE (where USER is your username and DATABASE is the name you provided dbtool).

Delete a database with the following: 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.

If you need to change your MySQL password for security purposes or you have forgotten it, you may do so with this command: You will be prompted to input a new password.

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: To learn more about the MySQL shell, review the MySQL manual. There is a web interface available for managing your databases at . Use your database password when logging into it.

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. mydatabase.sql scp ./mydatabase.sql USER@ssh.hcoop.net:~/]]>Finally, import the database on the shell server:

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:

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.

To create a PostgreSQL user account, enter the following command on ssh.hcoop.net: 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.

Additionally, dbtool is used to create new Postgres databases. Replace DATABASE with your desired database name: 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:

Delete a database with the following command: For security reasons, you cannot drop a database using the psql shell.

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: To learn more about the psql shell, take a look at the PostgreSQL manual.

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.

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.

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