MGR主備集群實(shí)現(xiàn)異步連接故障轉(zhuǎn)移的步驟
1.概述
MySQL 8.0.22 開始，支持異步連接故障切換機(jī)制，在現(xiàn)有主從復(fù)制連接失敗后，自動建立到新主的異步復(fù)制連接。
MySQL 8.0.23 開始，異步連接故障切換機(jī)制還支持組復(fù)制拓?fù)?，通過自動監(jiān)視組成員身份的更改并區(qū)分主節(jié)點(diǎn)和從節(jié)點(diǎn)，在組復(fù)制主節(jié)點(diǎn)故障選出新主節(jié)點(diǎn)后，自動建立到新主的異步復(fù)制連接。
MySQL 8.0.26 開始，組復(fù)制可以設(shè)置組成員在特定情況下采取的操作，比如成員操作 mysql_disable_super_read_only_if_primary 當(dāng)選擇一個新的primary時，使處于單主模式的組保持超級只讀模式，這樣該組只接受復(fù)制的事務(wù)，不接受來自客戶端的任何直接寫操作。
MySQL 8.0.27 開始，異步連接故障切換機(jī)制還允許組復(fù)制作為副本，組的主節(jié)點(diǎn)發(fā)生故障時，新的主節(jié)點(diǎn)自動重新連接到源端。新增成員操作mysql_start_failover_channels_if_primary，默認(rèn)情況下，此成員操作是啟用的，當(dāng)啟用此成員動作時，在"CHANGE replication SOURCE TO"語句中為復(fù)制通道設(shè)置SOURCE_CONNECTION_AUTO_FAILOVER=1時，組復(fù)制主節(jié)點(diǎn)復(fù)制通道上的異步連接故障轉(zhuǎn)移將激活。
2.架構(gòu)
MGR B 作為 MGR A 的備份
本次測試通過搭建2套MGR作為主備集群，進(jìn)行異步連接故障切換測試：
（1）當(dāng)主集群MGR A 的主節(jié)點(diǎn)發(fā)生故障時，備集群MGR B的主節(jié)點(diǎn)能夠?qū)崿F(xiàn)異步故障轉(zhuǎn)移，自動連接MGR A 的新主節(jié)點(diǎn)，主備集群同步不斷開，實(shí)現(xiàn)數(shù)據(jù)正常同步。
（2）當(dāng)備集群MGR B 的主節(jié)點(diǎn)發(fā)生故障時，MGR B 的新主節(jié)點(diǎn)能夠自動啟動復(fù)制通道，自動連接MGR A 主節(jié)點(diǎn)，主備集群同步不斷開，實(shí)現(xiàn)數(shù)據(jù)正常同步。
（3）設(shè)置MGR B成員行為，使處于單主模式的組 MGR B 保持超級只讀模式，這樣該組只接受復(fù)制的事務(wù)，不接受來自客戶端的任何直接寫。
3.測試
本次測試基于 MySQL 8.0.31，搭建2套MGR集群（單主模式）
3.1 搭建MGR集群
MGR A：
mysql>select*fromperformance_schema.replication_group_members; +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ |CHANNEL_NAME|MEMBER_ID|MEMBER_HOST|MEMBER_PORT|MEMBER_STATE|MEMBER_ROLE|MEMBER_VERSION|MEMBER_COMMUNICATION_STACK| +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ |group_replication_applier|44921323-bd95-11ed-8db5-00163e01f842|172.17.140.201|6002|ONLINE|SECONDARY|8.0.31|XCom| |group_replication_applier|6f904c5c-bd95-11ed-bc79-00163e01f842|172.17.140.201|6003|ONLINE|SECONDARY|8.0.31|XCom| |group_replication_applier|c9e254d3-bd94-11ed-8ae9-00163e01f842|172.17.140.201|6001|ONLINE|PRIMARY|8.0.31|XCom| +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ 3rowsinset(0.01sec)
MGR B：
select*fromperformance_schema.replication_group_members; +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ |CHANNEL_NAME|MEMBER_ID|MEMBER_HOST|MEMBER_PORT|MEMBER_STATE|MEMBER_ROLE|MEMBER_VERSION|MEMBER_COMMUNICATION_STACK| +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ |group_replication_applier|190f4674-be4a-11ed-9452-00163ea5c598|172.17.140.252|6005|ONLINE|PRIMARY|8.0.31|XCom| |group_replication_applier|3fa9e064-be4a-11ed-92c7-00163ea5c598|172.17.140.252|6006|ONLINE|SECONDARY|8.0.31|XCom| |group_replication_applier|6a9691c6-be4a-11ed-8a32-00163ea5c598|172.17.140.252|6007|ONLINE|SECONDARY|8.0.31|XCom| +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ 3rowsinset(0.00sec)
3.2 創(chuàng)建復(fù)制用戶
在MGR A 的主節(jié)點(diǎn)創(chuàng)建用于復(fù)制的用戶 repl_user
createuserrepl_user@'%'identifiedwithmysql_native_passwordby'abc123'; grantreplicationslaveon*.*torepl_user@'%'; grantselectonperformance_schema.*torepl_user@'%';
注意：這里復(fù)制用戶需要有 performance_schema 庫的select權(quán)限，主要用于獲取MGR A集群成員的狀態(tài)，如果沒有這個權(quán)限，將不能進(jìn)行異步連接故障轉(zhuǎn)移。
3.3停止MGR B的組復(fù)制
在MGR B的所有節(jié)點(diǎn)停止組復(fù)制，可先停止從節(jié)點(diǎn)，避免發(fā)生節(jié)點(diǎn)切換
mysql>stopgroup_replication;
注意：這里停止組復(fù)制是為了下一步創(chuàng)建異步復(fù)制通道，如果不停止復(fù)制通道，在運(yùn)行正常的MGR B 的主節(jié)點(diǎn)執(zhí)行創(chuàng)建復(fù)制通道的語句，會導(dǎo)致從節(jié)點(diǎn)退出組，報(bào)如下錯誤：
2023-03-09T1451.933981+08:0017887[ERROR][MY-013786][Repl]UnabletosetSOURCE_CONNECTION_AUTO_FAILOVERonanon-existentormisconfiguredreplicationchannel'mgra_to_mgrb',pleasecreatethechannelandrejointheservertothegroup. 2023-03-09T1451.934259+08:0017887[ERROR][MY-013487][Repl]Plugingroup_replicationreported:'AmessagesentthroughtheGroupReplicationmessagedeliverservicewasnotdeliveredsuccessfully.Theserverwillnowleavethegroup.Trytoaddtheserverbacktothegroupandcheckiftheproblempersists,orcheckpreviousmessagesinthelogforhintsofwhatcouldbetheproblem.' 2023-03-09T1451.934704+08:0017887[ERROR][MY-011712][Repl]Plugingroup_replicationreported:'Theserverwasautomaticallysetintoreadonlymodeafteranerrorwasdetected.' 2023-03-09T1455.166405+08:000[System][MY-011504][Repl]Plugingroup_replicationreported:'Groupmembershipchanged:Thismemberhasleftthegroup.'
3.4創(chuàng)建異步復(fù)制通道
在MGR B的所有節(jié)點(diǎn)創(chuàng)建異步復(fù)制通道
changereplicationsourcetosource_host='172.17.140.201',source_port=6001,source_user='repl_user',source_password='abc123',source_auto_position=1,source_connection_auto_failover=1,source_connect_retry=10,source_retry_count=3forchannel'mgra_to_mgrb';
source_host：MGR A 主節(jié)點(diǎn)的IP
source_port：MGR A 主節(jié)點(diǎn)的端口
source_connection_auto_failover=1：表示開啟自動故障轉(zhuǎn)移功能。
source_connect_retry：指定復(fù)制副本在與源端的連接超時后重新連接嘗試之間的間隔（秒）。
source_retry_count：設(shè)置復(fù)制副本在連接到源超時后進(jìn)行的最大重新連接嘗試次數(shù)。
3.5配置復(fù)制源
在MGR B 的主節(jié)點(diǎn)配置復(fù)制源
selectasynchronous_connection_failover_add_managed('mgra_to_mgrb','GroupReplication','bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb','172.17.140.201',6001,'',90,50);
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
|asynchronous_connection_failover_add_managed('mgra_to_mgrb','GroupReplication','bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb','172.17.140.201',6001,'',90,50)|
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
|TheUDFasynchronous_connection_failover_add_managed()executedsuccessfully.|
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1rowinset(0.02sec)
語法：
asynchronous_connection_failover_add_managed(channel,managed_type,managed_name,host,port,network_namespace,primary_weight,secondary_weight)
參數(shù)：
channel：上面創(chuàng)建的復(fù)制通道名稱；
managed_type：管理服務(wù)的類型，當(dāng)前可傳入的值必須是GroupReplication；
managed_name：管理組的識別符，即group_replication_group_name參數(shù)的值；
host：源端主機(jī)名；
port：源端端口號；
network_namespace：預(yù)留為將來使用，置空；
primary_weight：MGR主服務(wù)器的權(quán)重，值為1到100，值越大，優(yōu)先級越高；
secondary_weight：MGR從服務(wù)器的權(quán)重，值為1到100，值越大，優(yōu)先級越高；
3.6查看配置的用于異步連接切換的復(fù)制源
mysql>select*fromperformance_schema.replication_asynchronous_connection_failover; +--------------+----------------+------+-------------------+--------+--------------------------------------+ |CHANNEL_NAME|HOST|PORT|NETWORK_NAMESPACE|WEIGHT|MANAGED_NAME| +--------------+----------------+------+-------------------+--------+--------------------------------------+ |mgra_to_mgrb|172.17.140.201|6001||50|bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb| +--------------+----------------+------+-------------------+--------+--------------------------------------+ 1rowinset(0.00sec)
這里只顯示剛才配置的一個節(jié)點(diǎn)，啟動異步復(fù)制通道后就能看到MGR A所有節(jié)點(diǎn)。
3.7啟動 MGR B 組復(fù)制
在MGR B 的主節(jié)點(diǎn)重新引導(dǎo)啟動組復(fù)制
#選一個節(jié)點(diǎn)作為引導(dǎo)節(jié)點(diǎn) setglobalgroup_replication_bootstrap_group=ON; startgroup_replication; setglobalgroup_replication_bootstrap_group=OFF; #其余節(jié)點(diǎn)啟動組復(fù)制 startgroup_replication; #查看MGRB集群狀態(tài) +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ |CHANNEL_NAME|MEMBER_ID|MEMBER_HOST|MEMBER_PORT|MEMBER_STATE|MEMBER_ROLE|MEMBER_VERSION|MEMBER_COMMUNICATION_STACK| +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ |group_replication_applier|190f4674-be4a-11ed-9452-00163ea5c598|172.17.140.252|6005|ONLINE|PRIMARY|8.0.31|XCom| |group_replication_applier|3fa9e064-be4a-11ed-92c7-00163ea5c598|172.17.140.252|6006|ONLINE|SECONDARY|8.0.31|XCom| |group_replication_applier|6a9691c6-be4a-11ed-8a32-00163ea5c598|172.17.140.252|6007|ONLINE|SECONDARY|8.0.31|XCom| +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ 3rowsinset(0.00sec)
3.8啟動異步復(fù)制通道
在MGR B的主節(jié)點(diǎn)啟動
mysql>startreplicaforchannel'mgra_to_mgrb'; mysql>showreplicastatusG ***************************1.row*************************** Replica_IO_State:Waitingforsourcetosendevent Source_Host:172.17.140.201 Source_User:repl_user Source_Port:6001 Connect_Retry:10 Source_Log_File:binlog.000001 Read_Source_Log_Pos:2614 Relay_Log_File:relaylog-mgra_to_mgrb.000002 Relay_Log_Pos:2816 Relay_Source_Log_File:binlog.000001 Replica_IO_Running:Yes Replica_SQL_Running:Yes Replicate_Do_DB:
異步復(fù)制通道運(yùn)行正常，至此MGR A 與 MGR B已建立主備關(guān)系，MGR A主節(jié)點(diǎn)與 MGR B 主節(jié)點(diǎn)建立異步復(fù)制通道。
3.9查看配置的用于異步連接切換的復(fù)制源
mysql>select*fromperformance_schema.replication_asynchronous_connection_failover; +--------------+----------------+------+-------------------+--------+--------------------------------------+ |CHANNEL_NAME|HOST|PORT|NETWORK_NAMESPACE|WEIGHT|MANAGED_NAME| +--------------+----------------+------+-------------------+--------+--------------------------------------+ |mgra_to_mgrb|172.17.140.201|6001||90|bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb| |mgra_to_mgrb|172.17.140.201|6002||50|bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb| |mgra_to_mgrb|172.17.140.201|6003||50|bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb| +--------------+----------------+------+-------------------+--------+--------------------------------------+ 3rowsinset(0.01sec)
可以看到這里顯示了 MGR A的所有節(jié)點(diǎn)信息，weight 最高的為主節(jié)點(diǎn)
3.10故障模擬：主集群MGR A 的主節(jié)點(diǎn)發(fā)生故障
（1）查看當(dāng)前MGR A 信息
mysql>select*fromperformance_schema.replication_group_members; +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ |CHANNEL_NAME|MEMBER_ID|MEMBER_HOST|MEMBER_PORT|MEMBER_STATE|MEMBER_ROLE|MEMBER_VERSION|MEMBER_COMMUNICATION_STACK| +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ |group_replication_applier|44921323-bd95-11ed-8db5-00163e01f842|172.17.140.201|6002|ONLINE|SECONDARY|8.0.31|XCom| |group_replication_applier|6f904c5c-bd95-11ed-bc79-00163e01f842|172.17.140.201|6003|ONLINE|SECONDARY|8.0.31|XCom| |group_replication_applier|c9e254d3-bd94-11ed-8ae9-00163e01f842|172.17.140.201|6001|ONLINE|PRIMARY|8.0.31|XCom| +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ 3rowsinset(0.00sec)
當(dāng)前MGR A 主節(jié)點(diǎn)為 6001
（2）查看異步復(fù)制信息
登錄MGR B 主節(jié)點(diǎn)查看
mysql>showreplicastatusG ***************************1.row*************************** Replica_IO_State:Waitingforsourcetosendevent Source_Host:172.17.140.201 Source_User:repl_user Source_Port:6001 Connect_Retry:10 Source_Log_File:binlog.000001 Read_Source_Log_Pos:2614 Relay_Log_File:relaylog-mgra_to_mgrb.000002 Relay_Log_Pos:2816 Relay_Source_Log_File:binlog.000001 Replica_IO_Running:Yes Replica_SQL_Running:Yes Replicate_Do_DB:
當(dāng)前MGR B主節(jié)點(diǎn) 連接的是 MGR A的主節(jié)點(diǎn) 6001
（3）模擬MGR A故障
在MGR A主節(jié)點(diǎn)執(zhí)行切主命令，模擬MGR A主節(jié)點(diǎn)故障，將6002設(shè)置為新主節(jié)點(diǎn)
mysql>selectgroup_replication_set_as_primary('44921323-bd95-11ed-8db5-00163e01f842');
+--------------------------------------------------------------------------+
|group_replication_set_as_primary('44921323-bd95-11ed-8db5-00163e01f842')|
+--------------------------------------------------------------------------+
|Primaryserverswitchedto:44921323-bd95-11ed-8db5-00163e01f842|
+--------------------------------------------------------------------------+
1rowinset(0.04sec)
mysql>select*fromperformance_schema.replication_group_members; +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ |CHANNEL_NAME|MEMBER_ID|MEMBER_HOST|MEMBER_PORT|MEMBER_STATE|MEMBER_ROLE|MEMBER_VERSION|MEMBER_COMMUNICATION_STACK| +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ |group_replication_applier|44921323-bd95-11ed-8db5-00163e01f842|172.17.140.201|6002|ONLINE|PRIMARY|8.0.31|XCom| |group_replication_applier|6f904c5c-bd95-11ed-bc79-00163e01f842|172.17.140.201|6003|ONLINE|SECONDARY|8.0.31|XCom| |group_replication_applier|c9e254d3-bd94-11ed-8ae9-00163e01f842|172.17.140.201|6001|ONLINE|SECONDARY|8.0.31|XCom| +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ 3rowsinset(0.00sec)
（4）查看異步復(fù)制信息
登錄MGR B 主節(jié)點(diǎn)查看
mysql>showreplicastatusG ***************************1.row*************************** Replica_IO_State:Waitingforsourcetosendevent Source_Host:172.17.140.201 Source_User:repl_user Source_Port:6002 Connect_Retry:10 Source_Log_File:binlog.000001 Read_Source_Log_Pos:3343 Relay_Log_File:relaylog-mgra_to_mgrb.000003 Relay_Log_Pos:435 Relay_Source_Log_File:binlog.000001 Replica_IO_Running:Yes Replica_SQL_Running:Yes Replicate_Do_DB:
可以看到MGR B主節(jié)點(diǎn)已經(jīng)自動進(jìn)行異步連接故障轉(zhuǎn)移，與MGR A新主節(jié)點(diǎn)6002建立連接并且連接正常，在此過程中并不需要人為去操作。
3.11故障模擬：備集群MGR B 的主節(jié)點(diǎn)發(fā)生故障
（1）查看當(dāng)前MGR B信息
mysql>select*fromperformance_schema.replication_group_members; +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ |CHANNEL_NAME|MEMBER_ID|MEMBER_HOST|MEMBER_PORT|MEMBER_STATE|MEMBER_ROLE|MEMBER_VERSION|MEMBER_COMMUNICATION_STACK| +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ |group_replication_applier|190f4674-be4a-11ed-9452-00163ea5c598|172.17.140.252|6005|ONLINE|PRIMARY|8.0.31|XCom| |group_replication_applier|3fa9e064-be4a-11ed-92c7-00163ea5c598|172.17.140.252|6006|ONLINE|SECONDARY|8.0.31|XCom| |group_replication_applier|6a9691c6-be4a-11ed-8a32-00163ea5c598|172.17.140.252|6007|ONLINE|SECONDARY|8.0.31|XCom| +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ 3rowsinset(0.03sec)
（2）查看異步復(fù)制信息
登錄MGR B主節(jié)點(diǎn)6005
mysql>showreplicastatusG ***************************1.row*************************** Replica_IO_State:Waitingforsourcetosendevent Source_Host:172.17.140.201 Source_User:repl_user Source_Port:6002 Connect_Retry:10 Source_Log_File:binlog.000001 Read_Source_Log_Pos:3343 Relay_Log_File:relaylog-mgra_to_mgrb.000003 Relay_Log_Pos:435 Relay_Source_Log_File:binlog.000001 Replica_IO_Running:Yes Replica_SQL_Running:Yes Replicate_Do_DB:
當(dāng)前MGR B主節(jié)點(diǎn)與MGR A新主節(jié)點(diǎn)6002 異步復(fù)制連接正常
登錄MGR B從節(jié)點(diǎn)6006
mysql>showreplicastatusG ***************************1.row*************************** Replica_IO_State: Source_Host:172.17.140.201 Source_User:repl_user Source_Port:6001 Connect_Retry:10 Source_Log_File: Read_Source_Log_Pos:4 Relay_Log_File:relaylog-mgra_to_mgrb.000001 Relay_Log_Pos:4 Relay_Source_Log_File: Replica_IO_Running:No Replica_SQL_Running:No Replicate_Do_DB:
（3）模擬MGR B主節(jié)點(diǎn)故障
這里不能通過對MGR B執(zhí)行切主命令模擬故障，會報(bào)如下錯誤：
mysql>selectgroup_replication_set_as_primary('3fa9e064-be4a-11ed-92c7-00163ea5c598');
ERROR3910(HY000):Thefunction'group_replication_set_as_primary'failed.Thereisaslavechannelrunninginthegroup'scurrentprimarymember.
我們通過停止組復(fù)制讓MGR B主節(jié)點(diǎn)6005主動退出組，模擬故障
mysql>stopgroup_replication; QueryOK,0rowsaffected(11.67sec)
（4）查看當(dāng)前MGR B信息
mysql>select*fromperformance_schema.replication_group_members; +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ |CHANNEL_NAME|MEMBER_ID|MEMBER_HOST|MEMBER_PORT|MEMBER_STATE|MEMBER_ROLE|MEMBER_VERSION|MEMBER_COMMUNICATION_STACK| +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ |group_replication_applier|3fa9e064-be4a-11ed-92c7-00163ea5c598|172.17.140.252|6006|ONLINE|PRIMARY|8.0.31|XCom| |group_replication_applier|6a9691c6-be4a-11ed-8a32-00163ea5c598|172.17.140.252|6007|ONLINE|SECONDARY|8.0.31|XCom| +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ 2rowsinset(0.00sec)
當(dāng)前MGR B發(fā)生切主，新主節(jié)點(diǎn)為6006
（5）查看異步復(fù)制信息
登錄MGR B新主節(jié)點(diǎn)6006
mysql>showreplicastatusG ***************************1.row*************************** Replica_IO_State:Waitingforsourcetosendevent Source_Host:172.17.140.201 Source_User:repl_user Source_Port:6002 Connect_Retry:10 Source_Log_File:binlog.000001 Read_Source_Log_Pos:3343 Relay_Log_File:relaylog-mgra_to_mgrb.000002 Relay_Log_Pos:395 Relay_Source_Log_File:binlog.000001 Replica_IO_Running:Yes Replica_SQL_Running:Yes Replicate_Do_DB:
可以看到MGR B新主節(jié)點(diǎn)已經(jīng)自動進(jìn)行異步連接故障轉(zhuǎn)移，與MGR A主節(jié)點(diǎn)6002建立連接并且連接正常，在此過程中并不需要人為去操作。
3.12 設(shè)置備集群MGR B為超級只讀模式
配置MGR B集群所有節(jié)點(diǎn)為超級只讀模式，避免執(zhí)行誤操作。默認(rèn)情況下，MGR單主模式下主節(jié)點(diǎn)為讀寫模式，從節(jié)點(diǎn)為超級只讀模式。
當(dāng)一個組只是作為另一個組的備份的時候，只需要執(zhí)行復(fù)制事物就可以了，不需要登錄執(zhí)行其他客戶端事物，避免造成數(shù)據(jù)不一致的問題。因此我們可以通過 group_replication_disable_member_action() 函數(shù)設(shè)置成員操作，將備集群所有節(jié)點(diǎn)設(shè)置為超級只讀模式，即使切主后選出新的主節(jié)點(diǎn)也要保持超級只讀模式。
（1）查看MGR B當(dāng)前成員操作
mysql>select*fromreplication_group_member_actions; +------------------------------------------+------------------------+---------+----------+----------+----------------+ |name|event|enabled|type|priority|error_handling| +------------------------------------------+------------------------+---------+----------+----------+----------------+ |mysql_disable_super_read_only_if_primary|AFTER_PRIMARY_ELECTION|1|INTERNAL|1|IGNORE| |mysql_start_failover_channels_if_primary|AFTER_PRIMARY_ELECTION|1|INTERNAL|10|CRITICAL| +------------------------------------------+------------------------+---------+----------+----------+----------------+ 2rowsinset(0.02sec)
（2）設(shè)置MGR B為超級只讀模式
mysql>SELECTgroup_replication_disable_member_action("mysql_disable_super_read_only_if_primary","AFTER_PRIMARY_ELECTION");
+---------------------------------------------------------------------------------------------------------------+
|group_replication_disable_member_action("mysql_disable_super_read_only_if_primary","AFTER_PRIMARY_ELECTION")|
+---------------------------------------------------------------------------------------------------------------+
|OK|
+---------------------------------------------------------------------------------------------------------------+
1rowinset(0.04sec)
注意：這里執(zhí)行完此函數(shù)后，不會對當(dāng)前主節(jié)點(diǎn)產(chǎn)生影響，當(dāng)發(fā)生切主選擇出新主節(jié)點(diǎn)后，會將新主節(jié)點(diǎn)設(shè)置為超級只讀模式。
（3）停止MGR B主節(jié)點(diǎn)
mysql>stopgroup_replication; QueryOK,0rowsaffected(28.54sec) mysql>select*fromperformance_schema.replication_group_members; +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ |CHANNEL_NAME|MEMBER_ID|MEMBER_HOST|MEMBER_PORT|MEMBER_STATE|MEMBER_ROLE|MEMBER_VERSION|MEMBER_COMMUNICATION_STACK| +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ |group_replication_applier|190f4674-be4a-11ed-9452-00163ea5c598|172.17.140.252|6005|ONLINE|PRIMARY|8.0.31|XCom| |group_replication_applier|6a9691c6-be4a-11ed-8a32-00163ea5c598|172.17.140.252|6007|ONLINE|SECONDARY|8.0.31|XCom| +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ 2rowsinset(0.01sec)
MGR B新主節(jié)點(diǎn)為 6005
（4）查看MGR B新主節(jié)點(diǎn)是否進(jìn)入超級只讀模式
mysql>showvariableslike'super_read_only'; +-----------------+-------+ |Variable_name|Value| +-----------------+-------+ |super_read_only|ON| +-----------------+-------+ 1rowinset(0.05sec) mysql>createdatabasett; ERROR1290(HY000):TheMySQLserverisrunningwiththe--super-read-onlyoptionsoitcannotexecutethisstatement
此時新主節(jié)點(diǎn)處于超級只讀模式，不允許用戶執(zhí)行操作。
4.總結(jié)
通過異步連接故障切換機(jī)制，當(dāng)復(fù)制連接出現(xiàn)問題時，不需要人工介入手動去重新建立復(fù)制連接，副本會自動進(jìn)行異步故障轉(zhuǎn)移與新的節(jié)點(diǎn)建立連接。
異步復(fù)制通道的建立只能在2個MGR集群的主節(jié)點(diǎn)上。
審核編輯：劉清
-
連接器
+關(guān)注
關(guān)注
104文章
16143瀏覽量
147107 -
狀態(tài)機(jī)
+關(guān)注
關(guān)注
2文章
499瀏覽量
29161 -
MySQL
+關(guān)注
關(guān)注
1文章
906瀏覽量
29555 -
MYSQL數(shù)據(jù)庫
+關(guān)注
關(guān)注
0文章
97瀏覽量
10270
原文標(biāo)題：MGR主備集群實(shí)現(xiàn)異步連接故障轉(zhuǎn)移
文章出處：【微信號：OSC開源社區(qū)，微信公眾號：OSC開源社區(qū)】歡迎添加關(guān)注！文章轉(zhuǎn)載請注明出處。
發(fā)布評論請先 登錄
copy模式的DRDS集群
VxWorks中主備數(shù)據(jù)一致性功能組件的設(shè)計(jì)與實(shí)現(xiàn)
VxWorks中主備數(shù)據(jù)一致性功能組件的設(shè)計(jì)與實(shí)現(xiàn)
VxWorks中主備數(shù)據(jù)一致性功能組件的設(shè)計(jì)與實(shí)現(xiàn)
利用Maxim時鐘IC實(shí)現(xiàn)主備時鐘卡冗余,Implement
什么是異步轉(zhuǎn)移模式
雙機(jī)熱備與負(fù)載均衡的設(shè)計(jì)與實(shí)現(xiàn)
基于Hyper-V虛擬化技術(shù)實(shí)現(xiàn)故障轉(zhuǎn)移
異步電機(jī)轉(zhuǎn)子復(fù)合故障診斷
阿里云HBase推出普惠性高可用服務(wù)，獨(dú)家支持用戶的自建、混合云環(huán)境集群
三相異步電動機(jī)安裝步驟_三相異步電動機(jī)故障檢查方法
主備電源延時切換電路設(shè)計(jì)運(yùn)行原理
hyper-v 集群，hyper-v集群的共享存儲
工商網(wǎng)監(jiān)
評論