Installing a side instance of MySQL for testing purpose is a task that many administrators can perform without breaking a sweat. If you need to do that only once in a while, you need just to read the manual carefully, or to have some experience in this matter, and the task is accomplished quite easily.

If, however, your skills are below the Guru level, even to get this task done just once you may find yourself in trouble. And, let’s face it, even experienced administrators, when they need to do this several times, with different versions of MySQL, may have trouble doing it right. It would be nice to have a tool that takes care of the dirty details for you and gets the job done quietly, without interfering with existing installations, and without side effects.

Such a tool exists, it’s The MySQL Sandbox. It is a framework for testing features under any version of MySQL from 3.23 to 5.1. Whitout fuss, it will install one server under your home directory, and it will provide some useful commands to start and stop it, and to use it within the sandbox.

There are many reasons for installing a side server. One is testing a potentially dangerous application, and you don’t want to try it on a production server. Another reason is to try different versions of MySQL on a piece of code when hunting a bug. Or you are a consultant, your customers are all using different versions of the DBMS, and you need to test your procedures in an environment that is as close as possible to the your clients are using. I don’t know about you, but in my job I have all the above needs, sometimes all at once.

After having performed the task of installing a side instance of MySQL dozens of times, I realized that I was perhaps wasting too much time, especially in terms of responsiveness, since I could not answer to emergency problems as quickly as I would like. Therefore, I forced myself to put together most of my expertise into a Perl script, and the MySQL Sandbox was born.

Now, when I need to test something in any version of MySQL from the ancient 3.23 to the bleeding edge one in the Beta branch, I can do that in a few seconds. Literally.

With this package you can play with MySQL 5.x without need of using other computers. The server installed in the sandbox use non-standard ports and sockets, so that they won’t interfere with existing MYSQL installations.

Getting started

To use MySQL Sandbox you need a few things:

• The Sandbox package itself;
• Linux or FreeBSD operating system (it may work in other *NIX OSs, but has not been tested)
• a binary package of MySQL 3.23 or later
• Perl 5.8.1 or later (for installation only)
• a Bash compatible shell

Installation

To show you the simplest installation, let’s assume that you have already a MySQL binary installation, in its default location of /usr/local/mysql.

Unpack the distribution package in one empty directory and run the install script. For example:

$ ./install.pl

Now, assuming that johndoe is your username, (I sincerely hope it is not), you got MySQL 5.0 in /usr/local/mysql, and the directory from which you are installing is /home/johndoe/install/mysql_sandbox, you will be greeted by the following confirmation screen:

    The MySQL 5 Sandbox,  version 1.4 17-May-2006
    (C) 2006 Giuseppe Maxia, Stardata s.r.l.

installing with the following parameters:
sandbox_directory              = mysql_sandbox5_0
sandbox_port                   = 3310
datadir_from                   = archive
install_version                = 5.0
basedir                        = /usr/local/mysql
home_directory                 = /home/johndoe
my_file                        =
operating_system_user          = johndoe
db_user                        = datacharmer
db_password                    = datacharmer
force                          = 0
version_after_name             = 1
verbose                        = 0
do you agree? ([Y],n)

To better understand the options, have a look at Figure 1.

Figure 1: Basic Sandbox directory organization

Putting aside the other options for now, let’s focus on the directories. basedir is where you get the binaries from, i.e., in this case /usr/local/mysql.
home_directory is your $HOME, (/home/johndoe). It could be anywhere, but it should be a place where you’ve got all necessary writing privileges. Your $HOME is just a safe assumption. Under this directory, the installation process is going to create the sandbox_directory (red colored in the figure) and the data directory is just below it.

If you type Y, or just press ENTER, the installation progra will create /home/johndoe/mysql_sandbox5_0/, which will contain everything you need to work with this side instance.

Just cd to that directory, and use the ./start.sh command. You will see the following:

$ ./start.sh
/usr/local/mysql ~/mysql_sandbox5_0
~/mysql_sandbox5_0
sandbox server started
sandbox server started

Your server is now installed and ready for use. Go ahead and try it out..

$ ./use.sh
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1 to server version: 5.0.22

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

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

After that, you may look around. There is a configuration file my.sandbox.cnf, containing the starting options for you server. There is a USING file, containing a reminder of which version and basedir you were using. And there is a current_options.conf, containing the options used by the installation to create your sandbox. Should you need to recreate it, use the installation script again with this file as a parameter.

$ cd /install_directory
$ ./install.pl -f current_options.conf

When you are done, you may stop the server.

$ ./stop.sh

The server will go down quietly. You may erase the whole directory if you wish. There are some more interesting things that you can do.

Advanced installation

The above installation was easy. But actually I don’t recommend installing a sandbox from /usr/local/mysql.
The reason is that in such a location you install the current production release, and if you upgrade it, the sandbox will point to a version that is different from the one you originally intended.

I keep different versions grouped in a directory, conveniently named so that they can be easily accessed.
Usually, I unpack the max package, and then I rename the unpacked directory to the simple version name. So, mysql-max-5.0.21-linux-i686.tar.gz becomes 5.0.21. If I have several packages of the same version (it happens when testing the source code) I add a letter to the end.

