Monday, July 21, 2014

MySQL Fabric High Availability Failover Detection Tutorial


MySQL Fabric is an extensible framework for managing farms of MySQL Servers. Two features have been implemented - High Availability (HA) and scaling out using data sharding. These features can be used in isolation or in combination. (Ref : www.mysql.com/products/enterprise/fabric.html)


The following setup is configured to prepare the MySQL Fabric HA environment.  (Sharding is to be discussed later.)


Creating the MyGroup1 with 1 Master and 3 slaves as shown above where the port numbers for the MySQL Servers are to be configured as 3316, 3326, 3336 and 3346.  The MySQL Fabric Server has the MySQL as the backing store which the setup uses 3306.  The whole setup with this tutorial is configured on a single machine.  But you can freely extended this to multiple machine with configuration changes on those host names / ip addresses.

Pre-requisite :
  • Download and Install Software
  • Assuming you have the following 3 folders
    • MySQL BASEDIR=/usr/local/mysql
    • MYSQL DATADIR=/mysql/myfab
    •  This demo script folder on ~/demo
  • With the demo user, adding the MySQL Bin directory to the path
    • PATH=/usr/local/mysql/bin:$PATH;export PATH

The tutorial is configured

  • Setting up MySQL Fabric tutorial environment
    • Creating and Starting MySQL databases (3306, 3316, 3326, 3336, 3346)
    • Creating Fabric Users on databases
  • Configure and Start MySQL Fabric
    • Creating the MyGroup1 in Fabric
    •  Promoting a Database instance as Primary
    • Activating the HA detection within MySQL Fabric
  • Running Python application
    • Auto Failover with MySQL Fabric

