MySQL Fabric is a framework for MySQL Replication high availability, automatic failover and sharding. Technically, a MySQL Fabric daemon monitors a set of MySQL servers and takes appropriate actions upon failure. Clients use Fabric aware drivers to learn about failed servers and shards to distribute queries accordingly. Simple to understand, simple to sell, simple to raise false expectations and simple to fail [, dear Sales]. With the usual blog posts telling only the story of the first three sentences, major parts of the story are covered in silence.

Development preview = announcement of a vision != ready

You first challenge will be to find the documentation for the MySQL Fabric development preview. From the documentation overview page it takes three clicks down to the server side documentation for Fabric:

1. MySQL Workbench
2. MySQL Utilities 1.4+ (not the older one!)
3. MySQL Fabric

You better do not start your search in the MySQL Reference Manual under High Availability and Scalability. Fabric, a command line utility, is well hidden inside the documentation of a GUI tool. Eventually, you may find a download and attempt to install Fabric using an install prefix.

~/mysql-utilities-1.4.1/install/bin # ./mysqlfabric manage start
~/mysql-utilities-1.4.1/install/bin # Traceback (most recent call last):
  File "./mysqlfabric", line 23, in <module>
    from mysql.fabric.services import (
ImportError: No module named mysql.fabric.services
~/mysql-utilities-1.4.1/install/bin # export PYTHONPATH=/home/nixnutz/ftp/mysql-utilities-1.4.1/install/lib/python2.7/site-packages/

80% of the development community is out, true?

Pretty much all blogs and tutorials claim that Fabric aware drivers must be used. According to the manual, such drivers exist for Python and Java only. This covers an estimated 20% of the software development communities. Let’s cry for help:

~#47;mysql-utilities-1.4.1/install/bin # ./mysqlfabric help
Usage: %fabric <group> <cmd> [<option> ...] arg ...

mysqlfabric: error: Error (dispatch() takes exactly 3 arguments (1 given)).
Wrong number of parameters were provided for command (manage help).

~#47;mysql-utilities-1.4.1/install/bin # ./mysqlfabric help help help
Command (help, help) was not found.

Being among the 20% of priviledged Python or Java users you may not bother until you browse the etc/ directory for configuration files. Here you will find something seemingly related to PHP’s Doctrine…

~/mysql-utilities-1.4.1/install/bin # ls -la ../etc/mysql/
insgesamt 32
drwxr-xr-x 2 nixnutz users  4096 11. Feb 16:34 .
drwxr-xr-x 3 nixnutz users  4096 11. Feb 16:33 ..
-rw-r----- 1 nixnutz users   119 11. Feb 16:34 default_plugins.cnf
-rw-r--r-- 1 nixnutz users   527 11. Feb 16:34 fabric.cfg
-rw-r--r-- 1 nixnutz users 13132 11. Feb 16:34 mysql-fabric-doctrine-1.4.0.zip

There is something for the PHP driver but it is not documented. Why there is something for a PHP application before the driver part is ‘done done’ may make some scratch their heads.

Putting expectations straight and low

Any project should state what is included and what is not. Fabrics’ vision should be clear and bright. Please, see the slides.

But, Fabric is available as a development preview release only. Core functionality exists. The examples from the manual work for me. But, no other scenarios do. For example, mysqlfabric group import_topology lacks documentation and mysqlfabric group promote blog_repl fails for me. Likely, the latter cannot parse the GTID histories from my replication setup:

[DEBUG] 1393921605.845434 - Executor-3 - Start executing function: get_num_gtid(('0d4adf79-9329-11e3-8278-000c299b2a06:1-2,\n1113b4b6-9326-11e3-8264-000c299b2a06:8-9:13:40-45:48-54:56-58:60-62:64-66:69-71:73-74:76-78:80-82:84-85:88-90:92-93:95-98:100-101:103-106:109:111:115-118:121:123:127-130:132-135:138-140:143-149:151-153:155-157:159-161:164-166:168-169:171-173:175-177:179-180:183-185:187-188:190-193:195-196:198-201:204:206:210-213:216:218:222-225:227-230:232-234:236-238:241-247:249-251:253-255:257-259:262-264:266-267:269-271:273-275:277-278:281-283:285-286:288-291:293-294:296-299:302:304:308-311:314:316:320-323:325-328:331-337:339-341:343-345:347-349:352-354:356-357:359-361:363-365:367-368:371-373:375-376:378-381:383-384:386-389:392:394:398-401:404:406:410-413:415-418:420-422:424-426:428-430:432-434:436-438:441-447:449-451:453-455:457-459:462-464:466-467:469-471:473-475:477-478:481-483:485-486:488-491:493-494:496-499:502:504:508-511:514:516:520-523:525-528:531-536:539:541:545-549:551-553:555-557:559-561:564-566:568-569:571-573:575-577:579-580:583-584:586-587:589-592:594-595:597-600:603:605:609-612:615:617:621-624:626-629:632-637:640:642:646-650:652-654:656-658:660-662:665-667:669-670:672-674:676-678:680-681:684-685:687-688:690-693:695-696:698-701:704:706:710-713:716:718:722-725:727-732:735-740:743:745:749-753:755-757:759-761:763-765:768-770:772-773:775-777:779-781:783-784:787-789:791-792:794-797:799-800:802-805:808:810:814-817:820:822:826-829:831-834:836-838:840', None), {}).
[DEBUG] 1393921605.845557 - Executor-3 - Error executing function: get_num_gtid.

Developers life without Fabric aware drivers

Fabric aware drivers are part of the vision, they contribute to the developer experience. As you are waiting for things to mature and Fabric aware drivers to enter ‘done done’ state, you can try to write a Fabric aware application without a special driver. This may be required on the long run as well: MySQL does not provide a driver for each and every language. Also, if you want to use an old version of a driver or gather some background information about how to debug a driver, knowing what “Fabric aware” means is a must.

A Fabric aware PHP [C/C++, Perl, Ruby, …] application

The Fabric daemon and Fabric aware drivers/applications communicate with each others through XML RPC over HTTP, just like the mysqlfabric command line utility does. The RPC interface can be used, for example, to learn about running and failed servers and their roles. You can look up (logical) server groups, shard mappings, individual shards and so forth. Fabric aware drivers try to do these operations in the background in a way that is mostly transparent for the application. A Fabric aware application needs to the the XML RPC itself.

The list of RPC commands is not documented, but you can ask the mysqlfabric utility for a list of commands:

# ./mysqlfabric list-commands
group activate                   Activate a group.
group import_topology            Try to figure out the replication topology and import it into the state store.
group deactivate                 Deactivate a group.
group create                     Create a group.
group remove                     Remove a server from a group.
group add                        Add a server into group.
group lookup_servers             Return information on existing server(s) in a group.
group check_group_availability   Check if any server within a group has failed and report health information.
group destroy                    Remove a group.
group demote                     Demote the current master if there is one.
group promote                    Promote a server into master.
group lookup_groups              Return information on existing group(s).
group description                Update group's description.
manage list-commands             List the possible commands.
[...]

Generally speaking each command listed maps to one XML RPC call. The XML RPC call parameters and – possibly – some help can be obtained using mysqlfabric help <command>.

# ./mysqlfabric help group lookup_groups 
group lookup_groups(group_id): Return information on existing group(s).

Often, this is enough input to craft the XML required for the corresponding XML RPC call:

#  cat lookup_groups.xml
<?xml version="1.0" encoding="iso-8859-1"?>
<methodCall>
  <methodName>group.lookup_groups</methodName>
</methodCall>

Give it a try and POST the XML to the Fabric server, which listens on port 8080 by default, or whatever port you configured during the installation:

# curl  -H 'Content-Type: text/xml' -d @./lookup_groups.xml http://127.0.0.1:8080
<?xml version='1.0'?>
<methodResponse>
<params>
<param>
<value><array><data>
<value><boolean>1</boolean></value>
<value><string></string></value>
<value><array><data>
<value><array><data>
<value><string>blog_repl</string></value>
</data></array></value>
</data></array></value>
</data></array></value>
</param>
</params>
</methodResponse>

The XML RPC was successful and Fabric has returned a list of all configured high availability groups (see also the Fabric quickstart). Compare the XML reply with the reply from the mysqlfabric command line utility. The information is identical but presented differently.

# ./mysqlfabric  group lookup_groups 
Command :
{ success     = True
  return      = [['blog_repl']]
  activities  = 
}

Teach your application to issue an XML RPC call, parse the result and you have a Fabric aware application…

Fabric aware application: HA group support

Assume you are using Fabric for nothing but monitoring a simplistic group of three MySQL Replication servers, the case sketched in the Fabric quickstart. There is one master and two slaves, no further constraints or rules exist.

If an application wants to connect to such a logical Fabric high availability group it first needs to know the name of the group. Assume you have configured only one group and the groups name ‘blog_repl’ is known to the application. Then, all a most basic Fabric aware application would want to know from Fabric is:

• Which servers belong to the high availability group ‘blog_reply’?
• Which one is the master (writes), which ones are the slaves (reads)?

The Fabric command which answer these questions is:

 # ./mysqlfabric  group lookup_servers blog_repl
Command :
{ success     = True
  return      = [['28acd755-a37b-11e3-ace4-000c299b2a06', '127.0.0.1:3308', False, 'SECONDARY'], ['c3d24cc0-a37a-11e3-ace1-000c299b2a06', '127.0.0.1:3310', False, 'SECONDARY'], ['f159bc2e-a37a-11e3-ace2-000c299b2a06', '127.0.0.1:3309', True, 'PRIMARY']]
  activities  = 
}

Using the help output of the command, you can construct the XML request that a Fabric aware application has to send to get the questions answered:

# cat lookup_servers.xml
<?xml version="1.0" encoding="iso-8859-1"?>
<methodCall>
  <methodName>group.lookup_servers</methodName>
  <params>
    <param>
      <value><string>blog_repl</string></value>
    </param>
  </params>
</methodCall>

The verbose answer from Fabric is below. Your application must parse the XML and extract the relevant information to learn about the servers and their roles in the high availability group ‘blog_repl’:

# curl  -H 'Content-Type: text/xml' -d @./lookup_servers.xml http://127.0.0.1:8080
<?xml version='1.0'?>
<methodResponse>
<params>
<param>
<value><array><data>
<value><boolean>1</boolean></value>
<value><string></string></value>
<value><array><data>
<value><array><data>
<value><string>28acd755-a37b-11e3-ace4-000c299b2a06</string></value>
<value><string>127.0.0.1:3308</string></value>
<value><boolean>0</boolean></value>
<value><string>SECONDARY</string></value>
</data></array></value>
<value><array><data>
<value><string>c3d24cc0-a37a-11e3-ace1-000c299b2a06</string></value>
<value><string>127.0.0.1:3310</string></value>
<value><boolean>0</boolean></value>
<value><string>SECONDARY</string></value>
</data></array></value>
<value><array><data>
<value><string>f159bc2e-a37a-11e3-ace2-000c299b2a06</string></value>
<value><string>127.0.0.1:3309</string></value>
<value><boolean>1</boolean></value>
<value><string>PRIMARY</string></value>
</data></array></value>
</data></array></value>
</data></array></value>
</param>
</params>
</methodResponse>

XML RPC from PHP and parsing the reply

Ignoring all the glory details, the code – here PHP – to fetch the list of servers in a group from Fabric is rather trivial. The example implements a class with three methods to fetch a list of all servers, a list of the read-only slaves and a list of the read-write master. From here on, the classic pattern of using one connection for read-only requests and one connection for writes could be used. Of course, this is not very comfy. This is where the various Fabric aware drivers try to deliver better options…

<?php
class fabric_ha {
  protected $server;
  protected $group_id;
  protected $xml = '<?xml version="1.0" 
encoding="iso-8859-1"?><methodCall><methodName>group.lookup_servers</methodName><params><param><value><string>%s</string></value></param></params></methodCall>';

  public function __construct($server, $group_id) {
   $this->server = sprintf("http://%s/",$server);
   $this->group_id = $group_id;
  }

  public function getServerList($role = NULL) {
    $xml = sprintf($this->xml, $this->group_id);

    $opts = array('http' =>
      array(
        'method'  => 'POST',
        'header'  => 'Content-type: text/xml',
        'content' => $xml
      )
    );
    $context = stream_context_create($opts);

    $ret = array();
    $result = file_get_contents($this->server, false, $context);
    $xml = new SimpleXMLElement($result);
    $server_list = $xml->xpath('/methodResponse/params/param/value/array/data/value[3]/array/data/value');
    foreach ($server_list as $server_entry) {
      $server = $server_entry->xpath('array/data/value');
      if (!is_null($role) && ($server[3]->xpath('string')[0]->__toString() != $role)) {
        continue;
      } 
      $ret[] = array(
                  'server_uuid' => $server[0]->xpath('string')[0]->__toString(),
                  'server'      => $server[1]->xpath('string')[0]->__toString(),
                  'master'      => $server[2]->xpath('boolean')[0]->__toString(),
      );
    }
    return $ret;
  }

  public function getReadWriteServer() {
    return $this->getServerList('SECONDARY');
  }

  public function getReadOnlyServer() {
    return $this->getServerList('PRIMARY');
  }
}

$f = new fabric_ha("127.0.0.1:8080", 'blog_repl');
var_dump($f->getReadWriteServer());
var_dump($f->getReadOnlyServer());

How do you get to know the XPath expressions, I’ve used? That is an excellent question to ask at the next conference when you meet the designers of Fabric. Whatever, there’s the script in action:

# php fabric.php 
array(2) {
  [0]=>
  array(3) {
    ["server_uuid"]=>
    string(36) "28acd755-a37b-11e3-ace4-000c299b2a06"
    ["server"]=>
    string(14) "127.0.0.1:3308"
    ["master"]=>
    string(1) "0"
  }
  [1]=>
  array(3) {
    ["server_uuid"]=>
    string(36) "c3d24cc0-a37a-11e3-ace1-000c299b2a06"
    ["server"]=>
    string(14) "127.0.0.1:3310"
    ["master"]=>
    string(1) "0"
  }
}
array(1) {
  [0]=>
  array(3) {
    ["server_uuid"]=>
    string(36) "f159bc2e-a37a-11e3-ace2-000c299b2a06"
    ["server"]=>
    string(14) "127.0.0.1:3309"
    ["master"]=>
    string(1) "1"
  }
}

Failover: faster than I can type…

If now the master of your MySQL Replication setup fails, Fabric may detect this and promote a slave to become a master. I’ve shutdown my master on 127.0.0.1:3309 and Fabric has instantly reconfigured the replication setup as illustrated below. On my unloaded notebook the entire server-side failover takes less time than it takes me to write this sentence.

As soon as the master becomes unavailable, an application will see transaction aborts and connection errors. A Fabric aware application should now breath for a second and ask Fabric for a fresh list of servers. Then, the Fabric aware application shall begin using the updated list of servers. I’ve omitted the error handling code and show the result of fetching a fresh server list only.

nixnutz@linux-dstv:~/src/php-src> php fabric.php 
array(1) {
  [0]=>
  array(3) {
    ["server_uuid"]=>
    string(36) "c3d24cc0-a37a-11e3-ace1-000c299b2a06"
    ["server"]=>
    string(14) "127.0.0.1:3310"
    ["master"]=>
    string(1) "0"
  }
}
array(1) {
  [0]=>
  array(3) {
    ["server_uuid"]=>
    string(36) "28acd755-a37b-11e3-ace4-000c299b2a06"
    ["server"]=>
    string(14) "127.0.0.1:3308"
    ["master"]=>
    string(1) "1"
  }
}

Note that I say your application shall take a breath: throttle Fabric requests. Fabric’s slow Python HTTP XML RPC – cannot handle tousands of concurrent requests from all your PHP webservers (development preview…). There’s a simple proof for this overload prediction that you will see if you put Fabric in DEBUG mode and analyze what it does (it runsn some 20 SQL statements per XML RPC).

As you can see, Fabric may be of use to you even if there is no Fabric aware driver for your preferred programming language be it PHP, C/C++, Perl, Ruby or whatever else…

Happy hacking!

@Ulf_Wendel

# ./mysqlfabric group add blog_repl 127.0.0.1:3310 root ''
Procedure :
{ uuid        = f417492c-1a11-460b-87a5-fd8df9d811d8,
  finished    = True,
  success     = False,
  return      = ServerError: Server (0d4adf79-9329-11e3-8278-000c299b2a06) does not have the binary log or gtid enabled.,
  activities  = 
}