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};"