Quick DB Setups With MySQL Sandbox

Introduction

There are various reasons to set up quick "sandbox" instances of MySQL. You can use them to test different types of replication (such as master-master or various slave topologies), to test your code against different versions of MySQL, or to setup instances of MySQL on a per developer basis where each person has their own database running on a different port so they can breakdown/setup the DB easily or make schema changes without affecting other team members. A perfect tool to do all of these things easily is MySQL Sandbox.

Download the Prerequisites

To use MySQL sandbox effectively you need two things, the MySQL sandbox tool itself, and a MySQL tarball that the sandbox script can use to setup instances. You can download the latest MySQL sandbox version like so (2.0.12 as of this writing):

wget http://launchpad.net/mysql-sandbox/mysql-sandbox-2.0/2.0/+download/mysql_sandbox_2.0.12.tar.gz

You can download the MySQL tarball from any MySQL mirror. It's important to get the non RPM, Intel C/C++ compiled, glibc-2.3 version (look for a tar.gz file with "icc" in the filename) for example if you want version 5.1:

wget ftp://mirror.anl.gov/pub/mysql/Downloads/MySQL-5.1/mysql-5.1.30-linux-i686-icc-glibc23.tar.gz

Installing an Instance with MySQL Sandbox

First you need to extract the MySQL Sandbox tool and change directory to it:

$ tar xzvf mysql_sandbox_2.0.12.tar.gz

$ cd mysql_sandbox_2.0.12

The easiest and quickest way to create an instance is:

$ ./make_sandbox /path/to/mysql-X.X.XX-osinfo.tar.gz

where mysql-X.X.XX-osinfo.tar.gz is the MySQL tarball we just downloaded. And you're done. However, this will put the sandbox in a directory under your home directory ($HOME/sandboxes/msb_X_X_XX), which may or may not suit your purposes. It sets it up with default users, passwords, ports, and directory name. Let's fine tune things a bit.

Setting up a Custom Tuned Instance

I want to put my instance into a partition I created called /mnt/mysql_sandboxes. I've created a subdirectory in there called tarballs, which holds the MySQL tarball that we downloaded above which MySQL Sandbox will extract for setup. Since I'm installing version 5.1.30 I want to call the directory that houses the MySQL data files 5.1.30_single, but you can call it anything you like. I'll create a default user named jgoulah and a password goulah. By default it sets the port to the version number without the dots (5130 in this case) so we'll give it a custom port so that it listens on 10000 instead.

mysql_sandbox_2.0.12 $ ./make_sandbox \
/mnt/mysql_sandboxes/tarballs/mysql-5.1.30-linux-i686-icc-glibc23.tar.gz \
--upper_directory=/mnt/mysql_sandboxes/ --sandbox_directory=5.1.30_single \
--db_user=jgoulah --db_password=goulah --sandbox_port=10000

Here's the output:

unpacking /mnt/mysql_sandboxes/tarballs/mysql-5.1.30-linux-i686-icc-glibc23.tar.gz
Executing ./low_level_make_sandbox \
--basedir=/mnt/mysql_sandboxes/tarballs/5.1.30 \
--sandbox_directory=msb_5_1_30 \
--install_version=5.1 \
--sandbox_port=5130 \
--no_ver_after_name \
--upper_directory=/mnt/mysql_sandboxes/ \
--sandbox_directory=5.1.30_single \
--db_user=jgoulah \
--db_password=goulah \
--basedir=/mnt/mysql_sandboxes/tarballs/5.1.30 \
--sandbox_port=10000 \
--my_clause=log-error=msandbox.err
The MySQL Sandbox, version 2.0.12 16-Oct-2008
(C) 2006,2007,2008 Giuseppe Maxia, Sun Microsystems, Database Group
installing with the following parameters:
upper_directory = /mnt/mysql_sandboxes/
sandbox_directory = 5.1.30_single
sandbox_port = 10000
datadir_from = script
install_version = 5.1
basedir = /mnt/mysql_sandboxes/tarballs/5.1.30
my_file =
operating_system_user = jgoulah
db_user = jgoulah
db_password = goulah
my_clause = log-error=msandbox.err
prompt_prefix = mysql
prompt_body = [\h] {\u} (\d) > '
force = 0
no_ver_after_name = 1
verbose = 0
load_grants = 1
no_load_grants = 0
do you agree? ([Y],n) y
loading grants
. sandbox server started
installation options saved to current_options.conf.
To repeat this installation with the same options,
use ./low_level_make_sandbox --conf_file=current_options.conf
----------------------------------------
Your sandbox server was installed in /mnt/mysql_sandboxes//5.1.30_single

