MySQL Replication :: Recovering from corrupted relay logs

Recently, due to a unknown problem, mysql replication in our server was stopped. Checking the log, we found the following error

130225 21:17:32 [ERROR] Error in Log_event::read_log_event(): ‘read error’, data_len: 261, event_type: 2
130225 21:17:32 [ERROR] Error reading relay log event: slave SQL thread aborted because of I/O error
130225 21:17:32 [ERROR] Slave SQL: 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. Error_code: 1594
130225 21:17:32 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with “SLAVE START”. We stopped at log ‘mysql-bin.000025’ position 712572657

Belows are steps that we fixed the problem:

1. stop slave;
   
2. show slave statusG;
   • and write down the values of Relay_Master_Log_File and Exec_Master_Log_Pos. In our case its mysql-bin.000025 and 712572657, respectively.
3. reset slave;
   • this makes the slave “forget” its replication position in the master’s binary log.
4. Run

   CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000025',MASTER_LOG_POS=712572657,
   MASTER_HOST='XXX.XX.XX.XX',
   MASTER_USER='XXXXXXXXXXXX',
   MASTER_PASSWORD='XXXXXXXX';

5. start slave;
6. We now can delete previously created relay logs as they are not needed anymore

That’s all, and keep monitoring for any other possible problems on the replication process 🙂