search

Home >Database >Mysql Tutorial >In-depth understanding of the RECOVERING status of MySQL Group Replication

In-depth understanding of the RECOVERING status of MySQL Group Replication

黄舟
黄舟Original
2017-03-22 13:25:253066browse


Received an alarm notification, the group replication member of db3 is down. Wait for it to be repaired, start the server, and then start the mysql instance, go in and checkState, changed to RECOVERING, as shown below; 

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>

This state is somewhat similar to mongodb's sendary's RECOVERING Status, for the new MySQL Group Replication technology that just appeared, what should I do if I encounter this situation?

In this mode of mongodb, the secondary usually copies data from the primary database, so we follow this idea and look at the data directory of db2 and see that there are A lot of relay-bin recovery logs are executed in the application. The relay log package records as shown below

-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

Then in the mysql window, you can see several threads, one of which is Reading event from the relay log Thread means that the relay log is being applied: 

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>

After a while, after the data synchronization is completed, the bpe_service will change from RECOVERING to ONLINE status. This further confirms that the data has been synchronized. 

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>

Go to check the background error log. It shows that it has just started. When doing CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed', no value is obtained, and then see After the synchronization is completed later, the command to join the group replication member will be 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=&#39;&#39;. New state master_host=&#39;<NULL>&#39;, master_port= 0, master_log_file=&#39;&#39;, master_log_pos= 4, master_bind=&#39;&#39;”，

After the execution is completed, we will finally be told that "'This server was declared online within the replication group'" has been added to the group member and has become a member of the group. It has become a real-time ONLINE status: 

2017-01-17T08:23:15.710146Z 4 [Note] Plugin group_replication reported: &#39;auto_increment_increment is reset to 1&#39;
2017-01-17T08:23:15.710200Z 4 [Note] Plugin group_replication reported: &#39;auto_increment_offset is reset to 1&#39;
2017-01-17T08:23:15.710401Z 20 [Note] Error reading relay log event for channel &#39;group_replication_applier&#39;: slave SQL thread was killed
2017-01-17T08:23:15.711212Z 17 [Note] Plugin group_replication reported: &#39;The group replication applier thread was killed&#39;
2017-01-17T08:23:42.141239Z 4 [Note] Plugin group_replication reported: &#39;Group communication SSL configuration: group_replication_ssl_mode: "DISABLED"&#39;
2017-01-17T08:23:42.141451Z 4 [Note] Plugin group_replication reported: &#39;[GCS] Added automatically IP ranges 127.0.0.1/8,192.168.121.111/23 to the whitelist&#39;
2017-01-17T08:23:42.141990Z 4 [Note] Plugin group_replication reported: &#39;[GCS] Translated &#39;db2&#39; to 192.168.121.111&#39;
2017-01-17T08:23:42.142174Z 4 [Note] Plugin group_replication reported: &#39;[GCS] SSL was not enabled&#39;
2017-01-17T08:23:42.142220Z 4 [Note] Plugin group_replication reported: &#39;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"&#39;
2017-01-17T08:23:42.142936Z 28 [Note] &#39;CHANGE MASTER TO FOR CHANNEL &#39;group_replication_applier&#39; executed&#39;. 
Previous state master_host=&#39;<NULL>&#39;, master_port= 0, master_log_file=&#39;&#39;, master_log_pos= 4, master_bind=&#39;&#39;. 
New state master_host=&#39;<NULL>&#39;, master_port= 0, master_log_file=&#39;&#39;, master_log_pos= 4, master_bind=&#39;&#39;.

-- #（1）尝试启动默认的组复制，结果信息不对称，启动失败，然后开始初始化Group Replication
2017-01-17T08:23:42.166956Z 31 [Note] Slave SQL thread for channel &#39;group_replication_applier&#39; initialized, 
starting replication in log &#39;FIRST&#39; at position 0, relay log &#39;./bpe_service-relay-bin-group_replication_applier.000019&#39; position: 4
2017-01-17T08:23:42.166960Z 4 [Note] Plugin group_replication reported: &#39;Group Replication applier module successfully initialized!&#39;
2017-01-17T08:23:42.167064Z 4 [Note] Plugin group_replication reported: &#39;auto_increment_increment is set to 7&#39;
2017-01-17T08:23:42.167079Z 4 [Note] Plugin group_replication reported: &#39;auto_increment_offset is set to 12002&#39;
2017-01-17T08:23:42.167219Z 0 [Note] Plugin group_replication reported: &#39;state 4257 action xa_init&#39;
2017-01-17T08:23:42.167292Z 0 [Note] Plugin group_replication reported: &#39;Successfully bound to 0.0.0.0:24902 (socket=49).&#39;
2017-01-17T08:23:42.167341Z 0 [Note] Plugin group_replication reported: &#39;Successfully set listen backlog to 32 (socket=49)!&#39;
2017-01-17T08:23:42.167357Z 0 [Note] Plugin group_replication reported: &#39;Successfully unblocked socket (socket=49)!&#39;
2017-01-17T08:23:42.167400Z 0 [Note] Plugin group_replication reported: &#39;connecting to db2 24902&#39;
2017-01-17T08:23:42.167585Z 0 [Note] Plugin group_replication reported: &#39;Ready to accept incoming connections on 0.0.0.0:24902 (socket=49)!&#39;
2017-01-17T08:23:42.174427Z 0 [Note] Plugin group_replication reported: &#39;client connected to db2 24902 fd 50&#39;
2017-01-17T08:23:42.174807Z 0 [Note] Plugin group_replication reported: &#39;connecting to db2 24902&#39;
2017-01-17T08:23:42.175008Z 0 [Note] Plugin group_replication reported: &#39;client connected to db2 24902 fd 63&#39;
2017-01-17T08:23:42.175234Z 0 [Note] Plugin group_replication reported: &#39;connecting to db2 24902&#39;
2017-01-17T08:23:42.175411Z 0 [Note] Plugin group_replication reported: &#39;client connected to db2 24902 fd 65&#39;
2017-01-17T08:23:42.175614Z 0 [Note] Plugin group_replication reported: &#39;connecting to db2 24902&#39;
2017-01-17T08:23:42.175799Z 0 [Note] Plugin group_replication reported: &#39;client connected to db2 24902 fd 67&#39;
2017-01-17T08:23:42.176006Z 0 [Note] Plugin group_replication reported: &#39;connecting to db2 24902&#39;
2017-01-17T08:23:42.176176Z 0 [Note] Plugin group_replication reported: &#39;client connected to db2 24902 fd 69&#39;
2017-01-17T08:23:42.176377Z 0 [Note] Plugin group_replication reported: &#39;connecting to db2 24902&#39;
2017-01-17T08:23:42.176563Z 0 [Note] Plugin group_replication reported: &#39;client connected to db2 24902 fd 71&#39;
2017-01-17T08:23:42.176876Z 0 [Note] Plugin group_replication reported: &#39;connecting to db1 24901&#39;
2017-01-17T08:23:42.177312Z 0 [Note] Plugin group_replication reported: &#39;client connected to db1 24901 fd 73&#39;
2017-01-17T08:23:43.463474Z 0 [Note] Plugin group_replication reported: &#39;state 4257 action xa_snapshot&#39;
2017-01-17T08:23:43.463969Z 0 [Note] Plugin group_replication reported: &#39;new state x_recover&#39;
2017-01-17T08:23:43.464000Z 0 [Note] Plugin group_replication reported: &#39;state 4277 action xa_complete&#39;
2017-01-17T08:23:43.464275Z 0 [Note] Plugin group_replication reported: &#39;new state x_run&#39;
2017-01-17T08:23:44.619112Z 0 [Note] Plugin group_replication reported: &#39;Starting group replication recovery with view_id 14844510167669342:17&#39; 

