[MySQL] MySQL5.7主从同步故障ERROR 1594解决

雨点打透心脏的1/2处 2021-12-14 05:13 329阅读 0赞

## 问题： ##

MySQL5.7互为主从架构，其中一台因为断电异常，开机之后报错ERROR：1594：

mysql> show slave status \G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.1.3
                      Master_User: replication
                      Master_Port: 3306
                    Connect_Retry: 30
                  Master_Log_File: mysql-bin.005570
              Read_Master_Log_Pos: 36296540
                   Relay_Log_File: mysql-relay-bin.003663
                    Relay_Log_Pos: 95239931
            Relay_Master_Log_File: mysql-bin.005565
                 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: 95239768
                  Relay_Log_Space: 560728878
                  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.
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 1
                      Master_UUID: f037952e-b089-11e6-b7b6-005056b62dc0
                 Master_Info_File: /data/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: 
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 190708 09:37:53
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
    1 row in set (0.00 sec)
    
    mysql>

# #

## 解决： ##

1. 在故障的服务器上停止slave服务并且reset，在故障的位置重新配置主从同步（其中MASTER\_LOG\_FILE=为Relay\_Master\_Log\_File，MASTER\_LOG\_POS为Exec\_Master\_Log\_Pos）

mysql> stop slave;
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> reset slave all;
    Query OK, 0 rows affected (0.20 sec)
    
    mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.3',MASTER_USER='replication',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.005565',MASTER_LOG_POS=95239768;
    Query OK, 0 rows affected, 2 warnings (0.30 sec)

2. 启动slave验证，主从同步状态恢复

mysql> start slave;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> show slave status \G
    *************************** 1. row ***************************
                   Slave_IO_State: Queueing master event to the relay log
                      Master_Host: 192.168.1.3
                      Master_User: replication
                      Master_Port: 3306
                    Connect_Retry: 30
                  Master_Log_File: mysql-bin.005568
              Read_Master_Log_Pos: 44263196
                   Relay_Log_File: mysql-relay-bin.000002
                    Relay_Log_Pos: 651609
            Relay_Master_Log_File: mysql-bin.005565
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 95891094
                  Relay_Log_Space: 263736075
                  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: 113927
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 1
                      Master_UUID: f037952e-b089-11e6-b7b6-005056b62dc0
                 Master_Info_File: /data/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Reading event from the relay log
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
    1 row in set (0.10 sec)
    
    mysql>

主从同步恢复。

3. checksum校验

在两台主库上分别检查同一张表的checksum值，看是否一致,不一致需要[校验修复][Link 1]。

参考：[https://blog.51cto.com/suifu/1836551][Link 1]

[Link 1]: https://blog.51cto.com/suifu/1836551