sbtool - a new advanced entry in the MySQL Sandbox toolkit

MySQL Sandbox 2.0.17 introduces a new item in the toolkit. sbtool is a multi-purpose tool for advanced tasks with MySQL sandboxes. Most notables: assigned and used port detection, port range detection, replication tree, copying and moving sandboxes.

Overview

./sbtool --help
usage: ./sbtool [options]
 -o     --operation       (s) <> - what task to perform
   'info'     returns configuration options from a Sandbox
   'copy'     copies data from one Sandbox to another
   'ports'    lists ports used by the Sandbox
   'tree'     creates a replication tree
   'move'     moves a Sandbox to a different location
   'range'    finds N consecutive ports not yet used by the Sandbox
 -s     --source          (s) <> - source directory for move,copy
 -d     --dest            (s) <> - destination directory for move,copy
 -u     --only_used       (-) <> - for "ports" operation, shows only the used ones
 -i     --min_range       (i) <5000> - minimum port when searching for available ranges
 -x     --max_range       (i) <32000> - maximum port when searching for available ranges
 -z     --range_size      (i) <10> - size of range when searching for available port range
 -f     --format          (s)  - format for "ports" and "info"
   'perl'     fully structured information in Perl code
   'text'     plain text dump of requested information
 -p     --search_path     (s)  - search path for ports and info
 -a     --all_info        (-) <> - print more info for "ports" operation
        --tree_nodes      (s) <> - description of the tree (x-x x x-x x|x x x|x x)
        --mid_nodes       (s) <> - description of the middle nodes (x x x)
        --leaf_nodes      (s) <> - description of the leaf nodes (x x|x x x|x x)
        --tree_dir        (s) <> - which directory contains the tree nodes
 -v     --verbose         (-) <> - prints more info on some operations
 -h     --help            (-) <> - this screen

The list of features may look intimidating. The important ones are the values that you can give to the --operation modifier. The rest is a list of ancillary options to be used with the possible operations.

Port detection

The most straightforward operation is 'ports', which returns the list of ports that have been assigned to sandboxes installed under $SANDBOX_HOME (default $HOME/sandboxes).

$ ./sbtool -o ports
5077  0
5080  0
5130  0
5131  1
5133  1
5333  0
6080  0

The ports followed by a "0" have been assigned but are not currently in use. The ones followed by a "1" are in use. If you need only the latter, it's easy.

$ ./sbtool -o ports --only_used
5131  1
5133  1

If you want to use this information from a Perl script, there is an appropriate modifier.

$ ./sbtool -o ports --only_used --format=perl
$ports = {
          '5133' => 1,
          '5131' => 1
        };

Speaking about ports, if you want to make a custom installation of one or more sandboxes for which you need a range of ports, sbtool can help. Let's assume that you need at least 20 ports, not lower than 5130.

$ ./sbtool -o range --min_range=5130 --range_size=20
5134 -  5154

Moving a sandbox

Moving a sandbox has been in the wish list for long time. Finally, there is a clean way of moving a sandbox to an arbitrary location, keeping its functionality intact.
For example, let's move msb_5_1_32 to /tmp/xyz, and see if it works.

$ ./sbtool -o move  --source_dir=$HOME/sandboxes/msb_5_1_31 --dest_dir=/tmp/xyz
the old scripts have been saved as filename.bak

$ /tmp/xyz/start
. sandbox server started

$ /tmp/xyz/use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.31 MySQL Community Server (GPL)

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

mysql [localhost] {msandbox} ((none)) > exit
Bye

# Now we move the sandbox back to its previous location

$ ./sbtool -o move  --dest_dir=$HOME/sandboxes/msb_5_1_31 --source_dir=/tmp/xyz
the old scripts have been saved as filename.bak

The sbtool script knows when a sandbox is in use, and stops it before starting the moving process. After the move, all original scripts are saved as ".bak", and the current scripts are updated with the new location.

Copying a sandbox

Copying a sandbox meets a different need. You want mostly to copy the data from one server to another. You don't want to use replication, especially if it is a lot of data. sbtool can copy the data from one sandbox to another, stopping the source and destination when necessary. It will only copy across compatible versions (5.1 to 5.1 or newer, but not 5.0 to 5.1 or vice versa).
When copying a data directory, sbtool skips relay-log.info, the binary logs, and the relay logs.
Both the source and destination directories must be sandboxes, or the operation is refused.

