“Change master” to the rescue or how to revive MySQL replication

Sometime ago had an issue with MySQL replication that consecutively faulted with two different errors. Initially with this one:

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.

Thankfully, this error was easy to fix with the help of change master SQL statement:

change master to master_log_file='mysql-bin.000047', master_log_pos=152667618;

However, the second error kicked in immediately right after that:

Got fatal error 1236 from master when reading data from binary log: ‘Client requested master to start replication from impossible position; the first event ‘mysql-bin.000048’ at 223481321, the last event read from ‘/var/log/mysql/mysql-bin.000048’ at 4, the last byte read from ‘/var/log/mysql/mysql-bin.000048′ at 4.’

This one was also a no-brainer if you know how to fix them

Armed with mysqlbinlog it was easy to verify that there were no logs past 223481321 position:

mysqlbinlog --base64-output=decode-rows --verbose --start-position=223481321 ./mysql-bin.000048 
/*!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 /*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Thus the proper solution in that case was to manually point the slave to the next available bin log:

change master to master_log_file='mysql-bin.000049', master_log_pos=4;