Quick DB Setups With MySQL Sandbox
Quick DB Setups With MySQL Sandbox
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):
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:
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 \
Here's the output:
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
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):
Welcome to the MySQL monitor. Commands end with ; or \g.
Stop the instance:
Or start it back up again:
. 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 \
installing and starting master
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
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;
Connect to one of the slaves:
$ mysql -u msandbox --protocol=TCP -P 11001 -pmsandbox
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.
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.