Slave I/O: Got fatal error 1236

时间:2023-03-08 18:05:20
Slave I/O: Got fatal error 1236

【起因】

一次zabbix报警,从库[Warning] MySQL-repl was down  # 不知道主库/storage空间小于20%时为什么没有触发trigger

【从库错误日志】

161103 11:48:56 [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 first event 'v-mysql1-bin.002026' at 362014603, the last event read from './v-mysql1-bin.002026' at 362014762, the last byte read from './v-mysql1-bin.002026' at 362014781.', Error_code: 1236
161103 11:48:56 [Note] Slave I/O thread exiting, read up to log 'v-mysql1-bin.002026', position 362014762

【原因】

与主库sync_binlog配置有关,当主库 sync_binlog<>1,MySQL每写N次binary log,系统才会同步到磁盘,但是由于存储日志磁盘空间满而导致MySQL 没有将日志完全写入磁盘,binlog event被截断。

slave 读取该binlog file时就会报错"binlog truncated in the middle of event;

sync_binlog=n,当每进行n次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

sync_binlog=0(默认),当事务提交之后,MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘,而让Filesystem自行决定什么时候来做同步,或者cache满了之后才同步到磁盘。

【解决】

从库重新指向主库下一个可用的binary log并从起始位置开始

stop slave
CHANGE MASTER TO MASTER_LOG_FILE='v-mysql1-bin.002026', MASTER_LOG_POS=4;
start slave

参考:

https://www.percona.com/blog/2014/10/08/mysql-replication-got-fatal-error-1236-causes-and-cures/