MySQL Fabric : Testing High Availability Fail Over

Tine to test MySQL Fabric’s High Availability fail over. Last entry I set up a three node Fabric HA farm. This time I am going to ‘pull the plug’ on the servers in the farm and see if Fabric will sense the loss of the server and gracefully keep working.

To sum up the setup, I am using a laptop as the Fabric Controller and three virtual servers for the farm. The virtual servers are Vagrant instances

Example of Fabric environment tested
MySQL Fabric HA Diagram — Nice illustration of tested environment
creatively named db1 at 10.10.13.10, db2 at 10.10.13.20, and db3 at 10.10.13.30. All the MySQL instances are 5.6.23 and the MySQL Utilities are 1.6.1-alpha. See the last blog entry for details.

The MySQL Fabric HA farm has been started.
mysqlfabric group lookup_servers myfarm
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

server_uuid address status mode weight
------------------------------------ ----------- --------- ---------- ------
09d13be1-cdd0-11e4-8643-0800273b9c80 10.10.13.10 SECONDARY READ_ONLY 1.0
0f611996-cdd0-11e4-8643-0800273b9c80 10.10.13.20 SECONDARY READ_ONLY 1.0
11aae7e7-cdd0-11e4-8643-0800273b9c80 10.10.13.30 PRIMARY READ_WRITE 1.0

Number ’30 is the PRIMARY node. A simple test program’s connector queries the farm and is actually talking to ’30. So time to issue vagrant down db3 to power off that server.

With db3 shut down, the test program now is communicating with ’20. So far, so very good.

mysqlfabric group lookup_servers myfarm
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

server_uuid address status mode weight
------------------------------------ ----------- --------- ---------- ------
09d13be1-cdd0-11e4-8643-0800273b9c80 10.10.13.10 SECONDARY READ_ONLY 1.0
0f611996-cdd0-11e4-8643-0800273b9c80 10.10.13.20 PRIMARY READ_WRITE 1.0
11aae7e7-cdd0-11e4-8643-0800273b9c80 10.10.13.30 FAULTY READ_WRITE 1.0

MySQL Fabric has gracefully promoted ’20 from SECONDARY to PRIMARY. Time to bring down ’20!

mysqlfabric group lookup_servers myfarm
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

server_uuid address status mode weight
------------------------------------ ----------- ------- ---------- ------
09d13be1-cdd0-11e4-8643-0800273b9c80 10.10.13.10 PRIMARY READ_WRITE 1.0
0f611996-cdd0-11e4-8643-0800273b9c80 10.10.13.20 FAULTY READ_WRITE 1.0
11aae7e7-cdd0-11e4-8643-0800273b9c80 10.10.13.30 FAULTY READ_WRITE 1.0

And the test program is chatting with ’10.

MySQL Fabric gracefully handles the failure of two out of the three nodes in the High Availability farm.

And for the pedantic, shutting off the last server in the farm will generate a ‘Connection with Fabric failed’ message.

MySQL Fabric — Setup new High Availability Group and USE it

Back to MySQL Fabric! Travel, conferences, and other work have kept me away for too long. So lets start by setting up a new High Availability MySQL Fabric farm and query it for some data. I started with a new install to make sure I was getting everything covered so that one day I will wrap all this up in a comprehensive ‘cookbook’ style article so that anyone can go from Zero to Fabric Hero.

So the first step was to set up my test system with a fresh install of Ubuntu 14.04. Next was the setup of three ‘guest’ Unbuntu 14.04 virtual servers with Vagrant that will comprise the actual HA farm. Next came the installation of MySQL 5.6.23 from the MySQL Apt Repository on the Fabric Controller and the virtual servers. Next comes the installation of the MySQL Utilities on the Fabric Controller system along with the Python Connector. And this time I went with the MySQL Utilities 1.6.1-alpha to get the latest and greatest version of the Fabric software and the Utilities. Both the Fabric and Utilities team have been very busy working the coding magic and it shows.

Next comes setting up Replication between three guest servers. I set up the account for replication and the grants but did not start replication. I installed the good ol’ World database on the first of the virtual servers and then used mysqldbcopy to copy the database over to the other two virtual servers in a quick fashion. Now the replication farm was complete and it was time to get back to the Fabric controller system.

