welcome: please sign in

The following 341 words could not be found in the dictionary of 7 words (including 7 LocalSpellingWords) and are highlighted below:
about   above   access   accessible   Accessing   accidental   Account   account   addition   Additionally   adduser   admin   afs   all   All   already   also   alternate   Although   amount   an   analyze   and   another   any   applications   apply   appropriately   are   artifact   as   asked   aspects   at   available   avoid   backed   backup   Backups   backups   be   because   behind   Bs   bug   Bugzilla   bugzilla   but   by   called   can   cannot   case   Category   cgi   change   Changing   chapter   cleaned   com   command   component   connecting   Contents   Coop   copy   could   Create   create   created   createdb   creating   Creating   current   Currently   currently   custom   data   Database   database   Databases   databases   date   day   days   dbtool   Default   defaults   Delete   deleting   describes   desired   dev   different   directly   directory   do   doc   docs   doing   drop   dropdb   dump   dumps   easily   easy   en   encoding   enforcing   ensures   enter   everything   F8   file   filling   Finally   Follow   following   For   for   forgotten   format   from   full   future   gave   grant   granted   grants   gtid   had   handle   has   have   hcoop   home   host   hostname   how   html   http   https   if   If   import   in   In   index   input   instead   instructions   interactive   interface   into   Introduction   is   issues   it   It   know   learn   leave   Let   like   listens   ll   log   logging   look   making   managing   manipulate   manual   Manual   many   marsh   may   Member   members   model   more   My   mydatabase   mysql   mysqldump   name   named   native   need   net   never   new   not   note   Of   of   On   on   once   one   only   onto   open   option   or   org   original   originally   other   our   overcommitting   package   passwd   password   Password   per   Percona   percona   permissions   Permissions   phpmyadmin   Please   policy   port   ports   Postgre   postgres   Postgres   postgresql   prevent   probably   prompted   provided   psql   purged   purposes   put   quite   quota   quotas   re   really   reasons   refman   regain   Remember   remember   Replace   replace   requests   required   Resetting   retained   review   Run   run   running   safeguard   same   scp   secure   security   server   servers   set   seven   shell   shelob   should   simultaneously   since   single   small   so   software   Sometimes   specify   sql   ssh   stale   stated   storage   stored   supply   supported   sure   system   systems   Table   take   task   text   than   that   The   the   their   then   There   therefore   This   this   ticket   time   to   To   tool   transaction   Transferring   under   up   update   us   Use   use   used   user   username   users   using   utilizes   ve   version   very   volumes   want   We   we   web   when   where   which   will   wish   with   would   www   You   you   your   Your  

Clear message
Edit

MemberManual / Databases

This is the chapter of the MemberManual that describes how to use PostgreSQL and MySQL databases on HCoop servers.

Contents

  1. Introduction
  2. MySQL
    1. Create an Account
    2. Create a Database
    3. Delete a Database
    4. Changing Your Password
    5. Accessing Databases
    6. Transferring Databases
    7. Resetting Permissions
  3. PostgreSQL
    1. Create an Account
    2. Create a Database
    3. Delete a Database
    4. Accessing Databases
    5. Transferring Databases
  4. Database quotas
  5. Database Backups

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)