[Mysql] mysql-mgr模拟the master has purged binary logs containing GTIDs that the slave requires.', Error_code: 1236


本文总阅读量次

0、实验环境介绍：

mysql-mgr单主模式主节点1、从节点1、从节点2

1、实验一，手动删除主库binlog日志文件

从库2

1
执行stop group_replication;

主库：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
#切换日志，创建表gtid_test10,并查看当前的binlog日志文件：
flush logs;create table gtid_test10 (ID int) engine=innodb;
Query OK, 0 rows affected (0.04 sec)
Query OK, 0 rows affected (0.05 sec)
root@db 05:48:  [test]> show master status;
+---------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                                                                                                         |
+---------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-binlog.000029 |      493 |              |                  | 8182e5ae-af54-11e8-af0e-000d3a801ae2:1-13253,
c42e3372-ba21-11e8-99ed-000d3a800ed3:1-2,
d240752c-b809-11e8-8947-000d3a800ed3:1,
dd412cc2-ba1f-11e8-9ba2-000d3a801ae2:1-27 |
+---------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#切换日志，创建表gtid_test10,并查看当前的binlog日志文件：
root@db 05:48:  [test]> flush logs;create table gtid_test11 (ID int) engine=innodb;
Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected (0.05 sec)

root@db 05:48:  [test]> show master status;
+---------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                                                                                                         |
+---------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-binlog.000030 |      493 |              |                  | 8182e5ae-af54-11e8-af0e-000d3a801ae2:1-13253,
c42e3372-ba21-11e8-99ed-000d3a800ed3:1-2,
d240752c-b809-11e8-8947-000d3a800ed3:1,
dd412cc2-ba1f-11e8-9ba2-000d3a801ae2:1-28 |
+---------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
#主库手动移除binlog文件：
mv mysql-binlog.000029 /tmp/