Editing the /etc/mysql/fabric.cfg file, I put in a handy password where indicated. Then came the moment of truth — setting up Fabric and getting it running.

On the system that will be the Fabric Controller run the following:

fabric manage setup
[INFO] 1427317836.840587 - MainThread - Initializing persister: user (fabric), server (localhost:3306), database (fabric).
[INFO] 1427317846.353911 - MainThread - Initial password for admin/mysql set
Password set for admin/mysql from configuration file.
[INFO] 1427317846.388624 - MainThread - Password set for admin/mysql from configuration file.
[INFO] 1427317846.389658 - MainThread - Initial password for admin/xmlrpc set
Password set for admin/xmlrpc from configuration file.
[INFO] 1427317846.430082 - MainThread - Password set for admin/xmlrpc from configuration file.

The 1.6.1-alpha code has a new documentation page 8.2.3.1. Create the Associated MySQL Users for setting up various Fabric users. There are now accounts for Backing Store, Server User, Backup User, and Restore User and the recommended grants for each. Here I found a glitch as later on I had complaints that various users, created as per the docs, did not have all the permissions they needed. So I took a not-recommended-for-production-or-sanity shot cut and did a GRANT ALL on the account.

The fabric manage setup command creates a database named fabric. Currently there are nineteen tables in the database. I will have to blog about the contents in another post.

Now the Fabric magic can begin. Issue sudo mysqlfabric manage start --daemonize to get Fabric started and then we can add a group. For this example the farm will be called myfarm. mysqlfabric group create myfarm, use group lookup_servers myfarm to be certain the group is as desired.

Add servers to the group with mysqlfabric group add myfarm 10.10.13.x for each of the virtual servers. I set the server_ids to the last IP address number, so 10.10.13.10 was sever_id 10 and so forth.

Now it is time to see how the various severs on the farm are getting along.

mysqlfabric group lookup_servers myfarm
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

server_uuid address status mode weight
------------------------------------ ----------- --------- --------- ------
09d13be1-cdd0-11e4-8643-0800273b9c80 10.10.13.10 SECONDARY READ_ONLY 1.0
0f611996-cdd0-11e4-8643-0800273b9c80 10.10.13.20 SECONDARY READ_ONLY 1.0
11aae7e7-cdd0-11e4-8643-0800273b9c80 10.10.13.30 SECONDARY READ_ONLY 1.0

Note all the servers are SECONDARY and READ-ONLY.

Was the replication okay? A quick peek to make sure all was covered in that regard.
mysqlrpladmin --master=root:hidave@10.10.13.30 --slaves=root:hidave@10.10.13.10,root:hidave@10.10.13.20 health
WARNING: Using a password on the command line interface can be insecure.
# Checking privileges.
#
# Replication Topology Health:
+--------------+-------+---------+--------+------------+---------+
| host | port | role | state | gtid_mode | health |
+--------------+-------+---------+--------+------------+---------+
| 10.10.13.30 | 3306 | MASTER | UP | ON | OK |
| 10.10.13.10 | 3306 | SLAVE | UP | ON | OK |
| 10.10.13.20 | 3306 | SLAVE | UP | ON | OK |
+--------------+-------+---------+--------+------------+---------+
# ...done.

Replication looks just spiffy!

Time to create some magic by getting our HA group online.
mysqlfabric group promote myfarm
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

uuid finished success result
------------------------------------ -------- ------- ------
c4a60dd8-4bc5-4b52-9f6c-dd5443a95ce8 1 1 1

state success when description
----- ------- ------------- ------------------------------------------------------------------
3 2 1427828257.29 Triggered by .
4 2 1427828257.33 Executing action (_define_ha_operation).
5 2 1427828257.47 Executed action (_define_ha_operation).
3 2 1427828257.44 Triggered by .
4 2 1427828257.47 Executing action (_find_candidate_fail).
5 2 1427828257.6 Executed action (_find_candidate_fail).
3 2 1427828257.56 Triggered by .
4 2 1427828257.6 Executing action (_check_candidate_fail).
5 2 1427828257.68 Executed action (_check_candidate_fail).
3 2 1427828257.64 Triggered by .
4 2 1427828257.68 Executing action (_wait_slave_fail).
5 2 1427828257.84 Executed action (_wait_slave_fail).
3 2 1427828257.79 Triggered by .
4 2 1427828257.84 Executing action (_change_to_candidate).
5 2 1427828258.17 Executed action (_change_to_candidate).

