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]段下面:
- log-bin=mysql-bin
- binlog_format=ROW
- server-id = 29 #保證每個mysql實例唯一
- log-slave-updates=true
- gtid-mode=on
- enforce-gtid-consistency=true
- 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、創建組
- # mysqlfabric group create my_group
- Password for admin:
- Procedure :
- { uuid = 3f7e82bc-4291-4002-8688-1929fc63ed3e,
- finished = True,
- success = True,
- return = True,
- activities =
- }
要輸入xml-rpc密碼,可以將密碼指定到fabric的配置文件,或者設置disable_authentication = yes,需要重啟fabric(先mysqlfabric manage stop,然後修改配置文件,不然將報"Permission denied."錯誤)。
2、添加mysql實例到組
- # mysqlfabric group add my_group 172.17.0.50:3306
- Procedure :
- { uuid = 6e69a5a7-667b-4e63-92c3-2f9f4269d633,
- finished = True,
- success = True,
- return = True,
- activities =
- }
- # mysqlfabric group add my_group 172.17.0.47:3306
- Procedure :
- { uuid = 70a66b03-a16d-426d-a711-8f13da78fc8d,
- finished = True,
- success = True,
- return = True,
- activities =
- }
- # mysqlfabric group add my_group 172.17.0.48:3306
- Procedure :
- { uuid = 15a2e1ad-b726-4fa8-bdf3-a1701e870166,
- finished = True,
- success = True,
- return = True,
- activities =
- }
添加實例到組錯誤:
- # mysqlfabric --param=servers.user=fabric --param=servers.password=fabric@456 group add my_group 172.17.0.50:3306
- Password for admin:
- Procedure :
- { uuid = d2c0d969-5b8a-40c2-ba7a-1bc963c08824,
- finished = True,
- success = False,
- return = ServerError: Error accessing server (172.17.0.50:3306).,
- activities =
- }
日志:
- [DEBUG] 1409151193.274349 - Executor-4 - Statement (BEGIN, Params(()).
- [DEBUG] 1409151193.274764 - Executor-4 - Executing _add_server
- [DEBUG] 1409151193.274849 - Executor-4 - Statement (SELECT group_id, description, master_uuid, master_defined, status FROM groups WHERE group_id = %s, Params(('my_group',)).
- [DEBUG] 1409151193.275494 - Executor-4 - Start executing function: discover_uuid((), {'connection_timeout': 5, 'address': '172.17.0.50:3306'}).
- [DEBUG] 1409151193.276136 - Executor-4 - Error executing function: discover_uuid.
- [DEBUG] 1409151193.276203 - Executor-4 - _add_server failed, executing compensation
- [DEBUG] 1409151193.276255 - Executor-4 - Error accessing server (172.17.0.50:3306).
- [DEBUG] 1409151193.276308 - Executor-4 - Statement (ROLLBACK, Params(()).
- [DEBUG] 1409151193.276664 - Executor-4 - Complete job (60b0fa1f-3a39-43f3-aa71-46ea732aae84, 946e0199-53ca-4fdc-9b49-f134d44db476, mysql.fabric.services.server._add_server, Error).
將密碼寫到fabric配置文件是可以,使用命令行參數不能覆蓋配置文件參數,暫時沒有找到是什麼原因。
3、查看組中信息
可以看到所有實例的狀態都是SECONDARY
- # mysqlfabric group lookup_servers my_group
- Command :
- { success = True
- 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'}]
- activities =
- }
4、查看組健康詳細信息
- # mysqlfabric group health my_group
- Command :
- { success = True
- 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}}}
- activities =
- }
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:
- # mysqlfabric group promote my_group
- Procedure :
- { uuid = 21eb4d58-d7ec-41eb-a0e5-560eb5976272,
- finished = True,
- success = True,
- return = True,
- activities =
- }
執行同樣的命名將設置不同的服務器為primary,同時降級當前primary並選取一個新的。如果當前primary錯誤,執行同樣的命令能手動觸發選取一個新的primary。
一個標記位"faulty"狀態的服務器不能提升為secondary或primary,需要先轉換成"spare"狀態。使用命令mysqlfabric server set_status <server-address> spare
如果直接從"faulty"轉換成"secondary"將報錯:
- # mysqlfabric server set_status 3ecc746f-2e05-11e4-b448-560d7281695e secondary
- Procedure :
- { uuid = 06d0acb1-ad01-483b-a1d4-e13c4b775fcd,
- finished = True,
- success = False,
- return = ServerError: Cannot change server's (3ecc746f-2e05-11e4-b448-560d7281695e) status from (FAULTY) to (SECONDARY).,
- activities =
- }
查看組狀態和驗證是否復制正常:
- # mysqlfabric group lookup_servers my_group
- Command :
- { success = True
- 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'}]
- activities =
- }
使用show slave status查看slave狀態:
- # mysql -ufabric -pfabric@456 -h172.17.0.47 -e "show slave status\G;"
- *************************** 1. row ***************************
- Slave_IO_State: Connecting to master
- Master_Host: 172.17.0.50
- Master_User: fabric
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File:
- Read_Master_Log_Pos: 4
- Relay_Log_File: d4f404f647b0-relay-bin.000001
- Relay_Log_Pos: 4
- Relay_Master_Log_File:
- Slave_IO_Running: Connecting
- Slave_SQL_Running: Yes
- Replicate_Do_DB:
- Replicate_Ignore_DB:
- Replicate_Do_Table:
- Replicate_Ignore_Table:
- Replicate_Wild_Do_Table:
- Replicate_Wild_Ignore_Table:
- Last_Errno: 0
- Last_Error:
- Skip_Counter: 0
- Exec_Master_Log_Pos: 0
- Relay_Log_Space: 151
- Until_Condition: None
- Until_Log_File:
- Until_Log_Pos: 0
- Master_SSL_Allowed: No
- Master_SSL_CA_File:
- Master_SSL_CA_Path:
- Master_SSL_Cert:
- Master_SSL_Cipher:
- Master_SSL_Key:
- Seconds_Behind_Master: 0
- Master_SSL_Verify_Server_Cert: No
- Last_IO_Errno: 1045
- Last_IO_Error: error connecting to master '[email protected]:3306' - retry-time: 60 retries: 31
- Last_SQL_Errno: 0
- Last_SQL_Error:
- Replicate_Ignore_Server_Ids:
- Master_Server_Id: 0
- Master_UUID:
- Master_Info_File: /usr/local/Percona-Server-5.6.19-rel67.0-618.Linux.x86_64/data/master.info
- SQL_Delay: 0
- SQL_Remaining_Delay: NULL
- Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
- Master_Retry_Count: 86400
- Master_Bind:
- Last_IO_Error_Timestamp: 140827 16:42:50
- Last_SQL_Error_Timestamp:
- Master_SSL_Crl:
- Master_SSL_Crlpath:
- Retrieved_Gtid_Set:
- Executed_Gtid_Set: 7bd52611-2d44-11e4-af5f-3ecad7c2f82a:1-6
- Auto_Position: 1
看到連接錯誤:
因fabric使用其連接管理mysql實例的用戶配置slave,所有需要重新授權slave訪問master:
- # mysql -uroot -padmin@123 -h172.17.0.50 -e "grant replication slave on *.* to fabric@'172.17.0.%' identified by 'fabric@456'"
- # mysql -uroot -padmin@123 -h172.17.0.47 -e "grant replication slave on *.* to fabric@'172.17.0.%' identified by 'fabric@456'"
- # mysql -uroot -padmin@123 -h172.17.0.48 -e "grant replication slave on *.* to fabric@'172.17.0.%' identified by 'fabric@456'"
授權後重新查看slave狀態:看到連接正常,復制正常工作
- # mysql -ufabric -pfabric@456 -h172.17.0.48 -e 'show slave status\G'
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: 172.17.0.50
- Master_User: fabric
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: mysql-bin.000004
- Read_Master_Log_Pos: 3451
- Relay_Log_File: e294ab366580-relay-bin.000003
- Relay_Log_Pos: 448
- Relay_Master_Log_File: mysql-bin.000004
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- Replicate_Do_DB:
- Replicate_Ignore_DB:
- Replicate_Do_Table:
- Replicate_Ignore_Table:
- Replicate_Wild_Do_Table:
- Replicate_Wild_Ignore_Table:
- Last_Errno: 0
- Last_Error:
- Skip_Counter: 0
- Exec_Master_Log_Pos: 3451
- Relay_Log_Space: 4169
- Until_Condition: None
- Until_Log_File:
- Until_Log_Pos: 0
- Master_SSL_Allowed: No
- Master_SSL_CA_File:
- Master_SSL_CA_Path:
- Master_SSL_Cert:
- Master_SSL_Cipher:
- Master_SSL_Key:
- Seconds_Behind_Master: 0
- Master_SSL_Verify_Server_Cert: No
- Last_IO_Errno: 0
- Last_IO_Error:
- Last_SQL_Errno: 0
- Last_SQL_Error:
- Replicate_Ignore_Server_Ids:
- Master_Server_Id: 27
- Master_UUID: 19a37552-2d44-11e4-af5c-763d1493518d
- Master_Info_File: /usr/local/Percona-Server-5.6.19-rel67.0-618.Linux.x86_64/data/master.info
- SQL_Delay: 0
- SQL_Remaining_Delay: NULL
- Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
- Master_Retry_Count: 86400
- Master_Bind:
- Last_IO_Error_Timestamp:
- Last_SQL_Error_Timestamp:
- Master_SSL_Crl:
- Master_SSL_Crlpath:
- Retrieved_Gtid_Set: 19a37552-2d44-11e4-af5c-763d1493518d:1-10
- Executed_Gtid_Set: 19a37552-2d44-11e4-af5c-763d1493518d:1-10
- Auto_Position: 1
- # mysql -ufabric -pfabric@456 -h172.17.0.47 -e 'show slave status\G'
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: 172.17.0.50
- Master_User: fabric
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: mysql-bin.000004
- Read_Master_Log_Pos: 3451
- Relay_Log_File: d4f404f647b0-relay-bin.000004
- Relay_Log_Pos: 448
- Relay_Master_Log_File: mysql-bin.000004
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- Replicate_Do_DB:
- Replicate_Ignore_DB:
- Replicate_Do_Table:
- Replicate_Ignore_Table:
- Replicate_Wild_Do_Table:
- Replicate_Wild_Ignore_Table:
- Last_Errno: 0
- Last_Error:
- Skip_Counter: 0
- Exec_Master_Log_Pos: 3451
- Relay_Log_Space: 1205
- Until_Condition: None
- Until_Log_File:
- Until_Log_Pos: 0
- Master_SSL_Allowed: No
- Master_SSL_CA_File:
- Master_SSL_CA_Path:
- Master_SSL_Cert:
- Master_SSL_Cipher:
- Master_SSL_Key:
- Seconds_Behind_Master: 0
- Master_SSL_Verify_Server_Cert: No
- Last_IO_Errno: 0
- Last_IO_Error:
- Last_SQL_Errno: 0
- Last_SQL_Error:
- Replicate_Ignore_Server_Ids:
- Master_Server_Id: 27
- Master_UUID: 19a37552-2d44-11e4-af5c-763d1493518d
- Master_Info_File: /usr/local/Percona-Server-5.6.19-rel67.0-618.Linux.x86_64/data/master.info
- SQL_Delay: 0
- SQL_Remaining_Delay: NULL
- Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
- Master_Retry_Count: 86400
- Master_Bind:
- Last_IO_Error_Timestamp:
- Last_SQL_Error_Timestamp:
- Master_SSL_Crl:
- Master_SSL_Crlpath:
- Retrieved_Gtid_Set: 19a37552-2d44-11e4-af5c-763d1493518d:1-10
- Executed_Gtid_Set: 19a37552-2d44-11e4-af5c-763d1493518d:1-10
- Auto_Position: 1