Setting up MySQL Fabric Tutorial Environment ((Login as the demo user <e.g. mysql>)
server>mkdir ~/demo
server>mkdir ~/demo/config
server>mkdir ~/demo/scripts
server>mkdir /mysql/myfab

server>PATH=/usr/local/mysql/bin:$PATH;export PATH
server>MYSQLBASE=/usr/local/mysql;export MYSQLBASE
server>DEMODIR=/mysql/myfab;export DEMODIR

Creating 5 mysql databases on /mysql/myfab 
 
export DEMODIR=/mysql/myfab
$MYSQLBASE/scripts/mysql_install_db --basedir=$MYSQLBASE --datadir=$DEMODIR/data
$MYSQLBASE/scripts/mysql_install_db --basedir=$MYSQLBASE --datadir=$DEMODIR/data1
$MYSQLBASE/scripts/mysql_install_db --basedir=$MYSQLBASE --datadir=$DEMODIR/data2
$MYSQLBASE/scripts/mysql_install_db --basedir=$MYSQLBASE --datadir=$DEMODIR/data3
$MYSQLBASE/scripts/mysql_install_db --basedir=$MYSQLBASE --datadir=$DEMODIR/data4

Create the MySQL config file for the 5 MySQL databases
The table shows the mapping between the config files against different databases

config filedatadirport#server_id
~/demo/config/my0.cnf/mysql/myfab/data33061
~/demo/config/my1.cnf/mysql/myfab/data133162
~/demo/config/my2.cnf/mysql/myfab/data233263
~/demo/config/my3.cnf/mysql/myfab/data333364
~/demo/config/my4.cnf/mysql/myfab/data433465

The following is the sample config file for my0.cnf.  The red lines should be changed accordingly to each database in the above mapping table. 

File : ~/demo/config/my0.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/mysql/myfab/data   
port=3306 
server_id=1
socket=/mysql/myfab/data/mysqlfab.socket

log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
sync-master-info=1
log-bin=fab-bin.log
master-info-repository=TABLE
relay-log-info-repository=TABLE

The above configuration ensures GTID is used.  The following options are enabled
--gtid_mode=ON --log-bin --log-slave-updates --enforce-gtid-consistency

For details of GTID setup, refers to http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-howto.html

The ~/demo/config folder should now have 5 config files (my0cnf, my1.cnf, my2.cnf, my3.cnf and my4.cnf)

Starting up MySQL Server daemon (note : mysql bin directory is in the PATH)

mysqld --defaults-file=~/demo/config/my0.cnf > ~/demo/my0.out 2>&1 &
mysqld --defaults-file=~/demo/config/my1.cnf > ~/demo/my1.out 2>&1 &
mysqld --defaults-file=~/demo/config/my2.cnf > ~/demo/my2.out 2>&1 &
mysqld --defaults-file=~/demo/config/my3.cnf > ~/demo/my3.out 2>&1 &
mysqld --defaults-file=~/demo/config/my4.cnf > ~/demo/my4.out 2>&1 &


Create fabric user with privileges (Note : It is for demo purpose to grant all on *.*)  The password for fabric user is assumed to be 'secret'. 

mysql -u root -P3306 --protocol=tcp -e "grant all on *.* to 'fabric'@'localhost' identified by 'secret';"
mysql -u root -P3316 --protocol=tcp -e "grant all on *.* to 'fabric'@'localhost' identified by 'secret';"
mysql -u root -P3326 --protocol=tcp -e "grant all on *.* to 'fabric'@'localhost' identified by 'secret';"
mysql -u root -P3336 --protocol=tcp -e "grant all on *.* to 'fabric'@'localhost' identified by 'secret';"
mysql -u root -P3346 --protocol=tcp -e "grant all on *.* to 'fabric'@'localhost' identified by 'secret';"


Create fabric configuration file which determines the fabric server properties
For details of each parameters within fabric configuration file, refers to
http://dev.mysql.com/doc/mysql-utilities/1.5/en/fabric-cfgref.html


File : ~/demo/config/fabric.cfg
[DEFAULT]
prefix = /usr/local/mysql
sysconfdir = /usr/local/mysql/etc
logdir = /mysql/myfab
[storage]
address = localhost:3306
user = fabric
password = secret
database = fabric
connection_timeout = 6
connection_attempts = 6
connection_delay = 1

[servers]
user = fabric
password = secret

[protocol.xmlrpc]
address = localhost:32274
threads = 5
user = admin
password = admin
disable_authentication =no
realm = MySQL Fabric

[executor]
executors = 5

[logging]
level = INFO
url = file:////mysql/myfab/fabric.log

[sharding]
mysqldump_program = /usr/local/mysql/bin/mysqldump
mysqlclient_program = /usr/local/mysql/bin/mysql

[connector]
ttl = 1

[failure_tracking]
notifications = 300
notification_clients = 50
notification_interval = 60
failover_interval = 0
detections = 3
detection_interval = 6
detection_timeout = 1
prune_time = 3600

Setup MySQL Fabric Server with the configuration file (fabric.cfg)

mysqlfabric --config=~/demo/config/fabric.cfg manage setup

To verify the setup of mysqlfabric after the above command,
mysqlshow -ufabric -psecret --protocol=tcp -P3306 fabric

The following similar screenshot was captured after the execution of the verification command.  There are tables created within fabric database.






 Start up MySQL Fabric Server which connects to the backing database (3306).

mysqlfabric --config=config/fabric.cfg manage start > ~/demo/myfab/fabric.log 2>&1 &

The following output should be returned from the above execution.

Command :
{ success     = True
  return      = True
  activities  =
}

Create MySQL Fabric Group "mygroup1" with the 4 databases (3316, 3326, 3336 and 3346)

mysqlfabric --config=~/demo/config/fabric.cfg group create mygroup1
mysqlfabric --config=~/demo/config/fabric.cfg group add mygroup1 localhost:3316
mysqlfabric --config=~/demo/config/fabric.cfg group add mygroup1 localhost:3326
mysqlfabric --config=~/demo/config/fabric.cfg group add mygroup1 localhost:3336
mysqlfabric --config=~/demo/config/fabric.cfg group add mygroup1 localhost:3346

To verify the 'mygroup1' creation within MySQL Fabric, mysqlfabric group lookup_servers command is executed on the MySQL Fabric

mysqlfabric --config=~/demo/config/fabric.cfg group lookup_servers mygroup1
Command :
{ success     = True
  return      = [{'status': 'SECONDARY', 'server_uuid': 'fcfb6a5a-114d-11e4-b908-080027cf0ded', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': 'localhost:3316'}, {'status': 'SECONDARY', 'server_uuid': 'fd256486-114d-11e4-b908-080027cf0ded', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': 'localhost:3346'}, {'status': 'SECONDARY', 'server_uuid': 'fd2564dd-114d-11e4-b908-080027cf0ded', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': 'localhost:3326'}, {'status': 'SECONDARY', 'server_uuid': 'fd276da3-114d-11e4-b908-080027cf0ded', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': 'localhost:3336'}]
  activities  =
}

To have a better visual output of the mysqlfabric group lookup_servers command, the following status.py is created

File ~/demo/status.py
import json
import sys

data=json.loads(sys.argv[1].replace( '\'', '\"'))

print 'Address      \tServer UUID                           \tMode    \tStatus' 
for i in range(0, (len(data))):
    addr=data[i]['address']
    print data[i]['address'], '\t',\
        data[i]['server_uuid'], '\t', data[i]['mode'], '\t', \
        data[i]['status']


python status.py "`mysqlfabric --config=~/demo/config/fabric.cfg group lookup_servers mygroup1|grep return|sed 's/return      =//g'`"

Address          Server UUID                               Mode        Status
localhost:3316     fcfb6a5a-114d-11e4-b908-080027cf0ded     READ_ONLY     SECONDARY
localhost:3346     fd256486-114d-11e4-b908-080027cf0ded     READ_ONLY     SECONDARY
localhost:3326     fd2564dd-114d-11e4-b908-080027cf0ded     READ_ONLY     SECONDARY
localhost:3336     fd276da3-114d-11e4-b908-080027cf0ded     READ_ONLY     SECONDARY


By promoting the MySQL Fabric group 'mygroup1', one of the MySQL servers will be chosen to be primary.

mysqlfabric --config=~/demo/config/fabric.cfg group promote mygroup1

Address          Server UUID                               Mode        Status
localhost:3316     fcfb6a5a-114d-11e4-b908-080027cf0ded     READ_WRITE     PRIMARY
localhost:3346     fd256486-114d-11e4-b908-080027cf0ded     READ_ONLY     SECONDARY
localhost:3326     fd2564dd-114d-11e4-b908-080027cf0ded     READ_ONLY     SECONDARY
localhost:3336     fd276da3-114d-11e4-b908-080027cf0ded     READ_ONLY     SECONDARY

To active the Auto Failover detection with MySQL Fabric,

mysqlfabric --config=~/demo/config/fabric.cfg group activate mygroup

Now the MySQL Fabric Server group 'mygroup1' is created with 4 servers and failover detection is activated.   The next part of this tutorial will be about creating application that can use these MySQL database servers with master as READ-WRITE and slave for READ_ONLY operations.   If the master fails, another MySQL servers from the Fabric will be chosen to be the master.

The following 3 python files are created for the tutorial
FileDescription
setup_table.ha.pyCreate the table - mycustomer via READ_WRITE connection
add_cust_ha.pyAdd data into table - mycustomer via READ_WRITE and query the table content from READ_WRITE connection
read_cust_ha.py Retrieve data from table - mycustomer via READ_ONLY connection

File : ~/demo/scripts/setup_table_ha.py
import mysql.connector
from mysql.connector import fabric

conn = mysql.connector.connect(
    fabric={"host" : "localhost", "port" : 32274, "username": "admin",
"password" : "admin"},
    user="root", database="test", password="",
    autocommit=True
)

conn.set_property(mode=fabric.MODE_READWRITE, group="mygroup1")
cur = conn.cursor()
cur.execute(
"CREATE TABLE IF NOT EXISTS mycustomer ("
"   custid MEDIUMINT NOT NULL AUTO_INCREMENT, "
"   first_name CHAR(40), "
"   last_name CHAR(40),"
"   PRIMARY KEY (custid)"
")"
)

File : ~/demo/scripts/add_cust_ha.py
import mysql.connector
from mysql.connector import fabric

def add_subscriber(conn, first_name, last_name):
    conn.set_property(group="mygroup1", mode=fabric.MODE_READWRITE)
    cur = conn.cursor()
    cur.execute(
        "INSERT INTO mycustomer (first_name, last_name) VALUES (%s, %s)",
        (first_name, last_name)
        )

def listLast2(conn) :

    cur = conn.cursor()
    cur.execute(
        "SELECT @@hostname, @@port, custid, first_name, last_name "\
    "FROM mycustomer order by custid desc limit 2")
    print("Only the top 2 items")
    for row in cur:
        print row

conn = mysql.connector.connect(
    fabric={"host" : "localhost", "port" : 32274, "username": "admin",
"password" : "admin"},
    user="root", database="test", password="",
    autocommit=True
    )

conn.set_property(group="mygroup1", mode=fabric.MODE_READWRITE)

add_subscriber(conn,  "Billy", "Fish")
add_subscriber(conn,  "Billy", "Joel")
add_subscriber(conn,  "Arthur", "Askey")
add_subscriber(conn,  "Billy", "Fish")
add_subscriber(conn,  "Jimmy", "White")
add_subscriber(conn,  "Bobby", "Ball")
listLast2(conn)

print ("**** End ***")


File : ~/demo/scripts/read_cust_ha.py
import mysql.connector
from mysql.connector import fabric


def listLast2(conn) :
    conn.set_property(group="mygroup1", mode=fabric.MODE_READONLY)
    cur = conn.cursor()
    cur.execute(
        "SELECT @@hostname, @@port, custid, first_name, last_name "\
    "FROM mycustomer order by custid desc limit 2")
    print("Only the top 2 items")
    for row in cur:
        print row

conn = mysql.connector.connect(
    fabric={"host" : "localhost", "port" : 32274, "username": "admin",
    "password" : "admin"},
    user="root", database="test", password="",
    autocommit=True
    )

listLast2(conn)
listLast2(conn)
listLast2(conn)

print("*****End******");

Running the test
  1. setup the customer table
  2. add customer data which shows the data is inserted into the "PRIMARY" servers
  3. read customer data which shows the data is retrieved from different "SECONDARY" servers
  4. Looping the 'add_cust_ha.py" and shutdown the "PRIMARY", the failover detection promotes the secondary server to be the "PRIMARY"


$ python add_cust_ha.py
Only the top 2 items
(u'ima02', 3316, 18, u'Bobby', u'Ball')
(u'ima02', 3316, 17, u'Jimmy', u'White')
**** End ***
$ python read_cust_ha.py
Only the top 2 items
(u'ima02', 3326, 18, u'Bobby', u'Ball')
(u'ima02', 3326, 17, u'Jimmy', u'White')
Only the top 2 items
(u'ima02', 3336, 18, u'Bobby', u'Ball')
(u'ima02', 3336, 17, u'Jimmy', u'White')
Only the top 2 items
(u'ima02', 3346, 18, u'Bobby', u'Ball')
(u'ima02', 3346, 17, u'Jimmy', u'White')
*****End******

The results from the add_cust_ha.py and read_cust_ha.py shows execution can be scalable with READ operations to different MySQL database instances. where WRITE operations are centralized into PRIMARY server.

Run the Loop with "add_cust_ha.py" as follows

while [ 1 ]; do sleep 1; python add_cust_ha.py ; done

Open another terminal to shutdown the PRIMARY MYSQL database (e.g. 3316, with this tutorial, no password is given for 'root')

mysqladmin -uroot -h127.0.0.1 -P3316 shutdown

The PRIMARY MySQL Server will be faulty and MySQL Fabric should be able to detect the failure and promote another SECONDARY server to be the PRIMARY.  The loop will be able to resume service.

To verify the status after failover, use the same command of status.py

python status.py "`mysqlfabric --config=~/demo/config/fabric.cfg group lookup_servers mygroup1|grep return|sed 's/return      =//g'`"

Address          Server UUID                               Mode        Status
localhost:3316     fcfb6a5a-114d-11e4-b908-080027cf0ded     READ_WRITE     FAULTY
localhost:3346     fd256486-114d-11e4-b908-080027cf0ded     READ_WRITE     PRIMARY
localhost:3326     fd2564dd-114d-11e4-b908-080027cf0ded     READ_ONLY     SECONDARY
localhost:3336     fd276da3-114d-11e4-b908-080027cf0ded     READ_ONLY     SECONDARY

*************************** End of this Tutorial **********************************