Hmmm, a lot of information there but IS IT WORKING?? Quick run mysqlfabric group lookup_servers myfarm again!

mysqlfabric group lookup_servers myfarm
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

server_uuid address status mode weight
------------------------------------ ----------- --------- ---------- ------
09d13be1-cdd0-11e4-8643-0800273b9c80 10.10.13.10 SECONDARY READ_ONLY 1.0
0f611996-cdd0-11e4-8643-0800273b9c80 10.10.13.20 SECONDARY READ_ONLY 1.0
11aae7e7-cdd0-11e4-8643-0800273b9c80 10.10.13.30 PRIMARY READ_WRITE 1.0

Yes! We no have a PRIMARY and a pair of SECONDARY servers. But are they healthy?

mysqlfabric group health myfarm
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

uuid is_alive status is_not_running is_not_configured io_not_running sql_not_running io_error sql_error
------------------------------------ -------- --------- -------------- ----------------- -------------- --------------- -------- ---------
09d13be1-cdd0-11e4-8643-0800273b9c80 1 SECONDARY 0 0 0 0 False False
0f611996-cdd0-11e4-8643-0800273b9c80 1 SECONDARY 0 0 0 0 False False
11aae7e7-cdd0-11e4-8643-0800273b9c80 1 PRIMARY 0 0 0 0 False False

And then the High Availability monitoring is started by mysqlfabric group activate myfarm. Those are the steps for setting up a HA Fabric farm.

So lets write a test program, in Python, to see whom we talk to when we connect to the HA farm.

import mysql.connector
from mysql.connector import fabric

fabric_host = "localhost"
fabric_user = "admin"
fabric_password = "hidave"
fabric_group = "myfarm"

cnx = mysql.connector.connect(fabric = {'host' : fabric_host,
'username' : fabric_user,
'report_errors' : True,
'password' : fabric_password
} ,
user='root', password='hidave', autocommit = True, database='world')
cnx.set_property(group="myfarm")
cursor = cnx.cursor()

query = ("SHOW VARIABLEs LIKE 'server_id'")

cursor.execute(query)

for (server_id, value) in cursor:
print server_id, value
cursor.close()
cnx.close()

And when executed the program proudly returned:
python test01.py
server_id 30

Woo-hoo! The test talked to the server at 10.10.13.30! So we no have a three node MySQL Fabric Highly Available server farm.

A Tale of Two Conferences — Midwest PHP and Kansas Linuxfest

MySQL and I were proud to present talks at two recent events — Midwest PHP and Kansas Linuxfest — and it made me realize how vital the smaller shows are to the Opensource Community at large and MySQL in particular. I go to many larger shows like SCaLE where thousands attend. But is the smaller shows where I really get a chance to see what folks are doing with MySQL, learn about new software, and have a reasonable chance to enjoy the ‘hallway track’.

Both these shows would be considered small. Just over two hundred for MidwestPHP and somewhere in the one forties for Kansas. But both shows brought in top speakers from far and wide and combined them with top local talent. Often time the local talents are the real gems as they are often new faces with great ideas. Not all the great ideas are in the Silicon Valley or Austin.

MidwestPHP has been around for a few years and the speakers who attend know we will get spoiled by the organizers. This year many of us were humbled while playing ‘Cards Against Humanity’ by a rabbi at the speakers dinner social. We also know that the audience is coming to learn, will ask hard questions, and you had better bring your ‘A-game’. This is a PHP crowd that is looking for the latest and greatest information on their language of choice but are also actively looking at how to best incorporate CI, Databases, Containers, and other related tech.

