dcsimg

MySQL Sandbox: Treat MySQL Instances like Virtual Machines

Install isolated side-by-side MySQL instances right the first time with this time-saving virtual manager.

I remember meeting Giuseppe Maxia (aka The Data Charmer), probably the most famous Italian MySQL enthusiast and hacker, back at what must have been the first MySQL Conference. At the time I’d seen him on mailing lists and knew he gave talks from time to time, but I had no idea of the useful software he’d be creating. This week, I’d like give you a tour of one of his projects: MySQL Sandbox.

I think of it as a sort of virtual server manager for MySQL instances. In fact, the one-line intro on the project web site hints at that:

Quick painless install of side MySQL server in isolation. MySQL Sandbox is a tool for installing one or more MySQL servers in isolation, without affecting other servers.

Isolation tends to be the hardest thing to get right (aside from the automation itself) when trying to bring up multiple MySQL instances on a single machine. There are a lot of hard-coded default paths (and port numbers) that will cause you headaches unless your track down all the references and override the relevant settings. MySQL Sandbox takes care of all that work, giving you any number of totally isolated MySQL servers with their own data directory, port number, and socket. The severs can all be the same version, different versions, or a mix. It’s even possible to create several servers quickly and setup a replication topology to play with as well.

Getting MySQL Sandbox

Like many projects nowadays, MySQL Sandbox is on Launchpad. So you simply need an installation of bzr to get started. If you don’t already have it:

$ sudo apt-get install bzr

Or whatever the equivalent is in your operating system’s package manger. Then you can pull a copy of the tree and run through the installation process:

bzr branch lp:mysql-sandbox
cd mysql-sandbox
perl Makefile.PL
make
make test
sudo make install

If that looks familiar, that’s because MySQL Sandbox is written in Perl and packaged up like any code from the CPAN. Easy as pie.

The only other thing we need is at least one copy of a MySQL distribution to play with. Let’s grab a copy of the latest MySQL 5.4 release (tarball) which happens to be 5.4.1-beta at the time of this writing.

A Simple Sandbox

Alright, assuming that mysql-5.4.1-beta-linux-i686-glibc23.tar.gz is in your home directory, all it takes to get a MySQL instance running is a make_sandbox command. Here’s a look along with some output you’ll see.

$ cd ~
$ make_sandbox mysql-5.4.1-beta-linux-i686-glibc23.tar.gz
unpacking /home/jzawodn/mysql-5.4.1-beta-linux-i686-glibc23.tar.gz
Executing low_level_make_sandbox --basedir=/home/jzawodn/5.4.1 \
        --sandbox_directory=msb_5_4_1 \
        --install_version=5.4 \
        --sandbox_port=5410 \
        --no_ver_after_name \
        --my_clause=log-error=msandbox.err
    The MySQL Sandbox,  version 3.0.04
    (C) 2006,2007,2008,2009 Giuseppe Maxia
installing with the following parameters:
upper_directory                = /home/jzawodn/sandboxes
sandbox_directory              = msb_5_4_1
sandbox_port                   = 5410
check_port                     = 0
no_check_port                  = 0
datadir_from                   = script
install_version                = 5.4
basedir                        = /home/jzawodn/5.4.1
my_file                        =
operating_system_user          = jzawodn
db_user                        = msandbox
db_password                    = msandbox
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
no_show                        = 0
do you agree? ([Y],n)
090715 20:47:43 [Warning] Forcing shutdown of 2 plugins
090715 20:47:43 [Warning] Forcing shutdown of 2 plugins
loading grants
........... sandbox server started
Your sandbox server was installed in $HOME/sandboxes/msb_5_4_1

The whole process takes less than 30 seconds on my Thinkpad T61. Not bad!

Now I can connect to it using the new socket it created and the login credentials displayed at sandbox creation time.

$ mysql -u msandbox -pmsandbox -S /tmp/mysql_sandbox5410.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.4.1-beta MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Not bad, huh? It’s just a MySQL server like any other, albeit with some very conservative default settings for memory use and whatnot.

Replication Setup

How about setting up a master and a pair of salves to test out a replication configuration? Again, it’s easy.

