Using MySQL Sandbox to setup a MySQL Fabric cluster in devel_MySQL

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 theFabric Quick Start guidein 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:

• mysql-utilities-1.4.3-osx10.7.dmgMac OS X 10.7 (Architecture Independent), DMG Archive
• mysql-connector-python-1.2.2-osx10.7.dmgMac OS X 10.7 (Architecture Independent), DMG Archive

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

Step 2: Install MySQL Sandbox

MySQL Sandboxis available viaLaunchpadorCPAN. There isn't a major difference between the two, but I happened to choose CPAN.

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

Step 3: Download a 5.6 tarball

In my case, I downloadedmysql-5.6.17-osx10.7-x86.tar.gzMac 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 thefabricschema which stores configuration data. Here is the setup line I used:

$ <strong>make_sandbox mysql-5.6.17-osx10.7-x86.tar.gz</strong>unpacking /Users/morgo/Downloads/mysql-5.6.17-osx10.7-x86.tar.gzExecuting 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 Maxiainstalling with the following parameters:upper_directory				= /Users/morgo/sandboxessandbox_directory			= msb_5_6_17sandbox_port				 = 5617check_port					 =no_check_port				=datadir_from				 = scriptinstall_version				= 5.6basedir						= /Users/morgo/Downloads/5.6.17tmpdir						 =my_file						=operating_system_user		= morgodb_user						= msandboxremote_access				= 127.%bind_address				 = 127.0.0.1ro_user						= msandbox_rorw_user						= msandbox_rwrepl_user					= rsandboxdb_password					= msandboxrepl_password				= rsandboxmy_clause					= log-error=msandbox.errmaster						 =slaveof						=high_performance			 =prompt_prefix				= mysqlprompt_body					=[/h] {/u} (/d) >force						=no_ver_after_name			= 1verbose						=load_grants					= 1no_load_grants				 =no_run						 =no_show						=do you agree? ([Y],n) Yloading grants.. sandbox server startedYour sandbox server was installed in $HOME/sandboxes/msb_5_6_17

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

[DEFAULT]prefix =sysconfdir = /etclogdir = /var/log[storage]address = localhost:5617user = fabricpassword = secretdatabase = fabricauth_plugin = mysql_native_passwordconnection_timeout = 6connection_attempts = 6connection_delay = 1[servers]user = fabricpassword = secret[protocol.xmlrpc]address = localhost:32274threads = 5user = adminpassword = secretdisable_authentication = norealm = MySQL Fabricssl_ca =ssl_cert =ssl_key =[executor]executors = 5[logging]level = INFOurl = file:///var/log/fabric.log[sharding]mysqldump_program = /usr/bin/mysqldumpmysqlclient_program = /usr/bin/mysql[statistics]prune_time = 3600[failure_tracking]notifications = 300notification_clients = 50notification_interval = 60failover_interval = 0detections = 3detection_interval = 6detection_timeout = 1prune_time = 3600[connector]ttl = 1[client]password =

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

$ <strong>$HOME/sandboxes/msb_5_6_17/use -uroot</strong>Welcome to the MySQL monitor.Commands end with ; or /g.Your MySQL connection id is 5Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.mysql [localhost] {root} ((none)) > <strong>CREATE USER 'fabric'@'localhost' IDENTIFIED BY 'secret';</strong>Query OK, 0 rows affected (0.01 sec)mysql [localhost] {root} ((none)) > <strong>GRANT ALL ON fabric.* TO 'fabric'@'localhost';</strong>Query OK, 0 rows affected (0.00 sec)

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

$ <strong>mysqlfabric manage setup</strong>[INFO] 1401222720.664261 - MainThread - Initializing persister: user (fabric), server (localhost:5617), database (fabric).[INFO] 1401222721.245034 - MainThread - Initial password for admin/xmlrpc setPassword set for admin/xmlrpc from configuration file.[INFO] 1401222721.247700 - MainThread - Password set for admin/xmlrpc from configuration file.$ <strong>mysqlfabric manage start</strong>[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:

$ <strong>make_replication_sandbox mysql-5.6.17-osx10.7-x86.tar.gz</strong>installing and starting masterinstalling slave 1installing slave 2starting slave 1.. sandbox server startedstarting slave 2.. sandbox server startedinitializing slave 1initializing slave 2replication directory installed in $HOME/sandboxes/rsandbox_mysql-5_6_17$ <strong>$HOME/sandboxes/rsandbox_mysql-5_6_17/m -uroot</strong>Welcome to the MySQL monitor.Commands end with ; or /g.Your MySQL connection id is 5Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.master [localhost] {root} ((none)) > <strong>GRANT ALL ON *.* TO 'fabric'@'localhost' IDENTIFIED BY 'secret';</strong>Query OK, 0 rows affected (0.01 sec)master [localhost] {root} ((none)) > <strong>SHOW GLOBAL VARIABLES LIKE 'port';</strong>+---------------+-------+| Variable_name | Value |+---------------+-------+| port| 20082 |+---------------+-------+1 row in set (0.00 sec)master [localhost] {root} ((none)) > <strong>exit</strong>Bye

Then with MySQL Fabric:

$ <strong>mysqlfabric group create GLOBAL1</strong>Procedure :{ uuid= 23593cf1-c516-46ef-beaa-516b333353ca,finished= True,success = True,return= True,activities=}$ <strong>mysqlfabric group add GLOBAL1 localhost:20082</strong>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 toswitch to GTIDs:

$ <strong>$HOME/sandboxes/rsandbox_mysql-5_6_17/enable_gtid</strong># 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_17executing "stop" on slave 1executing "stop" on slave 2executing "stop" on master# executing "start" on /Users/morgo/sandboxes/rsandbox_mysql-5_6_17executing "start" on master. sandbox server startedexecuting "start" on slave 1. sandbox server startedexecuting "start" on slave 2. sandbox server started

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

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

We can find group commands with:

$ <strong>mysqlfabric group</strong>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 groupGLOBAL1:

$ <strong>mysqlfabric group lookup_servers GLOBAL1</strong>Command :{ success = Truereturn= [{'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:

$ <strong>mysqlfabric group promote GLOBAL1 --slave_id=e208ca4a-e5df-11e3-9dd5-4cd7500a4330</strong>Procedure :{ uuid= be791f70-be3e-4ee9-9872-e1cedce36791,finished= True,success = True,return= True,activities=}$ <strong>mysqlfabric group lookup_servers GLOBAL1</strong>Command :{ success = Truereturn= [{'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 tosetup additional HA groups for shards- MySQL Sandbox really simplifies the setup by bootstrapping replication.

One caveat to be aware of: Each copy ofmysqldrunning can take up quite a bit of RAM. It is possible totune this downusing some steps that I mentioned in an earlier post.