--#（2） 开始进行组内数据recovery
2017-01-17T08:23:44.619451Z 34 [Note] Plugin group_replication reported: &#39;Establishing group recovery connection with a possible donor. Attempt 1/10&#39;
2017-01-17T08:23:44.624181Z 34 [Note] &#39;CHANGE MASTER TO FOR CHANNEL &#39;group_replication_recovery&#39; executed&#39;. Previous state master_host=&#39;<NULL>&#39;, 
master_port= 0, master_log_file=&#39;&#39;, master_log_pos= 4, master_bind=&#39;&#39;. New state master_host=&#39;hch_test_web_1_24&#39;, master_port= 3317, 
master_log_file=&#39;&#39;, master_log_pos= 4, master_bind=&#39;&#39;.
2017-01-17T08:23:44.628853Z 34 [Note] Plugin group_replication reported: &#39;Establishing connection to a group replication
 recovery donor 664b9ce9-d7de-11e6-9e8c-18a99b763071 at hch_test_web_1_24 port: 3317.&#39;
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 &#39;START SLAVE Syntax&#39;
  in the MySQL Manual for more information.
2017-01-17T08:23:44.635870Z 36 [Note] Slave SQL thread for channel &#39;group_replication_recovery&#39; initialized, starting
 replication in log &#39;FIRST&#39; at position 0, relay log &#39;./bpe_service-relay-bin-group_replication_recovery.000001&#39; position: 4
2017-01-17T08:23:44.681897Z 35 [Note] Slave I/O thread for channel &#39;group_replication_recovery&#39;: 
connected to master &#39;repl@hch_test_web_1_24:3317&#39;,replication started in log &#39;FIRST&#39; at position 4
2017-01-17T08:43:34.219952Z 34 [Note] Plugin group_replication reported: &#39;Terminating existing group replication donor connection and 
purging the corresponding logs.&#39;
2017-01-17T08:43:34.220609Z 36 [Note] Slave SQL thread for channel &#39;group_replication_recovery&#39; exiting, 
replication stopped in log &#39;binlog.000042&#39; at position 185511578
2017-01-17T08:43:34.226869Z 35 [Note] Slave I/O thread killed while reading event for channel &#39;group_replication_recovery&#39;
2017-01-17T08:43:34.226886Z 35 [Note] Slave I/O thread exiting for channel &#39;group_replication_recovery&#39;, read up to log &#39;binlog.000042&#39;, position 185511578
2017-01-17T08:43:34.269973Z 34 [Note] &#39;CHANGE MASTER TO FOR CHANNEL &#39;group_replication_recovery&#39; executed&#39;. 
Previous state master_host=&#39;hch_test_web_1_24&#39;, master_port= 3317, master_log_file=&#39;&#39;,
 master_log_pos= 4, master_bind=&#39;&#39;. New state master_host=&#39;<NULL>&#39;, master_port= 0, master_log_file=&#39;&#39;, master_log_pos= 4, master_bind=&#39;&#39;.

-- # （3）已经数据恢复完成，加入组，成为组成员
2017-01-17T08:43:34.290920Z 0 [Note] Plugin group_replication reported: &#39;This server was declared online within the replication group&#39;

Summary: For the use of mysql group replication, some concepts such as mongodb sharding and primary-secondary ideas can be referred to, so that you can understand it It's easier.

The above is the detailed content of In-depth understanding of the RECOVERING status of MySQL Group Replication. For more information, please follow other related articles on the PHP Chinese website!

Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：Detailed introduction to the method of hiding the middle four digits of a mobile phone number using mysql sql statementsNext article：Detailed introduction to the method of hiding the middle four digits of a mobile phone number using mysql sql statements

Related articles

See more