Introduction During MySQL failover, binary logs on the old primary and relay logs on replicas can become corrupted due to incomplete writes, disk failures, or abrupt process termination. Corrupted logs prevent replication from resuming and may cause data inconsistency between the new primary and remaining replicas.
Symptoms - `ERROR: Corrupted replication relay log` in MySQL error log - `SHOW REPLICA STATUS\G` shows `Last_SQL_Error: Relay log read failure` - `mysqlbinlog` fails with `ERROR: Error in Log_event::read_log_event(): Sanity check failed` - Replica SQL thread stops with `Could not parse relay log event` - Failover script reports inconsistent GTID positions across replicas
Common Causes - Unclean shutdown of the old primary during failover - Disk I/O error during binlog write - Relay log incomplete when replica was forcefully restarted - `sync_binlog = 0` causing unwritten binlog data to be lost - Network interruption during binlog event transmission
Step-by-Step Fix 1. **Validate binary log integrity": ```bash # Check each binary log file mysqlbinlog /var/lib/mysql/binlog.000050 > /dev/null 2>&1 if [ $? -ne 0 ]; then echo "Corrupted: binlog.000050" fi
# Check the last binlog file specifically mysqlbinlog --verify-binlog-checksum /var/lib/mysql/binlog.000050 ```
- 1.**Check relay log integrity on the replica":
- 2.```sql
- 3.SHOW RELAYLOG EVENTS IN 'relay-bin.000010' LIMIT 10;
-- If corrupted, reset relay logs STOP REPLICA; RESET REPLICA; START REPLICA; ```
- 1.**Re-sync the replica from the new primary":
- 2.```bash
- 3.# On the new primary, get the current GTID position
- 4.mysql -e "SELECT @@global.gtid_executed;"
# On the corrupted replica, rebuild from scratch mysql -e "STOP REPLICA; RESET REPLICA ALL;"
# Restore from a recent backup mysql < /backup/full_backup.sql
# Reconfigure replication mysql -e "CHANGE REPLICATION SOURCE TO SOURCE_HOST='new-primary', SOURCE_AUTO_POSITION=1;" mysql -e "START REPLICA;" ```
- 1.**If only the last binlog is corrupted, skip it":
- 2.```sql
- 3.-- On the replica, find the last good position
- 4.SHOW RELAYLOG EVENTS IN 'relay-bin.000010'
- 5.FROM <last_known_good_position> LIMIT 10;
-- Skip to the next binlog file STOP REPLICA; CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE = 'binlog.000051', SOURCE_LOG_POS = 4; START REPLICA; ```
- 1.**Enable binlog checksums for future corruption detection":
- 2.```sql
- 3.-- Check current setting
- 4.SHOW VARIABLES LIKE 'binlog_checksum';
-- Enable CRC32 checksums SET GLOBAL binlog_checksum = 'CRC32';
-- In my.cnf -- [mysqld] -- binlog_checksum = CRC32 -- sync_binlog = 1 ```