MySQL Sandbox: Easily Using Multiple Database Servers in Isolation
Apr 16, 2008 / By Sheeri Cabral
Here are my liveblogging notes from MySQL Sandbox: Easily Using Multiple Database Servers in Isolation by Giuseppe Maxia
Giuseppe has been a community member since 2001, and in the past year or so, a MySQL Employee.
He likes to give things away for free — he gave away T-shirts to the early arrivers to the workshop, and that’s why he’s giving away the sandbox as well. The sandbox is NOT an official MySQL product. It is released from GPL, available from http://sf.net/projects/mysql-sandbox.
Why the sandbox? To be able to set up 1 server in under 10 seconds. And to be able to set up multiple MySQL instances very quickly, and to use them quickly.
The sandbox untars in seconds, for installing alternative servers, not main instance, it creates a separated environment (datadir, port, sockets) — for groups of related or unrelated servers. Really good for testing out new server versions. WARNING: If you do not use separated environment (separate datadirs, ports and sockets), you can corrupt your data.
Doing it manually (the old, hard way):
unpack tarball, move to separate directory, create db tables, create .cnf with port, socket, datadir, launch mysqld_safe manually, launch mysql commandline script with options.
OK for doing it once, but a good DBA will automate this if they’re doing this a lot, to avoid mistakes — for example, while doing QA to test several versions.
So the easy automated way — MySQL Sandbox!
Just provide the version # and it creates $HOME/VER/data, VER, /tmp/mysql_VER.sock
Version 5.1.24 datadir = $HOME/5_1_24/data port 5124 socket = /tmp/mysql_5124.sock Version 6.0.5 $HOME/6_0_5/data port 6005 socket = /tmp/mysql_6005.sock
(NOTE: you can have multiple instances of the same version)
In the case of a single sandbox — commands are
clear (removes all data and files in the datadir except for the mysql system db and tables), and
use (instead of typing
mysql -S /path/to/socket -u user -p you can use the
use shell command and it will read what it needs from the my_sandbox.cnf file).
In the case of multiple sandbox, you can use the same commands as with a single instance, but there are commands that can affect all the instances. These are
multi_cmd. The first three have the obvious results;
multi_cmd command executes command for all nodes — so you do not have to call it on each node. For example:
multi_cmd "select * from test.t1"
The easy way to install — download the package from Sourceforge. The sandbox doesn’t contain MySQL build, so you have to download a tarball or compile one yourself.
To install a single instance of MySQL 5.1.23:
Instead of using
express_install.pl, to install 1 master and 2 slaves, run:
To install multiple servers of the same version at the same time:
This installs 3 instances by default, but you can specify how many you want with options to set_many.pl.
For multiple servers of different versions, either:
Download the tarballs and run
set_custom_many.pl /path/to/mysql-OS-5.0.51.tar.gz /path/to/mysql-OS-5.1.23.tar.gz /path/to/mysql-OS-6.0.5.tar.gz.
Expand the tarballs in
$HOME/opt/mysql and run
set_custom_many.pl 5.0.51 5.1.23 6.0.5
Fine tuning –
express_install.pl /path/mysql-OS-5.1.23.tar.gz [option]… can customize port, datadir, enable federated tables, disable innodb, skip networking, and so on.
You can pick your default my.cnf “size” with the
express_install.pl /path/mysql-OS-5.1.23.tar.gz --my_file=small
express_install.pl /path/mysql-OS-5.1.23.tar.gz --my_file=medium
express_install.pl /path/mysql-OS-5.1.23.tar.gz --my_file=large
express_install.pl /path/mysql-OS-5.1.23.tar.gz --my_file=huge
These will use the my_small.cnf, my_medium.cnf, my_large.cnf or my_huge.cnf sample config files bundled with MySQL.
To easily fine tune the 50+ options in the sandbox, you can run
express_install.pl /path/mysql-OS-5.1.23.tar.gz --interactive
to ask you the values for each value — if you want to skip the rest of the questions while you are in the interactive mode and continue the install using default values for the rest of the questions, you can type “default” at any prompt. You can also enter “back” at any prompt to go back to the previous question; or enter “quit” at any prompt to quit the interactive server without completing the sandbox installation.
use shell command starts the mysql client, using the credentials in
my_sandbox.cnf. By the way, the default username/password = msandbox/msandbox, default root password = msandbox
There are shortcuts for using
mysqladmin for each instance in a sandbox. These shortcuts start the mysql client using the credentials in
Using a multi-instance sandbox
start_all starts the master, then slaves.
stop_all stops the slaves and then the master.
clear_all clears all the slaves and then the master.
multi_cmd was already mentioned to run the same command on all the instances. However, there are different commands to run a command on a single instance of a multi-instance sandbox. Instead of the
use command, the shell commands to use the multiple instances are:
m to use the master
s1 to use the first slave
s2 to use the second slave
n1 to use the first node
n2 to use the second node
n3 to use the third node