主从复制GTID模式下主库binary log被删除后的处理方法

1、数据库版本

root@localhost:mysql.sock  03:25:39 [test]>select version();+------------+| version()  |+------------+| 5.6.37-log |+------------+1 row in set (0.00 sec)

2、实验过程

首先建立基于GTID的主从同步，往主库插入数据，查看从库状态

root@localhost:mysql.sock  05:23:31 [test]>show slave status \G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.18.50                  Master_User: copy                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mybinlog.000003          Read_Master_Log_Pos: 191               Relay_Log_File: mysql-relay-bin.000006                Relay_Log_Pos: 399        Relay_Master_Log_File: mybinlog.000003             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 191              Relay_Log_Space: 689              Until_Condition: None               Until_Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 203306                  Master_UUID: 5385fd59-884a-11e7-90eb-080027eb4c97             Master_Info_File: /mydata/mysql/mysql_3306/master.info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it           Master_Retry_Count: 86400                  Master_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set: 5385fd59-884a-11e7-90eb-080027eb4c97:1-3            Executed_Gtid_Set: 5385fd59-884a-11e7-90eb-080027eb4c97:1-3                Auto_Position: 11 row in set (0.00 sec)

停止从库slave

root@localhost:mysql.sock  05:23:39 [test]>stop slave;Query OK, 0 rows affected (0.06 sec)

往主库插入数据，并purge掉部分binary log

root@localhost:mysql.sock  05:25:19 [test]>insert into helloa values (3,'nana');flush logs;Query OK, 1 row affected (0.00 sec)Query OK, 0 rows affected (0.03 sec)root@localhost:mysql.sock  05:25:37 [test]>show master status;+-----------------+----------+--------------+------------------+------------------------------------------+| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |+-----------------+----------+--------------+------------------+------------------------------------------+| mybinlog.000004 |      191 |              |                  | 5385fd59-884a-11e7-90eb-080027eb4c97:1-4 |+-----------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)root@localhost:mysql.sock  05:25:43 [test]>purge master logs to 'mybinlog.000004';Query OK, 0 rows affected (0.06 sec)root@localhost:mysql.sock  05:26:00 [test]>show variables like '%gtid%';+---------------------------------+------------------------------------------+| Variable_name                   | Value                                    |+---------------------------------+------------------------------------------+| binlog_gtid_simple_recovery     | OFF                                      || enforce_gtid_consistency        | ON                                       || gtid_executed                   |                                          || gtid_mode                       | ON                                       || gtid_next                       | AUTOMATIC                                || gtid_owned                      |                                          || gtid_purged                     | 5385fd59-884a-11e7-90eb-080027eb4c97:1-4 || simplified_binlog_gtid_recovery | OFF                                      |+---------------------------------+------------------------------------------+8 rows in set (0.00 sec)

打开从库slave，发现数据没有过来，查看slave状态，报错

root@localhost:mysql.sock  05:27:41 [test]>start slave;Query OK, 0 rows affected (0.00 sec)root@localhost:mysql.sock  05:27:45 [test]>select * from helloa;+------+------+| id   | name |+------+------+|    1 | yaya ||    2 | meme |+------+------+2 rows in set (0.00 sec)root@localhost:mysql.sock  05:27:49 [test]>show slave status \G:*************************** 1. row ***************************               Slave_IO_State:                   Master_Host: 192.168.18.50                  Master_User: copy                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mybinlog.000003          Read_Master_Log_Pos: 191               Relay_Log_File: mysql-relay-bin.000006                Relay_Log_Pos: 399        Relay_Master_Log_File: mybinlog.000003             Slave_IO_Running: No            Slave_SQL_Running: Yes              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 191              Relay_Log_Space: 689              Until_Condition: None               Until_Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: No                Last_IO_Errno: 1236                Last_IO_Error: 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.'               Last_SQL_Errno: 0               Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 203306                  Master_UUID: 5385fd59-884a-11e7-90eb-080027eb4c97             Master_Info_File: /mydata/mysql/mysql_3306/master.info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it           Master_Retry_Count: 86400                  Master_Bind:       Last_IO_Error_Timestamp: 170829 05:27:45     Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set: 5385fd59-884a-11e7-90eb-080027eb4c97:1-3            Executed_Gtid_Set: 5385fd59-884a-11e7-90eb-080027eb4c97:1-3                Auto_Position: 11 row in set (0.00 sec)

查看主库purged参数

root@localhost:mysql.sock  05:34:24 [test]>show variables like '%gtid%';+---------------------------------+------------------------------------------+| Variable_name                   | Value                                    |+---------------------------------+------------------------------------------+| binlog_gtid_simple_recovery     | OFF                                      || enforce_gtid_consistency        | ON                                       || gtid_executed                   |                                          || gtid_mode                       | ON                                       || gtid_next                       | AUTOMATIC                                || gtid_owned                      |                                          || gtid_purged                     | 5385fd59-884a-11e7-90eb-080027eb4c97:1-4 || simplified_binlog_gtid_recovery | OFF                                      |+---------------------------------+------------------------------------------+8 rows in set (0.00 sec)

解决方法：

root@localhost:mysql.sock  05:29:34 [test]>stop slave;Query OK, 0 rows affected (0.04 sec)root@localhost:mysql.sock  05:31:05 [test]>reset master;Query OK, 0 rows affected (0.01 sec)root@localhost:mysql.sock  05:31:14 [test]>set global gtid_purged='5385fd59-884a-11e7-90eb-080027eb4c97:1-4';Query OK, 0 rows affected (0.07 sec)root@localhost:mysql.sock  05:31:34 [test]>start slave;Query OK, 0 rows affected (0.01 sec)

继续往主库插入数据，查看从库状态恢复正常

root@localhost:mysql.sock  05:36:24 [test]>show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.18.50                  Master_User: copy                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mybinlog.000004          Read_Master_Log_Pos: 437               Relay_Log_File: mysql-relay-bin.000007                Relay_Log_Pos: 645        Relay_Master_Log_File: mybinlog.000004             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 437              Relay_Log_Space: 1097              Until_Condition: None               Until_Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 203306                  Master_UUID: 5385fd59-884a-11e7-90eb-080027eb4c97             Master_Info_File: /mydata/mysql/mysql_3306/master.info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it           Master_Retry_Count: 86400                  Master_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set: 5385fd59-884a-11e7-90eb-080027eb4c97:1-3:5            Executed_Gtid_Set: 5385fd59-884a-11e7-90eb-080027eb4c97:1-5                Auto_Position: 11 row in set (0.00 sec)