MySQL5.1 Master slave synchronization occurs with Relay log read failure error resolution

  • 2020-06-23 02:08:02
  • OfStack

It is well known that MySQL5.1's Replication is bad. Each release of MySQL updates on the synchronization aspect sees a whole heap at a time. But the MySQL 5.1 performance was outstanding. So it's tempting to use MySQL 5.1. So always come across 1 Bug. Such as:


mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.118
                  Master_User: repl_wu
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.005121
          Read_Master_Log_Pos: 64337286
               Relay_Log_File: relay-bin.003995
                Relay_Log_Pos: 18446697137031827760
        Relay_Master_Log_File: mysql-bin.005121
             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: 4
              Relay_Log_Space: 64337901
              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: 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.
1 row in set (0.00 sec)

You can see from above that either the relay day or the Master day is out of order.

First of all, if the relay day is broken, you just need to find the synchronization point, and then resynchronize, so that you can have a new relay day. If the day value on Master is broken, you're in trouble.

Empirically, this is a relay day problem. Treatment method:

You need to find the synchronization point.

The daily value is: Master_Log_File: ES24en-ES25en.005121, Relay_Master_Log_File: ES30en_bin.005121, and Master_ES33en_ES34en_ES35en for reference.

Execution time point of daily value:

Exec_Master_Log_Pos: 4

Now you can:


mysql>stop slave;
 
mysql>change master to Master_Log_File='mysql-bin.005121', Master_Log_Pos=4;
  
mysql>start slave;
 
mysql>show slave status\G;

Confirm.

Advice:

Please upgrade to MySQL-5.1.40 if you are using es53EN-5.1.36 or below & MySQL-5.1.37sp1


Related articles: