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
Thank you for the post, mysql sandbox save my time. Using this tool it’s so simple to setup and manage multiple mysql instances on the same machine.For me it’s helpful to test different replication topologies,cluster nodes and multi-version mysql testing.
Hello to all
I have been attempting to get just a small replication testing environment set up and I am running into problems. I have mysql-5.5.51-linux2.6-x86_64.tar.gz but when I run make_replication_sandbox mysql-5.5.51-linux2.6-x86_64.tar.gz – it runs and runs builds directories under $SANDBOX_HOME then dies:
installing and starting master
can’t start server
error installing the master
(512 )
I cannot figure this out – I have used sandbox before and have not had this issue. SBDEBUG does not shed a whole of light on the issue. Any help would be most appreciated.
Hi Joseph.
Sorry about the delay. Have a look on your master datadir, there is a file called msandbox.err . This file will tell you why your sandbox isn’t starting.
Please comment here if you have any other questions.