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

最新推荐文章于 2023-06-06 16:22:00 发布

cindysz110

最新推荐文章于 2023-06-06 16:22:00 发布

阅读量945

点赞数 1

分类专栏： MySQL 文章标签： MySQL1594 MySQL

本文链接：https://blog.csdn.net/wawa8899/article/details/95043524

版权

MySQL 专栏收录该内容

23 篇文章 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值，看是否一致,不一致需要校验修复。

mysql> checksum table aaa.user_role;
+--------------+------------+
| Table        | Checksum   |
+--------------+------------+
| aaa.user_role | 3496236889 |
+--------------+------------+
1 row in set (0.00 sec)

mysql>

参考：https://blog.51cto.com/suifu/1836551