MySQL5.6基于GTID复制的一次故障

zhancat200801

本人在虚拟机上做实验，主从IP如下：Master:192.168.1.107
Slave1:192.168.1.111
Slave2:192.168.1.112

每台虚拟机分配的磁盘空间仅仅有20G，由于在Master上执行sysbench进行基准测试，自动产生了大量数据和binlog，磁盘满了，结果导致了slave上的I/O线程断开了。
在从库上执行show slave status\G显示：

1. mysql> show slave status\G
   
2. *************************** 1. row ***************************
   
3.                Slave_IO_State:
   
4.                   Master_Host: 192.168.1.107
   
5.                   Master_User: repl
   
6.                   Master_Port: 3306
   
7.                 Connect_Retry: 60
   
8.               Master_Log_File:
   
9.           Read_Master_Log_Pos: 4
   
10.                Relay_Log_File: mysqld-relay-bin.000001
    
11.                 Relay_Log_Pos: 4
    
12.         Relay_Master_Log_File:
    
13.              Slave_IO_Running: No
    
14.             Slave_SQL_Running: Yes
    
15.               Replicate_Do_DB:
    
16.           Replicate_Ignore_DB: mysql,information_schema,performance_schema
    
17.            Replicate_Do_Table:
    
18.        Replicate_Ignore_Table:
    
19.       Replicate_Wild_Do_Table:
    
20.   Replicate_Wild_Ignore_Table:
    
21.                    Last_Errno: 0
    
22.                    Last_Error:
    
23.                  Skip_Counter: 0
    
24.           Exec_Master_Log_Pos: 0
    
25.               Relay_Log_Space: 151
    
26.               Until_Condition: None
    
27.                Until_Log_File:
    
28.                 Until_Log_Pos: 0
    
29.            Master_SSL_Allowed: No
    
30.            Master_SSL_CA_File:
    
31.            Master_SSL_CA_Path:
    
32.               Master_SSL_Cert:
    
33.             Master_SSL_Cipher:
    
34.                Master_SSL_Key:
    
35.         Seconds_Behind_Master: 0
    
36. Master_SSL_Verify_Server_Cert: No
    
37.                 Last_IO_Errno: 1236
    
38.                 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replica'
    
39.                Last_SQL_Errno: 0
    
40.                Last_SQL_Error:
    
41.   Replicate_Ignore_Server_Ids:
    
42.              Master_Server_Id: 107
    
43.                   Master_UUID: 6a2a620f-736c-11e6-8384-000c299dda36
    
44.              Master_Info_File: mysql.slave_master_info
    
45.                     SQL_Delay: 0
    
46.           SQL_Remaining_Delay: NULL
    
47.       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
    
48.            Master_Retry_Count: 86400
    
49.                   Master_Bind:
    
50.       Last_IO_Error_Timestamp: 160916 22:50:59
    
51.      Last_SQL_Error_Timestamp:
    
52.                Master_SSL_Crl:
    
53.            Master_SSL_Crlpath:
    
54.            Retrieved_Gtid_Set:
    
55.             Executed_Gtid_Set: 6a2a620f-736c-11e6-8384-000c299dda36:1-500046,
    
56. 90bced3c-736e-11e6-8392-000c291db4ba:1-12
    
57.                 Auto_Position: 1
    
58. 1 row in set (0.00 sec)
    

复制代码

在从库（192.168.1.111）查看错误日志：

tail -n200 var/log/mysqld.log

1. 2016-09-16 22:35:11 2283 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replica', Error_code: 1236
   
2. 2016-09-16 22:35:11 2283 [Note] Slave I/O thread exiting, read up to log 'FIRST', position 4
   
3. 2016-09-16 22:35:24 2283 [Note] Error reading relay log event: slave SQL thread was killed
   
4. 2016-09-16 22:36:01 2283 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='192.168.1.107', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
   
5. 2016-09-16 22:36:05 2283 [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.
   
6. 2016-09-16 22:36:05 2283 [Note] Slave I/O thread: connected to master 'repl@192.168.1.107:3306',replication started in log 'FIRST' at position 4
   
7. 2016-09-16 22:36:05 2283 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log './mysqld-relay-bin.000001' position: 4
   
8. 2016-09-16 22:36:05 2283 [ERROR] Error reading packet from server: Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replicated to the slave. Suggest to replicate any transactions that master has rolled back from slave to master, and/or commit empty transactions on master to account for transactions that have been (server_errno=1236)
   
9. 2016-09-16 22:36:05 2283 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replica', Error_code: 1236
   
10. 2016-09-16 22:36:05 2283 [Note] Slave I/O thread exiting, read up to log 'FIRST', position 4
    
11. 2016-09-16 22:38:20 2283 [Note] Error reading relay log event: slave SQL thread was killed
    
12. 2016-09-16 22:50:54 2283 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='192.168.1.107', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
    
13. 2016-09-16 22:50:59 2283 [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.
    
14. 2016-09-16 22:50:59 2283 [Note] Slave I/O thread: connected to master 'repl@192.168.1.107:3306',replication started in log 'FIRST' at position 4
    
15. 2016-09-16 22:50:59 2283 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log './mysqld-relay-bin.000001' position: 4
    
16. 2016-09-16 22:50:59 2283 [ERROR] Error reading packet from server: Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replicated to the slave. Suggest to replicate any transactions that master has rolled back from slave to master, and/or commit empty transactions on master to account for transactions that have been (server_errno=1236)
    
17. 2016-09-16 22:50:59 2283 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replica', Error_code: 1236
    
18. 2016-09-16 22:50:59 2283 [Note] Slave I/O thread exiting, read up to log 'FIRST', position 4
    

复制代码

针对以上种种错误提示，百度了一下 没有找到合适解决办法。在slave上重新stop slave并且change master也不行。

哪位高人遇到过这种问题，并且知道如何解决？谢谢！

shenyue_sam

到主库上show master status查一下主库当前的gtid（Executed_Gtid_Set），
然后到从库执行下面命令试试。
RESET MASTER;
set global gtid_purged = 'xxxx';                 -- 这里xxxx是主库的Executed_Gtid_Set
start slave;

意思是从库同步时，丢弃现有主库执行过的gtid。

主库磁盘满了以后是直接rm的binlog吗？可以在主库执行一下flush logs;，然后再按上面的步骤做一次试试。