Last_IO_Error: Got fatal error 1236 from master when reading data from binary log
2,124 total views, 1 views today
很久不关注MHA，最近看到已经升级到 MHA 0.58，开始支持MySQL的GTID。
GTID对于MySQL复制而言，已经是一场革命。复制变得更加简单，创建复制从库时无需指定主库的file和position，新引入的 master_auto_position=1 即可自动比对主从库之间的binlog差异，自动进行同步，无疑大大节省了DBA操作成本。
GTID的引入，对于DBA而言增加了学习成本。不过，这是值得的。多测试、多踩几个坑，对后续基于GTID的复制管理会更有把握。
在测试基于GTID的复制failover时，遇到了 “Last_IO_Error: Got fatal error 1236 from master when reading data from binary log”问题。在网上搜寻到了解决办法，但是无法再次复现该问题，很苦恼无法深入研究报错原因。
问题描述
|
1 |
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' |
解决办法
主库查看Executed_Gtid_Set值，并记录下来
|
1 2 3 4 5 6 7 8 9 |
mysql> show master status\G *************************** 1. row *************************** File: bin.000003 Position: 250 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 30e55c1a-7dc8-11e8-9868-44a84249562f:214-424, 85e12430-7dcc-11e8-b909-44a84229def5:1-3:6370852-7318398 1 row in set (0.00 sec) |
从库根据主库的 Executed_Gtid_Set，设置从库的gtid_purged
|
1 2 3 4 |
RESET MASTER; set global gtid_purged = '30e55c1a-7dc8-11e8-9868-44a84249562f:214-424,85e12430-7dcc-11e8-b909-44a84229def5:1-3:6370852-7318398'; --Master Executed_Gtid_Set start slave; show slave status\G |
关于gtid_purged
在MySQL官方文档中，有一段描述了设置 gtid_purged的场景。
|
1 2 3 |
Excluding transactions with gtid_purged. The master's global gtid_purged variable contains the set of all transactions that have been purged from the master's binary log. As with the method discussed previously (see Injecting empty transactions), you can record the value of gtid_executed on the server from which the snapshot was taken (in place of copying the binary logs to the new server). Unlike the previous method, there is no need to commit empty transactions (or to issue PURGE BINARY LOGS); instead, you can set gtid_purged on the slave directly, based on the value of gtid_executed on the server from which the backup or snapshot was taken. As with the method using empty transactions, this method creates a server that is functionally a snapshot, but in time is able to become a master as its binary log history converges with that of the replication master or group. |
使用gtid_purged排除事务。如果采用快照方式创建备库，那么当主库产生的binlog传递到从库并应用时，就会报错。通常，采用插入空事务来解决该问题。
|
1 2 3 4 5 6 |
SET GTID_NEXT='aaa-bbb-ccc-ddd:N'; BEGIN; COMMIT; SET GTID_NEXT='AUTOMATIC'; |
这里使用的方式是，记录主库的gtid_executed值，然后在从库将该值写入gtid_purged。
|
1 |
set global gtid_purged = 'aaa-bbb-ccc-ddd:N''; |
参考：
https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-failover.html #Excluding transactions with gtid_purged
https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-failover.html #Injecting empty transactions
https://aliang.org/MySQL/Last_IO_Error.html