Fix Corrupt relay logs

Master_Log_File: The name of the master binary log file from which the I/O thread is currently reading.
Relay_Log_File: The name of the relay log file from which the SQL thread is currently reading and executing.

Relay_Master_Log_File: The name of the master binary log file containing the most recent event executed by the SQL thread.


-- The status below is most often cause by network connection between slave and master being dropped while slave while slave is reading master's binary log to create the relay log.
-- The "Last_error:" message states that it could be master binary or slave relay log corruption. It is usually slave relay corruption because it is much easier to corrupt this log.



mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 157.229.16.12
Master_User: replicate
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000204
Read_Master_Log_Pos: 77410525
Relay_Log_File: isar.000292
Relay_Log_Pos: 102247
Relay_Master_Log_File: mysql-bin.000204
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: events
Replicate_Ignore_DB: epictide,quartz
Replicate_Do_Table:
Replicate_Ignore_Table: events.EMPI,events.DBMOTION,events.USERS,events.HPF,events.STENTOR,events.UPMC_EMPLOYEE,events.MEDIPAC,events.EPOT,events.UPMC_PATIENT,events.MARS,events.PSYCH_CONSULT,events.MISYS_PATLAB_SUMPLN_TEMP,events.EPIC_UPMC,events.MISYSPATLAB,events.WEEKLY_EXCEPTION_REPORT_HISTORY,events.EVENTS,events.UPMC_CERNER_AUDIT,events.DBMOTION_USER_LOOKUP,events.WEEKLY_ALLPATIENTS_REPORT_HISTORY,events.USERDELEGATES,events.UPMC_VIP
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error: 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.
Skip_Counter: 1
Exec_Master_Log_Pos: 339092
Relay_Log_Space: 77175318
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
1 row in set (0.00 sec)



-- make a note of the "Relay_Master_Log_File:" and "Exec_Master_Log_Pos:". These will be used as the values of the master_log_file and master_log_position in the change msater statement below.

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

-- delete all relay logs on slave. Most likey one of these is corrupt.
mysql> reset slave;
Query OK, 0 rows affected (0.19 sec)

-- force slave to re-create relay logs from binary logs on master.
mysql> change master to master_host='157.229.16.12', master_user='replicate', master_password='XXXXXXXXX', master_log_file='mysql-bin.000204', master_log_pos=339092;


-- restart slave
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

-- look at Seconds_Behind_Master: this should not be null. This number should trend downwards, but it may increase.
-- It is not 100% reliable and may go from 15,000 to 0 back to 15,000 if network connectivity is flakey.
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 157.229.16.12
Master_User: replicate
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000204
Read_Master_Log_Pos: 61500513
Relay_Log_File: isar.000002
Relay_Log_Pos: 5846212
Relay_Master_Log_File: mysql-bin.000204
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: events
Replicate_Ignore_DB: epictide,quartz
Replicate_Do_Table:
Replicate_Ignore_Table: events.EMPI,events.DBMOTION,events.USERS,events.HPF,events.STENTOR,events.UPMC_EMPLOYEE,events.MEDIPAC,events.EPOT,events.UPMC_PATIENT,events.MARS,events.PSYCH_CONSULT,events.MISYS_PATLAB_SUMPLN_TEMP,events.EPIC_UPMC,events.MISYSPATLAB,events.WEEKLY_EXCEPTION_REPORT_HISTORY,events.EVENTS,events.UPMC_CERNER_AUDIT,events.DBMOTION_USER_LOOKUP,events.WEEKLY_ALLPATIENTS_REPORT_HISTORY,events.USERDELEGATES,events.UPMC_VIP
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 6185069
Relay_Log_Space: 61161656
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 17641
1 row in set (0.00 sec)


-- if this does not work you may have master binary log corruption.