Biowulf High Performance Computing at the NIH
MySQL

MySQL is a general-purpose RDBMS, and is widely used as a backend for many applications.

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':

[node]$ mysql -u user -p -h biobase
local_mysql script

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 --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 node 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.

[node]$ module load mysql
[node]$ local_mysql create
[node]$ 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:

[node]$ 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.

[node]$ 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.5.54 Source distribution

Copyright (c) 2000, 2014, 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
[node]$

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.

[biowulf]$ mysql -u me -p'pass123' --host=node --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 and exit the interactive session

Make sure to stop mysql prior to exiting the interactive session.

[node]$ local_mysql stop

MySQL is stopped!
[node]$ exit
salloc.exe: Relinquishing job allocation 123456789
[biowulf]$

Create mysql with a permanent location

If the mysql files are required permanently, such that the server could be brought up again and again, 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.

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

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

[node]$ 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
...
[node]$ module load mysql
[node]$ local_mysql --basedir /data/user/mysql start

For more information about how to use local_mysql, type

local_mysql --help