This was the first year for the Kansas Linuxfest and in many ways it was like a family reunion. ‘Oh, you are the Linux family from Wichita? We’re the Linux family from Lawrence!’. This first year was as much establishing ties to the various Linux users in the state of Kansas as it was a Linuxfest. Kansas Linux Fest Logo There were a wide variety of subjects covered from Python & Pex to Go and often times two or three great talks where scheduled at the same time.

Both shows will be back in 2016 and you should seriously consider putting time on your calendar for them. If you have a local opensource event or user group in your area, it is well worth the effort to connect with them. It is not only a valuable way to network with others like yourself but you can learn from others.

I recommend going to a least one session on a subject you know little or nothing about. It often will spark ideas to aid in current projects or provide insights on how others approach challenges.

The big shows are great and I enjoy them. But seek out a smaller show as you get a much better chance to interact with those around you. The smaller regional shows also provide better grounds for hunting for folks to add to your professional network.

And for those of you attending Collaborate 15 please see me at the MySQL Demo Pod.

Toad for MySQL

Long before MySQL, DBAs have been using Toad. Toad is short for Tool for Oracle Application Developers and I remember the Oracle DBAs raving about how TOAD made so many things simpler. Now it is many years later and Dell has released TOAD for MySQL.

On the product page we are promised Toad™ for MySQL is a freeware development tool that enables you to quickly and efficiently create and execute queries, automate database object management, and develop SQL code. Toad MySQL provides utilities to compare, extract, and search for objects. The MySQL tool also enables you to record and play back keyboard commands, and transfer data across MySQL databases. The MySQL developer tool increases efficiency by helping you manage projects, import/export data, and administer the database. Sounds pretty good!

But how does it stack up against MySQL Workbench on my Windows Laptop?

Microsoft .NET Framework 4.5 is required before installing Toad.

Toad For MySQL? Yup, Toad is now available for MySQL
Toad For MySQL? Yup, Toad is now available for MySQL
Starting ‘Toad for MySQL Freeware’ starts off with links for video tutorials, other freeware, blogs, and a link to visit the Toad for MySQL Community.

Connections
Connecting to Toad is similar to Workbench but then there is just so much information to build a DSN.

Entity Relationship Mapping
Tables are dragged manually from the Object Explorer to the diagram window and foreign key mappings is automatically drawn. This is in contrast to Workbench that maps all tables.

Queries

Queries and results from that query in a separate pane.
Queries and results from that query in a separate pane.
Toad will provide hints for columns or keywords as you type queries. You can not get an Explain Plan as as their is an odd error about violation of a constraint and their is no Visual Explain. You do have an option for a drag-n-drop pivot table from query results. You can created tables, edit existing tables, drop tables and all the functions you would expect.

Build Queries
You can drag-n-drop to build queries. I grabbed the world.City table, dropped it on the canvas, and clicked on the Name Column. Next I dragged the Country table and clicked on that tables’ Name column. Toad automatically generated the SQL to to perform an inner join. And that is cool and should help infrequent query writers.

Not Workbench
Workbench has more functionality for things like system administration, backups, import, user admin, system statistics, and DBA oriented work.

So What IS Toad?
Toad is a query tool and fantastic for those who do not need all the admin functionality of Workbench. If you write SQL this may be an option for you. There is no Explain, Visual Explain, or any info on a query plan. And the ability to drag-n-drop tables may be what you need.

I will tray incorporating Toad in my normal work and see what else pops up.

RMOUG and SCaLE 13x this week

Denver and Los Angeles this week for RMOUG Training Days and SCaLE13x!

The Rocky Mountain User Group’s annual Training Days takes over the Colorado Convention Center this week. Over 130 sessions including my MySQL for Oracle DBAs and MySQL Workbench and Visual Explain.

Sakila Toy Dolphin
Plush Sakila MySQL mascot toy

Then a quick dash to LAX for the Friday MySQL Track at SCaLE 13x. That is right, a full day of MySQL topics. I will be speaking on MySQL’s NoSQL and The Proper Care and Feeding of a MySQL Server for Busy Linux Admins. Be sure to drop by our booth at SCaLE for swag, see MySQL 5.7, and MySQL Workbench.

MySQL Fabric — Three Node High Availability Server Farm

