Ephemeral databases

A database server is provided as part of the cluster to enable users to run jobs requiring databases or to improve jobs with high I/O by using a database instead of flat files. To provide the widest possible compatibility, both MySQL (MariaDB) and PostgreSQL are available.

In order to provide an appropriately high-speed service, the database server uses local SSDs. This means that disk space is limited so databases must only exist for the lifetime of the job.

Databases are not backed up

Ephemeral databases stored on the cluster database server are not backed up. To prevent potential data loss, we strongly recommend that users take regular backups of their database schema and data, usually in the form of a database dump. Please contact us if you require assistance with setting up database backups.

Requesting a database

To get access to an Ephemeral Database, please fill in and submit our database request form (QMUL users only) with details of your requirements including:

• MySQL or PostgreSQL
• Estimated size of database
• Intended usage (job scripts)
• Likely duration of jobs and project

Usage

MySQL

Once your request has been granted you will receive the following details:

mysql -u <username> -p <database> -h db1
Password: <password>
mysql -u <readonly_username> -p <database> -h db1
Password: <readonly_password>

These can then be used to connect to the database from any cluster node:

$ mysql -u abc123_example -D abc123_example -h db1
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13564
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [abc123_example]>  SHOW TABLES;
Empty set (0.00 sec)

MariaDB [abc123_example]>

MySQL user configuration

A .my.cnf file can be used to save the connection details for ease of use:

[client]
user=<username>
password=<password>
host=<hostname>
database=<database>

After creating this file you should use chmod to ensure only you can read it:

chmod 0600 ~/.my.cnf

Once a .my.cnf file is in place details are automatically read from the file:

$ cat ~/.my.cnf
[client]
user=abc123_example
password=examplepassword
host=db1
database=abc123_example

$ mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13576
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [abc123_example]>

Multiple databases can be added as additional sections and selected via the --defaults-group-suffix option e.g.

$ cat ~/.my.cnf
[client]
host=db1

[client_example]
user=abc123_example
password=examplepassword
database=abc123_example

[client_other]
user=abc123_other
password=otherpassword
database=abc123_other

$ mysql --defaults-group-suffix=_example
MariaDB [abc123_example]>
$ mysql --defaults-group-suffix=_other
MariaDB [abc123_other]>

When using multiple databases you should not specify per database settings in the [client] section.

Further information on .my.cnf files is available in the MySQL Documentation. with details on multiple sections available here.

Examples

The following examples expect a .my.cnf file to be in place, either with a default [client] section or with specific sections [client_example].

Loading into MySQL

Data can be loaded into MySQL via the mysql command:

# Single DB setup
mysql < dump.sql
# Multi DB setup
mysql --defaults-group-suffix=_example < dump.sql

Exporting from MySQL

Databases can be exported from MySQL using the mysqldump tool:

$ # Single DB setup
$ mysqldump <database_name> > dump.sql
Warning: mysqldump: ignoring option '--databases' due to invalid value 'database_name'
$ # Multi DB setup
$ mysqldump --defaults-group-suffix=_example <database_name> > dump.sql
Warning: mysqldump: ignoring option '--databases' due to invalid value 'database_name'

Warning: mysqldump: ignoring option '--databases' due to invalid value

This message can be safely ignored, see this MySQL bug for more details.

Clearing from MySQL

Running the following SQL commands will clear the database:

SELECT @sql:=concat('DROP TABLE ', group_concat(table_name)) FROM
       information_schema.tables WHERE TABLE_SCHEMA=(SELECT database());

PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;

DROP DATABASE

Using DROP DATABASE may result in issues connecting so should be avoided.

PostgreSQL

Once your request has been granted you will receive the following details:

psql -U <username> -d <database> -h db1
Password: <password>
psql -U <readonly_username> -d <database> -h db1
Password: <readonly_password>

These can then be used to connect to the database:

$ psql -U abc123_example -d abc123_example -h db1
Password for user abc123_example:
psql (9.5.7)
Type "help" for help.

abc123_example=> \dt
No relations found.
abc123_example=>

