tisdag 25 februari 2014

Easy HA and automatic failover using MySQL Fabric - Part I

The purpose of this blog-post and next one (Part II) is to see how MySQL Fabric can be used as a HA solution. Reason for this is many users today have the need for HA and automatic failover, but not many users need the sharding functionality, I wanted to focus on creating one HA group and see how failover is handled using MySQL Fabric.

Talking to customers and users of MySQL they all want to have HA, the normal demands are:
  • Do not want to alter application code
  • Easy to install and manage
There are a lot of solutions for achieving HA around MySQL in the MySQL ecosystem but I think one key benefit of MySQL Fabric is that it is built into the connectors. This will make the technology easy to adopt for developers and easy to manage for DBA's since there are a minimal of extra parts to take care of.

Getting started with MySQL Fabric, I consumed the following blogs and docs:
If you want to see all resources around MySQL Fabric I highly recommend this post in the MySQL forum for Fabric.

Install MySQL Fabric

My setup will have 4 MySQL instances, one for MySQL Fabric backing store and 3 servers for my HA group.

The setup will be as shown in picture on right side with our 4 MySQL instances running on port 63307 - 63309. Instance with port number 63306 will be used by MySQL Fabric to store internal meta data used by Fabric. MySQL instaces 63307 - 63309 will be part of a HA group called mygroup.

High-availability groups are very important part of MySQL fabric, these are collections of servers that shall work together to deliver a database service to the application that connects to MySQL Fabric.

In next post we will look at a python application and see how this works MySQL Fabric.

That was a bit of background but now we need to get started, first we need is 4 MySQL instances, I installed everything underneath /opt/fabric, see configuration options for my MySQL instances bellow.

mkdir /opt/fabric/mysql1;
mkdir /opt/fabric/mysql2;
mkdir /opt/fabric/mysql3;
mkdir /opt/fabric/mysql4;

mysql_install_db --datadir=
mysql_install_db --datadir=/opt/fabric/mysql2
mysql_install_db --datadir=/opt/fabric/mysql3
mysql_install_db --datadir=/opt/fabric/mysql4

mysqld --socket=/opt/fabric/mysql1/my.sock --port=63306 --datadir=/opt/fabric/mysql1 --server-id=1 \
/opt/fabric/mysql1/mysql.err --innodb_buffer_pool_size=64MB --gtid_mode=ON \
       --log-bin --log-slave-updates --enforce-gtid-consistency \
       --basedir=/path/to/mysql-src --innodb-flush-log-at-trx-commit=2 --log-bin \

       --performance-schema-instrument='%=ON' > /opt/fabric/mysql1/mysql.out & 2>&1

mysqld --socket=/opt/fabric/mysql2/my.sock --port=63307 --datadir=/opt/fabric/mysql2 --server-id=2 \
/opt/fabric/mysql2/mysql.err --innodb_buffer_pool_size=64MB --gtid_mode=ON \
       --log-bin --log-slave-updates --enforce-gtid-consistency \
       --basedir=/path/to/mysql-src --innodb-flush-log-at-trx-commit=2 --log-bin \

       --performance-schema-instrument='%=ON' > /opt/fabric/mysql2/mysql.out & 2>&1

And for instance 3 and 4 I used; port 63308 and 63309, server-id 3 and 4. 

Next step is to fetch MySQL Fabric code from here, MySQL Fabric is part of the latest release of MySQL Utilities called 1.4.1 alpha.
I opted for tar-file so used installation manual here, next step is to download and install Fabric aware connectors for Python, I again followed the manual here.

Now we are finally ready to start playing with MySQL fabric.
First we need to do some minor changes to MySQL Fabric configuration file (for me this was located at /etc/mysql/fabric.cfg), under section Storage you need to change address to your MySQL backing store (for me this is localhost:63306) and also see to if you have appropriate setting for user, password and database under the same section, you will need these in next step.

Lets configure our backing store for MySQL Fabric, I will use MySQL on port 63306 as backing store. All that's needed to do manually is creating the database with same name as in configuration above and also grants usage for the user created in configuration above.

