深度理解MySQL Group Replication的RECOVERING狀態(tài)
發(fā)表時間：2023-09-10 來源：明輝站整理相關(guān)軟件相關(guān)文章人氣：
[摘要]接收到報警通知，db3這臺組復(fù)制成員故障down機了，等修復(fù)好，啟動服務(wù)器，然后再啟動mysql實例，進去查看狀態(tài)，變成了RECOVERING，如下所示；mysql> SELECT * FROM performance_schema.replication_group_members;+--...
接收到報警通知，db3這臺組復(fù)制成員故障down機了，等修復(fù)好，啟動服務(wù)器，然后再啟動mysql實例，進去查看狀態(tài)，變成了RECOVERING，如下所示；
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------------+-------------+--------------+
CHANNEL_NAME MEMBER_ID MEMBER_HOST MEMBER_PORT MEMBER_STATE
+---------------------------+--------------------------------------+----------------------+-------------+--------------+
group_replication_applier 3381d155-d7d1-11e6-94f7-b8ca3af6e36c hch_test_dbm2_121_71 3317 ONLINE
group_replication_applier 664b9ce9-d7de-11e6-9e8c-18a99b763071 hch_test_web_1_24 3317 ONLINE
group_replication_applier 84dba8ff-d7d2-11e6-aa9a-18a99b76310d bpe_service 3317 RECOVERING
+---------------------------+--------------------------------------+----------------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql>
這種狀態(tài)有點類似mongodb的sendary的RECOVERING狀態(tài)，對于剛剛出現(xiàn)的MySQL Group Replication新技術(shù)，遇到這種情況，這種情況怎么辦？
在mongodb的這種模式下，一般是secondary從primary庫上不停復(fù)制數(shù)據(jù)，所以我們遵循這種思路，去看下db2的數(shù)據(jù)目錄，看到有很多relay-bin的recovery日志在應(yīng)用執(zhí)行，如下所示relay日志包記錄
-rw-r----- 1 mysql mysql 383 1月 17 16:08 bpe_service-relay-bin-group_replication_applier.000019
-rw-r----- 1 mysql mysql 502 1月 17 16:11 bpe_service-relay-bin-group_replication_applier.000020
-rw-r----- 1 mysql mysql 502 1月 17 16:23 bpe_service-relay-bin-group_replication_applier.000021
-rw-r----- 1 mysql mysql 421 1月 17 16:23 bpe_service-relay-bin-group_replication_applier.000022
-rw-r----- 1 mysql mysql 228 1月 17 16:23 bpe_service-relay-bin-group_replication_applier.index
-rw-r----- 1 mysql mysql 312 1月 17 16:24 bpe_service-relay-bin-group_replication_recovery.000007
-rw-r----- 1 mysql mysql 1.1G 1月 17 16:24 bpe_service-relay-bin-group_replication_recovery.000008
-rw-r----- 1 mysql mysql 391 1月 17 16:24 bpe_service-relay-bin-group_replication_recovery.000009
-rw-r----- 1 mysql mysql 312 1月 17 16:24 bpe_service-relay-bin-group_replication_recovery.000010
-rw-r----- 1 mysql mysql 1.1G 1月 17 16:24 bpe_service-relay-bin-group_replication_recovery.000011
-rw-r----- 1 mysql mysql 391 1月 17 16:24 bpe_service-relay-bin-group_replication_recovery.000012
-rw-r----- 1 mysql mysql 312 1月 17 16:24 bpe_service-relay-bin-group_replication_recovery.000013
-rw-r----- 1 mysql mysql 1.1G 1月 17 16:25 bpe_service-relay-bin-group_replication_recovery.000014
-rw-r----- 1 mysql mysql 391 1月 17 16:25 bpe_service-relay-bin-group_replication_recovery.000015
-rw-r----- 1 mysql mysql 312 1月 17 16:25 bpe_service-relay-bin-group_replication_recovery.000016
-rw-r----- 1 mysql mysql 1.1G 1月 17 16:25 bpe_service-relay-bin-group_replication_recovery.000017
-rw-r----- 1 mysql mysql 391 1月 17 16:25 bpe_service-relay-bin-group_replication_recovery.000018
-rw-r----- 1 mysql mysql 312 1月 17 16:25 bpe_service-relay-bin-group_replication_recovery.000019
-rw-r----- 1 mysql mysql 1.1G 1月 17 16:25 bpe_service-relay-bin-group_replication_recovery.000020
-rw-r----- 1 mysql mysql 391 1月 17 16:25 bpe_service-relay-bin-group_replication_recovery.000021
-rw-r----- 1 mysql mysql 312 1月 17 16:25 bpe_service-relay-bin-group_replication_recovery.000022
-rw-r----- 1 mysql mysql 1.1G 1月 17 16:25 bpe_service-relay-bin-group_replication_recovery.000023
-rw-r----- 1 mysql mysql 391 1月 17 16:25 bpe_service-relay-bin-group_replication_recovery.000024
-rw-r----- 1 mysql mysql 312 1月 17 16:25 bpe_service-relay-bin-group_replication_recovery.000025
-rw-r----- 1 mysql mysql 181M 1月 17 16:26 bpe_service-relay-bin-group_replication_recovery.000026
-rw-r----- 1 mysql mysql 1.2K 1月 17 16:25 bpe_service-relay-bin-group_replication_recovery.index
drwxr-x--- 2 mysql mysql 4.0K 1月 17 14:11 business_db
然后在mysql窗口，可以看到幾個線程，其中有一個就是Reading event from the relay log線程，就是表示正在應(yīng)用relay日志：
mysql> show full processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+-----------------------+
Id User Host db Command Time State Info
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+-----------------------+
28 system user NULL Connect 701 Suspending NULL
31 system user NULL Connect 701 Slave has read all relay log; waiting for more updates NULL
35 system user NULL Connect 699 Waiting for master to send event NULL
36 system user NULL Connect 4519 Reading event from the relay log NULL
38 root localhost NULL Query 0 starting show full processlist
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+-----------------------+
5 rows in set (0.00 sec)
mysql>
再過一會兒，數(shù)據(jù)同步完成之后，bpe_service就會從RECOVERING變成ONLINE狀態(tài)。這個更加證實了，數(shù)據(jù)已經(jīng)同步完成。
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------------+-------------+--------------+
CHANNEL_NAME MEMBER_ID MEMBER_HOST MEMBER_PORT MEMBER_STATE
+---------------------------+--------------------------------------+----------------------+-------------+--------------+
group_replication_applier 3381d155-d7d1-11e6-94f7-b8ca3af6e36c hch_test_dbm2_121_71 3317 ONLINE
group_replication_applier 664b9ce9-d7de-11e6-9e8c-18a99b763071 hch_test_web_1_24 3317 ONLINE
group_replication_applier 84dba8ff-d7d2-11e6-aa9a-18a99b76310d bpe_service 3317 ONLINE
+---------------------------+--------------------------------------+----------------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql>
去查看后臺error log日志顯示，剛開始啟動，在做CHANGE MASTER TO FOR CHANNEL ‘group_replication_applier’ executed’的時候，沒有取到值，然后看后面同步完后，就會執(zhí)行加入組復(fù)制成員的命令
“CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'.
Previous state master_host='hch_test_web_1_24', master_port= 3317, master_log_file='',
master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''”，
在執(zhí)行結(jié)束后，最后告訴我們“’This server was declared online within the replication group’”已經(jīng)加入到了組成員，而且變成了實時的ONLINE狀態(tài)：
2017-01-17T08:23:15.710146Z 4 [Note] Plugin group_replication reported: 'auto_increment_increment is reset to 1'
2017-01-17T08:23:15.710200Z 4 [Note] Plugin group_replication reported: 'auto_increment_offset is reset to 1'
2017-01-17T08:23:15.710401Z 20 [Note] Error reading relay log event for channel 'group_replication_applier': slave SQL thread was killed
2017-01-17T08:23:15.711212Z 17 [Note] Plugin group_replication reported: 'The group replication applier thread was killed'
2017-01-17T08:23:42.141239Z 4 [Note] Plugin group_replication reported: 'Group communication SSL configuration: group_replication_ssl_mode: "DISABLED"'
2017-01-17T08:23:42.141451Z 4 [Note] Plugin group_replication reported: '[GCS] Added automatically IP ranges 127.0.0.1/8,192.168.121.111/23 to the whitelist'
2017-01-17T08:23:42.141990Z 4 [Note] Plugin group_replication reported: '[GCS] Translated 'db2' to 192.168.121.111'
2017-01-17T08:23:42.142174Z 4 [Note] Plugin group_replication reported: '[GCS] SSL was not enabled'
2017-01-17T08:23:42.142220Z 4 [Note] Plugin group_replication reported: 'Initialized group communication with configuration:
group_replication_group_name: "e4668cea-d7ca-11e6-86b5-18a99b76310d"; group_replication_local_address: "db2:24902";
group_replication_group_seeds: "db1:24901,db2:24902,db3:24903"; group_replication_bootstrap_group: false;
group_replication_poll_spin_loops: 0; group_replication_compression_threshold: 1000000; group_replication_ip_whitelist: "AUTOMATIC"'
2017-01-17T08:23:42.142936Z 28 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'.
Previous state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
-- #（1）嘗試啟動默認的組復(fù)制，結(jié)果信息不對稱，啟動失敗，然后開始初始化Group Replication
2017-01-17T08:23:42.166956Z 31 [Note] Slave SQL thread for channel 'group_replication_applier' initialized,
starting replication in log 'FIRST' at position 0, relay log './bpe_service-relay-bin-group_replication_applier.000019' position: 4
2017-01-17T08:23:42.166960Z 4 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!'
2017-01-17T08:23:42.167064Z 4 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 7'
2017-01-17T08:23:42.167079Z 4 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 12002'
2017-01-17T08:23:42.167219Z 0 [Note] Plugin group_replication reported: 'state 4257 action xa_init'
2017-01-17T08:23:42.167292Z 0 [Note] Plugin group_replication reported: 'Successfully bound to 0.0.0.0:24902 (socket=49).'
2017-01-17T08:23:42.167341Z 0 [Note] Plugin group_replication reported: 'Successfully set listen backlog to 32 (socket=49)!'
2017-01-17T08:23:42.167357Z 0 [Note] Plugin group_replication reported: 'Successfully unblocked socket (socket=49)!'
2017-01-17T08:23:42.167400Z 0 [Note] Plugin group_replication reported: 'connecting to db2 24902'
2017-01-17T08:23:42.167585Z 0 [Note] Plugin group_replication reported: 'Ready to accept incoming connections on 0.0.0.0:24902 (socket=49)!'
2017-01-17T08:23:42.174427Z 0 [Note] Plugin group_replication reported: 'client connected to db2 24902 fd 50'
2017-01-17T08:23:42.174807Z 0 [Note] Plugin group_replication reported: 'connecting to db2 24902'
2017-01-17T08:23:42.175008Z 0 [Note] Plugin group_replication reported: 'client connected to db2 24902 fd 63'
2017-01-17T08:23:42.175234Z 0 [Note] Plugin group_replication reported: 'connecting to db2 24902'
2017-01-17T08:23:42.175411Z 0 [Note] Plugin group_replication reported: 'client connected to db2 24902 fd 65'
2017-01-17T08:23:42.175614Z 0 [Note] Plugin group_replication reported: 'connecting to db2 24902'
2017-01-17T08:23:42.175799Z 0 [Note] Plugin group_replication reported: 'client connected to db2 24902 fd 67'
2017-01-17T08:23:42.176006Z 0 [Note] Plugin group_replication reported: 'connecting to db2 24902'
2017-01-17T08:23:42.176176Z 0 [Note] Plugin group_replication reported: 'client connected to db2 24902 fd 69'
2017-01-17T08:23:42.176377Z 0 [Note] Plugin group_replication reported: 'connecting to db2 24902'
2017-01-17T08:23:42.176563Z 0 [Note] Plugin group_replication reported: 'client connected to db2 24902 fd 71'
2017-01-17T08:23:42.176876Z 0 [Note] Plugin group_replication reported: 'connecting to db1 24901'
2017-01-17T08:23:42.177312Z 0 [Note] Plugin group_replication reported: 'client connected to db1 24901 fd 73'
2017-01-17T08:23:43.463474Z 0 [Note] Plugin group_replication reported: 'state 4257 action xa_snapshot'
2017-01-17T08:23:43.463969Z 0 [Note] Plugin group_replication reported: 'new state x_recover'
2017-01-17T08:23:43.464000Z 0 [Note] Plugin group_replication reported: 'state 4277 action xa_complete'
2017-01-17T08:23:43.464275Z 0 [Note] Plugin group_replication reported: 'new state x_run'
2017-01-17T08:23:44.619112Z 0 [Note] Plugin group_replication reported: 'Starting group replication recovery with view_id 14844510167669342:17'
--#（2） 開始進行組內(nèi)數(shù)據(jù)recovery
2017-01-17T08:23:44.619451Z 34 [Note] Plugin group_replication reported: 'Establishing group recovery connection with a possible donor. Attempt 1/10'
2017-01-17T08:23:44.624181Z 34 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='<NULL>',
master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='hch_test_web_1_24', master_port= 3317,
master_log_file='', master_log_pos= 4, master_bind=''.
2017-01-17T08:23:44.628853Z 34 [Note] Plugin group_replication reported: 'Establishing connection to a group replication
recovery donor 664b9ce9-d7de-11e6-9e8c-18a99b763071 at hch_test_web_1_24 port: 3317.'
2017-01-17T08:23:44.629156Z 35 [Warning] Storing MySQL user name or password information in the master info repository is not secure and
is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax'
in the MySQL Manual for more information.
2017-01-17T08:23:44.635870Z 36 [Note] Slave SQL thread for channel 'group_replication_recovery' initialized, starting
replication in log 'FIRST' at position 0, relay log './bpe_service-relay-bin-group_replication_recovery.000001' position: 4
2017-01-17T08:23:44.681897Z 35 [Note] Slave I/O thread for channel 'group_replication_recovery':
connected to master 'repl@hch_test_web_1_24:3317',replication started in log 'FIRST' at position 4
2017-01-17T08:43:34.219952Z 34 [Note] Plugin group_replication reported: 'Terminating existing group replication donor connection and
purging the corresponding logs.'
2017-01-17T08:43:34.220609Z 36 [Note] Slave SQL thread for channel 'group_replication_recovery' exiting,
replication stopped in log 'binlog.000042' at position 185511578
2017-01-17T08:43:34.226869Z 35 [Note] Slave I/O thread killed while reading event for channel 'group_replication_recovery'
2017-01-17T08:43:34.226886Z 35 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'binlog.000042', position 185511578
2017-01-17T08:43:34.269973Z 34 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'.
Previous state master_host='hch_test_web_1_24', master_port= 3317, master_log_file='',
master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
-- # （3）已經(jīng)數(shù)據(jù)恢復(fù)完成，加入組，成為組成員
2017-01-17T08:43:34.290920Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group'
小結(jié)：mysql group replication的使用來說，有些理念比如mongodb的分片以及primary-secondary的思路理念可以參考下，這樣理解起來就會比較容易一些。
以上就是深度理解MySQL Group Replication的RECOVERING狀態(tài)的詳細內(nèi)容，更多請關(guān)注php中文網(wǎng)其它相關(guān)文章！
學(xué)習(xí)教程快速掌握從入門到精通的SQL知識。
