Loss-less replication using MySQL semi-syncronous replication and MySQL Fabric!

In my last post playing with MySQL fabric here and here I used MySQL fabric version 1.4.1 and MySQL 5.6, i must say lot's of things have happened over the last 20 months! MySQL have released several new versions of MySQL Fabric and also a new version of the server with MySQL 5.7.

Current GA version of MySQL Fabric is 1.5.6 and there is also a MySQL Fabric 1.6.2 beta, both versions can be downloaded from here.
As I said earlier, lots of new things have happened, looks like focus has been on making it easier to work with MySQL Fabric but also making output from fabric commands much easier to understand. With latest version 1.6.2 (not yet GA) fabric also have support for running multiple fabric instances, more news can be found here.

Semi-synchronous replication

Semi-synchronous replication is an enhancement to normal replication making it possible to failover to a slave server with minimal loss of data (will depend of durability settings of MySQL). In MySQL 5.7 we have introduced some changes making this feature even better, more details can be found here . So, how can we use semi-synchronous replication together with MySQL Fabric? Well it's not that hard to configure, just a few commands as explained in our manual, but hey let's try to upgrade the old MySQL fabric environment and make it use semi-synchronous replication!

Upgrading environment to MySQL 5.7

Some things in installation/configuration had to be modified moving from MySQL 5.6 to 5.7:

• installation process has changed a lot (more secure by default), if you want old behavior use --initialize-insecure to get back some old behavior.
• server-id is now mandatory if you enble binary logging, more information here and here.

For more information on what new in MySQL 5.7 and some removed or deprecated functionality read more here and here.

MySQL Fabric

Next step is to install MySQL Fabric, I will not cover the steps as this was covered on my old posts and the procedure for latest version of MySQL fabric is explained in detail in our manual.

Scenario is, we have MySQL Fabric up and running and 3 stand alone MySQL 5.7 servers waiting to be used by MySQL Fabric.

First step is to create our group and populate it with our 3 MySQL servers. We also run promote command to elect a primary server, the activate command enables automatic failover is primary server dies.

mysqlfabric group create group1 mysqlfabric group add group1 127.0.0.1:63301 mysqlfabric group add group1 127.0.0.1:63302 mysqlfabric group add group1 127.0.0.1:63303 mysqlfabric group promote group1 mysqlfabric group activate group1

Let's look at current status of our group of servers:

ted@ted-PORTEGE-Z30-A:/opt/MySQL-fabricII$ mysqlfabric group lookup_servers group1 Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e Time-To-Live: 1                          server_uuid         address    status       mode weight ------------------------------------ --------------- --------- ---------- ------ 8066541b-9423-11e5-ab31-7c7a910307fa 127.0.0.1:63301   PRIMARY READ_WRITE    1.0 839f04ac-9423-11e5-ab4d-7c7a910307fa 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0 8681ea65-9423-11e5-ad0f-7c7a910307fa 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0

All looks fine, our servers are up and running and group is ready!

Enabling Semi-synchronous replication

This whole procedure can be done without restarting our secondary server, for safety we set the secondary server in SPARE mode so it might not be elected as primary on case of failover whilst we are working with the server.

On Primary Server load semi-sync plugin and enable semi-sync replication:

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;

On one or both of the secondary servers; load semi-sync plugin, enable semi-sync replication, set server in SPARE mode and restart replication channel.

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1; mysqlfabric server set_status <server-uuid> SPARE mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD; mysqlfabric server set_status <server-uuid> SECONDARY

Verify semi-synchronous replication is up and running on slaves:

mysql> show status like  'Rpl_semi_sync_slave_status'; +----------------------------+-------+ | Variable_name              | Value | +----------------------------+-------+ | Rpl_semi_sync_slave_status | ON    | +----------------------------+-------+

We are done, you now have semi-sync replication up and running in your group!

My MySQL Fabric wish list for the future is:

• Feature request Semisynchronous replication support in MySQL Fabric.
• Fabric GUI section in workbench for managing my groups of servers. I would also like to extend this functionality to handle basic monitoring and help to architect/deploy groups or failed servers.
• Support for MySQL Group Replication in fabric, so we have three different ways of replicating data between servers in our HA-groups.