Fabric结合GTID实现Mysql复制高可用

in 编程
关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9
mysql版本:Percona server 5.6.19-67.0 Percona Server (GPL), Release 67.0, Revision 618


Mysql安装参考:
http://my.oschina.net/anthonyyau/blog/284092
Fabric State store安装参考:
http://my.oschina.net/anthonyyau/blog/307165
半同步复制参考:
http://my.oschina.net/anthonyyau/blog/269800

环境:
4个服务器实例运行Percona server 5.6.19-67.0,fabric node安装在单独的服务器,每个Mysql实例在一台服务器上;
fabric为启用复制的3个mysql实例提供高可用,应用使用fabric-aware的连接器路由事务和SQL语句到合适的服务器,透明的进行读和写操作;
当前仅仅支持异步primary backup复制(半同步复制需要手工完成)。primary处理所有写操作,Secondaries使用mysql复制从primary同步,可以进行读操作。

服务器信息:


一、mysql实例和fabric state store准备
Mysql安装好后,每个fabric管理的实例需要开启gtid、二进制日志(不需要启动复制,由fabric完成,但是不支持配置异步复制),将以下配置放到[mysqld]段下面:
    
  1. log-bin=mysql-bin
  2. binlog_format=ROW
  3. server-id=29#保证每个mysql实例唯一
  4. log-slave-updates=true
  5. gtid-mode=on
  6. enforce-gtid-consistency=true
  7. sync-master-info=1

数据库用户账号准备:
1、超级管理员账号,本例使用root@'172.17.42.1',密码为admin@123;
2、fabric管理mysql实例的账号,本例使用fabric@'172.17.42.1',密码为fabric@456;
3、mysql复制专用账号,本例使用fabric@'172.17.0.%',密码为fabric@456,需要与fabric管理mysql的账号密码一致;

查看mysqlfabric命令帮助:
mysqlfabric help:显示简短的语法信息和帮忙命令
mysqlfabric help commands: 列出所有可用命令和描述
mysqlfabric help groups: 列出可用命令组
mysqlfabric help [group] [command]: 提供命令的详细帮助信息

二、使用Fabric创建mysql复制高可用组
1、创建组
    
  1. # mysqlfabric group create my_group
  2. Passwordforadmin: 
  3. Procedure:
  4. {uuid        =3f7e82bc-4291-4002-8688-1929fc63ed3e,
  5.   finished    =True,
  6.   success    =True,
  7.  return     =True,
  8.   activities  = 
  9. }

要输入xml-rpc密码,可以将密码指定到fabric的配置文件,或者设置disable_authentication = yes,需要重启fabric(先mysqlfabric manage stop,然后修改配置文件,不然将报"Permission denied."错误)。

2、添加mysql实例到组
    
  1. # mysqlfabric group add my_group 172.17.0.50:3306
  2. Procedure:
  3. {uuid        =6e69a5a7-667b-4e63-92c3-2f9f4269d633,
  4.   finished    =True,
  5.   success    =True,
  6.  return     =True,
  7.   activities  = 
  8. }
  9. # mysqlfabric group add my_group 172.17.0.47:3306
  10. Procedure:
  11. {uuid        =70a66b03-a16d-426d-a711-8f13da78fc8d,
  12.   finished    =True,
  13.   success    =True,
  14.  return     =True,
  15.   activities  = 
  16. }
  17. # mysqlfabric group add my_group 172.17.0.48:3306
  18. Procedure:
  19. {uuid        =15a2e1ad-b726-4fa8-bdf3-a1701e870166,
  20.   finished    =True,
  21.   success    =True,
  22.  return     =True,
  23.   activities  = 
  24. }

添加实例到组错误:
    
  1. # mysqlfabric --param=servers.user=fabric --param=servers.password=fabric@456 group add my_group 172.17.0.50:3306
  2. Passwordforadmin: 
  3. Procedure:
  4. {uuid        =d2c0d969-5b8a-40c2-ba7a-1bc963c08824,
  5.   finished    =True,
  6.   success    =False,
  7.  return     =ServerError:Erroraccessing server(172.17.0.50:3306).,
  8.   activities  = 
  9. }

日志:
    
  1. [DEBUG]1409151193.274349-Executor-4-Statement(BEGIN,Params(()).
  2. [DEBUG]1409151193.274764-Executor-4-Executing_add_server
  3. [DEBUG]1409151193.274849-Executor-4-Statement(SELECT group_id,description,master_uuid,master_defined,status FROM groups WHERE group_id=%s,Params(('my_group',)).
  4. [DEBUG]1409151193.275494-Executor-4-Startexecuting function:discover_uuid((),{'connection_timeout':5,'address':'172.17.0.50:3306'}).
  5. [DEBUG]1409151193.276136-Executor-4-Errorexecuting function:discover_uuid.
  6. [DEBUG]1409151193.276203-Executor-4-_add_server failed,executing compensation
  7. [DEBUG]1409151193.276255-Executor-4-Erroraccessing server(172.17.0.50:3306).
  8. [DEBUG]1409151193.276308-Executor-4-Statement(ROLLBACK,Params(()).
  9. [DEBUG]1409151193.276664-Executor-4-Completejob(60b0fa1f-3a39-43f3-aa71-46ea732aae84,946e0199-53ca-4fdc-9b49-f134d44db476,mysql.fabric.services.server._add_server,Error).

将密码写到fabric配置文件是可以,使用命令行参数不能覆盖配置文件参数,暂时没有找到是什么原因。

3、查看组中信息
可以看到所有实例的状态都是SECONDARY
    
  1. # mysqlfabric group lookup_servers my_group
  2. Command:
  3. {success    =True
  4.  return     =[{'status':'SECONDARY','server_uuid':'19a37552-2d44-11e4-af5c-763d1493518d','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.50:3306'},{'status':'SECONDARY','server_uuid':'7bd52611-2d44-11e4-af5f-3ecad7c2f82a','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.47:3306'},{'status':'SECONDARY','server_uuid':'ade3ee53-2d44-11e4-af60-de532998e8a6','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.48:3306'}]
  5.   activities  = 
  6. }

4、查看组健康详细信息
    
  1. # mysqlfabric group health my_group
  2. Command:
  3. {success    =True
  4.  return     ={'19a37552-2d44-11e4-af5c-763d1493518d':{'status':'SECONDARY','is_alive':True,'threads':{'is_configured':False}},'7bd52611-2d44-11e4-af5f-3ecad7c2f82a':{'status':'SECONDARY','is_alive':True,'threads':{'is_configured':False}},'ade3ee53-2d44-11e4-af60-de532998e8a6':{'status':'SECONDARY','is_alive':True,'threads':{'is_configured':False}}}
  5.   activities  = 
  6. }

5、提升和降级master服务
创建高可用组后,fabric没有意识到任何复制拓扑。需要提升一个为primary,降级剩余的服务器自动为secondaries(slaves)。
查看命令帮助:
# mysqlfabric help group promote
group promote group_id  [--slave_id=NONE] [--update_only] [--synchronous]

如果只是想更新state store,跳过复制配置,使用--update_only参数。
如果slave没有提供,将选择一个最好的候选者,候选者必须开启二进制日志,同时跟master属于同一个组,跟master延时小。进行故障切换操作,选择这个候选者,同时将其他slave指向到新的master,同时更新state store。

提升一个mysql实例为master:
    
  1. # mysqlfabric group promote my_group
  2. Procedure:
  3. {uuid        =21eb4d58-d7ec-41eb-a0e5-560eb5976272,
  4.   finished    =True,
  5.   success    =True,
  6.  return     =True,
  7.   activities  = 
  8. }

执行同样的命名将设置不同的服务器为primary,同时降级当前primary并选取一个新的。如果当前primary错误,执行同样的命令能手动触发选取一个新的primary。

一个标记位"faulty"状态的服务器不能提升为secondary或primary,需要先转换成"spare"状态。使用命令mysqlfabric server set_status <server-address> spare
如果直接从"faulty"转换成"secondary"将报错:
    
  1. # mysqlfabric server set_status 3ecc746f-2e05-11e4-b448-560d7281695e secondary
  2. Procedure:
  3. {uuid        =06d0acb1-ad01-483b-a1d4-e13c4b775fcd,
  4.   finished    =True,
  5.   success    =False,
  6.  return     =ServerError:Cannotchange server's(3ecc746f-2e05-11e4-b448-560d7281695e)status from(FAULTY)to(SECONDARY).,
  7.   activities  = 
  8. }

查看组状态和验证是否复制正常:
    
  1. # mysqlfabric group lookup_servers my_group
  2. Command:
  3. {success    =True
  4.  return     =[{'status':'PRIMARY','server_uuid':'19a37552-2d44-11e4-af5c-763d1493518d','mode':'READ_WRITE','weight':1.0,'address':'172.17.0.50:3306'},{'status':'SECONDARY','server_uuid':'7bd52611-2d44-11e4-af5f-3ecad7c2f82a','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.47:3306'},{'status':'SECONDARY','server_uuid':'ade3ee53-2d44-11e4-af60-de532998e8a6','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.48:3306'}]
  5.   activities  = 
  6. }

使用show slave status查看slave状态:
    
  1. # mysql -ufabric -pfabric@456 -h172.17.0.47 -e "show slave status\G;"
  2. ***************************1.row***************************
  3.                Slave_IO_State:Connectingto master
  4.                  Master_Host:172.17.0.50
  5.                  Master_User:fabric
  6.                  Master_Port:3306
  7.                Connect_Retry:60
  8.              Master_Log_File: 
  9.          Read_Master_Log_Pos:4
  10.                Relay_Log_File:d4f404f647b0-relay-bin.000001
  11.                Relay_Log_Pos:4
  12.        Relay_Master_Log_File: 
  13.              Slave_IO_Running:Connecting
  14.            Slave_SQL_Running:Yes
  15.              Replicate_Do_DB: 
  16.          Replicate_Ignore_DB: 
  17.            Replicate_Do_Table: 
  18.        Replicate_Ignore_Table: 
  19.      Replicate_Wild_Do_Table: 
  20.  Replicate_Wild_Ignore_Table: 
  21.                    Last_Errno:0
  22.                    Last_Error: 
  23.                  Skip_Counter:0
  24.          Exec_Master_Log_Pos:0
  25.              Relay_Log_Space:151
  26.              Until_Condition:None
  27.                Until_Log_File: 
  28.                Until_Log_Pos:0
  29.            Master_SSL_Allowed:No
  30.            Master_SSL_CA_File: 
  31.            Master_SSL_CA_Path: 
  32.              Master_SSL_Cert: 
  33.            Master_SSL_Cipher: 
  34.                Master_SSL_Key: 
  35.        Seconds_Behind_Master:0
  36. Master_SSL_Verify_Server_Cert:No
  37.                Last_IO_Errno:1045
  38.                Last_IO_Error:error connecting to master'fabric@172.17.0.50:3306'-retry-time:60 retries:31
  39.                Last_SQL_Errno:0
  40.                Last_SQL_Error: 
  41.  Replicate_Ignore_Server_Ids: 
  42.              Master_Server_Id:0
  43.                  Master_UUID: 
  44.              Master_Info_File:/usr/local/Percona-Server-5.6.19-rel67.0-618.Linux.x86_64/data/master.info
  45.                     SQL_Delay:0
  46.           SQL_Remaining_Delay:NULL
  47.      Slave_SQL_Running_State:Slavehas read all relay log;waitingforthe slave I/O thread to update it
  48.            Master_Retry_Count:86400
  49.                  Master_Bind: 
  50.      Last_IO_Error_Timestamp:14082716:42:50
  51.      Last_SQL_Error_Timestamp: 
  52.                Master_SSL_Crl: 
  53.            Master_SSL_Crlpath: 
  54.            Retrieved_Gtid_Set: 
  55.            Executed_Gtid_Set:7bd52611-2d44-11e4-af5f-3ecad7c2f82a:1-6
  56.                Auto_Position:1

看到连接错误:
因fabric使用其连接管理mysql实例的用户配置slave,所有需要重新授权slave访问master:
    
  1. # mysql -uroot -padmin@123 -h172.17.0.50 -e "grant replication slave on *.* to fabric@'172.17.0.%' identified by 'fabric@456'" 
  2. # mysql -uroot -padmin@123 -h172.17.0.47 -e "grant replication slave on *.* to fabric@'172.17.0.%' identified by 'fabric@456'" 
  3. # mysql -uroot -padmin@123 -h172.17.0.48 -e "grant replication slave on *.* to fabric@'172.17.0.%' identified by 'fabric@456'" 

授权后重新查看slave状态:看到连接正常,复制正常工作
    
  1. # mysql -ufabric -pfabric@456 -h172.17.0.48 -e 'show slave status\G'
  2. ***************************1.row***************************
  3.                Slave_IO_State:Waitingformaster to send event
  4.                  Master_Host:172.17.0.50
  5.                  Master_User:fabric
  6.                  Master_Port:3306
  7.                Connect_Retry:60
  8.              Master_Log_File:mysql-bin.000004
  9.          Read_Master_Log_Pos:3451
  10.                Relay_Log_File:e294ab366580-relay-bin.000003
  11.                Relay_Log_Pos:448
  12.        Relay_Master_Log_File:mysql-bin.000004
  13.              Slave_IO_Running:Yes
  14.            Slave_SQL_Running:Yes
  15.              Replicate_Do_DB: 
  16.          Replicate_Ignore_DB: 
  17.            Replicate_Do_Table: 
  18.        Replicate_Ignore_Table: 
  19.      Replicate_Wild_Do_Table: 
  20.  Replicate_Wild_Ignore_Table: 
  21.                    Last_Errno:0
  22.                    Last_Error: 
  23.                  Skip_Counter:0
  24.          Exec_Master_Log_Pos:3451
  25.              Relay_Log_Space:4169
  26.              Until_Condition:None
  27.                Until_Log_File: 
  28.                Until_Log_Pos:0
  29.            Master_SSL_Allowed:No
  30.            Master_SSL_CA_File: 
  31.            Master_SSL_CA_Path: 
  32.              Master_SSL_Cert: 
  33.            Master_SSL_Cipher: 
  34.                Master_SSL_Key: 
  35.        Seconds_Behind_Master:0
  36. Master_SSL_Verify_Server_Cert:No
  37.                Last_IO_Errno:0
  38.                Last_IO_Error: 
  39.                Last_SQL_Errno:0
  40.                Last_SQL_Error: 
  41.  Replicate_Ignore_Server_Ids: 
  42.              Master_Server_Id:27
  43.                  Master_UUID:19a37552-2d44-11e4-af5c-763d1493518d
  44.              Master_Info_File:/usr/local/Percona-Server-5.6.19-rel67.0-618.Linux.x86_64/data/master.info
  45.                     SQL_Delay:0
  46.           SQL_Remaining_Delay:NULL
  47.      Slave_SQL_Running_State:Slavehas read all relay log;waitingforthe slave I/O thread to update it
  48.            Master_Retry_Count:86400
  49.                  Master_Bind: 
  50.      Last_IO_Error_Timestamp: 
  51.      Last_SQL_Error_Timestamp: 
  52.                Master_SSL_Crl: 
  53.            Master_SSL_Crlpath: 
  54.            Retrieved_Gtid_Set:19a37552-2d44-11e4-af5c-763d1493518d:1-10
  55.            Executed_Gtid_Set:19a37552-2d44-11e4-af5c-763d1493518d:1-10
  56.                Auto_Position:1
  57. # mysql -ufabric -pfabric@456 -h172.17.0.47 -e 'show slave status\G'
  58. ***************************1.row***************************
  59.                Slave_IO_State:Waitingformaster to send event
  60.                  Master_Host:172.17.0.50
  61.                  Master_User:fabric
  62.                  Master_Port:3306
  63.                Connect_Retry:60
  64.              Master_Log_File:mysql-bin.000004
  65.          Read_Master_Log_Pos:3451
  66.                Relay_Log_File:d4f404f647b0-relay-bin.000004
  67.                Relay_Log_Pos:448
  68.        Relay_Master_Log_File:mysql-bin.000004
  69.              Slave_IO_Running:Yes
  70.            Slave_SQL_Running:Yes
  71.              Replicate_Do_DB: 
  72.          Replicate_Ignore_DB: 
  73.            Replicate_Do_Table: 
  74.        Replicate_Ignore_Table: 
  75.      Replicate_Wild_Do_Table: 
  76.  Replicate_Wild_Ignore_Table: 
  77.                    Last_Errno:0
  78.                    Last_Error: 
  79.                  Skip_Counter:0
  80.          Exec_Master_Log_Pos:3451
  81.              Relay_Log_Space:1205
  82.              Until_Condition:None
  83.                Until_Log_File: 
  84.                Until_Log_Pos:0
  85.            Master_SSL_Allowed:No
  86.            Master_SSL_CA_File: 
  87.            Master_SSL_CA_Path: 
  88.              Master_SSL_Cert: 
  89.            Master_SSL_Cipher: 
  90.                Master_SSL_Key: 
  91.        Seconds_Behind_Master:0
  92. Master_SSL_Verify_Server_Cert:No
  93.                Last_IO_Errno:0
  94.                Last_IO_Error: 
  95.                Last_SQL_Errno:0
  96.                Last_SQL_Error: 
  97.  Replicate_Ignore_Server_Ids: 
  98.              Master_Server_Id:27
  99.                  Master_UUID:19a37552-2d44-11e4-af5c-763d1493518d
  100.              Master_Info_File:/usr/local/Percona-Server-5.6.19-rel67.0-618.Linux.x86_64/data/master.info
  101.                     SQL_Delay:0
  102.           SQL_Remaining_Delay:NULL
  103.      Slave_SQL_Running_State:Slavehas read all relay log;waitingforthe slave I/O thread to update it
  104.            Master_Retry_Count:86400
  105.                  Master_Bind: 
  106.      Last_IO_Error_Timestamp: 
  107.      Last_SQL_Error_Timestamp: 
  108.                Master_SSL_Crl: 
  109.            Master_SSL_Crlpath: 
  110.            Retrieved_Gtid_Set:19a37552-2d44-11e4-af5c-763d1493518d:1-10
  111.            Executed_Gtid_Set:19a37552-2d44-11e4-af5c-763d1493518d:1-10
  112.                Auto_Position:1

6、启动或禁用故障检测
如果primary故障,可能想自动提升一个次级到primary,重定向剩余的secondaries到新的primary:
    
  1. # mysqlfabric group activate my_group
  2. Procedure:
  3. {uuid        =1d78324a-d39d-4098-8774-b0c2359a57ed,
  4.   finished    =True,
  5.   success    =True,
  6.  return     =True,
  7.   activities  = 
  8. }

状态检测语句:
[DEBUG] 1409157170.648943 - FailureDetector(my_group) - Statement (SELECT server_uuid, server_address, mode, status, weight FROM servers WHERE group_id = %s, Params(('my_group',)).
[DEBUG] 1409157172.655084 - FailureDetector(my_group) - Statement (SELECT group_id, description, master_uuid, master_defined, status FROM groups WHERE group_id = %s, Params(('my_group',)).

测试关闭当前master,看是否选取新的master,fabric能自动进程故障切换:
查看当前组的状态:当前master为"
172.17.0.51
"
    
  1. # mysqlfabric group lookup_servers my_group
  2. Command:
  3. {success    =True
  4.  return     =[{'status':'SECONDARY','server_uuid':'19a37552-2d44-11e4-af5c-763d1493518d','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.50:3306'},{'status':'PRIMARY','server_uuid':'3ecc746f-2e05-11e4-b448-560d7281695e','mode':'READ_WRITE','weight':1.0,'address':'172.17.0.51:3306'},{'status':'SECONDARY','server_uuid':'ade3ee53-2d44-11e4-af60-de532998e8a6','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.48:3306'}]
  5.   activities  = 
  6. }

关闭当前master实例:
    
  1. # mysqladmin -h172.17.0.51 -uroot -padmin@123 shutdown

关闭172.17.0.51实例后,fabric已经标记它的状态为"FAULTY";
    
  1. # mysqlfabric group lookup_servers my_group
  2. Command:
  3. {success    =True
  4.  return     =[{'status':'SECONDARY','server_uuid':'19a37552-2d44-11e4-af5c-763d1493518d','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.50:3306'},{'status':'FAULTY','server_uuid':'3ecc746f-2e05-11e4-b448-560d7281695e','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.51:3306'},{'status':'PRIMARY','server_uuid':'ade3ee53-2d44-11e4-af60-de532998e8a6','mode':'READ_WRITE','weight':1.0,'address':'172.17.0.48:3306'}]
  5.   activities  = 
  6. }

重新启动172.17.0.51:3306实例,但是状态不会恢复:
    
  1. # mysql -h172.17.0.51 -uroot -padmin@123 -e "show master status"
  2. +------------------+----------+--------------+------------------+-------------------------------------------+
  3. |File           |Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set                       |
  4. +------------------+----------+--------------+------------------+-------------------------------------------+
  5. |mysql-bin.000003|     191|             |                 |19a37552-2d44-11e4-af5c-763d1493518d:1-10|
  6. +------------------+----------+--------------+------------------+-------------------------------------------+
  7. # mysqlfabric group lookup_servers my_group
  8. Command:
  9. {success    =True
  10.  return     =[{'status':'SECONDARY','server_uuid':'19a37552-2d44-11e4-af5c-763d1493518d','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.50:3306'},{'status':'FAULTY','server_uuid':'3ecc746f-2e05-11e4-b448-560d7281695e','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.51:3306'},{'status':'PRIMARY','server_uuid':'ade3ee53-2d44-11e4-af60-de532998e8a6','mode':'READ_WRITE','weight':1.0,'address':'172.17.0.48:3306'}]
  11.   activities  = 
  12. }

需要手动设置服务器的状态:
查看帮助命令:
# mysqlfabric help server set_status
server set_status server_id status  [--update_only] [--synchronous]  

Set a server's status.

支持的状态值为primary,secondary,spare,faulty。
能手动改变服务器的状态,改变到spare,避免写和读访问,同时保证当故障切换时不会被选取。

改变错误状态的实例(172.17.0.51)的状态为secondary,注意需要先转换成spare:
    
  1. # mysqlfabric server set_status 3ecc746f-2e05-11e4-b448-560d7281695e spare
  2. Procedure:
  3. {uuid        =e96802d7-5fc1-4a70-94d4-4d8d31d13960,
  4.   finished    =True,
  5.   success    =True,
  6.  return     =True,
  7.   activities  = 
  8. }
  9. # mysqlfabric server set_status 3ecc746f-2e05-11e4-b448-560d7281695e secondary
  10. Procedure:
  11. {uuid        =c74edd89-ac4a-45ce-8d18-22423ed73561,
  12.   finished    =True,
  13.   success    =True,
  14.  return     =True,
  15.   activities  = 
  16. }

查看状态:
    
  1. # mysqlfabric group lookup_servers my_group
  2. Command:
  3. {success    =True
  4.  return     =[{'status':'SECONDARY','server_uuid':'19a37552-2d44-11e4-af5c-763d1493518d','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.50:3306'},{'status':'SECONDARY','server_uuid':'3ecc746f-2e05-11e4-b448-560d7281695e','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.51:3306'},{'status':'PRIMARY','server_uuid':'ade3ee53-2d44-11e4-af60-de532998e8a6','mode':'READ_WRITE','weight':1.0,'address':'172.17.0.48:3306'}]
  5.   activities  = 
  6. }

禁用故障检测自动切换:
    
  1. # mysqlfabric group deactivate my_group
  2. Procedure:
  3. {uuid        =a88d2bb7-f0c7-46d8-925a-c982287d6447,
  4.   finished    =True,
  5.   success    =True,
  6.  return     =True,
  7.   activities  = 
  8. }

7、执行更新和查询
应用程序通过fabric-aware连接器连接到fabric,获取服务器、组信息。

测试脚本:
    
  1. # cat fabric_test1.py
  2. #!/usr/bin/env python
  3. #coding:utf-8
  4. # Author:  AnthonyYau --<q_yxian@163.com>
  5. # Purpose: fabric test scripts1.
  6. # Created: 2014/8/26
  7. importsys
  8. importmysql.connector
  9. frommysql.connectorimportfabric
  10. def main():
  11.    try:
  12.         conn=mysql.connector.connect(
  13.             fabric={"host":"localhost","port":32274,"username":"admin",
  14.                      "password":"admin"}, #指定使用xml-rpm连接fabric的用户名和密码
  15.             user='root',database='test',password='admin@123',autocommit=True #指定mysql实例的用户名和密码
  16.        )
  17.    exceptException,e:
  18.        printe
  19.         sys.exit(1)
  20.    else:
  21.         conn.set_property(mode=fabric.MODE_READWRITE,group="my_group") #指定访问模式,以及需要访问的HA组
  22.         cur=conn.cursor()
  23.         cur.execute(
  24.            "CREATE TABLE IF NOT EXISTS subscribers (" #创建表
  25.        " sub_no INT,"
  26.        " first_name CHAR(40),"
  27.        " last_name CHAR(4)"
  28.        ")"
  29.        )
  30. finally:
    conn.close()
  31.     
  32. if __name__=='__main__':
  33.     main()

    
  1. # cat fabric_test2.py   
  2. #!/usr/bin/env python
  3. #coding:utf-8
  4. # Author:  AnthonyYau --<q_yxian@163.com>
  5. # Purpose: fabric test scripts2.
  6. # Created: 2014/8/26
  7. importsys
  8. importmysql.connector
  9. from mysql.connectorimportfabric
  10. def add_subscriber(conn,sub_no,first_name,last_name):
  11.     conn.set_property(group="my_group",mode=fabric.MODE_READWRITE) #指定访问模式,以及访问的组
  12.     cur=conn.cursor()
  13.     cur.execute(
  14.        "INSERT INTO subscribers VALUES (%s,%s,%s)",
  15.        (sub_no,first_name,last_name)
  16.    )
  17. def  main():
  18.    try:
  19.         conn=mysql.connector.connect(
  20.             fabric={"host":"localhost","port":32274,"username":"admin",
  21.                      "password":"admin"}, #指定使用xml-rpc连接fabric的用户名和密码
  22.             user="root",database="test",password="admin@123",autocommit=True #指定访问mysql实例的用户名和密码
  23.        )
  24.     exceptException,e:
  25.         print e
  26.         sys.exit(1)
  27.    else:
  28.        #conn.set_property(group="my_group",mode=fabric.MODE_READWRITE)
  29.         add_subscriber(conn,72,"Billy","Fish") #调用函数,插入数据
  30.         add_subscriber(conn,500,"Billy","Joel")
  31.         add_subscriber(conn,1500,"Arthur","Askey")
  32.         add_subscriber(conn,5000,"Billy","Fish")
  33.         add_subscriber(conn,15000,"Jimmy","White")
  34.         add_subscriber(conn,17542,"Bobby","Ball")   
  35. finally:
    conn.close()
  36.     
  37. if __name__=='__main__':

执行测试程序:
    
  1. python fabric_test1.py
  2. python fabric_test2.py

检查结果(查看两台从服务器的数据):当前slave为
172.17.0.50和
172.17.0.51




    
  1. # mysqlfabric group lookup_servers my_group
  2. Command:
  3. {success    =True
  4.  return     =[{'status':'SECONDARY','server_uuid':'19a37552-2d44-11e4-af5c-763d1493518d','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.50:3306'},{'status':'SECONDARY','server_uuid':'3ecc746f-2e05-11e4-b448-560d7281695e','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.51:3306'},{'status':'PRIMARY','server_uuid':'ade3ee53-2d44-11e4-af60-de532998e8a6','mode':'READ_WRITE','weight':1.0,'address':'172.17.0.48:3306'}]
  5.   activities  = 
  6. }

使用脚本检查结果:
    
  1. # python fabric_test3.py 
  2. 72BillyFish
  3. 500BillyJoel
  4. 1500ArthurAske
  5. 5000BillyFish
  6. 15000JimmyWhit
  7. 17542BobbyBall

查询脚本如下:
     
  1. #!/usr/bin/env python
  2. #coding:utf-8
  3. # Author:  AnthonyYau --<q_yxian@163.com>
  4. # Purpose: fabric test scripts3.
  5. # Created: 2014/8/27
  6. importsys
  7. importmysql.connector
  8. from mysql.connectorimportfabric
  9. def  main():
  10.    try:
  11.         conn=mysql.connector.connect(
  12.             fabric={"host":"localhost","port":32274,"username":"admin",
  13.                                  "password":"admin"},
  14.                         user="root",database="test",password="admin@123",autocommit=True       
  15.        )
  16.     exceptException,e:
  17.         print e
  18.         sys.exit(1)
  19.    else:
  20.         conn.set_property(group="my_group",mode=fabric.MODE_READONLY) #使用readonly访问模式(从slave获取数据),也可以使用readwrite
  21.         cur=conn.cursor()
  22.         cur.execute('select * from test.subscribers')
  23.         results=cur.fetchall()
  24.        ifresults:
  25.            forid,f_name,l_name in results:
  26.                 print"%s\t%s\t%s"%(id,f_name,l_name)
  27. finally:
    conn.close()
  28.     
  29. if __name__=='__main__':
  30.     main()

使用mysql客户端进行检查:
    
  1. # mysql -uroot -padmin@123 -h172.17.0.51 -e "select * from test.subscribers"
  2. +--------+------------+-----------+
  3. |sub_no|first_name|last_name|
  4. +--------+------------+-----------+
  5. |   72|Billy     |Fish     |
  6. |   500|Billy     |Joel     |
  7. | 1500|Arthur   |Aske     |
  8. | 5000|Billy     |Fish     |
  9. | 15000|Jimmy     |Whit     |
  10. | 17542|Bobby     |Ball     |
  11. +--------+------------+-----------+
  12. [root@yau620~]#mysql-uroot-padmin@123-h172.17.0.50-e"select * from test.subscribers"
  13. +--------+------------+-----------+
  14. |sub_no|first_name|last_name|
  15. +--------+------------+-----------+
  16. |   72|Billy     |Fish     |
  17. |   500|Billy     |Joel     |
  18. | 1500|Arthur   |Aske     |
  19. | 5000|Billy     |Fish     |
  20. | 15000|Jimmy     |Whit     |
  21. | 17542|Bobby     |Ball     |
  22. +--------+------------+-----------+

三、组维护
1、找出哪些服务器在组中,查看组状态
    
  1. # mysqlfabric group lookup_servers my_group
  2. Command:
  3. {success    =True
  4.  return     =[{'status':'PRIMARY','server_uuid':'19a37552-2d44-11e4-af5c-763d1493518d','mode':'READ_WRITE','weight':1.0,'address':'172.17.0.50:3306'},{'status':'SECONDARY','server_uuid':'7bd52611-2d44-11e4-af5f-3ecad7c2f82a','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.47:3306'},{'status':'SECONDARY','server_uuid':'ade3ee53-2d44-11e4-af60-de532998e8a6','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.48:3306'}]
  5.   activities  = 
  6. }

2、从组中移除mysql实例
有时需要将secondaries离线进行维护,在停止服务之前,需要先从组中移除它,避免fabric错误检查触发任何动作:
# mysqlfabric help group remove
group remove group_id server_id  [--synchronous]  

Remove a server from a group.

如从my_group组中移除server_uuid为7bd52611-2d44-11e4-af5f-3ecad7c2f82a的实例:
    
  1. # mysqlfabric group remove my_group 7bd52611-2d44-11e4-af5f-3ecad7c2f82a
  2. Procedure:
  3. {uuid        =07317ba2-497c-43f0-b8a9-e43ef9ded834,
  4.   finished    =True,
  5.   success    =True,
  6.  return     =True,
  7.   activities  = 
  8. }
  9. # mysqlfabric group lookup_servers my_group
  10. Command:
  11. {success    =True
  12.  return     =[{'status':'PRIMARY','server_uuid':'19a37552-2d44-11e4-af5c-763d1493518d','mode':'READ_WRITE','weight':1.0,'address':'172.17.0.50:3306'},{'status':'SECONDARY','server_uuid':'ade3ee53-2d44-11e4-af60-de532998e8a6','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.48:3306'}]
  13.   activities  = 
  14. }

然后可以重新添加到组,将自动开启复制:
    
  1. # mysqlfabric group add my_group 172.17.0.47:3306
  2. Procedure:
  3. {uuid        =39344790-3af8-4049-92a6-669bd2642a1c,
  4.   finished    =True,
  5.   success    =True,
  6.  return     =True,
  7.   activities  = 
  8. }

查看复制状态:
    
  1. # mysql -ufabric -pfabric@456 -h172.17.0.47 -e 'show slave status\G'
  2. ***************************1.row***************************
  3.                Slave_IO_State:Waitingformaster to send event
  4.                  Master_Host:172.17.0.50
  5.                  Master_User:fabric
  6.                  Master_Port:3306
  7.                Connect_Retry:60
  8.              Master_Log_File:mysql-bin.000004
  9.          Read_Master_Log_Pos:3451
  10.                Relay_Log_File:d4f404f647b0-relay-bin.000002
  11.                Relay_Log_Pos:408
  12.        Relay_Master_Log_File:mysql-bin.000004
  13.              Slave_IO_Running:Yes
  14.            Slave_SQL_Running:Yes
  15.              Replicate_Do_DB: 
  16.          Replicate_Ignore_DB: 
  17.            Replicate_Do_Table: 
  18.        Replicate_Ignore_Table: 
  19.      Replicate_Wild_Do_Table: 
  20.  Replicate_Wild_Ignore_Table: 
  21.                    Last_Errno:0
  22.                    Last_Error: 
  23.                  Skip_Counter:0
  24.          Exec_Master_Log_Pos:3451
  25.              Relay_Log_Space:619
  26.              Until_Condition:None
  27.                Until_Log_File: 
  28.                Until_Log_Pos:0
  29.            Master_SSL_Allowed:No
  30.            Master_SSL_CA_File: 
  31.            Master_SSL_CA_Path: 
  32.              Master_SSL_Cert: 
  33.            Master_SSL_Cipher: 
  34.                Master_SSL_Key: 
  35.        Seconds_Behind_Master:0
  36. Master_SSL_Verify_Server_Cert:No
  37.                Last_IO_Errno:0
  38.                Last_IO_Error: 
  39.                Last_SQL_Errno:0
  40.                Last_SQL_Error: 
  41.  Replicate_Ignore_Server_Ids: 
  42.              Master_Server_Id:27
  43.                  Master_UUID:19a37552-2d44-11e4-af5c-763d1493518d
  44.              Master_Info_File:/usr/local/Percona-Server-5.6.19-rel67.0-618.Linux.x86_64/data/master.info
  45.                     SQL_Delay:0
  46.           SQL_Remaining_Delay:NULL
  47.      Slave_SQL_Running_State:Slavehas read all relay log;waitingforthe slave I/O thread to update it
  48.            Master_Retry_Count:86400
  49.                  Master_Bind: 
  50.      Last_IO_Error_Timestamp: 
  51.      Last_SQL_Error_Timestamp: 
  52.                Master_SSL_Crl: 
  53.            Master_SSL_Crlpath: 
  54.            Retrieved_Gtid_Set: 
  55.            Executed_Gtid_Set:19a37552-2d44-11e4-af5c-763d1493518d:1-10
  56.                Auto_Position:1
3、添加一个全新的Mysql实例(实例IP为172.17.0.51)到HA group组
创建数据库复制账号:
    
  1. # mysql -uroot -padmin@123 -h172.17.0.51 -e "grant replication slave on *.* to fabric@'172.17.0.%' identified by 'fabric@456'"
  2. # mysql -uroot -padmin@123 -h172.17.0.51 -e "show slave status"

添加到my_group组:
    
  1. # mysqlfabric group add my_group 172.17.0.51:3306 
  2. Procedure:
  3. {uuid        =45c32c66-6e60-44d8-8406-308de01b1ef9,
  4.   finished    =True,
  5.   success    =True,
  6.  return     =True,
  7.   activities  = 
  8. }

查看复制状态,fabric已经开启复制:
    
  1. # mysql -uroot -padmin@123 -h172.17.0.51 -e "show slave status\G"
  2. ***************************1.row***************************
  3.                Slave_IO_State:Waitingformaster to send event
  4.                  Master_Host:172.17.0.50
  5.                  Master_User:fabric
  6.                  Master_Port:3306
  7.                Connect_Retry:60
  8.              Master_Log_File:mysql-bin.000004
  9.          Read_Master_Log_Pos:3451
  10.                Relay_Log_File:9355b5f279ad-relay-bin.000003
  11.                Relay_Log_Pos:448
  12.        Relay_Master_Log_File:mysql-bin.000004
  13.              Slave_IO_Running:Yes
  14.            Slave_SQL_Running:Yes
  15.              Replicate_Do_DB: 
  16.          Replicate_Ignore_DB: 
  17.            Replicate_Do_Table: 
  18.        Replicate_Ignore_Table: 
  19.      Replicate_Wild_Do_Table: 
  20.  Replicate_Wild_Ignore_Table: 
  21.                    Last_Errno:0
  22.                    Last_Error: 
  23.                  Skip_Counter:0
  24.          Exec_Master_Log_Pos:3451
  25.              Relay_Log_Space:4169
  26.              Until_Condition:None
  27.                Until_Log_File: 
  28.                Until_Log_Pos:0
  29.            Master_SSL_Allowed:No
  30.            Master_SSL_CA_File: 
  31.            Master_SSL_CA_Path: 
  32.              Master_SSL_Cert: 
  33.            Master_SSL_Cipher: 
  34.                Master_SSL_Key: 
  35.        Seconds_Behind_Master:0
  36. Master_SSL_Verify_Server_Cert:No
  37.                Last_IO_Errno:0
  38.                Last_IO_Error: 
  39.                Last_SQL_Errno:0
  40.                Last_SQL_Error: 
  41.  Replicate_Ignore_Server_Ids: 
  42.              Master_Server_Id:27
  43.                  Master_UUID:19a37552-2d44-11e4-af5c-763d1493518d
  44.              Master_Info_File:/usr/local/Percona-Server-5.6.19-rel67.0-618.Linux.x86_64/data/master.info
  45.                     SQL_Delay:0
  46.           SQL_Remaining_Delay:NULL
  47.      Slave_SQL_Running_State:Slavehas read all relay log;waitingforthe slave I/O thread to update it
  48.            Master_Retry_Count:86400
  49.                  Master_Bind: 
  50.      Last_IO_Error_Timestamp: 
  51.      Last_SQL_Error_Timestamp: 
  52.                Master_SSL_Crl: 
  53.            Master_SSL_Crlpath: 
  54.            Retrieved_Gtid_Set:19a37552-2d44-11e4-af5c-763d1493518d:1-10
  55.            Executed_Gtid_Set:19a37552-2d44-11e4-af5c-763d1493518d:1-10
  56.                Auto_Position:1

4、关闭primary
primary不能从组中移除,使用group demote关闭在组中的任何primary,不会重新选取一个新master,同时也不会关闭故障检测。
    
  1. # mysqlfabric group demote my_group
  2. Procedure:
  3. {uuid        =448df7ff-f0b5-4868-be15-8b505ec38c19,
  4.   finished    =True,
  5.   success    =True,
  6.  return     =True,
  7.   activities  = 
  8. }

查看组状态:所有mysql实例的状态都已经变成SECONDARY
    
  1. # mysqlfabric group lookup_servers my_group
  2. Command:
  3. {success    =True
  4.  return     =[{'status':'SECONDARY','server_uuid':'19a37552-2d44-11e4-af5c-763d1493518d','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.50:3306'},{'status':'SECONDARY','server_uuid':'3ecc746f-2e05-11e4-b448-560d7281695e','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.51:3306'},{'status':'SECONDARY','server_uuid':'7bd52611-2d44-11e4-af5f-3ecad7c2f82a','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.47:3306'},{'status':'SECONDARY','server_uuid':'ade3ee53-2d44-11e4-af60-de532998e8a6','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.48:3306'}]
  5.   activities  = 
  6. }

5、手动提升一个服务组,选取一个master
当前master是172.17.0.48:
    
  1. # mysqlfabric group promote my_group
  2. Procedure:
  3. {uuid        =d3072441-a023-42bd-8fe8-6746a620189d,
  4.   finished    =True,
  5.   success    =True,
  6.  return     =True,
  7.   activities  = 
  8. }
  9. # mysqlfabric group lookup_servers my_group
  10. Command:
  11. {success    =True
  12.  return     =[{'status':'SECONDARY','server_uuid':'19a37552-2d44-11e4-af5c-763d1493518d','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.50:3306'},{'status':'SECONDARY','server_uuid':'3ecc746f-2e05-11e4-b448-560d7281695e','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.51:3306'},{'status':'SECONDARY','server_uuid':'7bd52611-2d44-11e4-af5f-3ecad7c2f82a','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.47:3306'},{'status':'PRIMARY','server_uuid':'ade3ee53-2d44-11e4-af60-de532998e8a6','mode':'READ_WRITE','weight':1.0,'address':'172.17.0.48:3306'}]
  13.   activities  = 
  14. }

重新选取一个新master:fabric已经自动选取了172.17.0.47作为master
    
  1. # mysqlfabric group promote my_group
  2. Procedure:
  3. {uuid        =c779bfa6-5a57-4d60-9899-d64b85e9bee3,
  4.   finished    =True,
  5.   success    =True,
  6.  return     =True,
  7.   activities  = 
  8. }
  9. # mysqlfabric group lookup_servers my_group
  10. Command:
  11. {success    =True
  12.  return     =[{'status':'SECONDARY','server_uuid':'19a37552-2d44-11e4-af5c-763d1493518d','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.50:3306'},{'status':'SECONDARY','server_uuid':'3ecc746f-2e05-11e4-b448-560d7281695e','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.51:3306'},{'status':'PRIMARY','server_uuid':'7bd52611-2d44-11e4-af5f-3ecad7c2f82a','mode':'READ_WRITE','weight':1.0,'address':'172.17.0.47:3306'},{'status':'SECONDARY','server_uuid':'ade3ee53-2d44-11e4-af60-de532998e8a6','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.48:3306'}]
  13.   activities  = 
  14. }

6、移除组(使用group destroy)
如果组没有服务器,是空的,能移除组
    
  1. # mysqlfabric group create my_test
  2. Procedure:
  3. {uuid        =d53ba76d-1ed0-4e9d-96b3-01102b46f0bf,
  4.   finished    =True,
  5.   success    =True,
  6.  return     =True,
  7.   activities  = 
  8. }
  9. # mysqlfabric group destroy my_test
  10. Procedure:
  11. {uuid        =7046fea7-6e87-4dc0-afd4-e21ea399f9c1,
  12.   finished    =True,
  13.   success    =True,
  14.  return     =True,
  15.   activities  = 
  16. }

也能强制的移除非空的组(使用--force):
    
  1. # mysqlfabric group create my_test
  2. Procedure:
  3. {uuid        =be15e7bd-e597-4e67-aa4d-4ff34e0d3af8,
  4.   finished    =True,
  5.   success    =True,
  6.  return     =True,
  7.   activities  = 
  8. }
  9. # mysqlfabric group add my_test 172.17.0.52:3306
  10. Procedure:
  11. {uuid        =da981fe4-eeaf-4a3f-a770-d82d60a2ad64,
  12.   finished    =True,
  13.   success    =True,
  14.  return     =True,
  15.   activities  = 
  16. }
  17. # mysqlfabric group lookup_servers my_test
  18. Command:
  19. {success    =True
  20.  return     =[{'status':'SECONDARY','server_uuid':'7bd52611-2d44-11e4-af5f-3ecad7c2f82a','mode':'READ_ONLY','weight':1.0,'address':'172.17.0.52:3306'}]
  21.   activities  = 
  22. }
  23. # mysqlfabric group destroy my_test
  24. Procedure:
  25. {uuid        =63b0ec92-ffa2-4f54-9c21-530b1532d21c,
  26.   finished    =True,
  27.   success    =False,
  28.  return     =GroupError:Group(my_test)is not empty.,
  29.   activities  = 
  30. }
  31. # mysqlfabric group destroy my_test --force
  32. Procedure:
  33. {uuid        =37c5b49b-c451-41ad-9193-c9e1f06edaa9,
  34.   finished    =True,
  35.   success    =True,
  36.  return     =True,
  37.   activities  = 
  38. }

四、总结
1、使用fabric实现mysql复制高可用,以组为单位对mysql实例进行管理,可以管理多个HA组;
2、当前fabric只能实现自动配置异步复制,不支持半同步复制;
3、mysql实例进行恢复后,fabric不会自动改变实例的状态,需要手工修改;
4、能在线添加(自动配置好复制,立即同步数据)和删除mysql实例;
5、fabric state store和fabric note当前是单点,虽然
fabric-aware连接器可以缓存路由信息(可以配置ttl值);
6、使用fabric不需要对slave配置vip,不像MHA和MMM等需要对vip进行管理;
7、mysql支持需要支持gtid,也是就要升级到mysql 5.6.10以上版本;



参考:
1、
http://www.clusterdb.com/mysql-fabric/mysql-fabric-adding-high-availability-to-mysql
2、官档:
http://dev.mysql.com/doc/mysql-utilities/1.5/en/fabric-quick-start-replication.html



来自为知笔记(Wiz)



关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9
扫一扫关注公众号添加购物返利助手,领红包
Comments are closed.

推荐使用阿里云服务器

超多优惠券

服务器最低一折,一年不到100!

朕已阅去看看