$ ./sbtool -o copy -s $HOME/sandboxes/msb_5_1_31/ -d $HOME/sandboxes/msb_5_1_33/

Replication trees

This option is very much required for testing.
You want to have one master, two or more relay slaves, and several final slaves.
For example. let's assume that you need this schema:

             1  
            |  
     +------+-----+-----------+
     2            3           4                   
     |            |           |  
 +---+---+    +---+---+   +---+---+
 |   |   |    |       |   |   |   |  
 5   6   7    8       9   10  11  12

The way to go is to create a multi_sandbox with 12 nodes, and then using sbtool to make the tree.

$ make_multiple_sandbox --how_many_nodes=12 5.0.77
...
group directory installed on $HOME/sandboxes/multi_msb_5_0_77

$ ./sbtool -o tree --mid_nodes="2 3 4" --leaf_nodes="5 6 7|8 9|10 11 12" --tree_dir=$HOME/sandboxes/multi_msb_5_0_77
node 1 is master
. sandbox server started
. sandbox server started
enabling node 2 to relay updates
. sandbox server started
   node 2 is slave of node 1
. sandbox server started
. sandbox server started
       node 5 is slave of node 2
. sandbox server started
. sandbox server started
       node 6 is slave of node 2
. sandbox server started
. sandbox server started
       node 7 is slave of node 2
. sandbox server started
enabling node 3 to relay updates
. sandbox server started
   node 3 is slave of node 1
. sandbox server started
. sandbox server started
       node 8 is slave of node 3
. sandbox server started
. sandbox server started
       node 9 is slave of node 3
. sandbox server started
enabling node 4 to relay updates
. sandbox server started
   node 4 is slave of node 1
. sandbox server started
. sandbox server started
       node 10 is slave of node 4
. sandbox server started
. sandbox server started
       node 11 is slave of node 4
. sandbox server started
. sandbox server started
       node 12 is slave of node 4

Let's test it:

gmax@gmac3[sandboxes]$ multi_msb_5_0_77/n1 -e "create table test.t1(i int); insert into test.t1 values (@@server_id)"
gmax@gmac3[sandboxes]$ multi_msb_5_0_77/use_all "select * from test.t1"
# server: 1:
i
101
# server: 2:
i
102
# server: 3:
i
103
# server: 4:
i
104
# server: 5:
i
105
# server: 6:
i
106
# server: 7:
i
107
# server: 8:
i
108
# server: 9:
i
109
# server: 10:
i
110
# server: 11:
i
111
# server: 12:
i
112

$ multi_msb_5_0_77/n1 -e "show slave hosts"
+-----------+-------+-------+-------------------+-----------+
| Server_id | Host  | Port  | Rpl_recovery_rank | Master_id |
+-----------+-------+-------+-------------------+-----------+
|       102 | node2 | 19778 |                 0 |       101 |
|       104 | node4 | 19778 |                 0 |       101 |
|       103 | node3 | 19778 |                 0 |       101 |
+-----------+-------+-------+-------------------+-----------+

$ multi_msb_5_0_77/n2 -e "show slave hosts"
+-----------+-------+-------+-------------------+-----------+
| Server_id | Host  | Port  | Rpl_recovery_rank | Master_id |
+-----------+-------+-------+-------------------+-----------+
|       105 | node5 | 19779 |                 0 |       102 |
|       106 | node6 | 19779 |                 0 |       102 |
|       107 | node7 | 19779 |                 0 |       102 |
+-----------+-------+-------+-------------------+-----------+

$ multi_msb_5_0_77/n3 -e "show slave hosts"
+-----------+-------+-------+-------------------+-----------+
| Server_id | Host  | Port  | Rpl_recovery_rank | Master_id |
+-----------+-------+-------+-------------------+-----------+
|       109 | node9 | 19780 |                 0 |       103 |
|       108 | node8 | 19780 |                 0 |       103 |
+-----------+-------+-------+-------------------+-----------+

So, this is it. Now the code needs some testing. Please comment on this blog, and report bugs at the MySQL Sandbox home.