PostgreSQL user configuration

A .pgpass file can be used to save the connection details for ease of use:

<hostname>:<port>:<database>:<username>:<password>

After creating this file you should use chmod to ensure only you can read it:

chmod 0600 ~/.pgpass

Once a .pgpass file is in place passwords are automatically read from the file:

$ cat ~/.pgpass
db1:5432:abc123_example:abc123_example:examplepassword

$ psql -U abc123_example -d abc123_example -h db1
psql (9.5.7)
Type "help" for help.

abc123_example=>

Multiple databases can be added as additional lines and the password will be correctly selected when connecting e.g.

$ cat ~/.pgpass
db1:5432:abc123_example:abc123_example:examplepassword
db1:5432:abc123_other:abc123_other:otherpassword
db1:5432:abc123_different:abc123_different:differentpassword

$ psql -U abc123_example -d abc123_example -h db1
abc123_example=>
$ psql -U abc123_other -d abc123_other -h db1
abc123_other=>

Further information on .pgpass files is available in the PostgreSQL Documentation.

Loading into PostgreSQL

plpgsql

You make see this error: ERROR: must be owner of extension plpgsql This can be safely ignored.

ERROR: relation already exists

This error means the database is not empty and the data being loaded may be duplicated. To ensure data consistency the database should be cleared first.

PostgreSQL can either use plain SQL dumps or a custom format with built in compression. Additional information is available in the PostgreSQL documentation.

Loading plain SQL

Data can be loaded into PostgreSQL via the psql command:

psql -U <username> -d <database> -h db1 -f db.sql

Loading compressed PostgreSQL custom format

Custom format data can be loaded into PostgreSQL via the pg_restore command:

pg_restore -U <username> -d <database> -h db1 db.dump

Exporting from PostgreSQL

Exporting plain SQL

Databases can be exported from PostgreSQL using the pg_dump tool:

pg_dump -U <username> <database> -h db1 --no-privileges > db.sql

Exporting compressed PostgreSQL custom format

Databases can be exported as a custom format from PostgreSQL using the pg_dump tool and the -Fc option:

pg_dump -U <username> <database> -h db1 --no-privileges -Fc > db.dump

Clearing from PostgreSQL

psql -U <username> <database> -h db1 -c "DROP OWNED BY <username>;"

Example job scripts

MySQL basic job

This is a basic job script that loads a MySQL database, executes some code, exports the database and cleans up the database.

#!/bin/bash
#$ -cwd
#$ -j y
#$ -pe smp 1
#$ -l h_rt=1:0:0
#$ -l h_vmem=4G

DB_NAME=abc123_example

# Load DB
mysql --defaults-group-suffix=_example < input_db.sql

# Run job
./code

# Export DB
mysqldump --defaults-group-suffix=_example ${DB_NAME} > output_db.sql

# Clean up DB
mysql --defaults-group-suffix=_example << EOF
SELECT @sql:=concat('DROP TABLE ', group_concat(table_name)) FROM
information_schema.tables WHERE TABLE_SCHEMA=(SELECT database());
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
EOF

PostgreSQL basic job

This is a basic job script that loads a PostgreSQL database, executes some code, exports the database and cleans up the database.

#!/bin/bash
#$ -cwd
#$ -j y
#$ -pe smp 1
#$ -l h_rt=1:0:0
#$ -l h_vmem=4G

DB_USER=abc123_example
DB_NAME=abc123_example
DB_HOST=db1

# Load DB
psql -U ${DB_USER} ${DB_NAME} -h ${DB_HOST} -f input_db.sql

# Run job
./code

# Export DB
pg_dump -U ${DB_USER} ${DB_NAME} -h ${DB_HOST} --no-privileges > output_db.sql

# Clean up DB
psql -U ${DB_USER} ${DB_NAME} -h ${DB_HOST} -c "DROP OWNED BY ${DB_USER};"

References

MySQL references

• MariaDB website
• MariaDB Documentation
• MySQL Documentation

PostgreSQL references

• PostgreSQL website
• Documentation