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:
- Host a database server elsewhere and access it from Kamiak
- 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