Running MySQL

Overview

Most software which utilizes databases like MySQL, MariaDB, and PostgreSQL expect to have a persistent always-online database server available to use. Kamiak does not provide server hosting and as such cannot host these persistent database instances. In order to run software on Kamiak which needs a database server you have two options:

  1. Host a database server elsewhere and access it from Kamiak
  2. Run your own database server as a compute job on Kamiak

Option 1 is the most straightforward approach but requires you to acquire server hosting, build your own database server/instance, and maintain it.  Off-site or “cloud” services can be utilized for this purpose but may not provide adequate performance for intense workloads.  Virtual server hosting is available within WSU from ITS and CAHNRS.  ITS also provides physical server hosting in the same datacenter Kamiak resides in.  In any case you are strongly suggested to contact Kamiak support before moving forward with any purchase to ensure it will be able to utilized by compute jobs on Kamiak.

Option 2 is a more complex approach.  In order to do this you will need to develop a workflow that includes starting a database server within your job, running your software which utilizes the server, then shutting down the database server when the job ends.  You must handle your data safely. For example, flush transactions and gracefully stop the database server when the job unexpectedly ends by being canceled (time limit, preemption, etc).  You will also need to be careful not to under-provision or over-provision resources for your database job as much as is feasible (i.e. limit the threads/cores the server can utilize to match what the job requests).

The following job scripts should be considered a proof-of-concept example of running a database instance within a compute job and not be used with real data as-is.

Single-node Database Job – MySQL

The simplest way of running a database server in a job is to run it on a single compute node and run your computation within the very same job. In this example we will create a server instance of MySQL using the binaries available on Kamiak. You may wish to compile/install your own build of a newer version.

To start with, let’s define where we will store our database. This is where we will store not just the database itself but also logs, sockets, and PID files.  Inside of it we’ll then create data and temp directories:

export db_base_path=/path/to/your/db
mkdir $db_base_path/data
mkdir $db_base_path/temp

Next we will prepare the data directory:

mysql_install_db --datadir="$db_base_path/data"

Create a my.cnf file using your preferred editor:

nano $db_base_path/my.cnf

Add the following contents:

[client]
user=root
socket=/path/to/your/db/mysql.sock

[mysqld]
datadir=/path/to/your/db/data
socket=/path/to/your/db/mysql.sock
tmpdir=/path/to/your/db/temp
log-error=/path/to/your/db/mysql.log
pid-file=/path/to/your/db/mysql.pid
skip-networking

Protect it:

chmod 600 $db_base_path/my.cnf

Now we can start the database server and stop it as a test:

mysqld_safe --defaults-file="$db_base_path/my.cnf" --no-auto-restart
mysqladmin --defaults-file="$db_base_path/my.cnf" shutdown

At this point our new database instance is ready to go. We’ll need to use the defaults-file option when using any MySQL commands. Run the same mysqld_safe command within a job to start the server. Below is an example job script showing exactly that:

#!/bin/bash
#SBATCH -t 0-01:00  # Runtime in D-HH:MM


echo "Starting MySQL on host $HOSTNAME"


db_base_path="/path/to/your/db"


function stop_mysql_exit {
    if [ -f "$db_base_path/mysql.pid" ];then
        echo "Stopping MySQL"
        mysqladmin --defaults-file="$db_base_path/my.cnf" shutdown
    fi

    exit
}


# Call our stop_mysql_exit function when we exit. Even if Slurm cancels the job this should still run.
# If we were using csh or anything that is not bash we would need to trap SIGINT and SIGTERM and
# also add a call to stop_mysql_exit at the end of the script.
trap 'stop_mysql_exit' EXIT


# Start the server
mysqld_safe --defaults-file="$db_base_path/my.cnf" --no-auto-restart


# Wait for the server to start (this could loop forever if mysqld failed to start)
while true; do
    if mysqladmin --defaults-file="$db_base_path/my.cnf" ping 2>/dev/null; then
        break
    fi

    sleep 1
done


# Just an example, your work would happen here
cat <<'EOF' | mysql --defaults-file="$db_base_path/my.cnf"
show databases;
use test;
show tables;
EOF


echo "Completed MySQL on host $HOSTNAME"

Which gives this output:

Starting MySQL on host cn85
170723 16:42:17 mysqld_safe Logging to '/path/to/your/db/mysql.log'.
170723 16:42:17 mysqld_safe Starting mysqld daemon with databases from /path/to/your/db/data
mysqld is alive
Database
information_schema
mysql
performance_schema
test
Completed MySQL on host cn85
Stopping MySQL