A lightweight MySQL sandbox script

For a long time I’ve been maintaining a set of scripts inspired by Giuseppe Maxia’s MySQL Sandbox, which is a Swiss Army Knife for starting and stopping server instances for jobs such as testing, development, trying out a new version, and so on. My scripts are unpublished, until now. I’ve just kept them in my Dropbox’s bin folder, which I add to my $PATH.

It’s not worth explaining why I use my own scripts, except for saying that I keep dozens or even more MySQL versions unpacked in my home directory at any given time, and I find it a little easier to use these lightweight scripts than the more fully-featured MySQL Sandbox tools.

Usage assumes some conventions are followed. I “install” each version of the server by downloading the generic tarball. Then I unpack it and move it to $HOME/mysql/servers/VERSION, where VERSION is something like 5.5.27. If it is a Percona Server or MariaDB version, I make VERSION include the ‘-rel’ suffix. Then I clean out all of the extra directories and files, so that the bin directory basically includes only the mysqld executable, the share directory has little more than the English message files, and so on. I usually don’t need things like the test files, and tools such as the mysql command-line client vary little between server versions in most cases, so I just use the system’s installed versions or place a single copy of these into my $PATH. With this many versions of the server unpacked, I want to save disk space. I use the mysql_install_db script to create a data directory inside the unpacked server directory, and I’m ready to go.

To start a server, all I have to do is say ms VERSION and it boots directly in my terminal. To stop it, I simply send it a kill signal.

To connect to it and execute commands, I use the mc command. This will find a running server and then pass along the command-line arguments; to connect to a specific server I use the -P option with a port number. The scripts choose a port based on the server’s version number, such as 5527 for version 5.5.27. Socket files are a hassle in a scenario like this; ports are easier for me to use. Therefore I connect over TCP only, and I put protocol=tcp in the client section in my $HOME/.my.cnf file.

To sum up: unpack and trim down the server versions, naming them according to a naming convention, set up a data directory, set up $HOME/.my.cnf, and then create two programs in the $PATH. Here is the ms script:


which="$1"; shift
if [ -z "${which}" ]; then
   echo "Specify a server"

cd ~/mysql/server

server="$(ls | grep "${which}" | head -n1)"
if [ ! -d "${server}" ]; then
   echo "No such server ${which}"
cd "${server}"

MSG=$(find . -name english);
BIN=$(find . -name mysqld);
PLUG=$(find . -name ha_innodb_plugin.so.*);
if [ "$PLUG" ]; then
   PLUG=$(basename "$PLUG")

PORT=$(pwd | awk -F/ '{print $NF}' | sed -e 's/\.\([1-9]\)$/.0\1/' | cut -d- -f1 | tr -d .);
if [[ "$BASEDIR" = *rel* ]]; then
   PORT=$((PORT + 10000));

# write out the my.cnf
cat > my.cnf <<EOF
user     = root
port     = $PORT
protocol = tcp
socket   = $BASEDIR/data/mysql.sock
prompt   = "$PORT> "
datadir  = $BASEDIR/data/
port     = $PORT
server_id= $PORT
socket   = $BASEDIR/data/mysql.sock
language = $MSG
basedir  = $BASEDIR
log-bin  = mysql-bin
relay-log = relay-bin
table_cache = 100

echo "$PORT" > "/tmp/mysql-$PORT"
$BIN --defaults-file=my.cnf --pid-file=/tmp/mysql.$PORT.pid "$@"
rm "/tmp/mysql-$PORT"

And here is the mc command, which I’ve tweaked a little to run on Mac OSX as well as Linux:

# Discover which server is currently running, and use it
case $(uname) in
   port="$(netstat -antp 2>/dev/null | awk '/mysqld/{print $4}' | cut -d: -f2 | head -n1)"
   mysql --port=$port "$@"
   port=$(ls /tmp/mysql-* | cut -d- -f2 | sort -n | head -n1)
   mysql --port=$port "$@"

This is pretty basic, and certainly not something I’d promote as generically useful or ready for the masses, but perhaps it will help someone else who has the (unusual?) usage requirements I have.

See Also

I'm Baron Schwartz, the founder and CEO of VividCortex. I am the author of High Performance MySQL and lots of open-source software for performance analysis, monitoring, and system administration. I contribute to various database communities such as Oracle, PostgreSQL, Redis and MongoDB. More about me.