MySQL master-slave replication recovery
MySQL master-slave replication stopped on slave due to an error.
Run on slave DB:
SHOW SLAVE STATUS;
Slave_IO_Running: Yes Slave_SQL_Running: No Last_SQL_Errno: 1594 Last_SQL_Error: Relay log read failure: Could not parse relay log event entry... Relay_Log_File: _RELAY_LOG_FILE_ Relay_Master_Log_File: _RELAY_MASTER_LOG_FILE_ Exec_Master_Log_Pos: _EXEC_MASTER_LOG_POS_
The error may be caused by either a corrupted master binlog file or relay binlog file.
Run on master OS to check whether the master binlog file is corrupted:
mysqlbinlog /var/lib/mysql/_RELAY_MASTER_LOG_FILE_ > /dev/null
If the master binlog file is corrupted, the replication setup has to be done again.
Run on slave OS to check whether the relay binlog file is corrupted:
mysqlbinlog /var/lib/mysql/_RELAY_LOG_FILE_ > /dev/null
ERROR: Error in Log_event::read_log_event(): 'Event too small', data_len: 0, event_type: 0 ERROR: Could not read entry at offset 12345678: Error in log format or read error.
If the relay binlog file is corrupted, we can reset the relay binlog files.
Run on slave DB to reset the relay binlog files:
STOP SLAVE; RESET SLAVE; START SLAVE;
The slave will start replication from the next GTID.
Instead, if the error is not caused by a corrupted relay binlog file but an offending transaction, we can skip that transaction. (Notice offending transactions often indicate inconsistency between the master and slave's data. Skipping them might not be sufficient to recover from the inconsistency.)
Run on master OS:
mysqlbinlog --start-position=_EXEC_MASTER_LOG_POS_ _RELAY_MASTER_LOG_FILE_ | less
In the result, find the
GTID_NEXT of the transaction to be skipped:
SET @@SESSION.GTID_NEXT= '_GTID_NEXT_'
Run on slave DB to skip the transaction:
SET autocommit = 0; SET GTID_NEXT= '_GTID_NEXT_'; BEGIN; COMMIT; SET GTID_NEXT='AUTOMATIC'; START SLAVE;
The slave will start replication after the skipped GTID.