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:

mysql-utilities-1.4.3-osx10.7.dmg Mac OS X 10.7 (Architecture Independent), DMG Archive

mysql-connector-python-1.2.2-osx10.7.dmg Mac 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 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.