2

My Slave machine stopped somehow, and when I ran START SLAVE to start replication, I got following error,

    SLAVE 1 !!! > show slave status \G
    *************************** 1. row ***************************
                 Slave_IO_State: Waiting for master to send event
                    Master_Host: < My Master's IP >
                    Master_User: repl
                    Master_Port: 3306
                  Connect_Retry: 60
                Master_Log_File: master-bin.000777
            Read_Master_Log_Pos: 358597143
                 Relay_Log_File: slave-relay.000634
                  Relay_Log_Pos: 95144877
          Relay_Master_Log_File: master-bin.000777
               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: mysql.%,information_schema.%, tmp1.%,performance_schema.%,mysql.%,information_schema.%, tmp1.%,performance_schema.%
                     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: 0
            Exec_Master_Log_Pos: 95264000
                Relay_Log_Space: 358481990
                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)

I followed this post,

https://stackoverflow.com/questions/12097696/mysql-replication-fails-with-error-could-not-parse-relay-log-event-entry

But my doubts are not clear and I don't want my slave to be crashed, So, could anyone please guide me to solve it ?

In My case : should I try

CHANGE MASTER TO master_log_file='master-bin.000777', master_log_pos=358597143;  ?

Note : My Read_Master_Log_Pos and Exec_Master_Log_Pos are not same

5
  • 1
    If you do CHANGE MASTER TO do not use the Read_Master_Log_Pos value. Use Exec_Master_Log_Pos value (95264000) or you'll break replication for sure. Jan 11, 2016 at 13:56
  • Use stop slave;SQL_SLAVE_SKIP_COUNTER=1;start slave to see if it works. It seems position at given log file have corrupted event. Let us know if it helps, it it does not help you may need to use the very next event in the same file in change master to start replication from that event then. Jan 11, 2016 at 15:19
  • @Michael-sqlbot : That was the point I was confused, Thank you for clearing it ! Please share any article, if you have, to understand it .........
    – Hytool
    Jan 12, 2016 at 3:45
  • @Nawaz...... isn't SQL_SLAVE_SKIP_COUNTER used when you are stuck with any sql statement or query ? which is not there in my case ! I used SQL_SLAVE_SKIP_COUNTER earlier when My slave was stuck with < OPTIMIZE TABLE > query ! this statement shoudnt be used for any DML or DDL statements I think !
    – Hytool
    Jan 12, 2016 at 3:48
  • 1
    The advice from @NawazSohail is not appropriate for the observed conditions, and should not be followed here. You can't blissfully skip over an event of unknown content without consequences, which may not become evident until later. Jan 12, 2016 at 8:31

1 Answer 1

2

There are 4 possible explanations mentioned in the error message, and a fifth one that isn't mentioned.

  • 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.

  • not mentioned in the message, it seems possible that this condition could be triggered if your slave is an older version of MySQL server than the master, particularly across versions, such as a master running 5.6.x and a slave running 5.5.x. Slaves must almost always be the same version as the master, or newer. When upgrading, slaves are done first, master last.

Replication involves the slave requesting a streaming copy of the master's binary log across the network, which is stored in the relay log by the slave IO thread, then executed by the slave SQL thread.

In this case, the slave SQL thread has found incomprehensible content in the relay log, and has concluded that the log was corrupt on the master, or has been corrupted on the slave, or was corrupted in transit. TCP is "reliable" but its checksums are somewhat primitive and specific corruption patterns can go undetected.

Using CHANGE MASTER TO will discard the slave's relay logs and restart copying at the specified log position, which needs to match Execute_master_log_pos, which is where the slave SQL thread is currently pointing for its next needed event (and isn't valid).

If the log on the master is corrupt, this will not fix things, because the corrupt data will just be transferred again. In this case, the only option will be to rebuild the slave.

Otherwise, CHANGE_MASTER_TO should remedy the problem if the fault lies in the slave'a relay log (which can be caused by having run out of disk space on the slave, under just the right conditions) or was caused by the network.

The mysqlbinlog utility can be used to validate the logs on both machines, if desired, and will be necessary on the master if you want to confirm the corruption when CHANGE_MASTER_TO doesn't fix the issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.