NAME
MySQL::Sandbox::Recipes - Collection of recipes for MySQL Sandbox
PURPOSE
This package is a collection of HOW-TO brief tutorials and recipes for MySQL Sandbox
This is work in progress. You may see that some of these recipes are still stubs. I am working on them in my free time, but I am definitely going to complete it.
Installing MySQL::Sandbox
All the recipes here need the MySQL Sandbox http://launchpad.net/mysql-sandbox. Therefore you need to install it, to be able to follow these recipes. For a general description of the application, see MySQL::Sandbox. A presentation on this matter is available at Slideshare http://www.slideshare.net/datacharmer/mysql-sandbox-3
the easy way - as root
This is so easy, that I am almost ashamed to show it. Anyway, here goes.
# cpan MySQL::Sandboxthe ambitious way - as unprivileged user
It is not difficult, but it requires some steps that depend on your environment. To simplify the example, let's assume that you are using perl 5.8.8 and you want to install in $HOME/usr/local.
Download MySQL Sandbox tarball from cpan
Set the variables that you will need
mkdir $HOME/usr/local export PATH=$HOME/usr/local/bin export PERL5LIB=$HOME/usr/local/lib/perl5/site_perl/5.8.8Build and install
perl Makefile.PL PREFIX=$HOME/usr/local make make test make install
It is a bit complex, but you need to do the setup only once. After that, you can use MySQL Sandbox in your user space.
Another solution, which can be habdy if you use a box without Perl and without root access, is to install Perl in your user space, and then you can use CPAN to install any package in your user space.
SINGLE SERVER RECIPES
Creating a single sandbox
Download or build a MySQL server tarball. Its name is something like mysql-X.X.X-Your_Operating_System.tar.gz
Make the sandbox
make_sandbox /path/to/mysql-X.X.X-Your_Operating_System.tar.gzNotice that this command will expand the tarball into a directory
X.X.X, located in the same path where tghe tarball is. If you want to expand in a centralized directory, see the next recipe.If you have already a centralized repository for your binaries (see next recipe), then you can create a sandbox even more easily:
make_sandbox 5.1.34 make_replication_sandbox 5.1.34
Easily creating more sandboxes after the first one
If you want to reuse the same binaries more than once, you can set up a repository for such binaries, under $SANDBOX_BINARY, which by default is $HOME/opt/mysql If you have such a directory, you have two choices.
- manually
-
Expand the tarball, and move it under $HOME/opt/mysql (or set $SANDBOX_BINARY to a path that suits you better). Name the directory with the version number. For example:
cd $HOME/opt/mysql gunzip -c /path/to/mysql-5.1.34-osx10.5-x86.tar.gz | tar -xf - mv mysql-5.1.34-osx10.5-x86 5.1.34Now, next time you want to install a sandbox using 5.1.34, all you need to do is say
make_sandbox 5.1.34 - automatically
-
If you want to combine installation and centralization of the binaries in one go, you can:
make_sandbox /path/to/mysql-5.1.34-osx10.5-x86.tar.gz --export_binariesThe expanded tarball is copied to the $SANDBOX_BINARY path, and you can refer to the version number only for future installations.
Using a single sandbox
When you create a sandbox, the last line of the application output tells you where it was created. If you don't remember where it is, the default location is $HOME/sandboxes/msb_X_X_XX, where X_X_XX is the version number. For example if you have installed
make_sandbox 5.1.34your sandbox is under $HOME/sandboxes/msb_5_1_34.
To use it, move to that directory and invoke the ./use script. This script is a shortcut to invoke the mysql command line client, with all the appropriate parameters.
./msb_5_1_34/use
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.34 MySQL Community Server (GPL)
.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
.
mysql [localhost] {msandbox} ((none)) > The default user is 'msandbox' and the password is 'msandbox'. The same password is also used by the 'root' account.
The 'use' script accepts all the additional options and commands that you would use with the mysql client. For example:
./use -e 'show schemas'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
./use -N -B -e 'select version()'
5.1.34You don't need to be inside the sandbox to use this script. You can invoke it with a relative or absolute path.
$HOME/sandboxes/msb_5_1_34/use -e 'select version()'
+-----------+
| version() |
+-----------+
| 5.1.34 |
+-----------+Notice that this script is created when the sandbox is installed. It is heavily customized for that sandbox only, and it is not replaceable by the same script from another sandbox. Even if you move it to another directory, it will still refer to the sandbox in which it was created. See 'moving a sandbox' for a safe way of moving a sandbox with all its scripts to a different directory.
Creating a single sandbox with user-defined port and directory
When you create a sandbox, you can fine tune the final product by adding options. You can list of all the available options easily:
low_level_make_sandbox --helpYou can pass any of the options listed to make_sandbox. In particular, since we need to change the port and directory, here's the deed:
make_sandbox 5.1.34
# installs on $SANDBOX_HOME/msb_5_1_34 with port 5134
make_sandbox 5.1.34 --sandbox_port=7800 --sandbox_directory=mickeymouse
# installs on $SANDBOX_HOME/mickeymouse with port 7800See the next recipe for an automatic way of getting a different port and directory.
Creating a single sandbox with automatic port checking
make_sandbox 5.1.34 --check_portThis option will check if port 5134 is used. If it is, it will check the next available port, until it finds a free one. In the previous statement context, 'free' means not only 'in use', but also allocated by another sandbox that is currently not running. If the directory msb_5_1_34 is used, the sandbox is created in msb_5_1_34_a. The application keeps checking for free ports and unused directories until it finds a suitable combination.
Creating a single sandbox with a specific option file
make_sandbox 5.1.34 --my_file=largeThis option installs the option file template my-large.cnf, which ships with every MySQL release. Similarly, you can choose 'small' or 'huge' to load the appropriate template. If you have your favorite option file, you can specify the full path to it
make_sandbox 5.1.34 --my_file=/path/to/my_smart.cnfIn all cases, the sandbox installer skips all the options that are necessary to keep the sandbox isolated and efficient (user, port,socket,datadir,basedir).
Creating a single sandbox from a source directory
After you have compiled MySQL from source, you can create a sandbox with the new binaries:
make_sandbox_from_source $PWD single [options]The options are the same that you can pass to make_sandbox. This script creates a tarball, and then invokes make_sandbox to finish the job.
Starting or restarting a single sandbox with temporary options
Every single server sandbox has a 'start' script, which does what the name suggests. Not only that, but you can start a sandbox with additional parameters for the mysqld daemon.
./start --key-buffer=3GThe 'restart' script accepts parameters in the same way. While 'start' only works when the sandboxed server is not running, 'restart' makes sure that the server is stopped, and then invokes 'start' with the optional parameters.
Stopping a sandbox
Inside every single sandbox there are two scripts that can stop a sandbox.
./stopThe 'stop' script attempts a clean stop, using mysqladmin. This works most of the time, especially if you are using a GA release.
./send_killThe 'send_kill' script achieve the same result, but with a different path. First, it tries to stop the sandboxed server nicely, by sending a kill -15 to the server PID. If this fails, after a reasonable timeout, it sends a deadly kill -9 and removes the PID file. This brutal method is not recommended for general usage, but it is sometimes necessary when dealing with servers in early stages of their development.
Cleaning up a sandbox
./clearThe 'clear' script will empty the data directory, trying to clean the system as much as possible. If the server is running when you invoke the script, it will issue a drop database query for each schema. It will also truncate the general and slow query log tables, if they exist. If the server is not running, 'clear' will clean up the data directory as best as it can. Notice however that invoking 'clear' when the server is not running will not remove stored routines and events. WARNING! No confirmation is asked! The data will be gone in 1 second.
Copying sandbox contents to another
Using 'sbtool', the Sandbox helper, this task is easy to perform. You need two sandboxes. This command cannnot copy to an arbitrary directory, but only to an existing sandbox. The first sandbox is where the data is stored. You need to use the --source_dir option (or -s for short). The second directory, identified with --dest_dir, (or -d) is the place where the data is copied. WARNING! the destination data is overwritten! This command skips binary logs, relay logs, and general logs.
sbtool -o copy \
-s /path/to/source/sandbox \
-d /path/to/destination/sandboxSome important points to remember:
This command only copies from a single sandbox to another. If you want to copy from a masterto several slaves, you need to run the command multiple times.
Before copying, this command stops both source and destination sandbox.
Moving a sandbox
sbtool -o move \
-s /path/to/source/sandbox \
-d /path/to/destination/directoryThis command stops the sandbox, moves it to the requested directory, and then changes all the paths in the scripts, to refer to the new directory.
If the destination directory already exists, the command fails.
Changing port to an existing sandbox
sbtool -o port \
-s /path/to/source/sandbox \
--new_port=XXXXTHis is similar to moving a sandbox, except that the directory is not changed. The sandbox is stopped, and the port changed in all the scripts.
Removing a sandbox completely
sbtool -o delete \
-s /path/to/source/sandbox This is a combination of using the 'clear' script and removing the sandbox. There is a safety check, though. If the sandbox is a permanent one (see next recipe), the operation aborts. Otherwise, the script calls 'clear' (clear_all if it is a group sandbox) and then removes all the contents.
WARNING! No confirmation is asked! The data will be gone in 1 second.
Making a sandbox permanent
MySQL Sandbox has been created with testing in mind. As such, the sandboxes are expendable, and there are some commands to get rid of them easily and quickly. However, you don't want always to do this. Sometimes, you need to keep the data for some time, to record it, or to copy it somewhere, or to use it for some similar testing. Whatever the reason, in these cases you don't want your data to disappear accidentally. The 'preserve' command, part of the sbtool, disables the 'clear' script in your sandbox, to avoid unpleasant "oh no" moments. Notice that nothing will protect you against hastily typed 'DROP DATABASE' queries! If you really need your data around, make a copy!
sbtool -o preserve \
-s /path/to/source/sandbox If you change your mind, invoking sbtool with the 'unpreserve' operation, makes the sandbox erasable again.
sbtool -o unpreserve \
-s /path/to/source/sandbox Starting and stopping a sandbox when the host server starts
There is no built-in solution for this recipe. Every operating system has its own method to launch applications at start up. The important point is that probably you don't need it. MySQL Sandbox is a tool for testing, aiming at creating temporary MySQL servers, and as such you should not need to launch a sandbox at start up. If you really need it, the MySQL manual explain how to launch at startup the main instance of the database.
Installing a sandbox from already installed binaries
make_sandbox_from_installed VERSION [options]This script (introduced in 2.0.99e) creates a repository of symbolic links from already installed binaries, arranging them in a way that the MySQL Sandbox easily recognizes. You should move to a directory where you want to keep this repository, before invoking this script. For example:
mkdir -p $HOME/opt/mysql
cd $HOME/opt/mysql
make_sandbox_from_installed 5.1.34 --no_confirmAfter this call, you can then install sandboxes (single or multiple) with a simple
make_{replication_|multiple_}sandbox 5.1.34MULTIPLE SERVER RECIPES
Creating a standard replication sandbox
make_replication_sandbox /path/to/tarball5.1.34.tar.gz As easy as making a single sandbox, this command creates a replication system with one master an two slaves. If you have set the repository under $SANDBOX_BINARY (see "Easily creating more sandboxes after the first one"), then you can also say
make_replication_sandbox 5.1.34If you want a different number of slaves, you can add an option
make_replication_sandbox --how_many_slaves=5 5.1.34Using a replication sandbox
When you create a replication sandbox, the last line of the application output tells you where it was created. If you don't remember where it is, the default location is $HOME/sandboxes/rsandbox_X_X_XX, where X_X_XX is the version number. For example if you have installed
make_replication_sandbox 5.1.34your sandbox is under $HOME/sandboxes/rsandbox_5_1_34.
To use it, move to that directory and invoke one of these scripts:
* ./m for the master
* ./s1 for the first slave
* ./s2 for the second slave, s3 for the third one, and so on
* ./use_all to send a command to the master and all slaves
* ./check_slaves to see as a quick glance if the slaves are workingAll the above scripts are shortcuts to the 'use' script in the appropriate directory. The 'use' script was described in the section dedicated to single server recipes.
Creating a circular replication sandbox
To create a circular replication system, you use the same command used for standard replication, but you add an option to indicate that you want a circular topology.
make_replication_sandbox --circular=4 /path/to/tarball5.1.34.tar.gzThe above command creates a system with 4 nodes, where node 1 is master of 2, node 2 is master of 3, node 3 is master of 4, and node 4, to close the circle, is master of 1.
If you have a binary repository under $SANDBOX_BINARY, you can use the simplified call:
make_replication_sandbox --circular=4 5.1.34Using a circular sandbox
Same as using a replication sandbox (see above), but there are no 'm' and 's#' scripts. Instead, there are 'n#' scripts, to access node 1 (n1), node 2, (n2) and so on. You may try this:
$ ./n1 -e 'create table test.t1 (i int)'
$ ./n3 -e 'insert into test.t1 values (3)'
$ ./use_all 'select * from test.t1'
# server: 1:
i
3
# server: 2:
i
3
# server: 3:
i
3
# server: 4:
i
3Creating a group of unrelated sandboxes (same version)
make_multiple_sandbox /path/to/tarball
make_multiple_sandbox --how_many_nodes=4 /path/to/tarball A group of unrelated sandboxes is a set of three or more servers installed under the same directory. What is the purpose of this? It is useful whenever you need to play with several servers without being tied by the master/slave relationship. One possible reason is to try new replication schemes with a clean setup. Or you may want to test the Federated engine. Another common usage is when you need to test different approaches to the same problem, and you want to compare results quickly.
Creating a group of unrelated sandboxes (different versions)
make_multiple_custom_sandbox /path/to/tarball5.1.34 /path/to/tarball5.0.77
make_multiple_custom_sandbox 5.1.34 5.0.77 5.4.0Similar to the previous one, the composite group is a collection of servers of different versions, all under the same directory. It can be useful when you want to compare some scheme in different versions, and automate the operations as much as possible.
Using a group of sandboxes
Same as using a replication sandbox (see above), but there are no 'm' and 's#' scripts. Instead, there are 'n#' scripts, to access node 1 (n1), node 2, (n2) and so on.
A group of sandboxes is easily manged with the 'use_all' script. For example, you may want to pass a SQL instruction to all the servers
./use_all 'select something from dbname.tablename where x=1'Or you want to execute a SQL script for each server
./use_all 'source somescript.sql'The internal organization of the directory makes it easy to do more complex operations with all the servers within the group. For example, if you want to measure the speed of execution of the same statement for each server (assuming that you have different setups for each one), you can create a shell script:
./stop_all
for N in 1 2 3
do
echo "processing node $N"
./node$N/start --some_mysqld_option=somevalue
time ./node$N/use -e 'select something_heavy from somedb.sometable'
./node$N/stop
doneCreating a group sandbox with port checking
make_replication_sandbox --check_base_port \
--sandbox_directory=something_unique /path/to/tarball
make_multiple_sandbox --check_base_port \
--sandbox_directory=something_unique /path/to/tarball Creating a group sandbox with a specific option file
export MASTER_OPTIONS="--my_file=huge"
export SLAVE_OPTIONS="--my_file=large"
make_replication_sandbox 5.1.34
export MASTER_OPTIONS="--my_file=/path/to/my.cnf"
export SLAVE_OPTIONS="--my_file=/path/to/my_other.cnf"
make_replication_sandbox 5.1.34
export NODE_OPTIONS="--my_file=large"
make_replication_sandbox 5.1.34
export NODE_OPTIONS="--my_file=large"
make_multiple_sandbox 5.1.34Creating a group sandbox from a source directory
Same as for a single sandbox, but use the 'replication' or 'multiple' keyword when calling the script
Starting or restarting a group sandbox with temporary options
./start_all [options]
./restart_all [options]Stopping a group sandbox
./stop_all
./send_kill_allCleaning up a group sandbox
./clear_allMoving a group sandbox
sbtool -o move \
-s /path/to/source/sandbox \
-d /path/to/destination/directoryRemoving a group sandbox completely
sbtool -o delete \
-s /path/to/source/sandbox Making a group sandbox permanent
sbtool -o preserve \
-s /path/to/source/sandbox
sbtool -o unpreserve \
-s /path/to/source/sandbox Managing sandboxes in groups
Using more than one SANDBOX_HOME
export SANDBOX_HOME=/my/alternative/directory
make_sandbox 5.1.34 --check_portStopping all sandboxes at once
$SANDBOX_HOME/stop_allStarting all sandboxes at once
$SANDBOX_HOME/start_allRunning the same query on all sandboxes
$SANDBOX_HOME/use_all 'select something_cool'Testing with sandboxes
Running the built-in test suite
Download and expand the MySQL Sandbox package
perl Makefile.PL
make
TEST_VERSION=/path/to/mysql/tarball make test
# or
TEST_VERSION=5.1.34 make testThe TEST_VERSION environment variable contains a version or the path to a tarball used by the test suite. If no test version is provided, most of the tests are skipped. The test suite creates a private SANDBOX_HOME under ./t/test_sb. All the sandboxes needed for the test are executed there. If something goes wrong, that is the place to inspect for clues.
Creating and running your own tests
Look at the ./t directory inside the MySQL Sandbox package (previous recipe). There are several .sb files, which are good examples of what you can do with the simple testing language. If this is not enough, you can use Perl itself. There are a few .sb.pl files, which are plugins for test_sandbox. Once you create your file, you can run it with
test_sandbox --user_test=your_test.sbTo use a Perl test, name the test with a .sb.pl extension.
test_sandbox --user_test=your_test.sb.plCOPYRIGHT
Version 3.0
Copyright (C) 2006,2007,2008,2009 Giuseppe Maxia
Home Page http://launchpad.net/mysql-sandbox/
LEGAL NOTICE
This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; version 2 of the License.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA