Using MySQL Sandbox to setup a MySQL Fabric cluster in development

With Fabric’s official GA release this week, I thought I would post a spin on how to setup a development environment loosely based on the Fabric Quick Start guide in the manual.

The notable change, is the use of MySQL Sandbox for bootstrapping each of the MySQL instances.

Step 1: Install the MySQL Utilities + Python Connector

In my case, I downloaded:

The GUI install in both cases works as expected. Next, Next, Done!

Step 2: Install MySQL Sandbox

MySQL Sandbox is available via Launchpad or CPAN. There isn’t a major difference between the two, but I happened to choose CPAN.

The advantage of installing Sandbox over a utility such as mysqld_multi, is that it’s much easier to setup and tear down replication.

Step 3: Download a 5.6 tarball

In my case, I downloaded mysql-5.6.17-osx10.7-x86.tar.gz Mac OS X 10.7 (x86, 32-bit), Compressed TAR Archive. On Mac OS X it is useful to use Google Chrome rather than Safari, as you do not want the file automatically decompressed.

Important: Download only! MySQL Sandbox does the install in the next step.

Step 4: Setup the Fabric Backing Store

The first copy of MySQL to setup is the MySQL Fabric backing store. This will house the fabric schema which stores configuration data. Here is the setup line I used:

$ make_sandbox mysql-5.6.17-osx10.7-x86.tar.gz
unpacking /Users/morgo/Downloads/mysql-5.6.17-osx10.7-x86.tar.gz
Executing low_level_make_sandbox --basedir=/Users/morgo/Downloads/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.43
    (C) 2006-2013 Giuseppe Maxia
installing with the following parameters:
upper_directory                = /Users/morgo/sandboxes
sandbox_directory              = msb_5_6_17
sandbox_port                   = 5617
check_port                     =
no_check_port                  =
datadir_from                   = script
install_version                = 5.6
basedir                        = /Users/morgo/Downloads/5.6.17
tmpdir                         =
my_file                        =
operating_system_user          = morgo
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) Y
loading grants
.. sandbox server started
Your sandbox server was installed in $HOME/sandboxes/msb_5_6_17

Using a configuration similar to the MySQL manual, with the password simply being secret I saved the following to /etc/mysql/fabric.cfg:

[DEFAULT]
prefix =
sysconfdir = /etc
logdir = /var/log

[storage]
address = localhost:5617
user = fabric
password = secret
database = fabric
auth_plugin = mysql_native_password
connection_timeout = 6
connection_attempts = 6
connection_delay = 1

[servers]
user = fabric
password = secret

[protocol.xmlrpc]
address = localhost:32274
threads = 5
user = admin
password = secret
disable_authentication = no
realm = MySQL Fabric
ssl_ca =
ssl_cert =
ssl_key =

[executor]
executors = 5

[logging]
level = INFO
url = file:///var/log/fabric.log

[sharding]
mysqldump_program = /usr/bin/mysqldump
mysqlclient_program = /usr/bin/mysql

[statistics]
prune_time = 3600

[failure_tracking]
notifications = 300
notification_clients = 50
notification_interval = 60
failover_interval = 0
detections = 3
detection_interval = 6
detection_timeout = 1
prune_time = 3600

[connector]
ttl = 1

[client]
password =

I also need to create the user in MySQL for Fabric to connect as:

$ $HOME/sandboxes/msb_5_6_17/use -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
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] {root} ((none)) > CREATE USER 'fabric'@'localhost' IDENTIFIED BY 'secret';
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {root} ((none)) > GRANT ALL ON fabric.* TO 'fabric'@'localhost';
Query OK, 0 rows affected (0.00 sec)

Fabric can then install the system tables, and be started with:

$ mysqlfabric manage setup
[INFO] 1401222720.664261 - MainThread - Initializing persister: user (fabric), server (localhost:5617), database (fabric).
[INFO] 1401222721.245034 - MainThread - Initial password for admin/xmlrpc set
Password set for admin/xmlrpc from configuration file.
[INFO] 1401222721.247700 - MainThread - Password set for admin/xmlrpc from configuration file.
$ mysqlfabric manage start
[INFO] 1401222753.862997 - MainThread - Initializing persister: user (fabric), server (localhost:5617), database (fabric).
[INFO] 1401222753.867357 - MainThread - Loading Services.
[INFO] 1401222753.902501 - MainThread - Fabric node starting.
[INFO] 1401222753.910188 - MainThread - Starting Executor.
[INFO] 1401222753.910300 - MainThread - Setting 5 executor(s).
[INFO] 1401222753.910602 - Executor-0 - Started.
[INFO] 1401222753.911354 - Executor-1 - Started.
[INFO] 1401222753.912450 - Executor-2 - Started.
[INFO] 1401222753.913185 - Executor-3 - Started.
[INFO] 1401222753.914456 - Executor-4 - Started.
[INFO] 1401222753.914701 - MainThread - Executor started.
[INFO] 1401222753.927123 - MainThread - Starting failure detector.
[INFO] 1401222753.928365 - XML-RPC-Server - XML-RPC protocol server ('127.0.0.1', 32274) started.
[INFO] 1401222753.928731 - XML-RPC-Server - Setting 5 XML-RPC session(s).
[INFO] 1401222753.928991 - XML-RPC-Session-0 - Started XML-RPC-Session.
[INFO] 1401222753.929455 - XML-RPC-Session-1 - Started XML-RPC-Session.
[INFO] 1401222753.930168 - XML-RPC-Session-2 - Started XML-RPC-Session.
[INFO] 1401222753.930747 - XML-RPC-Session-3 - Started XML-RPC-Session.
[INFO] 1401222753.931320 - XML-RPC-Session-4 - Started XML-RPC-Session.

Step 5: Build out a HA group

The next step is to bootstrap a 3-node replication sandbox, and then add it as a HA Group in Fabric.

First with MySQL Sandbox:

$ make_replication_sandbox mysql-5.6.17-osx10.7-x86.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
$ $HOME/sandboxes/rsandbox_mysql-5_6_17/m -uroot
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.

master [localhost] {root} ((none)) > GRANT ALL ON *.* TO 'fabric'@'localhost' IDENTIFIED BY 'secret';
Query OK, 0 rows affected (0.01 sec)

master [localhost] {root} ((none)) > SHOW GLOBAL VARIABLES LIKE 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 20082 |
+---------------+-------+
1 row in set (0.00 sec)

master [localhost] {root} ((none)) > exit
Bye

Then with MySQL Fabric:

$ mysqlfabric group create GLOBAL1
Procedure :
{ uuid        = 23593cf1-c516-46ef-beaa-516b333353ca,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}
$ mysqlfabric group add GLOBAL1 localhost:20082
Procedure :
{ uuid        = e72decdf-6524-45b4-9eb6-6d757c0daf3a,
  finished    = True,
  success     = False,
  return      = ServerError: Server (e208ca4a-e5df-11e3-9dd5-4cd7500a4330) does not have the binary log or gtid enabled.,
  activities  =
}

An error! What this error is saying, is that Fabric is designed to specifically take advantage of Replication with GTIDs which are designed to handle smoother failover if the topology changes. Thankfully, MySQL Sandbox has an easy way of being able to switch to GTIDs:

$ $HOME/sandboxes/rsandbox_mysql-5_6_17/enable_gtid
# option 'master-info-repository=table' added to master configuration file
# option 'relay-log-info-repository=table' added to master configuration file
# option 'gtid_mode=ON' added to master configuration file
# option 'log-slave-updates' added to master configuration file
# option 'enforce-gtid-consistency' added to master configuration file
# option 'master-info-repository=table' added to node1 configuration file
# option 'relay-log-info-repository=table' added to node1 configuration file
# option 'gtid_mode=ON' added to node1 configuration file
# option 'log-slave-updates' added to node1 configuration file
# option 'enforce-gtid-consistency' added to node1 configuration file
# option 'master-info-repository=table' added to node2 configuration file
# option 'relay-log-info-repository=table' added to node2 configuration file
# option 'gtid_mode=ON' added to node2 configuration file
# option 'log-slave-updates' added to node2 configuration file
# option 'enforce-gtid-consistency' added to node2 configuration file
# executing "stop" on /Users/morgo/sandboxes/rsandbox_mysql-5_6_17
executing "stop" on slave 1
executing "stop" on slave 2
executing "stop" on master
# executing "start" on /Users/morgo/sandboxes/rsandbox_mysql-5_6_17
executing "start" on master
. sandbox server started
executing "start" on slave 1
. sandbox server started
executing "start" on slave 2
. sandbox server started

With this change, adding servers to Fabric will now work:

$ mysqlfabric group add GLOBAL1 localhost:20082
Procedure :
{ uuid        = c9da0536-0f3f-4d75-9f29-1ac6c12ff686,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}
$ mysqlfabric group add GLOBAL1 localhost:20083
Procedure :
{ uuid        = 92b42645-311f-423b-86b4-3a56870db244,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}
$ mysqlfabric group add GLOBAL1 localhost:20084
Procedure :
{ uuid        = 1f16a586-4a60-4b7d-a339-be3830614984,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}

We can find group commands with:

$ mysqlfabric group

Commands available in group 'group' are:
    group activate group_id  [--synchronous]
    group description group_id  [--description=NONE] [--synchronous]
    group deactivate group_id  [--synchronous]
    group create group_id  [--description=NONE] [--synchronous]
    group remove group_id server_id  [--synchronous]
    group add group_id address  [--timeout=5] [--update_only] [--synchronous]
    group health group_id
    group lookup_servers group_id  [--server_id=NONE] [--status=NONE] [--mode=NONE]
    group destroy group_id  [--force] [--synchronous]
    group demote group_id  [--update_only] [--synchronous]
    group promote group_id  [--slave_id=NONE] [--update_only] [--synchronous]
    group lookup_groups  [--group_id=NONE]

Of which the useful option will be to list the servers in our group GLOBAL1:

$ mysqlfabric group lookup_servers GLOBAL1
Command :
{ success     = True
  return      = [{'status': 'SECONDARY', 'server_uuid': 'e208ca4a-e5df-11e3-9dd5-4cd7500a4330', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': 'localhost:20082'}, {'status': 'SECONDARY', 'server_uuid': 'e931694e-e5df-11e3-9dd5-4cd64dfe115b', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': 'localhost:20083'}, {'status': 'SECONDARY', 'server_uuid': 'ea61dfba-e5df-11e3-9dd5-4bd23bb2cbe3', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': 'localhost:20084'}]
  activities  =
}

The UUID in use here, is the same one that is used for Replication with GTIDs. To promote the first server as a master:

$ mysqlfabric group promote GLOBAL1 --slave_id=e208ca4a-e5df-11e3-9dd5-4cd7500a4330
Procedure :
{ uuid        = be791f70-be3e-4ee9-9872-e1cedce36791,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}
$ mysqlfabric group lookup_servers GLOBAL1
Command :
{ success     = True
  return      = [{'status': 'PRIMARY', 'server_uuid': 'e208ca4a-e5df-11e3-9dd5-4cd7500a4330', 'mode': 'READ_WRITE', 'weight': 1.0, 'address': 'localhost:20082'}, {'status': 'SECONDARY', 'server_uuid': 'e931694e-e5df-11e3-9dd5-4cd64dfe115b', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': 'localhost:20083'}, {'status': 'SECONDARY', 'server_uuid': 'ea61dfba-e5df-11e3-9dd5-4bd23bb2cbe3', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': 'localhost:20084'}]
  activities  =
}

Next steps

This How-to can easily be extended to setup additional HA groups for shards – MySQL Sandbox really simplifies the setup by bootstrapping replication.

One caveat to be aware of: Each copy of mysqld running can take up quite a bit of RAM. It is possible to tune this down using some steps that I mentioned in an earlier post.

Published by

morgo

I joined MySQL AB in 2006, left, and am now back at Oracle working on the MySQL team. I’ve also worked at Percona and InPowered.