May
20
2014
MySQL Sandbox
Posted by Marcelo Altmann on Tue 20 May 2014 12:56 UTC
Tags:

Hi there, today we will learn about an amazing tool that every single MySQL dba must know, I’m talking about MySQL Sandbox.

MySQL Sandbox is developed by Giuseppe Maxia (The Data Charmer), it’s a tool that make the installation of MySQL servers very easy. If you need to quickly create a MySQL instance for test or a replication setup(it supports master slave, circular and master master replication), this it the tool.

INSTALLATION:

Go to http://mysqlsandbox.net/ and get the latest version (I got from launchpad): 

yum install perl perl-ExtUtils-MakeMaker perl-Test-Simple
wget https://launchpad.net/mysql-sandbox/mysql-sandbox-3/mysql-sandbox-3/+download/MySQL-Sandbox-3.0.44.tar.gz
tar -zxvf MySQL-Sandbox-3.0.44.tar.gz
cd MySQL-Sandbox-3.0.44
perl Makefile.PL
make
make test
make install

CREATING A SINGLE SANDBOX:

To create a single sandbox, all you need is the mysql package that you want install and the make_sandbox command: 

[root@localhost ~]# make_sandbox mysql-5.6.17-linux-glibc2.5-i686.tar.gz 
unpacking /root/mysql-5.6.17-linux-glibc2.5-i686.tar.gz
Executing low_level_make_sandbox --basedir=/root/5.6.17 \
        --sandbox_directory=msb_5_6_17 \
        --install_version=5.6 \
        --sandbox_port=5617 \
        --no_ver_after_name \
        --my_clause=log-error=msandbox.err
    The MySQL Sandbox,  version 3.0.44
    (C) 2006-2013 Giuseppe Maxia
installing with the following parameters:
upper_directory                = /root/sandboxes
sandbox_directory              = msb_5_6_17
sandbox_port                   = 5617
check_port                     = 
no_check_port                  = 
datadir_from                   = script
install_version                = 5.6
basedir                        = /root/5.6.17
tmpdir                         = 
my_file                        = 
operating_system_user          = root
db_user                        = msandbox
remote_access                  = 127.%
bind_address                   = 127.0.0.1
ro_user                        = msandbox_ro
rw_user                        = msandbox_rw
repl_user                      = rsandbox
db_password                    = msandbox
repl_password                  = rsandbox
my_clause                      = log-error=msandbox.err
master                         = 
slaveof                        = 
high_performance               = 
prompt_prefix                  = mysql
prompt_body                    =  [\h] {\u} (\d) > 
force                          = 
no_ver_after_name              = 1
verbose                        = 
load_grants                    = 1
no_load_grants                 = 
no_run                         = 
no_show                        = 
do you agree? ([Y],n) 
loading grants
.. sandbox server started
Your sandbox server was installed in $HOME/sandboxes/msb_5_6_17

To use it you can call the use script inside the sandbox folder: 

[root@localhost ~]# $HOME/sandboxes/msb_5_6_17/use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

You can manage your sandbox by calling the start / stop / restart / status script inside the sandbox folder

CREATING A MASTER SLAVE REPLICATION:

To create a master slave replication topology (by default is set to 1 master and 2 slaves but it can be changed passing the --how_many_nodes parameter) we will use the make_replication_sandbox command: 

[root@localhost ~]# make_replication_sandbox mysql-5.6.17-linux-glibc2.5-i686.tar.gz 
installing and starting master
installing slave 1
installing slave 2
starting slave 1
.... sandbox server started
starting slave 2
.. sandbox server started
initializing slave 1
initializing slave 2
replication directory installed in $HOME/sandboxes/rsandbox_mysql-5_6_17

To use it we can call the use script, for replication, the use script will be located inside the nodeN/Master folder: 

[root@localhost ~]# #MASTER
[root@localhost ~]# /root/sandboxes/rsandbox_mysql-5_6_17/master/use 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

master [localhost] {msandbox} ((none)) > ^DBye
[root@localhost ~]# #SLAVE 1
[root@localhost ~]# /root/sandboxes/rsandbox_mysql-5_6_17/node1/use 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

slave1 [localhost] {msandbox} ((none)) > ^DBye
[root@localhost ~]# #SLAVE 2
[root@localhost ~]# /root/sandboxes/rsandbox_mysql-5_6_17/node2/use 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

slave2 [localhost] {msandbox} ((none)) >

On replication sandbox, you can manage the individual sandbox by calling the start / stop / restart / status script inside the node / master folder or you can call the scripts ending with _all located on the sandbox folder (start_all / stop_all / restart_all / status_all).

CREATING A MULTI MASTER REPLICATION:

To create a multi-master sandbox we will use the make_replication_sandbox with --master_master option: 

[root@localhost ~]# make_replication_sandbox --master_master mysql-5.6.17-linux-glibc2.5-i686.tar.gz
installing node 1
installing node 2
# server: 1: 
# server: 2: 
# server: 1: 
# server: 2: 
Circular replication activated
group directory installed in $HOME/sandboxes/rcsandbox_mysql-5_6_17

That is it, you can find more information using the help parameter on make_multiple_custom_sandbox make_multiple_sandbox make_replication_sandbox make_sandbox make_sandbox_from_installed and make_sandbox_from_source