Biowulf High Performance Computing at the NIH
MySQL

MySQL is a general-purpose RDBMS, and is widely used as a backend for many applications. On Biowulf, users can either request a database on the mysql host biobase.nih.gov, or run a temporary Mysql instance on a node, or run an occasional Mysql instance pointing to a permanent location in the user's data area. These options are detailed below.

Choose your modality
Modality Pros Cons
biobase.nih.gov
  • always available
  • accessible within NIH network
  • most reliable data storage
  • no root access
  • strict limits on databases and use
  • controlled and restricted by HPC Staff
local_mysql
  • easy to create and manage
  • full root access
  • full control over administration
  • fastest and most reliable access
  • must be started within an HPC job
  • not accessible outside cluster
  • temporary instance on /lscratch
  • data must be archived between jobs
local_mysql --basedir
  • easy to create and manage
  • full root access
  • full control over administration
  • no need to archive data
  • must be started within an HPC job
  • not accessible outside cluster
  • data stability not 100% reliable
  • access speed affected by network slowdowns
  • InnoDB engine not available
Mysql database on biobase.nih.gov

The mysql host biobase.nih.gov is available for user accounts. This server is available only within the NIH network, and is intended only for use with applications run on HPC @ NIH hosts. To obtain a mysql account on biobase.nih.gov, please contact staff@hpc.nih.gov.

One of the primary purposes for biobase.nih.gov is hosting the backend databases for our local mirror of the UCSC Genome Browser. Users can access the local databases using a special read-only account. Please contact staff@hpc.nih.gov for details.

Using biobase from the cluster

Because nodes in the cluster are sequestered to an internal network, the host 'biobase.nih.gov' should not be used as the mysql host. Instead, use 'biobase':

[node1]$ mysql -u user -p -h biobase
Temporary Mysql instance

Once a mysql module has been loaded, the command local_mysql allows a mysql instance to be started on a node. The local_mysql has four sub-commands:

Here is an example of how to start a mysql server on an interactive node, using the dynamically-allocated local /lscratch disk as the root directory.

Start interactive session

In this example, we allocate 10 GB of local scratch space for the session. See https://hpc.nih.gov/docs/userguide.html#local for more information about using /lscratch. See https://hpc.nih.gov/docs/userguide.html#int for more information on allocating interactive sessions. Job and node ids have been changed to protect the innocent.

[biowulf]$ sinteractive --mem=10g --gres=lscratch:10
salloc.exe: Pending job allocation 123456789
salloc.exe: job 123456789 queued and waiting for resources
salloc.exe: job 123456789 has been allocated resources
salloc.exe: Granted job allocation 123456789
salloc.exe: Waiting for resource configuration
salloc.exe: Nodes node1 are ready for job

Initialize and start a mysql instance

Once the interactive session is ready, load the mysql module of choice, initialize and start the mysql instance.

By default, local_mysql will set the root password of the instance to mysql123. This can be changed using the option --password.

[node1]$ module load mysql
[node1]$ local_mysql create

The default engine is MyISAM. This can be changed by editing my.cnf prior to starting the server:

[node1]$ cat /lscratch/$SLURM_JOB_ID/mysql/my.cnf
...
default_storage_engine      = MyISAM
default_tmp_storage_engine  = MyISAM
...
[node1]$ vi /lscratch/$SLURM_JOB_ID/mysql/my.cnf
[node1]$ cat /lscratch/$SLURM_JOB_ID/mysql/my.cnf
...
default_storage_engine      = InnoDB
default_tmp_storage_engine  = InnoDB
...

Now start up the server:

[node1]$ local_mysql start
MySQL is running now
You can now log in using 'mysql -u root -p'mysql123' --socket=/lscratch/123456789/mysql/mysql.sock'

By default, the initialization procedure creates a new directory under /lscratch (/lscratch/$SLURM_JOB_ID/mysql) and fills it with files and directories:

[node1]$ ls -F /lscratch/123456789/mysql
bin/  data/  my.cnf  mysql.err  mysql.pid  mysql.sock  share/  support-files/  tmp/

Log in do a few things

Once the mysql instance is up and running, you can log in using either:

By default, local_mysql will set the port to 55555. This is different than the standard mysql port of 3306 and therefore must be explicitly specified either by using the socket file or with the --port option. The port can be changed using the option --port.

[node1]$ mysql -u root -p'mysql123' --socket=/lscratch/123456789/mysql/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.30 Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> CREATE USER me IDENTIFIED BY 'pass123';
mysql> CREATE DATABASE `my_db`;
mysql> GRANT ALL ON `my_db`.* TO me;
mysql> USE my_db;
mysql> CREATE TABLE my_table (x int, y int, z int);
mysql> DESC my_table;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x     | int(11) | YES  |     | NULL    |       |
| y     | int(11) | YES  |     | NULL    |       |
| z     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
mysql> exit
[node1]$

Log in from another machine

While the interactive session is running, the mysql instance will be accessible from any node within the Biowulf cluster using the --host and --port options.

[node2]$ mysql -u me -p'pass123' --host=node1 --port=55555

By default the root account initially created does not allow access from outside the original host where mysql was initialized. Either modify the root account (not good idea) or create an alternate account (better idea) for access from other nodes.

Stop mysql prior to archiving and exiting the interactive session

[node1]$ local_mysql stop

MySQL is stopped!

Archive the contents

The contents of the temporary server instance can be archived into a single tarball for restarting later and on a different node. This is the recommended way of keeping instances long-term.

[node1]$ local_mysql archive --archivefile=/data/$USER/save.tgz
[node1]$ ls -l /data/$USER/save.tgz
-rw-r--r-- 1 user user 1371882 Jul  1 11:35 /data/user/save.tgz

Exit the interactive session

Lastly, exit the interactive session. The contents of /lscratch will be destroyed!

[node1]$ exit
salloc.exe: Relinquishing job allocation 123456789
[biowulf]$

Start a new interactive session, restore the contents, and start mysql again

[biowulf]$ sinteractive --mem=10g --gres=lscratch:10
salloc.exe: Pending job allocation 234567891
... 
[node3]$ module load mysql
[node3]$ local_mysql restore --archivefile=/data/$USER/save.tgz
[node3]$ local_mysql start

MySQL is running now
You can now log in using 'mysql -u root -p'mysql123' --socket=/lscratch/234567891/mysql/mysql.sock'

Mysql database with permanent location in /data

If the mysql files are required permanently, such that the server could be brought up again and again, and archiving is not a possibility (see above), then don't take the default --basedir value. Instead, point the --basedir option to a directory in a /data area.

By default mysql files are written to /lscratch. This can be changed using the option --basedir. local_mysql will attempt to create the directory path given by --basedir, and will complain if the path already exists.

[node1]$ module load mysql
[node1]$ local_mysql --basedir /data/user/mysql start
...
[node1]$ mysql -u root -p'mysql123' --host=$SLURM_NODELIST --port=55555
mysql>

99% of all MySQL administrators install the server on a locally available hard drive or SSD. Thus, /lscratch is ideal. However, the use of a remotely mounted shared filesystem for MySQL storage is non-ideal and may result in errors. For example, it is not possible to use the InnoDB engine on your /data directory and results in errors when tables are created.

And as always be sure to stop the server when you're done using it:

[node1]$ local_mysql --basedir /data/user/mysql stop

Restart the mysql server

Then later on, the server can be started up with the original database files:

[biowulf]$ sinteractive
...
[node1]$ module load mysql
[node1]$ local_mysql --basedir /data/user/mysql start

For more information about how to use local_mysql, type

local_mysql --help