It's now installed and started up, we can see that the process is running with the correct options:

$ ps -ef | grep mysql | grep jgoulah

jgoulah 11128 1 0 13:48 pts/3 00:00:00 /bin/sh /mnt/mysql_sandboxes/tarballs/5.1.30/bin/mysqld_safe --defaults-file=/mnt/mysql_sandboxes//5.1.30_single/my.sandbox.cnf
jgoulah 11203 11128 0 13:48 pts/3 00:00:00 /mnt/mysql_sandboxes/tarballs/5.1.30/bin/mysqld --defaults-file=/mnt/mysql_sandboxes//5.1.30_single/my.sandbox.cnf --basedir=/mnt/mysql_sandboxes/tarballs/5.1.30 --datadir=/mnt/mysql_sandboxes//5.1.30_single/data --user=jgoulah --log-error=/mnt/mysql_sandboxes//5.1.30_single/data/msandbox.err --pid-file=/mnt/mysql_sandboxes//5.1.30_single/data/mysql_sandbox10000.pid --socket=/tmp/mysql_sandbox10000.sock --port=10000

And we can connect to it on the port 10000:

$ mysql -u jgoulah --protocol=TCP -P 10000 -pgoulah

You can also go into the directory where we've installed this and there are some convenience scripts:

$ cd /mnt/mysql_sandboxes/5.1.30_single/

You can run the use script to connect into mysql (same thing we just did above except we don't have to remember our port, user, or pass):

$ ./use

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.30 MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql [localhost] {jgoulah} ((none)) >

Stop the instance:

$ ./stop

Or start it back up again:

$ ./start

. sandbox server started

There are a few other scripts which you can experiment with in this directory which are documented here.

Setting up a Replicated Instance

The nice thing about this tool is it will also setup replicated instances of MySQL with a single command. This allows you to test your application under a replicated environment, or even test different replication topologies including multiple slaves or multi-master replication. We'll use similar options as the single instance above, except we'll use port 11000 this time (the slaves get port + 1, ..., port + n where n is number of slaves). We'll put the install into /mnt/mysql_sandboxes/5.1.30_replicated. Note this time we use the make_replication_sandbox script:

mysql_sandbox_2.0.12 $ ./make_replication_sandbox \
/mnt/mysql_sandboxes/tarballs/mysql-5.1.30-linux-i686-icc-glibc23.tar.gz \
--upper_directory=/mnt/mysql_sandboxes/ \
--replication_directory=5.1.30_replicated --sandbox_base_port=11000

installing and starting master
installing slave 1
installing slave 2
starting slave 1
. sandbox server started
starting slave 2
. sandbox server started
initializing slave 1
initializing slave 2
replication directory installed on /mnt/mysql_sandboxes//5.1.30_replicated

Now we have a master and two slaves going. Note that the command to setup replicated sandboxes will not let us specify the user as we did with the single instance, but two users are created by default:

User: root@localhost Password: msandbox
User: msandbox@% Password: msandbox

You can run the use script as shown above, or connect directly to the master:

$ mysql -u msandbox --protocol=TCP -P 11000 -pmsandbox

Create a database:

mysql> create database jg_repl_test;
mysql> exit;

Connect to one of the slaves:

$ mysql -u msandbox --protocol=TCP -P 11001 -pmsandbox
mysql> show databases like '%jg_%';

+------------------+
| Database (%jg_%) |
+------------------+
| jg_repl_test     |
+------------------+

And we can see that it has replicated across. There are various options you can give the make_replication_sandbox command, for example you can give it the --master_master option to set up a multi master instance.

Conclusion

We've seen how to create a single instance of MySQL and also a replicated master with two slaves. Each of these takes only a few seconds to setup once you have the directory layout decided. There really isn't a much easier way to setup scratch instances of MySQL for all kinds of different purposes.