Friday, February 19, 2016

binary log corruption - MySQL

https://www.percona.com/blog/2013/02/15/replication-checksums-in-mysql-5-6/
https://alexzeng.wordpress.com/2013/10/17/how-to-fix-mysql-slave-after-relay-log-corrupted/

I have had innodb come back up from a crash with errors like...

Last_Errno: 1594
Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

and this has been a fix for me....

mysql> show slave status\G

mysql> stop slave;

-- Use Relay_Master_Log_File and Exec_Master_Log_Pos from SHOW SLAVE STATUS
mysql> change master to master_log_file='BINLOG-NAME.000###', master_log_pos=210####16;

mysql> start slave;

mysql> show slave status\G

and we're off.....

 Last_SQL_Errno: 0
 Last_SQL_Error:



-----

Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

the master no longer has the binary log
if the current long file is close to what is on the server….

Relay_Master_Log_File: binarylogs.000749

is close too….

[root@mysql2 ~]# ls -alh /var/lib/mysql/binarylogs.0007*
-rw-rw---- 1 mysql mysql 365M Jul 12 16:54 /var/lib/mysql/binarylogs.000750

then we can try to just move to the next file...

stop slave
CHANGE MASTER TO MASTER_LOG_FILE='binarylogs.000750', MASTER_LOG_POS=4;
start slave;


This SEEMS to have worked in my case.