明辉手游网中心：是一个免费提供流行视频软件教程、在线学习分享的学习平台！

文章发布
全站
软 件 文 章
热门搜索：片段 高清 播放器 快退 快进 3D功能 
资讯首页腾讯视频 爱奇艺 暴风影音 酷狗音乐 酷我音乐影音大全软件下载软件教程电脑系统下载
聊天工具办公软件杀毒教程系统工具图形图像电脑学习应用软件网络软件苹果应用注册码网站教程技术开发安卓教程其它教程
您当前所在位置：网视手游站 -> 应用软件 -> 深度理解MySQL Group Replication的RECOVERING状态

深度理解MySQL Group Replication的RECOVERING状态

发表时间：2023-09-10 来源：明辉站整理相关软件相关文章人气：

[摘要]接收到报警通知，db3这台组复制成员故障down机了，等修复好，启动服务器，然后再启动mysql实例，进去查看状态，变成了RECOVERING，如下所示；mysql> SELECT * FROM performance_schema.replication_group_members;+--...


接收到报警通知，db3这台组复制成员故障down机了，等修复好，启动服务器，然后再启动mysql实例，进去查看状态，变成了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>

这种状态有点类似mongodb的sendary的RECOVERING状态，对于刚刚出现的MySQL Group Replication新技术，遇到这种情况，这种情况怎么办？

在mongodb的这种模式下，一般是secondary从primary库上不停复制数据，所以我们遵循这种思路，去看下db2的数据目录，看到有很多relay-bin的recovery日志在应用执行，如下所示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线程，就是表示正在应用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>

再过一会儿，数据同步完成之后，bpe_service就会从RECOVERING变成ONLINE状态。这个更加证实了，数据已经同步完成。

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’的时候，没有取到值，然后看后面同步完后，就会执行加入组复制成员的命令

“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=''”，

在执行结束后，最后告诉我们“’This server was declared online within the replication group’”已经加入到了组成员，而且变成了实时的ONLINE状态：

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）尝试启动默认的组复制，结果信息不对称，启动失败，然后开始初始化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） 开始进行组内数据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）已经数据恢复完成，加入组，成为组成员
2017-01-17T08:43:34.290920Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group'

小结：mysql group replication的使用来说，有些理念比如mongodb的分片以及primary-secondary的思路理念可以参考下，这样理解起来就会比较容易一些。

以上就是深度理解MySQL Group Replication的RECOVERING状态的详细内容，更多请关注php中文网其它相关文章！


学习教程快速掌握从入门到精通的SQL知识。




标签：深度理解MySQL Group Replication的RECOVERING状态 

推荐资讯 总人气榜

最新教程 本月人气

联系我们|版权声明|下载帮助|关于我们|网站地图|手机站
Copyright © 2015-2020 www.22122511.com AllRights Reserved

本站资源均来自互联网收集 如有侵犯到您利益的地方请及时联系管理删除，敬请见谅!