[MySQL] 数据库复制异常恢复之Error_code: 1236

小咪咪 2023-07-23 03:49 13阅读 0赞

环境：两台服务器双机备份，数据库采用主主复制  
说明：以下主机指HA集群的主机  
报错：Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size'  
恢复：将备机数据库复制位置恢复到主机mysql-bin.xxxxxx的最大位置（比报错中的位置小）

假设HA Server为node1和node2，数据库异常的服务器是node2，报错如下：

Last_IO_Errno: 1236
    Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size'

1、查看MySQL错误日志  
查看mysql error日志，首先找一下mysql error 日志的位置  
***mysql>*** show variables like 'log\_error';  
查看mysql error日志，搜索记录的报错  
2017-05-12T01:13:44.574768Z 2 \[ERROR\] Slave I/O for channel '': Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size', Error\_code: 1236  
2017-05-12T01:13:44.574785Z 2 \[Note\] Slave I/O thread exiting for channel '', read up to log 'mysql-bin.000011', position 245338990

2.2 找到需要恢复的位置  
查看node1的mysql-bin.000011，查看方法：使用mysqlbinlog命令  
\# ./mysqlbinlog ~/somedir/mysql-bin.000011 > 1.txt  
\# vi 1.txt  
……  
\# at 245141048  
\#170512  9:07:27 server id 1  end\_log\_pos 245141079 CRC32 0x41910c65    Xid = 149441519  
COMMIT/\*!\*/;  
SET @@SESSION.GTID\_NEXT= 'AUTOMATIC' /\* added by mysqlbinlog \*/ /\*!\*/;  
DELIMITER ;  
\# End of log file  
在文件的最后找到了这个数245141048，是小于错误发生位置245338990的最大整数

3、恢复备机数据库  
在node2的数据库执行如下操作  
mysql> stop slave;  
mysql> change master to master\_log\_file='mysql-bin.000011',master\_log\_pos=245141048;  
mysql> start slave;  
再查看node2数据库状态，恢复正常。