So how do you use MySQL Fabric to set up a Highly Available Server Farm? The last two postings in this series were on installing Fabric on a master and then setting up slaves. Now it is time to get get the Fabric Farm started.

The Fabric controller is node number 1 and the slaves are 10, 20, and 30 at IP 10.10.13.1, 10, 20, and 30 respectively. I am keeping with the last octet of the IP addresses for clarity. I am trying to recreate the Fabric Farm from this image. Highly Available Fabric Farm

On the 10, 20, and 30 systems we need to add the following to the mysld section of the my.cnf
[mysqld]
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
report-host=192.168.0.201
report-port=3306
server-id=1
log-bin=mysql1-bin.log

Next set up replication grants on the data nodes and the controller. We want the ability to use any of the slaves and the READ-WRITE master plus be able to switch them at will.
mysql> CHANGE MASTER TO MASTER_HOST='10.10.13.10', MASTER_USER='replication', MASTER_PASSWORD='*****', MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
I started out with 10 as the replication master and used the above on 20 and 30.

A 5.7 Problem
The data farm machines are running MySQL 5.6 but my Fabric controller is running 5.7. If you are also running 5.7 note that by default your SQL MODE is strict and you will get
mysqlfabric manage setup
[INFO] 1423080020.902196 – MainThread – Initializing persister: user (Fabric), server (localhost:3306), database (fabric).
Error: Command (CREATE TABLE log (subject VARCHAR(40) NOT NULL, reported TIMESTAMP /*!50604 (6) */ NOT NULL, reporter VARCHAR(64) NOT NULL, message TEXT, category int NOT NULL, type int NOT NULL, INDEX key_subject_reported (subject, reported), INDEX key_reporter (reporter), INDEX key_reported (reported), INDEX key_category (category), INDEX key_type (type)), ()) failed accessing (localhost:3306). 1067 (42000): Invalid default value for ‘reported'

Opps! 5.7 has STRICT for SQL MODE as the default and I assumed reported was being fed no data. So it was time to set the default SQL MODE to ANSI and restart my Fabric controller’s instance.

So Create the Fabric Database already!
mysqlfabric manager setup creates the necessary tables in the Fabric state store. These tables will be used to store the topology information. The admin user created here can be used to create other users that can connect to fabric as can be seen below. And this command needs to be run only once.

mysqlfabric manage setup
[INFO] 1423080461.323271 - MainThread - Initializing persister: user (Fabric), server (localhost:3306), database (fabric).
Finishing initial setup
=======================
Password for admin user is not yet set.
Password for admin/xmlrpc:
Repeat Password:
Password set.
Password set.
No result returned

Side note 1: For over over a decade I have been typing the five letter m-y-s-q-l group alone for a good part of my professional workday and my fingers have a hard time adding f-a-b-r-i-c. Fellow old timers take care.
Side note 2: It can get confusion which password for which function is being asked for at any one time. I am sure more familiarity on my part would help but I can see folks getting confused easily.
Side note 3: In the early days of MySQL cluster I had to dig into the various pieces as it was not intuitive how things fit together and Fabric for me is the same way. I hope more time will on Fabric will build familiarity.

Orville and Wilbur Wright Time
mysqlfabric manage start &
[1] 12690
dstokes@dstokes-E7240:~/vagrant$ [INFO] 1423080590.563764 - MainThread - Initializing persister: user (Fabric), server (localhost:3306), database (fabric).
[INFO] 1423080590.571004 - MainThread - Loading Services.
[INFO] 1423080590.585766 - MainThread - MySQL-RPC protocol server started, listening on localhost:32275
[INFO] 1423080590.592161 - MainThread - Fabric node starting.
[INFO] 1423080590.596949 - MainThread - Starting Executor.
[INFO] 1423080590.597056 - MainThread - Setting 5 executor(s).
[INFO] 1423080590.597318 - Executor-0 - Started.
[INFO] 1423080590.597674 - Executor-1 - Started.
[INFO] 1423080590.597998 - Executor-2 - Started.
[INFO] 1423080590.598294 - Executor-3 - Started.
[INFO] 1423080590.599090 - Executor-4 - Started.
[INFO] 1423080590.599224 - MainThread - Executor started.
[INFO] 1423080590.607582 - MainThread - Starting failure detector.
[INFO] 1423080590.609208 - XML-RPC-Server - XML-RPC protocol server ('127.0.0.1', 32274) started.
[INFO] 1423080590.609764 - XML-RPC-Server - Setting 1 XML-RPC session(s).
[INFO] 1423080590.610071 - XML-RPC-Session-0 - Started XML-RPC-Session.