$ make_replication_sandbox mysql-5.4.1-beta-linux-i686-glibc23.tar.gz
installing and starting master
090715 20:53:31 [Warning] Forcing shutdown of 2 plugins
090715 20:53:31 [Warning] Forcing shutdown of 2 plugins
installing slave 1
090715 20:53:43 [Warning] Forcing shutdown of 2 plugins
090715 20:53:43 [Warning] Forcing shutdown of 2 plugins
installing slave 2
090715 20:53:43 [Warning] Forcing shutdown of 2 plugins
090715 20:53:44 [Warning] Forcing shutdown of 2 plugins
starting slave 1
........... sandbox server started
starting slave 2
............ sandbox server started
initializing slave 1
initializing slave 2
replication directory installed in $HOME/sandboxes/rsandbox_5_4_1

All done in under 30 seconds.

You can check to verify that it’s working by picking one of the slave sockets and running show slave status from the command line.

$ mysql -u msandbox -pmsandbox -S /tmp/mysql_sandbox11787.sock -e 'show slave status \G'
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: msandbox
                  Master_Port: 11786
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 636
               Relay_Log_File: mysql_sandbox11787-relay-bin.000002
                Relay_Log_Pos: 781
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...

Administrative Commands

With all these servers running, MySQL Sandbox also makes it easier to manage them all with some simple administrative commands. Each of these is actually a script stored in a subdirectory of your ~/sandboxes directory. The commands are:

  • start: start the server
  • stop: stop the server
  • clear: remove any data so you can begin anew
  • use: a wrapper that runs the mysql command-line tool, connecting to the instance
  • send_kill: sends kill -TERM and then, if necessary kill -KILL to the server process
  • my: starts one of mysqldump, mysqladmin, mysqlbinlog using credentals from my_sandbox.cnf

There are also command available for manipulating groups of servers: start_all, stop_all, clear_all, and use_all. They’re just like their counterparts above but perform the action on each server in a set.

It’s worth stopping here to look at how the ~/sandboxes directory is organized. Assuming you followed the commands earlier in this article, your directory should have a similar structure to what you see here.

First, the top level:

$ ls -l sandboxes
-rwxr-xr-x 1 jzawodn jzawodn   62 2009-07-15 20:53 clear_all
drwxr-xr-x 3 jzawodn jzawodn 4096 2009-07-15 20:47 msb_5_4_1
-rwxr-xr-x 1 jzawodn jzawodn   64 2009-07-15 20:53 restart_all
drwxr-xr-x 5 jzawodn jzawodn 4096 2009-07-15 20:54 rsandbox_5_4_1
-rwxr-xr-x 1 jzawodn jzawodn 2112 2009-07-15 20:53 sandbox_action
-rwxr-xr-x 1 jzawodn jzawodn   66 2009-07-15 20:53 send_kill_all
-rwxr-xr-x 1 jzawodn jzawodn   62 2009-07-15 20:53 start_all
-rwxr-xr-x 1 jzawodn jzawodn   61 2009-07-15 20:53 stop_all
-rwxr-xr-x 1 jzawodn jzawodn   60 2009-07-15 20:53 use_all

As you can see, there are top-level commands that which operate on all the sandboxes we’ve created so far. Each “set” actually exist in its own subdirectory.

The first is a single server:

$ ls -l sandboxes/msb_5_4_1/
-rwxr-xr-x 1 jzawodn jzawodn 1702 2009-07-15 20:47 change_paths
-rwxr-xr-x 1 jzawodn jzawodn 1481 2009-07-15 20:47 change_ports
-rwxr-xr-x 1 jzawodn jzawodn 1732 2009-07-15 20:47 clear
drwx------ 4 jzawodn jzawodn 4096 2009-07-15 20:47 data
-rw-r--r-- 1 jzawodn jzawodn  187 2009-07-15 20:47 grants.mysql
-rwxr-xr-x 1 jzawodn jzawodn  920 2009-07-15 20:47 load_grants
-rwxr-xr-x 1 jzawodn jzawodn 1807 2009-07-15 20:47 my
-rw-r--r-- 1 jzawodn jzawodn 1606 2009-07-15 20:47 my.sandbox.cnf
-rwxr-xr-x 1 jzawodn jzawodn  923 2009-07-15 20:47 proxy_start
-rwxr-xr-x 1 jzawodn jzawodn  884 2009-07-15 20:47 restart
-rwxr-xr-x 1 jzawodn jzawodn 1413 2009-07-15 20:47 send_kill
-rwxr-xr-x 1 jzawodn jzawodn 1822 2009-07-15 20:47 start
-rwxr-xr-x 1 jzawodn jzawodn 1501 2009-07-15 20:47 stop
-rwxr-xr-x 1 jzawodn jzawodn 1291 2009-07-15 20:47 use
-rw-r--r-- 1 jzawodn jzawodn   54 2009-07-15 20:47 USING

