Fixing MySQL replication after slaves’s relay log was corrupted

MySQL replication on slave (version 5.1.61) has stopped. Slave_IO_Running was marked as Yes, but Slave_SQL_Running as No. Simple stop/start slave didn’t help so further problem analysis was needed. It seemed that current slave’s relay log was corrupted because testing with “mysqlbinlog” has printed out an error. Therefore, the solution was to discard current relay binlogs and to point slave to the last master binlog position.

Here is complete output from show slave status\G on stopped slave server:

               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.1.79.48
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.002046
          Read_Master_Log_Pos: 639600842
               Relay_Log_File: triton-relay-bin.001957
                Relay_Log_Pos: 243
        Relay_Master_Log_File: mysql-bin.002045
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: pretinac_radio,web
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: web.logging_www,web.logging_raspored,web.web_korisnik
      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: 103641119
              Relay_Log_Space: 983411603
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1594
               Last_SQL_Error: [the same error description as in Last_Error]

To fix the error, current binlog files on slave should be discarded and set new position. Before setting new binlog position it’s important to remember Relay_Master_Log_File and Exec_Master_Log_Pos values:

Relay_Master_Log_File: mysql-bin.002045
Exec_Master_Log_Pos: 103641119

OK, with this values, new binlog position can be set:

# stop slave
mysql> stop slave;

# make slave forget its replication position in the master's binary log
mysql> reset slave;

# change slave to start reading from stopped position
mysql> change master to master_log_file='mysql-bin.002045', master_log_pos=103641119;

# start slave
mysql> start slave;

Just to note that “reset slave” will delete master.info, relay-log.info and all the relay log files, so it’s not needed to clean leftovers in /var/lib/mysql directory. After all commands were executed, slave has reconnected to the master and start to read SQL statements (Seconds_Behind_Master value was not NULL any more). Complete “reset slave” documentation can be read on MySQL 5.1 – RESET SLAVE Syntax page.

32 thoughts on “Fixing MySQL replication after slaves’s relay log was corrupted”

  2. @Marco – I’m glad it was helpful. Believe me, not once I was reading my own post to fix MySQL replication. It’s funny how info like this can be forgotten soon. Anyway, thanks for feedback.
    Cheers!

  3. Thanks for having such a straightforward page dedicated to the solution. We had a slave server go down and your instructions here made short work of something I was afraid was going to be much more difficult.

  5. This is a nice and succinct instruction page for dealing with this issue. One thing to note, if you don’t have your replication username/password in your configuration, reset slave will also forget that information, and you’ll have to include it again when issuing CHANGE MASTER to reset the log position.

    This trick has worked for me in the past, but when I tried it today replication fails again right away again with the same error. The slave I/O began running, but the Slave SQL is stopped again with an error. When I run mysqlbinlog against the new relay log where it stopped, it shows <pre>”ERROR: Error in Log_event::read_log_event(): ‘Sanity check failed’, data_len: 7299955, event_type: 97″</pre> There doesn’t seem to be an issue with the binary log it is reading. This server is running version 5.1.51 community.

  6. Doh! It helps if you don’t make a typo when specifying your log position – apparently the cause of the ‘sanity check failed’ message. Fortunately it choked at the first transaction, so nothing was processed inadvertently out of sequence. I did a reset slave again and reset the log position back to the start of the transaction and all is well!

  9. My slave has very high seconds behind master.

    So I thought of dump master data & dump into slave, but in this process I had taken a note of the current bin log position in Mysql Master.

    But didnt stopped slave service..Is it good idea to proceed with the dump or do I need to stop the slave service before talking dump?

  14. I have a particular problem, during replication, i lost some records in some of my tables, and then replication continues like normal.

    So, my question is, if i have identified those records, what is the flow i have to follow to leave them from master to slave?

    Thanks in advance

  17. Hi,
    I am facing same issue but i afraid if this can be resolved:

    Master_Log_File: mysql-bin.000205
          Read_Master_Log_Pos: 676689168
               Relay_Log_File: relay-bin.000208
                Relay_Log_Pos: 908725578
        Relay_Master_Log_File: mysql-bin.000073
             Slave_IO_Running: Yes
            Slave_SQL_Running: No

    Master_Log_File: mysql-bin.000205 is way far than where it has stopped Relay_Master_Log_File: mysql-bin.000073. I do not see mysql-bin.000073 anywhere as it might have got purged. So, please suggest me a way out to resolve this.

    Nikhil

  18. @Nikhil – If “mysql-bin” file is deleted, then it’s not possible to catch “the sync” with this trick. One solution can be export data from master and import to the slave server. On the other hand, if you have more than one slave server please see my post How to add a new MySQL slave.

    Hope this will help you to fix the problem.

  20. Your post from 2013 just saved my database like a charm :)
    A big thank you!

Leave a Comment