Now the three data nodes can be turned into a server farm.
mysqlfabric group create davesfarm
Password for admin:
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

uuid finished success result
------------------------------------ -------- ------- ------
cc54e1b2-4e54-4d02-99b4-3692d35fcce1 1 1 1

state success when description
----- ------- ------------- ------------------------------------------------------------------
3 2 1423080661.24 Triggered by .
4 2 1423080661.25 Executing action (_create_group).
5 2 1423080661.27 Executed action (_create_group).

This farm is called davesfarm by me but just 5ca1ab1e-a007-feed-f00d-cab3fe13249e by Fabric.

So what does the farm look like?
mysqlfabric group lookup_servers davesfarm
Password for admin:
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

server_uuid address status mode weight
----------- ------- ------ ---- ------

After the farm is created, it needs to be made active.

mysqlfabric group activate davesfarm
Password for admin:
[INFO] 1423081400.519033 - Executor-0 - Monitoring group (davesfarm).
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

uuid finished success result
------------------------------------ -------- ------- ------
5ac9a044-c650-428e-ba50-50a6718f2342 1 1 1

state success when description
----- ------- ------------- ------------------------------------------------------------------
3 2 1423081400.49 Triggered by .
4 2 1423081400.5 Executing action (_activate_group).
5 2 1423081400.53 Executed action (_activate_group).

Cool!

mysqlfabric group lookup_groups
Password for admin:
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

group_id description failure_detector master_uuid
--------- ----------- ---------------- -----------
davesfarm None 1 None

Now Add an admin
An admin is needed to populate the farm.
mysqlfabric user add client --user=admin
Password for admin:
Add a new Fabric user
=====================
Username: client
Protocol (default xmlrpc):
Password:
Repeat Password:

Select role(s) for new user
ID Role Name Description and Permissions
-- ---------- ---------------------------
1 superadmin Role for Administrative users
+ Full access to all core Fabric functionality
2 useradmin Role for users dealing with user administration
+ User administration
+ Role administration
3 connector Role for MySQL Connectors
+ Access to dump commands
+ Reporting to Fabric

Enter comma separated list of role IDs or names: 1
Fabric user added.
No result returned

And On This Farm He Had a …
group add davesfarm 10.10.13.10
Password for admin:
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

uuid finished success result
------------------------------------ -------- ------- ------
550b1ba6-082d-4fef-9808-8fdfd6397de9 1 1 1

state success when description
----- ------- ------------- ------------------------------------------------------------------
3 2 1423083005.22 Triggered by .
4 2 1423083005.23 Executing action (_add_server).
5 2 1423083005.27 Executed action (_add_server).

mysqlfabric group add davesfarm 10.10.13.20
Password for admin:
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

uuid finished success result
------------------------------------ -------- ------- ------
4fd59253-bd10-42b9-82cd-0d61959a695c 1 1 1

state success when description
----- ------- ------------- ------------------------------------------------------------------
3 2 1423083297.97 Triggered by .
4 2 1423083297.98 Executing action (_add_server).
5 2 1423083298.01 Executed action (_add_server).
mysqlfabric group add davesfarm 10.10.13.30
...

Welcome to Farmville!!!
mysqlfabric group lookup_servers davesfarm
Password for admin:
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

server_uuid address status mode weight
------------------------------------ ----------- --------- --------- ------
0977d639-abbb-11e4-a804-0800273b9c80 10.10.13.30 SECONDARY READ_ONLY 1.0
1f96a235-abb9-11e4-a7f8-0800273b9c80 10.10.13.10 SECONDARY READ_ONLY 1.0
304e20cd-abba-11e4-a7ff-0800273b9c80 10.10.13.20 SECONDARY READ_ONLY 1.0