As you can see, the individual commands are there as well as the server’s data directory and some other bits. The use script makes it trivial to connect without having to know about socket files, as I did earlier:

$ ./sandboxes/msb_5_4_1/use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.4.1-beta MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql [localhost] {msandbox} ((none)) >

Finally, let’s have a look at the replication sandbox we configured:

$ ls -l sandboxes/rsandbox_5_4_1/
total 56
-rwxr-xr-x 1 jzawodn jzawodn  157 2009-07-15 20:53 check_slaves
-rwxr-xr-x 1 jzawodn jzawodn  353 2009-07-15 20:53 clear_all
-rwxr-xr-x 1 jzawodn jzawodn  589 2009-07-15 20:54 initialize_slaves
-rwxr-xr-x 1 jzawodn jzawodn   65 2009-07-15 20:53 m
drwxr-xr-x 3 jzawodn jzawodn 4096 2009-07-15 20:53 master
drwxr-xr-x 3 jzawodn jzawodn 4096 2009-07-15 20:53 node1
drwxr-xr-x 3 jzawodn jzawodn 4096 2009-07-15 20:53 node2
-rwxr-xr-x 1 jzawodn jzawodn  112 2009-07-15 20:53 restart_all
-rwxr-xr-x 1 jzawodn jzawodn   54 2009-07-15 20:53 s1
-rwxr-xr-x 1 jzawodn jzawodn   54 2009-07-15 20:53 s2
-rwxr-xr-x 1 jzawodn jzawodn  310 2009-07-15 20:53 send_kill_all
-rwxr-xr-x 1 jzawodn jzawodn  496 2009-07-15 20:53 start_all
-rwxr-xr-x 1 jzawodn jzawodn  280 2009-07-15 20:53 stop_all
-rwxr-xr-x 1 jzawodn jzawodn  297 2009-07-15 20:53 use_all

Here we have another level of indirection. Each “node” in the replication setup (master, node1, and node2) exists as their own subdirectories. And if you look, you’ll see that each has a set of the same commands inside it. However, at the top level is a set of multi-server commands you can use to manipulate the master and slaves together.

Shutting everything down is as simple as:

$ ./sandboxes/rsandbox_5_4_1/stop_all
executing "stop" on slave 1
executing "stop" on slave 2
executing "stop" on master

Again, it’s easy.

Anyone, Anywhere, and Advanced

In case you haven’t noticed so far, none of this requires root access to do. The sandboxes run as you (or whichever user starts them) and are nicely self-contained. That means you never need to ask anyone for permission to try out a small network of upgraded MySQL servers. Just create them yourself and go to town.

MySQL Sandbox is great for being able to setup and tear down MySQL instances in a hurry. But if you end up keeping some of them around for a whlie, you may be interested in sbtool, which comes with MySQL Sandbox. sbtool helps with the management of existing sandboxes, allowing you to easily copy data from one sandbox to another, gather a list of all TCP ports in use, completely remove a sandbox, setup multi-level replication, and more. You can find full documentation on-line in the MySQL Forge wiki.

Another feature worth discussing is using MySQL Sanbox to setup a multi-version group of servers. If you have multiple server tarballs available, you can ask MySQL Sandbox to set them up in one pass. The make_multiple_custom_sandbox does this for you.

$ make_multiple_custom_sandbox /path/to/tarball1.tar.gz /path/to/tarball2.tar.gz /path/to/tarball3.tar.gz ...

It will configure one instance of each tarball you point it at.

Finally, the make_sandbox_from_source command is handy if you’re used to building your own binaries from source. You can point it at a source tree to make any of the sandbox setups (single, multiple, replication, etc.).

Conclusion

MySQL Sandbox is an amazing productivity boost if you spend a lot of time setting up MySQL instances to test new code, experiment with various settings, or just mess around. It provides a very fast and convenient way to initiate any number of MySQL servers with minimal input and time.

Giuseppe has a good overview MySQL Sandbox 3.0 presentation available on SlideShare which showcases even more of the features in MySQL Sandbox.

Fatal error: Call to undefined function aa_author_bios() in /opt/apache/dms/b2b/linux-mag.com/site/www/htdocs/wp-content/themes/linuxmag/single.php on line 62