MySQL Sandbox: Easily Using Multiple Database Servers in Isolation by Giuseppe Maxia

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
Two examples:

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)

Once installed:
In the case of a single sandbox — commands are start, stop and 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 start_all, stop_all, clear_all, and 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:
express_install.pl /path/mysql-OS-5.1.23.tar.gz

Instead of using express_install.pl, to install 1 master and 2 slaves, run:
set_replication.pl /path/mysql-OS-5.1.23.tar.gz

To install multiple servers of the same version at the same time:
set_many.pl /path/mysql-OS-5.1.23.tar.gz
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.
or:
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 my_file option:
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.

The 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 mysqldump, mysqlbinlog and mysqladmin for each instance in a sandbox. These shortcuts start the mysql client using the credentials in my_sandbox.cnf.
my sqldump
my sqlbinlog
my sqladmin

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:

Replication sandbox
m to use the master
s1 to use the first slave
s2 to use the second slave

Multi-node sandbox
n1 to use the first node
n2 to use the second node
n3 to use the third node

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
Two examples:

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)

Once installed:
In the case of a single sandbox — commands are start, stop and 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 start_all, stop_all, clear_all, and 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:
express_install.pl /path/mysql-OS-5.1.23.tar.gz

Instead of using express_install.pl, to install 1 master and 2 slaves, run:
set_replication.pl /path/mysql-OS-5.1.23.tar.gz

To install multiple servers of the same version at the same time:
set_many.pl /path/mysql-OS-5.1.23.tar.gz
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.
or:
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 my_file option:
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.

The 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 mysqldump, mysqlbinlog and mysqladmin for each instance in a sandbox. These shortcuts start the mysql client using the credentials in my_sandbox.cnf.
my sqldump
my sqlbinlog
my sqladmin

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:

Replication sandbox
m to use the master
s1 to use the first slave
s2 to use the second slave

Multi-node sandbox
n1 to use the first node
n2 to use the second node
n3 to use the third node