MySQL at NERSC

MySQL software is installed at NERSC in the mysql module. Client and server software is supplied. NERSC does not host an open MySQL server for its users.

Using the MySQL client

Load the mysql module to put the MySQL binaries in your shell search path. MySQL client utilities can connect outbound to a properly configured remote MySQL server from both interactive and compute nodes.

% module load mysql
% mysql -h remote_mysql_server_hostname -u mysql_username

Using the MySQL API

Your code can connect to a MySQL database server by embedding MySQL API calls in your code. After you load the mysql module, these environment variables are defined:

	MYSQL_INCDIR         path to MySQL header files 
	MYSQL_LIBDIR	     path to MySQL libraries

Using a local MySQL server

You may start a MySQL server as part of your job at NERSC. This server can be accessed while your job is running. This is recommended only for those users who have significant experience using and administering MySQL databases. The procedure described below assumes familiarity with MySQL.

Please do not start a server without adequate security. Doing so will jeopardize the integrity of your data. You do not want to allow other NERSC user to connect to your running server. Any attempt to connect to the server from outside NERSC will fail because the incoming network ports are blocked.

How to set up and use a local MySQL server

The following procedure will set up a MySQL database and allow it to be accessed during a run.

Set up the database

1. Decide where the database files will be stored in a shared file system. A good place might be $SCRATCH/mysql.
2. Create a configuration file, a good name for it is $HOME/mysql/my.cnf. A sample my.cnf might look like (replace myusername with your username):

   [mysqld]
   datadir=/scratch/scratchdirs/myusername/mysql
   socket=/tmp/mysql.myusername.sock
   port=6406
   
   [mysql]
   socket=/tmp/mysql.myusername.sock
   port=6406
   
   [safe_mysqld]
   pid-file=/tmp/mysqld.myusername.pid
   err-log=/scratch/scratchdirs/myusername/mysql/mysqld.log
   

   Note that the port is not the standard MySQL port (3306); if everyone tried to use 3306 on an non-dedicated node there would be port conflicts. For the same reason, don't use the port number in this example! Any unused high port (ports above 6000 are typically unusued).
3. Initialize the database with

   % mysql_install_db --defaults-file=$HOME/mysql/my.cnf 
   

   This initialization step only needs to be done once - to set up the base database files.

Start the server

1. If working on Jacquard, type qsub -I to start an interactive PBS job on a compute node. This will put you on a dedicated node and you will not compete with other users who might also be using MySQL.
2. Start the server with

   % mysqld_safe --defaults-file=$HOME/mysql/my.cnf &
   

Start a client and configure the database

1. You should now be able to use MySQL client utilities; start with something like:

   % mysql --defaults-file=$HOME/mysql/my.cnf -u root
   

2. Set the root password and remove any MySQL users with blank passwords.
3. Grant access to a user or users at 128.55.0.0/255.255.0.0. You might also need to grant access to users at 10.0.0.0/255.0.0.0.
4. Create databases, tables, data, etc.; or import your database.

Stop the server

1. When you are finished, stop the database with:

   kill `cat /tmp/mysqld.myusername.pid`
   

   (If working on Jacquard using qsub -I, type exit to end the job.)

Using a local server in batch jobs

You can start a server in a batch script and then communicate to it from tasks running on different compute nodes. Start the server in your script; serial commands are executed by the lead task. Save the hostname in a file. Make sure you have granted server access to users on the 10.x.x.x and 128.55.x.x networks.

The following example batch (PBS) script starts the server and then runs another script in parallel so that all tasks talk to the server. (An equivalent IBM LoadLeveler script has LoadLeveler directives instead of PBS directives and uses "poe" instead of "mpirun".) .

# Sample MySQL batch script - test_mysql.pbs
# LoadLeveler scripts are similar
#PBS -V
#PBS -S /bin/tcsh
#PBS -l nodes=2:ppn=2,walltime=00:05:00
#PBS -N test 
#PBS -o mysql_test.out
#PBS -e mysql_test.err
#PBS -q debug 


module load mysql 

hostname >$HOME/mysql/mysql.server

mysqld_safe --defaults-file=$HOME/mysql/my.cnf &

sleep 10

cd $PBS_O_WORKDIR
mpirun -np 4 ./checkcx.csh

echo "Killing MySQL server..."
kill `cat /tmp/mysqld.myusername.pid`

#!/bin/csh
# checkcx.csh - simple script to test mysql



module load mysql 

set server=`cat $HOME/mysql/mysql.server`

mysql --defaults-file=$HOME/mysql/my.cnf -h $server -u myusername <<_EOS
status;
show databases;
quit
_EOS

Running on Jacquard with qsub test_mysql.pbs produces output that begins:

PBS Leader node is jaccn260

Job setup time: Mon Jan 30 16:20:12 PST 2006

Setting up security

Job startup at  Mon Jan 30 16:20:17 PST 2006

----------------------------------------------------------------

Warning: no access to tty (Bad file descriptor).
Thus no job control in this shell.
Directory: /usr/common/homes/m/myusername
[1] 5285
Starting mysqld daemon with databases from /scratch/scratchdirs/myusername/mysql
--------------
mysql  Ver 14.7 Distrib 4.1.16, for unknown-linux-gnu (x86_64) using  EditLine wrapper

Connection id:          1
Current database:
Current user:           myusername@jaccn261-m.nersc.gov
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         4.1.16
Protocol version:       10
Connection:             jaccn260 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               6406
Uptime:                 10 sec

Threads: 2  Questions: 3  Slow queries: 0  Opens: 11  Flush tables: 1  
	Open tables: 5  Queries per second avg: 0.300
--------------

Database
mysql
test
--------------

[...]