mysql -uroot -proot -S /opt/fabric/mysql1/my.sock
mysql> create database fabric;
mysql> CREATE USER fabric@localhost IDENTIFIED BY 'fabric';
mysql> GRANT ALL ON fabric.* TO fabric@localhost;

Once this step is done we can proceed to initiate our backing store before we can finally start our fabric instance.

# Configure Fabric data node (63306)
bash$ mysqlfabric manage setup

# Check newly created tables
mysqlshow -ufabric -pfabric -h127.0.0.1 -P63306 fabric

# Start fabric
bash$ mysqlfabric manage start

Next step is to create our HA group and add our 3 MySQL instances.

# Create group and add my MySQL instances
bash$ mysqlfabric group create mygroup
bash$ mysqlfabric group add mygroup root root
bash$ mysqlfabric group add mygroup root root
bash$ mysqlfabric group add mygroup root root

# Control state of Fabric:
bash$ mysqlfabric group lookup_servers mygroup
bash$ mysqlfabric group check_group_availability mygroup

# Promote one of your servers at master, <UUID> is optional.
bash$ mysqlfabric group promote mygroup <UUID for one of the servers above>

# Activate automatic failover
bash$ mysqlfabric group activate mygroup

After this step you should see Fabric is running and one of your MySQL instances have been elected as primary and the other ones are slaves.

bash$ mysqlfabric group lookup_servers mygroup
Command :
{ success     = True
  return      = [['ef4593df-9e2b-11e3-8a45-5c260a4a26ad', '', True, 'PRIMARY'], ['ef49ce23-9e2b-11e3-8a45-5c260a4a26ad', '', False, 'SECONDARY'], ['ef49d0b6-9e2b-11e3-8a45-5c260a4a26ad', '', False, 'SECONDARY']]
  activities  =

We now have a running MySQL fabric and all our MySQL instances are ready to start working!

In the next blog post (Part II) we will create a example application and look at some failover scenarios.

See you soon!

7 kommentarer:

  1. What is the arrow pointing from the 63306 database to the 63307,63308,63309 databases? If the 63306 is the Fabric backing store, there is no connection with the application databases. It's merely an implementation detail in the Fabric server. The Fabric aware connector makes no connection to this database, but to the Fabric server instead. I think the diagram would better represent the architecture if there were a box around the 63306 database representing the Fabric server. This is what the `mysqlfabric' commands run against and what the connectors talk to when retrieving HA/sharding configurations.

    1. Hi Jess, good catch, you are correct, the 63306 database in fact represents the MySQL Fabric node in the model. I used a different shape (bigger) and colour compared to MySQL instances (63307 - 63309) but perhaps should also put it in a box and tag it with "MySQL Fabric node" to make it more clear.
      Thanks for the comment!

  2. You should probably remove the "replication" arrows between the Fabric node and the servers. There is no replication going on there, but Fabric node connect to servers to execute, e.g., a fail-over.

    1. Hi Mats, it seems my skills at creating understandable drawings is not great. Purpose of that line was only to show that fabric will communicate with the servers in the HA group to manage replication topology, will create a line separate and in different colour to make it clear. Thanks for pointing this out!

  3. Hi,

    I am facing the following issue while adding mysql instance into the group

    mysqlfabric group create group1

    success : false
    return : server error : Error Accessing Server (

    What could be the reasons.

    Waiting for your positive reply


    1. Hi,

      Great to see you are trying out Fabric!

      Normally you do not add any servers when creating your HA-group, try to run only: mysqlfabric group lookup_servers mygroup

      If you get "Error accessing Server" when adding servers to your group (group add) then action will depend on version of Fabric you are using. Wich pre-GA version you specified user to connect with after 'add' command but with latest version you would rather specify this in fabric.cfg (section [servers]).

      Hope this helps!


    2. Hi,

      First of all, I love you man. Ted, Thank you so much. I posted this issue on many blogs but none of them replied.

      You gave me the clue. I am using MySQL utilities 1.4.3 which is latest on MySQL website. For solution, I used the [servers] section of my fabric.cfg as you mentioned but I also give the password = 'myPassword' so that while adding the instance it uses the user and password values to connect the instance.

      I hope, I clearly forward the solution in my text. If someone needs clarity or wants my help, most welcome but only on this BLOG. :)