My side servers organization is something like the one shown in figure 2. (Actually, I may have up to 20 different versions, but for most people this is more than enough).

Figure 2: Advanced Sandbox directory organization

If you want to get the same organization, just download the binary packages for your operating system (or compile it if you must) and for each version you may need to use, and unpack them in the same directory. Rename them appropriately, so that each directory is named after a version number, and you are ready to install.

If you want to achieve the same result as in the default installation, you should specify where the basedir option, so that the installation program will create appropriate configuration files and scripts.

./install --basedir=/opt/mysql/5.0.21

Should you run this command, though, you will get a different result

/home/johndoe/mysql_sandbox5_0 already exists.
'--force' option not specified.
Installation halted

As a security measure the Sandbox installer will refuse to overwrite existing directories, unless you instruct it explicitly to do so with the –force option.

But let’s take a look at some of the more interesting features. The
complete list is always available using ./install.pl --help.

Building the data directory

By default, the mysql database comes with two users. The datacharmer user has been granted all privileges except grant. This user can connect from any host. The root user has got all privileges, including grant.This user can connect only from localhost.

You can control the creation of the mysql database with the -datadir_from=[source]. The default value for [source] is archive, and this will use the packaged mysql database that was just described:

--datadir_from=archive

Use dir:[name] to import an existing mysql database:

--datadir_from=dir:/home/johndoe/my_default_mysql_db

To create the grant tables from scratch, use script:

--datadir_from=script

If you change the way your data directory is created, you should also modify the username and password you want to use. The installer will make a grants.mysql file containing the commands you should run as root to instantiate them. In this case, you can start using your sandbox by typing

$ ./use.sh -u root -p
Enter password:

and then pressing ENTER for an empty password. Once inside the client, run source grants.mysql, and your users will be created with their appropriate passwords.

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1 to server version: 5.0.22

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

mysql [localhost] {root} ((none)) > source grants.mysql

Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {root} (mysql) >

After that, you can run the script without additional arguments.

$ ./use.sh
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2 to server version: 5.0.22

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

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

Using the installation wizard

There are a few more options worth mentioning, but I won’t get into detail about them now. You would not remember them all (heck, I don’t remember them, even though I wrote the whole thing!). So I will mention the only one you’re gonna need if you want to fine tune your sandbox installation without memorizing too many things. Just run this one:

./install.pl --interactive

Then the installation program will turn into a wizard (a text-based one, but a wizard nonetheless) that will guide you step-by-step through all the available options. The output looks like this:

~/install/mysql_sandbox ~/install/mysql_sandbox/docs
Enter the values for each option
To leave the interactive choice and accept default values
for the remaining options, enter 'default'
To go to the previous item, enter 'back'
To quit the installation without any action, enter 'quit'

-----------------------------------------------------------------
home_directory
   The home directory. (default: $HOME (/home/johndoe))
Your choice: (default value [/home/johndoe])
-----------------------------------------------------------------
sandbox_directory
   Where to install the sandbox, under home-directory
Your choice: (default value [mysql_sandbox])
-----------------------------------------------------------------
sandbox_port
   The port number to use for the sandbox server.
   (Default: 3310)
Your choice: (default value [3310])

Thirteen more options follow (and possibly more, depending on how much time has elapsed between my writing and your reading this piece).
For each option, you could either press ENTER, accepting the default value, which is shown in brackets, or insert the value that is appropriate for your needs. If you have already changed what you wanted, and don’t want to go through the rest of the options list, you could enter default, and you leave the wizard, accepting default values for the remaining options.
If you want to cancel the installation, just enter quit and the program is terminated without performing any action at all.
To reenter the previous option, type back.

Cookbook

Here are some recipes on a few common situations.

Testing recent software on an older version

Let’s say you developed an application, you tested it with the current production ready version (5.0), and it works fine. Before releasing to the wide public, though, you want to test it with earlier versions, to prevent unpleasant surprises to your support department.
Using the Sandbox, the task is easy. For example, to install the latest release from version 4.0, you should enter:

$ ./install.pl --basedir=/opt/mysql/4.0.27 
            --sandbox_directory=mysql_sandbox_4_0_27 
            --install_version=4.0 --sandbox_port=4027 
            --no_ver_after_name

That will create a sandbox directory with a distinct name, and a port with the same number as the version itself.
If that does not sound easy, you are right. It’s easier than doing it manually, but the task can become even easier.
Starting from Sandbox 1.5, there is an additional installing program, called express_install.pl. To accomplish exactly the same result, you can enter

$ ./express_install.pl /opt/mysql/4.0.27

If you are using /opt/mysql/ as your binary repository, you can even omit the path. The express install will generate for you the necessary options for you.

$ ./express_install.pl 4.0.27
Executing ./install.pl --basedir=/opt/mysql/4.0.27 
            --sandbox_directory=mysql_sandbox_4_0_27 
            --install_version=4.0 
            --sandbox_port=4027 
            --no_ver_after_name

    The MySQL Sandbox,  version 1.5 23-May-2006
    (C) 2006 Giuseppe Maxia, Stardata s.r.l.
