本文说明：
本文参照了BLOG http://blog.csdn.net/seteor/article/details/42741613 做的实验，非常感谢
fabric实现HA功能，自动故障切换，然后用python脚本测试HA
环境说明：master node ：192.168.10.228
slave node1：192.168.10.229
slave node2：192.168.10.230
fabric node ：192.168.10.232
fabric store：192.168.10.232
安装mysql-utilities
前提条件：
1、python V2.6
2、mysql python connector
到MYSQL官网下载rpm包，然后安装：
[root@yw32 mha]# rpm -ivh mysql-connector-python-2.1.3-1.el6.x86_64.rpm
warning: mysql-connector-python-2.1.3-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:mysql-connector-python ########################################### [100%]
[root@yw32 mha]# rpm -ivh mysql-utilities-1.5.6-1.el6.noarch.rpm
warning: mysql-utilities-1.5.6-1.el6.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:mysql-utilities ########################################### [100%]
创建backing store mysql访问账号：
grant all on fabric.* to fabric@'%' identified by 'fabric@123';
创建fabric node server到各个mysql的访问账号：
grant all on *.* to fabric@'%' identified by '123456';
配置fabric：
[DEFAULT]
prefix =
sysconfdir = /etc
logdir = /var/log
[storage]
address = 192.168.10.232:3306
user = fabric
password = fabric@123
database = fabric
auth_plugin = mysql_native_password
connection_timeout = 6
connection_attempts = 6
connection_delay = 1
[servers]
user = fabric
password = 123456
[protocol.xmlrpc]
address = 0.0.0.0:32274
threads = 5
user = admin
password = admin
disable_authentication = no
realm = MySQL Fabric
ssl_ca =
ssl_cert =
ssl_key =
[protocol.mysql]
address = localhost:32275
user = admin
password = secret
disable_authentication = no
ssl_ca =
ssl_cert =
ssl_key =
[executor]
executors = 5
[logging]
level = INFO
url = file:///var/log/fabric.log
[sharding]
mysqldump_program = /usr/bin/mysqldump
mysqlclient_program = /usr/bin/mysql
[statistics]
prune_time = 3600
[failure_tracking]
notifications = 300
notification_clients = 50
notification_interval = 60 #
failover_interval = 0
detections = 3
detection_interval = 6
detection_timeout = 1
prune_time = 3600
[connector]
ttl = 1
[client]
password =
创建fabric backing store 库：
[root@yw32 mysql]# mysqlfabric manage setup --param=storage.user=fabric --param=storage.password=fabric@123
[INFO] 1459496461.164009 - MainThread - Initializing persister: user (fabric), server (192.168.10.232:3306), database (fabric).
[INFO] 1459496470.121373 - MainThread - Initial password for admin/mysql set
Password set for admin/mysql from configuration file.
[INFO] 1459496470.162701 - MainThread - Password set for admin/mysql from configuration file.
[INFO] 1459496470.165474 - MainThread - Initial password for admin/xmlrpc set
Password set for admin/xmlrpc from configuration file.
[INFO] 1459496470.198827 - MainThread - Password set for admin/xmlrpc from configuration file.
启动fabric nodes：
mysqlfabric manage start --daemonize
创建一个HA组my_group：
[root@yw32 mysql]# mysqlfabric group create my_group
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
de19dcbf-7cb3-4783-84f7-2f636bef4e15 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x20c2050>.
4 2 1.4595e+09 Executing action (_create_group).
5 2 1.4595e+09 Executed action (_create_group).
往HA组添加成员 192.168.10.228:3306
[root@yw32 mysql]# mysqlfabric group add my_group 192.168.10.228:3306
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
551981f6-de1c-4660-aef6-cbc9980a9489 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x20c2410>.
4 2 1.4595e+09 Executing action (_add_server).
5 2 1.4595e+09 Executed action (_add_server).
往HA组添加成员 192.168.10.229:3306
[root@yw32 mysql]# mysqlfabric group add my_group 192.168.10.229:3306
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
8e09e2d8-b7e7-46f7-be1a-266613dbbd8d 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x20c2410>.
4 2 1.4595e+09 Executing action (_add_server).
5 2 1.4595e+09 Executed action (_add_server).
往HA组添加成员 192.168.10.230:3306
[root@yw32 mysql]# mysqlfabric group add my_group 192.168.10.230:3306
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
0dff18a2-d8ee-4b33-88a9-74c12e19ce9d 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x20c2410>.
4 2 1.4595e+09 Executing action (_add_server).
5 2 1.4595e+09 Executed action (_add_server).
查看HA组的成员，发现status都是SECONDARY,即便你已经部署了主从，它也不能识别
[root@yw32 mysql]# mysqlfabric group lookup_servers my_group
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
server_uuid address status mode weight
------------------------------------ ------------------- --------- --------- ------
4c622c12-ebdd-11e5-8f8f-0050569c155b 192.168.10.229:3306 SECONDARY READ_ONLY 1.0
5348580a-f629-11e5-92b4-0050569c4a6a 192.168.10.228:3306 SECONDARY READ_ONLY 1.0
88c90835-f644-11e5-9365-0050569c0ea8 192.168.10.230:3306 SECONDARY READ_ONLY 1.0
选举一个主库：
[root@yw32 mysql]# mysqlfabric group promote my_group
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
f3e50f87-2109-44af-9144-ceae99bcc9f1 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x1ed83d0>.
4 2 1.4595e+09 Executing action (_define_ha_operation).
5 2 1.4595e+09 Executed action (_define_ha_operation).
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x202cc90>.
4 2 1.4595e+09 Executing action (_find_candidate_fail).
5 2 1.4595e+09 Executed action (_find_candidate_fail).
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x202ccd0>.
4 2 1.4595e+09 Executing action (_check_candidate_fail).
5 2 1.4595e+09 Executed action (_check_candidate_fail).
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x202ca50>.
4 2 1.4595e+09 Executing action (_wait_slave_fail).
5 2 1.4595e+09 Executed action (_wait_slave_fail).
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x2031a90>.
4 2 1.4595e+09 Executing action (_change_to_candidate).
5 2 1.4595e+09 Executed action (_change_to_candidate).
查看HA组状态，发现已经有一个primary节点了
[root@yw32 mysql]# mysqlfabric group lookup_servers my_group
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
server_uuid address status mode weight
------------------------------------ ------------------- --------- ---------- ------
4c622c12-ebdd-11e5-8f8f-0050569c155b 192.168.10.229:3306 SECONDARY READ_ONLY 1.0
5348580a-f629-11e5-92b4-0050569c4a6a 192.168.10.228:3306 SECONDARY READ_ONLY 1.0
88c90835-f644-11e5-9365-0050569c0ea8 192.168.10.230:3306 PRIMARY READ_WRITE 1.0
默认情况下，fabric没有自动故障切换功能，需要激活：
[root@yw32 mysql]# mysqlfabric group activate my_group
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
8cfd2816-c12a-4dae-8921-16a3dd265e2b 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x20c25d0>.
4 2 1.4595e+09 Executing action (_activate_group).
5 2 1.4595e+09 Executed action (_activate_group).
[root@yw32 mysql]# mysqlfabric group lookup_servers my_group
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
server_uuid address status mode weight
------------------------------------ ------------------- --------- ---------- ------
4c622c12-ebdd-11e5-8f8f-0050569c155b 192.168.10.229:3306 SECONDARY READ_ONLY 1.0
5348580a-f629-11e5-92b4-0050569c4a6a 192.168.10.228:3306 SECONDARY READ_ONLY 1.0
88c90835-f644-11e5-9365-0050569c0ea8 192.168.10.230:3306 PRIMARY READ_WRITE 1.0
关闭主节点后，会选举一个新的节点作为主节点。
[root@yw32 mysql]# mysqlfabric group lookup_servers my_group
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
server_uuid address status mode weight
------------------------------------ ------------------- --------- ---------- ------
4c622c12-ebdd-11e5-8f8f-0050569c155b 192.168.10.229:3306 SECONDARY READ_ONLY 1.0
5348580a-f629-11e5-92b4-0050569c4a6a 192.168.10.228:3306 PRIMARY READ_WRITE 1.0
88c90835-f644-11e5-9365-0050569c0ea8 192.168.10.230:3306 FAULTY READ_WRITE 1.0
[root@yw32 mysql]# mysqlfabric group
Commands available in group 'group' are:
group activate group_id [--synchronous]
group description group_id [--description=NONE] [--synchronous]
group deactivate group_id [--synchronous]
group create group_id [--description=NONE] [--synchronous]
group remove group_id server_id [--synchronous]
group add group_id address [--timeout=NONE] [--update_only] [--synchronous]
group health group_id
group lookup_servers group_id [--server_id=NONE] [--status=NONE] [--mode=NONE]
group destroy group_id [--synchronous]
group demote group_id [--update_only] [--synchronous]
group promote group_id [--slave_id=NONE] [--update_only] [--synchronous]
group lookup_groups [--group_id=NONE]
如何把FAULTY状态的节点重新加入HA组呢？
首先remove faulty节点，然后重新add进来即可
[root@yw32 ~]# mysqlfabric group remove my_group 192.168.10.228:3306
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
3859e365-2c5a-4a47-ba5a-6ab2d48e9248 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.45982e+09 Triggered by <mysql.fabric.events.Event object at 0x20c2510>.
4 2 1.45982e+09 Executing action (_remove_server).
5 2 1.45982e+09 Executed action (_remove_server).
[root@yw32 ~]# mysqlfabric group lookup_servers my_group
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
server_uuid address status mode weight
------------------------------------ ------------------- --------- ---------- ------
4c622c12-ebdd-11e5-8f8f-0050569c155b 192.168.10.229:3306 SECONDARY READ_ONLY 1.0
88c90835-f644-11e5-9365-0050569c0ea8 192.168.10.230:3306 PRIMARY READ_WRITE 1.0
[root@yw32 ~]# mysqlfabric group add my_group 192.168.10.228:3306
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
dbe6321c-2c07-478e-bddf-6d6c560f9a74 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.45982e+09 Triggered by <mysql.fabric.events.Event object at 0x20c2410>.
4 2 1.45982e+09 Executing action (_add_server).
5 2 1.45982e+09 Executed action (_add_server).
[root@yw32 ~]# mysqlfabric group lookup_servers my_group
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
server_uuid address status mode weight
------------------------------------ ------------------- --------- ---------- ------
4c622c12-ebdd-11e5-8f8f-0050569c155b 192.168.10.229:3306 SECONDARY READ_ONLY 1.0
5348580a-f629-11e5-92b4-0050569c4a6a 192.168.10.228:3306 SECONDARY READ_ONLY 1.0
88c90835-f644-11e5-9365-0050569c0ea8 192.168.10.230:3306 PRIMARY READ_WRITE 1.0
#当HA组所有节点都重启后，节点不能自动从faulty状态恢复回来，需要先demote和deactivate，然后依次修改状态为spare和secondary，最后选举master
mysqlfabric group demote my_group
mysqlfabric group deactivate my_group
mysqlfabric server set_status 5348580a-f629-11e5-92b4-0050569c4a6a spare
mysqlfabric server set_status 5348580a-f629-11e5-92b4-0050569c4a6a secondary
mysqlfabric group promote my_group
测试脚本：
1、建表脚本
#!/usr/bin/python
import sys
import mysql.connector
from mysql.connector import fabric
def main():
try:
conn = mysql.connector.connect(
fabric ={"host":"localhost","port":32274,"username":"admin",
"password":"admin"},
user = 'fabric',database = 'test',password = '123456',autocommit =True
)
except Exception,e:
print e
sys.exit(1)
else:
conn.set_property(mode=fabric.MODE_READWRITE,group="my_group")
cur = conn.cursor()
cur.execute(
"CREATE TABLE IF NOT EXISTS t1 ("
" id INT,"
" first_name varchar(40),"
" last_name varchar(40)"
")"
)
finally:
conn.close()
if __name__ == '__main__':
main()
2.插入数据测试脚本：
#!/usr/bin/python
import sys
import mysql.connector
from mysql.connector import fabric
defadd_subscriber(conn,id,first_name,last_name):
conn.set_property(group="mygroup",mode=fabric.MODE_READWRITE)
cur = conn.cursor()
cur.execute(
"INSERT INTO t1 VALUES (%s,%s,%s)",
(id,first_name,last_name)
)
def main():
try:
conn = mysql.connector.connect(
fabric ={"host":"localhost","port":32274,"username":"admin",
"password":"admin"},
user="fabric",database="mha",password="123456",autocommit=True
)
except Exception,e:
print e
sys.exit(1)
else:
#conn.set_property(group="mygroup",mode=fabric.MODE_READWRITE)
add_subscriber(conn, 1, "Tom", "Jerry")
add_subscriber(conn, 2, "Jack", "Tom")
add_subscriber(conn, 3, "Lily", "Lee")
finally:
conn.close()
if __name__ == '__main__':
main()
#常用相关命令
mysqlfabricgroup create mygroup #创建HA组
mysqlfabricgroup destroy mygroup #删除HA组
mysqlfabricgroup add mygroup 192.168.247.132:3306 #添加组成员
mysqlfabricgroup remove mygroup c505ce10-9bc0-11e4-bfd4-000c290e7abe #移出组成员
mysqlfabricgroup lookup_servers mygroup #查看组成员
mysqlfabricgroup promote mygroup #选举master
mysqlfabric groupactivate mygroup #激活自动故障转移
mysqlfabric group deactivatemygroup #禁用自动故障转移
mysqlfabric serverset_status server_uuid status #变更服务器状态
mysqlfabrichelp manage #manage命令帮助
mysqlfabrichelp group #group命令帮助
mysqlfabrichelp server #server命令帮助
总结：
1. 程序必须通过Fabric-aware connectors来实现路由，目前只支持PHP,Python 和Java;
2. mysql实例进行恢复后，fabric不会自动改变实例的状态，需要手工修改;
3. fabric state store和fabric note当前是单点，mysqlfabric进程本身不提供容错，出现故障时需要重启，当mysql fabric不可用时，fabric-aware连接器能够通过本地的cache继续路由操作(配置ttl值)；
4. 基于GTID的复制本身存在一些限制。
参考文档：http://blog.csdn.net/seteor/article/details/42741613
309
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
