Introduction

Maatkit uses persistent MySQL sandboxes for all its test scripts. Most scripts will not work if the sandbox servers are not running. This wiki outlines how to setup the sandboxes and how to use the Sandbox common module which helps test scripts interface with sandbox servers.

Details

Maatkit doesn't use MySQL Sandbox directly; it has its own similar scripts in trunck/sandbox. Therefore, you do not actually need to install MySQL Sandbox. However, before the Maatkit sandbox servers will work, you may need to do the following steps.

1. Download MySQL binary and Set Special Environment Vars

On Ubuntu, InnoDB may not work for a sandbox server using the official Ubuntu MySQL server package. (On Ubuntu 8.04 this was the case, but it seems to work with Ubuntu 9.04.) If you're not running Ubuntu, you may be able to skip this step. To check if InnoDB will work for a sandbox server on your system with a preexisting MySQL installation, try to start the Maatkit sandbox servers by running trunk/sandbox/make_master-slave and look for the following:

   Waiting for sandbox to start: sandbox 12345 failed to start.
   ****** WARNING sandbox doesn't have a working InnoDB! ******
   Waiting for sandbox to start: sandbox 12346 failed to start.
   ****** WARNING sandbox doesn't have a working InnoDB! ******

If you see those warnings, then you'll need to continue with this step.

Download and extract a copy of the MySQL binary for your platform. For Linux binaries the link is http://dev.mysql.com/downloads/mysql/5.0.html#linux. (I had problems getting the 5.1 binaries to start as a sandbox, but the 5.0 binaries work without problem; your mileage may vary.) Then set the environment variables MSANDBOX_BASEDIR and MSANDBOX_MYSQLD_SAFE. For example:

   $ env | grep MSANDBOX
   MSANDBOX_BASEDIR=/home/daniel/mysql_binaries/mysql-5.0.82-linux-x86_64-glibc23
   MSANDBOX_MYSQLD_SAFE=/home/daniel/mysql_binaries/mysql-5.0.82-linux-x86_64-glibc23/bin/mysqld_safe

Usually, these are set by adding the following to your ~/.bashrc:

   MSANDBOX_BASEDIR=/home/daniel/mysql_binaries/mysql-5.0.82-linux-x86_64-glibc23
   MSANDBOX_MYSQLD_SAFE=$MSANDBOX_BASEDIR/bin/mysqld_safe

(You have to logout and log back in for these variables to be set automatically.) Finally, try to start the Maatkit sandbox servers again (trunk/sandbox/make_master-slave). If all goes well, you should see:

   Waiting for sandbox to start: sandbox 12345 has started.
   Waiting for sandbox to start: sandbox 12346 has started.

If all does not go well, seek help on the Maatkit discussion list or in #maatkit on Freenode.

2. Installing realpath

You may need to install realpath on your system. Ubuntu 8.04 required:

   sudo apt-get install realpath

3. Creating the Initial Sandbox Environment

The first thing you must do is initially start the sandboxes on your machine. This is done with the following command (if you didn't already do it in step 1):

   sandbox/make_master-slave

That creates a master server on port 12345 (/tmp/12345) and a slave on port 12346 (/tmp/12346). If you need another slave, you can run sandbox/make_slave 12347 and it will be slaved to the 12345 master.

Perhaps Sandbox.pm will be able to create the sandboxes automatically, but at present it is left to you to initially create the sandbox environment. This only needs to be done once.

There is also a sandbox/create_master-master script which will create a master-master replication setup between two servers, master1 on port 12348 and master2 on port 12349. Currently, this is only used by the mk-table-sync test script.

4. Using Sandbox.pm In a Test Script

In a test script, the basic usage of Sandbox.pm starts with:

   require '../../common/DSNParser.pm';
   require '../../common/Sandbox.pm';
   my $dp = new DSNParser();
   my $sb = new Sandbox(basedir => '/tmp', DSNParser => $dp);

   my $master_dbh = $sb->get_dbh_for('master')
      or BAIL_OUT('Cannot connect to sandbox master');
   my $slave_dbh  = $sb->get_dbh_for('slave1')
      or BAIL_OUT('Cannot connect to sandbox slave1');

Remember that the paths in the require pragma are relative; this example comes from mk-table-checksum/t/mk-table-checksum.t.

Next, here is a quick synopsis of Sandbox subroutines that you may find useful:

   $sb->load_file('master', 'samples/file.txt');  # mysql < samples/file.txt
   $sb->create_dbs($master_dbh, [qw(test)]);  # CREATE DATABASE `test`
   $sb->use('master', '-e "STOP SLAVE"'); # mysql -e "STOP SLAVE"
   $sb->wipe_clean($master_dbh);
   $sb->wipe_clean($slave_dbh);

5. Clean Up After Yourself!

Notice the calls to wipe_clean(): this subroutine should be called at the end of the test script for every sandbox that was used or modified. This causes all databases to be removed on the given sandbox.

To remove all databases outside of Sandbox.pm, you can use one of:

sandbox/stop_all
sandbox/stop_master-slave
sandbox/stop_master-master

Depending on which sandboxes you've invoked.

Reserved Sandbox Port/ID Numbers

For the sake of all our sanities, please respect the following reserved sandbox port/ID numbers and do not attempt to make a sandbox that will surprise and kill the harmony of the other test scripts.

common/t/MasterSlave.t is a special case. Since this test needs to reassign the slaves, instead of fiddling with the persistent sandbox, MasterSlave.t sets up a second replication sandbox environment on ports 2900-2903.

Sakila Database

Some tests require the sakila database, like TableParser.t for example. Since the database is a few megs and not used very often, it is not included in the sandbox servers by default. Therefore, you must manually load the sakila database in order to prevent certain tests from being skipped.

When writing tests that require the database, be sure to enclose the tests in a SKIP block that checks whether or not the database is installed. For example:

   SKIP: {
      skip 'Sandbox master does not have the sakila database', 4
         unless @{$dbh->selectcol_arrayref('SHOW DATABASES LIKE "sakila"')};
      # tests if sakila database is loaded
   };

Errors

MySQL 5.0.77 sandboxes on Ubuntu 8.04 would not start due to missing /usr/share/mysql/errmsg.txt file. To fix this issue:

sudo ln -s /path/to/mysql-5.0.77/share/mysql /usr/share

If you get "Bail out! Cannot connect to sandbox master" or something similar when trying to run a test script but the sandbox servers are started, you may need to add

[client]
user=msandbox
pass=msandbox

to your ~/.my.cnf.