When using MySQL Master-Slave (or Master-Master) replication, there are situations where, for various reasons, the slave's version is lower than the master's version.
If the binlog_format=MIXED option is used, this can result in the master writing a record to its binary log in the following format:
BINLOG 'XMawShMEAAAARwAAAEp5UwUAAGkAAAAAAAAACHNhblahblahblah'
The slave will not be able to execute this record because it simply "does not know how to handle it yet".
The first sign that this has occurred will be an "SLAVE DOWN" alert from the monitoring system and an entry in the slave's MySQL log similar to this:
090917 14:55:51 [ERROR] Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 71, event_type: 19
090917 14:55:51 [ERROR] Error reading relay log event: slave SQL thread aborted because of I/O error
090917 14:55:51 [ERROR] Slave: 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. Error_code: 0
090917 14:55:51 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'bin-us1.000022' position 17007149
Attempts to skip the problematic records using the old reliable method:
slave stop;
set global sql_slave_skip_counter=1;
slave start;
do not help in this case.
As a solution, I suggest the following:
Stop the slave: "SLAVE STOP;"
Extract the binary log name and the position where the error occurred from the error text.
In this case, it is bin-us1.000022 and position 17007149.
Execute the query "FLUSH LOGS;" on the master.
Dump the binary log starting from the found position:
mysqlbinlog --start-position=17007149 bin-us1.000022 > bin-us1-from17007149.sql
Load the resulting dump on the slave using the "--force" flag:
mysql --force -uuser -ppassword database < bin-us1-from17007149.sql
Execute "FLUSH LOGS;" on the slave.
In the data directory, find the relay-log with the highest number and a size of approximately 100 bytes (in my case, this is mysqld-relay-bin.000037).
In the relay-log.info file in the same directory,
replace the first line with the name of the newly found relay-log, write 0 in the second line, the next binary log after the "corrupted" one in the third line, and 0 in the fourth line.
It will look something like this:
/var/lib/mysql/mysqld-relay-bin.000037
0
bin-us1.000023
0
Then, on the slave, execute:
CHANGE MASTER TO MASTER_LOG_FILE='bin-us1.000023', MASTER_LOG_POS=0;
SLAVE START;
And finally, to ensure that everything has started correctly:
SHOW SLAVE STATUS\G