In Part I of this blog series we installed and configured our MySQL Fabric, now we are ready to start test our python application against MySQL Fabric.
HA and failover
import mysql.connector
from mysql.connector import fabric
import time
def add_employee(conn, emp_no, first_name, last_name):
try:
conn.set_property(group="mygroup", mode=fabric.MODE_READWRITE)
cur = conn.cursor()
cur.execute(
"INSERT INTO employees VALUES (%s, %s, %s)",
(emp_no, first_name, last_name)
)
except mysql.connector.Error:
print "Database connection error, trying to reconnect ..."
conn=connect()
def find_employee(conn, emp_no):
try:
conn.set_property(group="mygroup", mode=fabric.MODE_READONLY)
cur = conn.cursor()
cur.execute(
"SELECT first_name, last_name FROM employees "
"WHERE emp_no = %s", (emp_no, )
)
except mysql.connector.Error:
print "Database connection error, trying to reconnect ..."
conn=connect()
for row in cur:
print row
# Address of the Fabric, not the host we are going to connect to.
def connect():
try:
conn=mysql.connector.connect(
fabric={"host" : "localhost", "port" : 8080},
user="root", database="test", password="root",
autocommit=True
)
except mysql.connector.Error:
print "Error trying to get a new database connection"
quit()
return conn
conn = connect()
conn.set_property(group="mygroup", mode=fabric.MODE_READWRITE)
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS employees")
cur.execute(
"CREATE TABLE employees ("
" emp_no INT, "
" first_name CHAR(40), "
" last_name CHAR(40)"
")"
)
high=10
for x in range(0, high):
add_employee(conn, x, "John"+":"+str(x), "Doe")
high+=1
while True:
add_employee(conn, high, "John"+":"+str(high), "Doe")
time.sleep(0.5)
for x in range(high-5, high):
find_employee(conn, x)
high+=1
from mysql.connector import fabric
import time
def add_employee(conn, emp_no, first_name, last_name):
try:
conn.set_property(group="mygroup", mode=fabric.MODE_READWRITE)
cur = conn.cursor()
cur.execute(
"INSERT INTO employees VALUES (%s, %s, %s)",
(emp_no, first_name, last_name)
)
except mysql.connector.Error:
print "Database connection error, trying to reconnect ..."
conn=connect()
def find_employee(conn, emp_no):
try:
conn.set_property(group="mygroup", mode=fabric.MODE_READONLY)
cur = conn.cursor()
cur.execute(
"SELECT first_name, last_name FROM employees "
"WHERE emp_no = %s", (emp_no, )
)
except mysql.connector.Error:
print "Database connection error, trying to reconnect ..."
conn=connect()
for row in cur:
print row
# Address of the Fabric, not the host we are going to connect to.
def connect():
try:
conn=mysql.connector.connect(
fabric={"host" : "localhost", "port" : 8080},
user="root", database="test", password="root",
autocommit=True
)
except mysql.connector.Error:
print "Error trying to get a new database connection"
quit()
return conn
conn = connect()
conn.set_property(group="mygroup", mode=fabric.MODE_READWRITE)
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS employees")
cur.execute(
"CREATE TABLE employees ("
" emp_no INT, "
" first_name CHAR(40), "
" last_name CHAR(40)"
")"
)
high=10
for x in range(0, high):
add_employee(conn, x, "John"+":"+str(x), "Doe")
high+=1
while True:
add_employee(conn, high, "John"+":"+str(high), "Doe")
time.sleep(0.5)
for x in range(high-5, high):
find_employee(conn, x)
high+=1
So, lets start this application up and see what happens.
# Start demo program
bash$ python fabric-demo.py
(u'John:0', u'Doe')
(u'John:1', u'Doe')
(u'John:2', u'Doe')
(u'John:3', u'Doe')
(u'John:4', u'Doe')
(u'John:5', u'Doe')
......
bash$ python fabric-demo.py
(u'John:0', u'Doe')
(u'John:1', u'Doe')
(u'John:2', u'Doe')
(u'John:3', u'Doe')
(u'John:4', u'Doe')
(u'John:5', u'Doe')
......
It's working, lets see how the load is spread.
Uptime: 9176 Threads: 5 Questions: 200842 Slow queries: 0 Opens: 74 Flush tables: 1 Open tables: 64 Queries per second avg: 21.887
ted@ted-Latitude-E6410:/opt/MySQL-fabric/src$ mysqladmin -uroot -proot -h127.0.0.1 -P63308 status
Uptime: 9178 Threads: 6 Questions: 211802 Slow queries: 0 Opens: 76 Flush tables: 1 Open tables: 65 Queries per second avg: 23.077
ted@ted-Latitude-E6410:/opt/MySQL-fabric/src$ mysqladmin -uroot -proot -h127.0.0.1 -P63309 status
Uptime: 9181 Threads: 5 Questions: 212836 Slow queries: 0 Opens: 74 Flush tables: 1 Open tables: 64 Queries per second avg: 23.182
ted@ted-Latitude-E6410:/opt/MySQL-fabric/src$ mysqladmin -uroot -proot -h127.0.0.1 -P63308 status
Uptime: 9178 Threads: 6 Questions: 211802 Slow queries: 0 Opens: 76 Flush tables: 1 Open tables: 65 Queries per second avg: 23.077
ted@ted-Latitude-E6410:/opt/MySQL-fabric/src$ mysqladmin -uroot -proot -h127.0.0.1 -P63309 status
Uptime: 9181 Threads: 5 Questions: 212836 Slow queries: 0 Opens: 74 Flush tables: 1 Open tables: 64 Queries per second avg: 23.182
All MySQL servers in the group are sharing the work load, that's great!
Switchover for regular maintenance
This is for when you need to take your primary offline and do regular maintenance, this could be anything from OS update or adding more disk to machine. All we need to do in this case if to tell MySQL Fabric to promote a new primary, since we like control we can also tell Fabric wich server we want to elect as primary.bash$ mysqlfabric group lookup_servers mygroup
Command :
{ success = True
return = [['fae19070-9ea5-11e3-8d61-5c260a4a26ad', '127.0.0.1:63307', False, 'SECONDARY'], ['fae19b5f-9ea5-11e3-8d61-5c260a4a26ad', '127.0.0.1:63308', True, 'PRIMARY'], ['fae19b62-9ea5-11e3-8d61-5c260a4a26ad', '127.0.0.1:63309', False, 'SECONDARY']]
activities =
}
bash$ mysqlfabric group promote mygroup fae19070-9ea5-11e3-8d61-5c260a4a26ad
Procedure :
{ uuid = 37e80ebe-9a1f-4d26-a647-d62ce6c49fd1,
finished = True,
success = True,
return = True,
activities =
}
bash$ mysqlfabric group lookup_servers mygroup
Command :
{ success = True
return = [['fae19070-9ea5-11e3-8d61-5c260a4a26ad', '127.0.0.1:63307', True, 'PRIMARY'], ['fae19b5f-9ea5-11e3-8d61-5c260a4a26ad', '127.0.0.1:63308', False, 'SECONDARY'], ['fae19b62-9ea5-11e3-8d61-5c260a4a26ad', '127.0.0.1:63309', False, 'SECONDARY']]
activities =
}
First we ran lookup_servers to see the current state of Fabric, next we promote MySQL instance in port 63307 to be new primary and finally we verify that our changes went through. Most of the time this operation is not affecting the application but somethime we see that the "Database connection error, trying to reconnect ..." is triggered and application reconnects.
Failover in case of Primary failure
Now, the interesting part begins, let's see what happens when we kill our primary server in the group. First we need to find our what server is primary.bash$ mysqlfabric group lookup_servers mygroup
Command :
{ success = True
return = [['ef4593df-9e2b-11e3-8a45-5c260a4a26ad', '127.0.0.1:63307', True, 'PRIMARY'], ['ef49ce23-9e2b-11e3-8a45-5c260a4a26ad', '127.0.0.1:63309', False, 'SECONDARY'], ['ef49d0b6-9e2b-11e3-8a45-5c260a4a26ad', '127.0.0.1:63308', False, 'SECONDARY']]
activities =
}
So, MySQL instance running on port 63307 is elected as primary server for my group. Get pid for this instance and lets kill it!
bash$ pgrep -fl mysql | grep 63307
ted@ted-Latitude-E6410:/opt/MySQL-fabric$ pgrep -fl mysql | grep 63307
24762 .......
bash$ kill -9 24762
In the fabric log you will now see:
[INFO] 1393349679.578709 - FailureDetector(mygroup) - Server (ef4593df-9e2b-11e3-8a45-5c260a4a26ad) in group (mygroup) has been lost.
[INFO] 1393349679.578908 - FailureDetector(mygroup) - Master (ef4593df-9e2b-11e3-8a45-5c260a4a26ad) in group (mygroup) has been lost.
ted@ted-Latitude-E6410:/opt/MySQL-fabric$ pgrep -fl mysql | grep 63307
24762 .......
bash$ kill -9 24762
In the fabric log you will now see:
[INFO] 1393349679.578709 - FailureDetector(mygroup) - Server (ef4593df-9e2b-11e3-8a45-5c260a4a26ad) in group (mygroup) has been lost.
[INFO] 1393349679.578908 - FailureDetector(mygroup) - Master (ef4593df-9e2b-11e3-8a45-5c260a4a26ad) in group (mygroup) has been lost.
Application is still working, no hickups and nothing stopped, let's see how MySQL Fabric has reorganized itself.
bash$ mysqlfabric group lookup_servers mygroup
Command :
{ success = True
return = [['ef4593df-9e2b-11e3-8a45-5c260a4a26ad', '127.0.0.1:63307', False, 'FAULTY'], ['ef49ce23-9e2b-11e3-8a45-5c260a4a26ad', '127.0.0.1:63309', True, 'PRIMARY'], ['ef49d0b6-9e2b-11e3-8a45-5c260a4a26ad', '127.0.0.1:63308', False, 'SECONDARY']]
activities =
}
Command :
{ success = True
return = [['ef4593df-9e2b-11e3-8a45-5c260a4a26ad', '127.0.0.1:63307', False, 'FAULTY'], ['ef49ce23-9e2b-11e3-8a45-5c260a4a26ad', '127.0.0.1:63309', True, 'PRIMARY'], ['ef49d0b6-9e2b-11e3-8a45-5c260a4a26ad', '127.0.0.1:63308', False, 'SECONDARY']]
activities =
}
MySQL instance on port 63307 is now considered as Faulty by Fabric and MySQL instance running on port 63309 took over as primary. Everything worked as expected. First time I forgot to run command to set group as active "mysqlfabric group activate mygroup", this meant no automatic failure detection and no new primary was elected so my python program died ...
If you don't want automatic failure detection or need to take your HA group offline you can always deactivate the group, if you do this you need to run the promote command manually to get new primary if your current one dies.
Conclusion
I'm very positive by this first encounter with MySQL Fabric, I like the fact that it is using the connectors to handle failover, no need to have VIP address or DNS manipulation solution in front of MySQL to achieve failover, of course you can always invest in a load balancer but this is normally very costly and adds one more product to take care of.As a future wish I hope we will have support for Fabric in all our connectors so all of you can leverage the usefulness of MySQL Fabric!