从库2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
执行start group_replication;
#查看从库2的日志
2018-09-18T05:49:55.983888Z 93 [Note] Plugin group_replication reported: 'Establishing group recovery connection with a possible donor. Attempt 1/10'
2018-09-18T05:49:55.984034Z 0 [Note] Plugin group_replication reported: 'Group membership changed to dax-mysql-slave:3306, dax-mysql-master:3306, dax-mysql-slave2:3306 on view 15371510730966421:31.'
2018-09-18T05:49:56.054981Z 93 [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='dax-mysql-master', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2018-09-18T05:49:56.144944Z 93 [Note] Plugin group_replication reported: 'Establishing connection to a group replication recovery donor 8182e5ae-af54-11e8-af0e-000d3a801ae2 at dax-mysql-master port: 3306.'
2018-09-18T05:49:56.155058Z 95 [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.
2018-09-18T05:49:56.157141Z 95 [Note] Slave I/O thread for channel 'group_replication_recovery': connected to master 'repl@dax-mysql-master:3306',replication started in log 'FIRST' at position 4
2018-09-18T05:49:56.179441Z 96 [Note] Slave SQL thread for channel 'group_replication_recovery' initialized, starting replication in log 'FIRST' at position 0, relay log './relay-log-group_replication_recovery.000001' position: 4
2018-09-18T05:49:56.326072Z 95 [ERROR] Error reading packet from server for channel 'group_replication_recovery': Could not open log file (server_errno=1236)
2018-09-18T05:49:56.326236Z 95 [ERROR] Slave I/O for channel 'group_replication_recovery': Got fatal error 1236 from master when reading data from binary log: 'Could not open log file', Error_code: 1236
#因为已经手动删除了binlog文件，从库2无法从主库获取到binlog文件，报错
2018-09-18T05:49:56.326310Z 95 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'mysql-binlog.000029', position 4
2018-09-18T05:49:56.326422Z 93 [Note] Plugin group_replication reported: 'Terminating existing group replication donor connection and purging the corresponding logs.'
2018-09-18T05:49:56.347776Z 96 [Note] Error reading relay log event for channel 'group_replication_recovery': slave SQL thread was killed
2018-09-18T05:49:56.438760Z 93 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='dax-mysql-master', master_port= 3306, 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=''.
2018-09-18T05:49:56.510091Z 93 [Note] Plugin group_replication reported: 'Retrying group recovery connection with another donor. Attempt 2/10'
2018-09-18T05:49:56.572561Z 93 [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='dax-mysql-slave', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
#从库2开始尝试从从库1节点获取binlog文件，因为只是删除了主库节点的binlog文件，从库1的文件未删除，从库2获取到所需要的文件，开始进行同步。
2018-09-18T05:49:56.643413Z 93 [Note] Plugin group_replication reported: 'Establishing connection to a group replication recovery donor 66d67181-ba5b-11e8-9c54-000d3a800ed3 at dax-mysql-slave port: 3306.'
2018-09-18T05:49:56.643906Z 99 [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.
2018-09-18T05:49:56.646363Z 99 [Note] Slave I/O thread for channel 'group_replication_recovery': connected to master 'repl@dax-mysql-slave:3306',replication started in log 'FIRST' at position 4
2018-09-18T05:49:56.666439Z 100 [Note] Slave SQL thread for channel 'group_replication_recovery' initialized, starting replication in log 'FIRST' at position 0, relay log './relay-log-group_replication_recovery.000001' position: 4
2018-09-18T05:49:56.882987Z 93 [Note] Plugin group_replication reported: 'Terminating existing group replication donor connection and purging the corresponding logs.'
2018-09-18T05:49:56.905507Z 99 [Note] Slave I/O thread killed while reading event for channel 'group_replication_recovery'
2018-09-18T05:49:56.905535Z 99 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'mysql-binlog.000005', position 10512
2018-09-18T05:49:56.999936Z 93 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='dax-mysql-slave', master_port= 3306, 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=''.
2018-09-18T05:49:57.084035Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group'

同步完成。

2、实验二，使用purge命令删除主库binlog文件

从库2

1
stop group_replication:

主库1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
#切换日志，创建表gtid_test12,并查看当前的binlog日志文件：
root@db 06:04:  [test]> flush logs;create table gtid_test12 (ID int) engine=innodb;
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.05 sec)
root@db 06:04:  [test]> show master status;
+---------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                                                                                                         |
+---------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-binlog.000033 |      493 |              |                  | 8182e5ae-af54-11e8-af0e-000d3a801ae2:1-13253,
c42e3372-ba21-11e8-99ed-000d3a800ed3:1-2,
d240752c-b809-11e8-8947-000d3a800ed3:1,
dd412cc2-ba1f-11e8-9ba2-000d3a801ae2:1-30 |
+---------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

#切换日志，创建表gtid_test13,并查看当前的binlog日志文件：
root@db 06:04:  [test]> flush logs;create table gtid_test13 (ID int) engine=innodb;
Query OK, 0 rows affected (0.04 sec)
Query OK, 0 rows affected (0.05 sec)
root@db 06:04:  [test]> show master status;
+---------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                                                                                                         |
+---------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-binlog.000034 |      493 |              |                  | 8182e5ae-af54-11e8-af0e-000d3a801ae2:1-13253,
c42e3372-ba21-11e8-99ed-000d3a800ed3:1-2,
d240752c-b809-11e8-8947-000d3a800ed3:1,
dd412cc2-ba1f-11e8-9ba2-000d3a801ae2:1-31 |
+---------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
#主库清除mysql-binlog.000034之前的所有binlog日志：
purge binary logs to 'mysql-binlog.000034';
#查看日志log目录下的34之前的日志已经全部清除：
ll mysql-binlog.*
-rw-r----- 1 mysql mysql 493 Sep 18 06:04 mysql-binlog.000034
-rw-r----- 1 mysql mysql  36 Sep 18 06:22 mysql-binlog.index

#查看主库gtid信息，gtid_purged已经有记录：
show global variables like '%gtid%';
root@db 06:37:  [test]> show global variables like '%gtid%';
+---------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name                                     | Value                                                                                                                                                                     |
+---------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery                       | ON                                                                                                                                                                        |
| enforce_gtid_consistency                          | ON                                                                                                                                                                        |
| group_replication_allow_local_disjoint_gtids_join | ON                                                                                                                                                                        |
| group_replication_gtid_assignment_block_size      | 1000000                                                                                                                                                                   |
| gtid_executed                                     | 8182e5ae-af54-11e8-af0e-000d3a801ae2:1-13253,
c42e3372-ba21-11e8-99ed-000d3a800ed3:1-2,
d240752c-b809-11e8-8947-000d3a800ed3:1,
dd412cc2-ba1f-11e8-9ba2-000d3a801ae2:1-31 |
| gtid_executed_compression_period                  | 1000                                                                                                                                                                      |
| gtid_mode                                         | ON                                                                                                                                                                        |
| gtid_owned                                        |                                                                                                                                                                           |
| gtid_purged                                       | 8182e5ae-af54-11e8-af0e-000d3a801ae2:1-13253,
c42e3372-ba21-11e8-99ed-000d3a800ed3:1-2,
d240752c-b809-11e8-8947-000d3a800ed3:1,
dd412cc2-ba1f-11e8-9ba2-000d3a801ae2:1-30 |
| session_track_gtids                               | OFF                                                                                                                                                                       |
+---------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.01 sec)

从节点2

1
start group_replication;

查看从库2的日志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
2018-09-18T06:38:36.664372Z 107 [Note] Plugin group_replication reported: 'Initialized group communication with configuration: group_replication_group_name: "dd412cc2-ba1f-11e8-9ba2-000d3a801ae2"; group_replication_local_address: "dax-mysql-slave2:24901"; group_replication_group_seeds: "dax-mysql-slave:24901,dax-mysql-master:24901,dax-mysql-slave2:24901"; group_replication_bootstrap_group: false; group_replication_poll_spin_loops: 0; group_replication_compression_threshold: 1000000; group_replication_ip_whitelist: "AUTOMATIC"'
2018-09-18T06:38:36.664408Z 107 [Note] Plugin group_replication reported: '[GCS] Configured number of attempts to join: 0'
2018-09-18T06:38:36.664428Z 107 [Note] Plugin group_replication reported: '[GCS] Configured time between attempts to join: 5 seconds'
2018-09-18T06:38:36.664457Z 107 [Note] Plugin group_replication reported: 'Member configuration: member_id: 3306101; member_uuid: "c6ac9ccd-b80b-11e8-b968-000d3a801bf4"; single-primary mode: "true"; group_replication_auto_increment_increment: 1; '
2018-09-18T06:38:36.664812Z 109 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 493, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
2018-09-18T06:38:36.736702Z 107 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!'
2018-09-18T06:38:36.736749Z 107 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 1'
2018-09-18T06:38:36.736774Z 107 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 3306101'
2018-09-18T06:38:36.736708Z 112 [Note] Slave SQL thread for channel 'group_replication_applier' initialized, starting replication in log 'FIRST' at position 0, relay log './relay-log-group_replication_applier.000014' position: 4
2018-09-18T06:38:36.737167Z 0 [Note] Plugin group_replication reported: 'XCom protocol version: 3'
2018-09-18T06:38:36.737227Z 0 [Note] Plugin group_replication reported: 'XCom initialized and ready to accept incoming connections on port 24901'
2018-09-18T06:38:38.815550Z 107 [Note] Plugin group_replication reported: 'This server is working as secondary member with primary member address dax-mysql-master:3306.'
2018-09-18T06:38:38.815711Z 0 [ERROR] Plugin group_replication reported: 'Group contains 3 members which is greater than group_replication_auto_increment_increment value of 1. This can lead to an higher rate of transactional aborts.'
2018-09-18T06:38:38.816035Z 117 [Note] Plugin group_replication reported: 'Establishing group recovery connection with a possible donor. Attempt 1/10'
2018-09-18T06:38:38.816125Z 0 [Note] Plugin group_replication reported: 'Group membership changed to dax-mysql-slave:3306, dax-mysql-master:3306, dax-mysql-slave2:3306 on view 15371510730966421:33.'
2018-09-18T06:38:38.881138Z 117 [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='dax-mysql-slave', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
#从节点2选择从从节点1获取binlog文件，因为从节点1文件没有删除，获取binlog文件正常。
2018-09-18T06:38:38.959786Z 117 [Note] Plugin group_replication reported: 'Establishing connection to a group replication recovery donor 66d67181-ba5b-11e8-9c54-000d3a800ed3 at dax-mysql-slave port: 3306.'
2018-09-18T06:38:38.960147Z 119 [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.
2018-09-18T06:38:38.962244Z 119 [Note] Slave I/O thread for channel 'group_replication_recovery': connected to master 'repl@dax-mysql-slave:3306',replication started in log 'FIRST' at position 4
2018-09-18T06:38:38.982193Z 120 [Note] Slave SQL thread for channel 'group_replication_recovery' initialized, starting replication in log 'FIRST' at position 0, relay log './relay-log-group_replication_recovery.000001' position: 4
2018-09-18T06:38:39.194864Z 117 [Note] Plugin group_replication reported: 'Terminating existing group replication donor connection and purging the corresponding logs.'
2018-09-18T06:38:39.218168Z 119 [Note] Slave I/O thread killed while reading event for channel 'group_replication_recovery'
2018-09-18T06:38:39.218197Z 119 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'mysql-binlog.000005', position 11362
2018-09-18T06:38:39.313623Z 117 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='dax-mysql-slave', master_port= 3306, 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=''.
2018-09-18T06:38:39.400236Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group'

同步完成。

3、实验三，删除主库节点和从节点1的binlog文件。

从库2

1
stop group_replication;

主库1：

1
2
3
4
5
6
7
8
9
10
11
3切换日志，创建表gtid_test14,并查看当前的binlog日志文件：
flush logs;create table gtid_test14 (ID int) engine=innodb;
show master status;
#切换日志，创建表gtid_test15,并查看当前的binlog日志文件：
flush logs;create table gtid_test15 (ID int) engine=innodb;
show master status;
#清除mysql-binlog.000036之前的日志：
purge binary logs to 'mysql-binlog.000036';
[root@dax-mysql-master log]# ll mysql-binlog.*
-rw-r----- 1 mysql mysql 493 Sep 18 06:53 mysql-binlog.000036
-rw-r----- 1 mysql mysql  36 Sep 18 06:56 mysql-binlog.index

从库1删除binlog日志：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
show master status;
+---------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                                                                                                                                                 |
+---------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-binlog.000005 |    11728 |              |                  | 324a6fd1-ba55-11e8-b3ff-000d3a800ed3:1,
8182e5ae-af54-11e8-af0e-000d3a801ae2:1-13253,
c42e3372-ba21-11e8-99ed-000d3a800ed3:1-2,
d240752c-b809-11e8-8947-000d3a800ed3:1,
dd412cc2-ba1f-11e8-9ba2-000d3a801ae2:1-34 |
+---------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
#切换binlog日志：
flush logs;
show master status;
root@db 06:57:  [test]> show master status;
+---------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                                                                                                                                                 |
+---------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-binlog.000006 |      350 |              |                  | 324a6fd1-ba55-11e8-b3ff-000d3a800ed3:1,
8182e5ae-af54-11e8-af0e-000d3a801ae2:1-13253,
c42e3372-ba21-11e8-99ed-000d3a800ed3:1-2,
d240752c-b809-11e8-8947-000d3a800ed3:1,
dd412cc2-ba1f-11e8-9ba2-000d3a801ae2:1-34 |
+---------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
[root@dax-mysql-slave log]# ll 
total 96
-rw-r----- 1 mysql mysql 53478 Sep 18 06:49 error.log
-rw-r----- 1 mysql mysql   177 Sep 17 08:39 mysql-binlog.000001
-rw-r----- 1 mysql mysql   437 Sep 17 09:21 mysql-binlog.000002
-rw-r----- 1 mysql mysql   217 Sep 17 09:25 mysql-binlog.000003
-rw-r----- 1 mysql mysql   209 Sep 17 09:26 mysql-binlog.000004
-rw-r----- 1 mysql mysql 11774 Sep 18 06:57 mysql-binlog.000005
-rw-r----- 1 mysql mysql   350 Sep 18 06:57 mysql-binlog.000006
-rw-r----- 1 mysql mysql   216 Sep 18 06:57 mysql-binlog.index
-rw-r--r-- 1 mysql mysql     0 Sep 17 08:36 mysqld.log
-rw-r----- 1 mysql mysql  1968 Sep 18 06:57 slow.log
#删除mysql-binlog.000006之前的所有日志：
purge binary logs to 'mysql-binlog.000006';
[root@dax-mysql-slave log]# ll 
total 68
-rw-r----- 1 mysql mysql 53478 Sep 18 06:49 error.log
-rw-r----- 1 mysql mysql   350 Sep 18 06:57 mysql-binlog.000006
-rw-r----- 1 mysql mysql    36 Sep 18 06:58 mysql-binlog.index
-rw-r--r-- 1 mysql mysql     0 Sep 17 08:36 mysqld.log
-rw-r----- 1 mysql mysql  1968 Sep 18 06:57 slow.log

从库2

1
start group_replicatinon;

查看从库2的日志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
2018-09-18T06:59:20.236425Z 136 [Note] Plugin group_replication reported: 'Retrying group recovery connection with another donor. Attempt 2/10'
2018-09-18T06:59:20.309947Z 136 [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='dax-mysql-slave', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2018-09-18T06:59:20.391623Z 136 [Note] Plugin group_replication reported: 'Establishing connection to a group replication recovery donor 66d67181-ba5b-11e8-9c54-000d3a800ed3 at dax-mysql-slave port: 3306.'
2018-09-18T06:59:20.391891Z 142 [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.
2018-09-18T06:59:20.394226Z 142 [Note] Slave I/O thread for channel 'group_replication_recovery': connected to master 'repl@dax-mysql-slave:3306',replication started in log 'FIRST' at position 4
2018-09-18T06:59:20.416722Z 143 [Note] Slave SQL thread for channel 'group_replication_recovery' initialized, starting replication in log 'FIRST' at position 0, relay log './relay-log-group_replication_recovery.000001' position: 4
2018-09-18T06:59:20.424400Z 142 [ERROR] Error reading packet from server for channel 'group_replication_recovery': The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires. (server_errno=1236)
2018-09-18T06:59:20.424467Z 142 [ERROR] Slave I/O for channel 'group_replication_recovery': Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.', Error_code: 1236
2018-09-18T06:59:20.424488Z 142 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'FIRST', position 4
2018-09-18T06:59:20.424640Z 136 [Note] Plugin group_replication reported: 'Terminating existing group replication donor connection and purging the corresponding logs.'
2018-09-18T06:59:20.436806Z 143 [Note] Error reading relay log event for channel 'group_replication_recovery': slave SQL thread was killed
2018-09-18T06:59:20.522269Z 136 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='dax-mysql-slave', master_port= 3306, 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=''.
2018-09-18T06:59:20.603837Z 136 [Note] Plugin group_replication reported: 'Retrying group recovery connection with another donor. Attempt 3/10'
2018-09-18T07:00:20.679461Z 136 [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='dax-mysql-slave', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2018-09-18T07:00:20.762021Z 136 [Note] Plugin group_replication reported: 'Establishing connection to a group replication recovery donor 66d67181-ba5b-11e8-9c54-000d3a800ed3 at dax-mysql-slave port: 3306.'
2018-09-18T07:00:20.762368Z 146 [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.
2018-09-18T07:00:20.764842Z 146 [Note] Slave I/O thread for channel 'group_replication_recovery': connected to master 'repl@dax-mysql-slave:3306',replication started in log 'FIRST' at position 4
2018-09-18T07:00:20.787728Z 147 [Note] Slave SQL thread for channel 'group_replication_recovery' initialized, starting replication in log 'FIRST' at position 0, relay log './relay-log-group_replication_recovery.000001' position: 4
2018-09-18T07:00:20.794590Z 146 [ERROR] Error reading packet from server for channel 'group_replication_recovery': The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires. (server_errno=1236)
2018-09-18T07:00:20.794623Z 146 [ERROR] Slave I/O for channel 'group_replication_recovery': Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.', Error_code: 1236
2018-09-18T07:00:20.794635Z 146 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'FIRST', position 4
2018-09-18T07:00:20.794786Z 136 [Note] Plugin group_replication reported: 'Terminating existing group replication donor connection and purging the corresponding logs.'
2018-09-18T07:00:20.807977Z 147 [Note] Error reading relay log event for channel 'group_replication_recovery': slave SQL thread was killed
2018-09-18T07:00:20.895774Z 136 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='dax-mysql-slave', master_port= 3306, 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=''.
#从库2多次尝试从从库1获取binlog日志，没有成功，切换master_host节点，尝试从主库获取binlog日志文件：
2018-09-18T07:00:20.980112Z 136 [Note] Plugin group_replication reported: 'Retrying group recovery connection with another donor. Attempt 4/10'
2018-09-18T07:00:21.052930Z 136 [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='dax-mysql-master', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2018-09-18T07:00:21.141126Z 136 [Note] Plugin group_replication reported: 'Establishing connection to a group replication recovery donor 8182e5ae-af54-11e8-af0e-000d3a801ae2 at dax-mysql-master port: 3306.'
2018-09-18T07:00:21.141498Z 150 [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.
2018-09-18T07:00:21.143773Z 150 [Note] Slave I/O thread for channel 'group_replication_recovery': connected to master 'repl@dax-mysql-master:3306',replication started in log 'FIRST' at position 4
2018-09-18T07:00:21.166993Z 151 [Note] Slave SQL thread for channel 'group_replication_recovery' initialized, starting replication in log 'FIRST' at position 0, relay log './relay-log-group_replication_recovery.000001' position: 4
2018-09-18T07:00:21.171461Z 150 [ERROR] Error reading packet from server for channel 'group_replication_recovery': The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires. (server_errno=1236)
2018-09-18T07:00:21.171518Z 150 [ERROR] Slave I/O for channel 'group_replication_recovery': Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.', Error_code: 1236
2018-09-18T07:00:21.171528Z 150 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'FIRST', position 4
2018-09-18T07:00:21.171645Z 136 [Note] Plugin group_replication reported: 'Terminating existing group replication donor connection and purging the corresponding logs.'
2018-09-18T07:00:21.186565Z 151 [Note] Error reading relay log event for channel 'group_replication_recovery': slave SQL thread was killed
2018-09-18T07:00:21.271867Z 136 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='dax-mysql-master', master_port= 3306, 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=''.
....
[ERROR] Slave I/O for channel 'group_replication_recovery': Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.', Error_code: 1236

因为主库1和从库1的binlog文件都删除，从库2无法同步，最后退出集群。

4、查看主库清除的日志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 show global variables like '%gtid%';
+---------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name                                     | Value                                                                                                                                                                     |
+---------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery                       | ON                                                                                                                                                                        |
| enforce_gtid_consistency                          | ON                                                                                                                                                                        |
| group_replication_allow_local_disjoint_gtids_join | ON                                                                                                                                                                        |
| group_replication_gtid_assignment_block_size      | 1000000                                                                                                                                                                   |
| gtid_executed                                     | 8182e5ae-af54-11e8-af0e-000d3a801ae2:1-13253,
c42e3372-ba21-11e8-99ed-000d3a800ed3:1-2,
d240752c-b809-11e8-8947-000d3a800ed3:1,
dd412cc2-ba1f-11e8-9ba2-000d3a801ae2:1-35 |
| gtid_executed_compression_period                  | 1000                                                                                                                                                                      |
| gtid_mode                                         | ON                                                                                                                                                                        |
| gtid_owned                                        |                                                                                                                                                                           |
| gtid_purged                                       | 8182e5ae-af54-11e8-af0e-000d3a801ae2:1-13253,
c42e3372-ba21-11e8-99ed-000d3a800ed3:1-2,
d240752c-b809-11e8-8947-000d3a800ed3:1,
dd412cc2-ba1f-11e8-9ba2-000d3a801ae2:1-33 |
| session_track_gtids                               | OFF                                                                                                                                                                       |
+---------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

从库2尝试过滤掉缺失的那部分日志，然后重新加入集群：

1
2
3
4
stop group_replication;
reset master;
set global GTID_PURGED="8182e5ae-af54-11e8-af0e-000d3a801ae2:1-13253,c42e3372-ba21-11e8-99ed-000d3a800ed3:1-2,d240752c-b809-11e8-8947-000d3a800ed3:1,dd412cc2-ba1f-11e8-9ba2-000d3a801ae2:1-33";
start group_replication;

再次查看从库2的日志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
2018-09-18T07:08:05.524626Z 125 [Note] @@GLOBAL.GTID_PURGED was changed from '' to '8182e5ae-af54-11e8-af0e-000d3a801ae2:1-13253,
c42e3372-ba21-11e8-99ed-000d3a800ed3:1-2,
d240752c-b809-11e8-8947-000d3a800ed3:1,
dd412cc2-ba1f-11e8-9ba2-000d3a801ae2:1-33'.
2018-09-18T07:08:05.524661Z 125 [Note] @@GLOBAL.GTID_EXECUTED was changed from '' to '8182e5ae-af54-11e8-af0e-000d3a801ae2:1-13253,
c42e3372-ba21-11e8-99ed-000d3a800ed3:1-2,
d240752c-b809-11e8-8947-000d3a800ed3:1,
dd412cc2-ba1f-11e8-9ba2-000d3a801ae2:1-33'.
2018-09-18T07:08:20.457841Z 125 [Note] Plugin group_replication reported: 'Group communication SSL configuration: group_replication_ssl_mode: "DISABLED"'
2018-09-18T07:08:20.457971Z 125 [Note] Plugin group_replication reported: '[GCS] Added automatically IP ranges 10.0.7.51/24,127.0.0.1/8 to the whitelist'
2018-09-18T07:08:20.458188Z 125 [Note] Plugin group_replication reported: '[GCS] Translated 'dax-mysql-slave2' to 10.0.7.51'
2018-09-18T07:08:20.458321Z 125 [Warning] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the whitelist. It is mandatory that it is added.'
2018-09-18T07:08:20.458401Z 125 [Note] Plugin group_replication reported: '[GCS] SSL was not enabled'
2018-09-18T07:08:20.458433Z 125 [Note] Plugin group_replication reported: 'Initialized group communication with configuration: group_replication_group_name: "dd412cc2-ba1f-11e8-9ba2-000d3a801ae2"; group_replication_local_address: "dax-mysql-slave2:24901"; group_replication_group_seeds: "dax-mysql-slave:24901,dax-mysql-master:24901,dax-mysql-slave2:24901"; group_replication_bootstrap_group: false; group_replication_poll_spin_loops: 0; group_replication_compression_threshold: 1000000; group_replication_ip_whitelist: "AUTOMATIC"'
2018-09-18T07:08:20.458473Z 125 [Note] Plugin group_replication reported: '[GCS] Configured number of attempts to join: 0'
2018-09-18T07:08:20.458480Z 125 [Note] Plugin group_replication reported: '[GCS] Configured time between attempts to join: 5 seconds'
2018-09-18T07:08:20.458503Z 125 [Note] Plugin group_replication reported: 'Member configuration: member_id: 3306101; member_uuid: "c6ac9ccd-b80b-11e8-b968-000d3a801bf4"; single-primary mode: "true"; group_replication_auto_increment_increment: 1; '
2018-09-18T07:08:20.458664Z 181 [Note] Plugin group_replication reported: 'Detected previous RESET MASTER invocation or an issue exists in the group replication applier relay log. Purging existing applier logs.'
2018-09-18T07:08:20.531810Z 181 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='', master_port= 3306, 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=''.
2018-09-18T07:08:20.632692Z 125 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!'
2018-09-18T07:08:20.632736Z 125 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 1'
2018-09-18T07:08:20.632758Z 125 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 3306101'
2018-09-18T07:08:20.632694Z 184 [Note] Slave SQL thread for channel 'group_replication_applier' initialized, starting replication in log 'FIRST' at position 0, relay log './relay-log-group_replication_applier.000001' position: 4
2018-09-18T07:08:20.632947Z 0 [Note] Plugin group_replication reported: 'XCom protocol version: 3'
2018-09-18T07:08:20.632977Z 0 [Note] Plugin group_replication reported: 'XCom initialized and ready to accept incoming connections on port 24901'
2018-09-18T07:08:22.329812Z 125 [Note] Plugin group_replication reported: 'This server is working as secondary member with primary member address dax-mysql-master:3306.'
2018-09-18T07:08:22.329901Z 0 [ERROR] Plugin group_replication reported: 'Group contains 3 members which is greater than group_replication_auto_increment_increment value of 1. This can lead to an higher rate of transactional aborts.'
2018-09-18T07:08:22.330145Z 189 [Note] Plugin group_replication reported: 'Establishing group recovery connection with a possible donor. Attempt 1/10'
2018-09-18T07:08:22.330221Z 0 [Note] Plugin group_replication reported: 'Group membership changed to dax-mysql-slave:3306, dax-mysql-master:3306, dax-mysql-slave2:3306 on view 15371510730966421:37.'
2018-09-18T07:08:22.394512Z 189 [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='dax-mysql-master', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2018-09-18T07:08:22.468142Z 189 [Note] Plugin group_replication reported: 'Establishing connection to a group replication recovery donor 8182e5ae-af54-11e8-af0e-000d3a801ae2 at dax-mysql-master port: 3306.'
2018-09-18T07:08:22.468378Z 191 [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.
2018-09-18T07:08:22.470340Z 191 [Note] Slave I/O thread for channel 'group_replication_recovery': connected to master 'repl@dax-mysql-master:3306',replication started in log 'FIRST' at position 4
2018-09-18T07:08:22.490866Z 192 [Note] Slave SQL thread for channel 'group_replication_recovery' initialized, starting replication in log 'FIRST' at position 0, relay log './relay-log-group_replication_recovery.000001' position: 4
2018-09-18T07:08:22.632774Z 189 [Note] Plugin group_replication reported: 'Terminating existing group replication donor connection and purging the corresponding logs.'
2018-09-18T07:08:22.653083Z 191 [Note] Slave I/O thread killed while reading event for channel 'group_replication_recovery'
2018-09-18T07:08:22.653098Z 191 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'mysql-binlog.000036', position 1381
2018-09-18T07:08:22.734186Z 189 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='dax-mysql-master', master_port= 3306, 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=''.
2018-09-18T07:08:22.808784Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group'

此时从库2与主库、从库1的数据是不一致的，gtid_test14表时不存在的，因此需要使用pt-table-checksum和pt-table-sync去同步，但是该方法在mysql-mgr环境下测试没有成功。
另一种方法是直接备份一份主节点的最新备份数据，在从库2上恢复。

mysql
目录
  1. 1. 0、实验环境介绍：
  2. 2. 1、实验一，手动删除主库binlog日志文件
  3. 3. 2、实验二，使用purge命令删除主库binlog文件
  4. 4. 3、实验三，删除主库节点和从节点1的binlog文件。
  5. 5. 4、查看主库清除的日志

Proudly powered by Hexo and Theme by Lap
本站访客数人次
© 2020 zeven0707's blog