Monday 15 October 2018

Fix MySQL slave relay log crash

One of my MySQL server replication stopped cos of this server auto reboot,here the details from SHOW SLAVE STATUS\G;

              Slave_IO_State: Queueing master event to the relay log
                  Master_Host: xx.xx.xx.xxx
                  Master_User: repl
                  Master_Port:  3306
                Connect_Retry: 10
            Master_Log_File: logbin.014750
   Read_Master_Log_Pos: 582392000
             Relay_Log_File: relay-bin.000323
               Relay_Log_Pos: 582391127
  Relay_Master_Log_File: logbin.014750
           Slave_IO_Running: Yes
        Slave_SQL_Running: No
            Replicate_Do_DB:
      Replicate_Ignore_DB:
         Replicate_Do_Table:
   Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
                      Last_Errno: 1594
                        Last_Error: 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.
                 Skip_Counter: 0
  Exec_Master_Log_Pos: 582390967
          Relay_Log_Space: 582392371
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1594
                Last_IO_Error: [the same error description as in Last_Error]
                ......

To fix this error, we need setting the new position for slave start to sync, the very important we need to find the Relay_Master_Log_File and Exec_Master_Log_Pos  values:

Relay_Master_Log_File: logbin.014750
Exec_Master_Log_Pos: 582390967

With the 2 values we can reset the position:
mysql> stop slave;
mysql> reset slave;
mysql> change master to master_log_file='logbin.014750', master_log_pos=582390967;
mysql> start slave;

Show slave status\G again, it works.