So now the farm is populated but we need to create a pecking order by making one of them the primary.

mysqlfabric group promote davesfarm --slave_id=1f96a235-abb9-11e4-a7f8-0800273b9c80
Password for admin:
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

uuid finished success result
------------------------------------ -------- ------- ------
af79f3e0-fda2-4531-8aab-c8a48ad036a6 1 1 1

state success when description
----- ------- ------------- ------------------------------------------------------------------
3 2 1423084103.73 Triggered by .
4 2 1423084103.73 Executing action (_define_ha_operation).
5 2 1423084103.75 Executed action (_define_ha_operation).
3 2 1423084103.74 Triggered by .
4 2 1423084103.75 Executing action (_check_candidate_fail).
5 2 1423084103.77 Executed action (_check_candidate_fail).
3 2 1423084103.77 Triggered by .
4 2 1423084103.77 Executing action (_wait_slave_fail).
5 2 1423084103.81 Executed action (_wait_slave_fail).
3 2 1423084103.8 Triggered by .
4 2 1423084103.81 Executing action (_change_to_candidate).
5 2 1423084103.93 Executed action (_change_to_candidate).

mysqlfabric group lookup_servers davesfarm
Password for admin:
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

server_uuid address status mode weight
------------------------------------ ----------- --------- ---------- ------
0977d639-abbb-11e4-a804-0800273b9c80 10.10.13.30 SECONDARY READ_ONLY 1.0
1f96a235-abb9-11e4-a7f8-0800273b9c80 10.10.13.10 PRIMARY READ_WRITE 1.0
304e20cd-abba-11e4-a7ff-0800273b9c80 10.10.13.20 SECONDARY READ_ONLY 1.0

So Fabric has been started, a farm has been established, and it is ready for testing. But the testing will have to wait for another post.

And if you want to stop Fabric, the command is:
shell> mysqlfabric manage stop

Some blogs to help:

MySQL Fabric — Second Steps, er, Threads

MySQL Fabric will be used set up a three node High Availability server farm and this is the second part of a series. If you missed the last post, I will be doing a live demo (gulp!) on Fabric for the Triangle MySQL User Group in Raleigh. But part of the pain of live demos is how do you get multiple servers set up to make a server farm. E-I-E-I-oh?

Of course getting the slaves up on one laptop and running correctly is the biggest expenditure of time for this demo. For this I will use Vagrant. I am using Vagrant to set up three identical servers using VirtualBox to provide the servers I need for the demo. I am using Ubuntu.
sudo apt-get install vagrant VirtualBox
mkdir vagerant
cd vagrant
vagrant box add db1 https://github.com/kraksoft/vagrant-box-ubuntu/releases/download/14.04/ubuntu-14.04-amd64.box
vagrant init db1

Then I created db2 and db3 using the last two steps. Be sure to add the vbguest plugin so you can have a shared folder and some otehr features.
vagrant plugin install vagrant-vbguest
There is a problem with the current Vagrant for getting a shared folder to work (hint — shared folder for your files you want to install on the farm server like the MySQL 5.6 tarball).
To get the shared folder to work, boot the servers and then sudo ln -s /opt/VBoxGuestAdditions-4.3.10/lib/VBoxGuestAdditions /usr/lib/VBoxGuestAdditions .

The Vagrantfile is where the three boxes are defined and the interesting parts follows:

config.vm.define "db1" do |db1|
db1.vm.box = "db1"
db1.vm.network :private_network, ip: "10.10.13.10"
end

config.vm.define "db2" do |db2|
db2.vm.box = "db2"
db2.vm.network :private_network, ip: "10.10.13.20"
end

config.vm.define "db3" do |db3|
db3.vm.box = "db3"
db3.vm.network :private_network, ip: "10.10.13.30"
end
This provides three boxes creatively named db1, db2, and db3 on a private ’10’ network.

The nest step it to run vagrant up, ssh to each of the boxes, and make things ready for installing MySQL. This includes apt-getting libaio1, adding the address to /etc/host and setting the hostname. Then install MySQL 5.6 and setup a user named fabric on each instance.

The next post will cover setting up the HA Fabric farm.