mysql数据库binlog日志截断报错，导致mysql主从同步失败故障排查
Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master
报错信息收集：
1.登陆mysql查询slave 状态；
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.16.x
Master_User: rsync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.002333
Read_Master_Log_Pos: 2828282975
> Relay_Log_Pos: 142159071
Relay_Master_Log_File: mysql-bin.002333
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: 142158925
Relay_Log_Space: 2828283637
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the first event 'mysql-bin.001841' at 826366537, the last event read from './mysql-bin.002333' at 2828282975, the last byte read from './mysql-bin.002333' at 2828283169.'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
1 row in set (0.00 sec)
2.mysql error日志报错！
more /data/3306/mylog/mysqlderror.log
140123 2:05:14 [ERROR] Error reading packet from server: binlog truncated in the middle of event; consider out of disk space on master; the first event 'mysql-bin.001841' at 826366537, the
last event read from './mysql-bin.002333' at 2828282975, the last byte read from './mysql-bin.002333' at 2828283169. ( server_errno=1236)
140123 2:05:14 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the fi
rst event 'mysql-bin.001841' at 826366537, the last event read from './mysql-bin.002333' at 2828282975, the last byte read from './mysql-bin.002333' at 2828283169.', Error_code: 1236
140123 2:05:14 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.002333', position 2828282975
从以上报错信息来看是mysql binlog被截断
排查
1查看主节点binlog
/mysql/bin/mysqlbinlog /data/3306/data/mysql-bin.002333 --start-position=2828266574 --stop-position=2828282975
[root@cn-mysql03 ~]# /mysql/bin/mysqlbinlog /data/3306/data/mysql-bin.002333 --start-position=2828266574 --stop-position=2828282975
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#140123 1:46:33 server> BINLOG '
+QPgUg8DAAAAZwAAAGsAAAAAAAQANS41LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 2828266574
# at 2828267161
# at 2828279625
# at 2828280668
# at 2828281262
# at 2828281779
# at 2828282383
ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 607, event_type: 23
ERROR: Could not read entry at offset 2828282975: Error in log format or read error. #报错binlog位置2828282975，是mysql-bin.002333最后一个position位置
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
查看mysql官网的说明,当binlog最后遇到一个大事务时会将日志记录在这个日志,而不会跨binlog记录一个事务,于是我继续查看看mysql-bin.002334的日志头内容；
[root@cn-mysql03 ~]# /mysql/bin/mysqlbinlog /data/3306/data/mysql-bin.002334 --start-position=107|more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#140123 2:04:59 server> use `ptmind_56fbce4e`/*!*/;
SET TIMESTAMP=1390413899/*!*/;
SET @@session.pseudo_thread_id=5575071/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
DROP TABLE IF EXISTS `sum_page_visits_stats_olap_m_tmp` /* generated by server */
/*!*/;
这个日志的开头也没有说明是继续上一个binlog的最后一个事务.
确认以上问题后，重新从下一个日志开始启动主从同步；
重新change master指定新binlog恢复主从同步
mysql> change master to master_host='192.168.16.x', master_port=3306, master_user='xxx', master_password='xxxxx', master_log_file='mysql-bin.002334', master_log_pos=0;
mysql> slave start;
mysql> show slave status\G
mysql> show slave status\G
*************************** 1. row ***************************
QQ群⑧：
窥视卡
雷达卡
发表于 2018-9-30 08:27:39
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