installing with the following parameters:
home_directory                 = /home/johndoe
sandbox_directory              = mysql_sandbox_4_0_27
sandbox_port                   = 4027
datadir_from                   = archive
install_version                = 4.0
basedir                        = /opt/mysql/4.0.27
my_file                        =
operating_system_user          = johndoe
db_user                        = datacharmer
db_password                    = datacharmer
force                          = 0
no_ver_after_name              = 1
verbose                        = 0
do you agree? ([Y],n) n

If you want, you may add some options to express_install.pl. Everything after the version (or the complete basedir) is passed to install.pl. For example:

$ ./express_install.pl 4.0.27 --interactive
Executing ./install.pl --basedir=/opt/mysql/4.0.27 
                --sandbox_directory=mysql_sandbox_4_0_27 
                --install_version=4.0 
                --sandbox_port=4027 
                --no_ver_after_name 
                --interactive

Enter the values for each option
* To leave the interactive choice and accept default values
     for the remaining options, enter 'default'
* To go to the previous item, enter 'back'
* To quit the installation without any action, enter 'quit'

-----------------------------------------------------------------
home_directory
   The home directory. (default: $HOME (/home/johndoe))
Your choice: (current value [/home/johndoe]) quit

Using the Sandbox to perform a main MySQL installation

If you want to mimic a normal binary installation using the Sandbox, you can do it, by supplying the following options during to the installation program.

home_directory                 = /usr/local/
sandbox_directory              = mysql
sandbox_port                   = 3306
datadir_from                   = script
install_version                = 5.0
basedir                        = /usr/local/mysql
my_file                        = large
operating_system_user          = johndoe
db_user                        = datacharmer
db_password                    = datacharmer
force                          = 1
no_ver_after_name              = 1
verbose                        = 0

The force option is necessary because it will overwrite existing files.

Running install.pl with the above parameters will get you an installation very close to the default one. In addition to that, you will have three bash scripts (_start.sh_, stop.sh, use.sh), but you can also start and stop the server using the normal mysql.server script.
So why would you do that? Actually, you shouldn’t. I am showing you how to do it so that you would get acquainted with the tool’s flexibility.
The main reason why you shouldn’t do that, of course, is that putting your data under the /usr/ directory is seldom a good idea. You may use a symbolic link for the data directory, but in general you should avoid having your data in the same place where you keep your applications.

So the best usage for the Sandbox would be to install a new data directory in an appropriate partition with enough free storage. It will save time and you’ll get the same result as if you’d done it manually. Only neater.

Creating a sandbox using an existing my.cnf with a given version

When you are testing an existing application, or hunting for a bug, it’s often important to setup a server with a specific my.cnf.
You know already that the myfile option will accept a {small|large|huge} keyword, and it will find a sample configuration file from $BASEDIR/support-files. Something that is also stated in the help text, but you may overlook, is that you can instead supply the full path of an existing my.cnf.

For example:

$ ./express_install.pl /opt/5.0.21 --my_file=/opt/mysql/4.1.19/my.cnf

The installation program will skip from the given installation file those options that are indispensable to setup a proper sandbox, and will include all remaining options in the final my.sandbox.cnf, inserting a comment in the file to remind you the origin of such options.

Troubleshooting

Nothing is perfect and MySQL Sandbox is no exception. There are a couple of things that can go wrong. More of that can happen, of course, but for now I wish to report just a few things that may happen to you.

sandbox server not started yet

When you enter ./start.sh, usually you see the welcoming message sandbox server started, and your are ready to use it.
Sometimes, though, you see a message saying sandbox server not started yet. That may be bad news, but it may only mean that the server is still building the files that are necessary for its functioning. For example, if your setup calls for a huge InnoDB tablespace, it may take a while before the server is up and running.
In these cases, have a look at the hostname.err file in the data directory. If the last message is along the line of “file such and such did not exist. new to be created”, it means that you have to wait a few seconds.
Look at the data directory, if you see a .pid file, everything was fine. If you don’t, than back to the error log, and try to figure out what was wrong.

Character set information not found

One of the cases that may happen, but only in some Linux distributions, is that a old version sandbox will complain about something along the lines of not finding a file that actually exists.

The message may say: Character set information not found in ‘/opt/mysql/x.x.xx/share/mysql/english/errmsg.sys’
You look at /opt/mysql/x.x.xx/share/mysql/english/, and indeed the errmsg.sys file is there. I think it’s a bug, but since it only happens in older versions, and only in Debian distributions, I will let it at that. The workaround that I found needs a root intervention. You need to set a symbolic link between your basedir and /usr/local/mysql. After that, the server will start.
In non-Debian system I have never had this problem.

Parting thoughts

The MySQL Sandbox is a tool that can simplify your administrative tasks. It does it for me. I hope it will be useful for others as well.
Try it, and leave a comment here to let me know if it was helpful, or if you had problems.
Comments welcome.

Update Thanks to Roland Bouman for his thorough review of this text.