【MySQL】Last_SQL_Errno: 1594Relay log read failure: Could not parse relay log event entry...问题总结处理

最新推荐文章于 2024-10-16 11:46:36 发布

原创最新推荐文章于 2024-10-16 11:46:36 发布 · 925 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

mysql相关专栏收录该内容

17 篇文章

订阅专栏

本文详细描述了如何处理MySQL中Relaylogreadfailure错误，包括检查并修复master和slave的binlog，以及在主从复制中确保数据完整性的关键步骤。通过实例演示了如何定位问题、调整参数和验证从库更新，强调了在主从复制前获取完整binlog的重要性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

备库报错：

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.

遇到这个问题

处理方法就是重新修下下binlog和pos值，从一个新的值重新备份，但有一个问题需要注意，这样的备库数据是最新的，但不是最全的，从pos之前的数据就没有了。

步骤如下：

1.关闭slave（备库操作）

(root@localhost:)[(none)]> stop slave;

Query OK, 0 rows affected (0.04 sec)

2.查看master的binlog和pos值(在主服操作)

(root@localhost:)[(none)]> show master status ;

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.045602 | 63365507 | | | |

+------------------+----------+--------------+------------------+-------------------+

3.修改slave的pos和binlog值

(root@localhost:)[(none)]> change master to master_log_file='mysql-bin.045602 ',master_log_pos= 63365507;

Query OK, 0 rows affected, 33837 warnings (9.37 sec)

4.启动slave

(root@localhost:)[(none)]> start slave;

Query OK, 0 rows affected (0.04 sec)

5.查看状态(备库)

(root@localhost:)[(none)]> show slave status \G;

两个yes，而且没有error 证明成功

下面验证下备库是否为最新的

现在搭建好两个mysql数据库

版本为：/usr/local/mysql3307/bin/mysql Ver 14.14 Distrib 5.7.31, for linux-glibc2.12 (x86_64) using EditLine wrapper

暂时先不开启主从，在主库中添加一个表，并创建一些测试数据

主库：

mysql> use test;
Database changed

mysql> show create table t1\G;;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified

ERROR: 
No query specified

mysql> insert into t1(id,name) values(1,"zhang");
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1(id,name) values(2,"zhan");
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1(id,name) values(3,"zha");
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1(id,name) values(4,"1111");
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | zhang |
|    2 | zhan  |
|    3 | zha   |
|    4 | 1111  |
+------+-------+
4 rows in set (0.01 sec)

从库配置好，并且是创建完表之后的数据

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| binarylog.000003 |  2645011 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从库配置完my.cnf重启后
执行
mysql> change master to master_host='192.168.25.11',master_user='test',master_port=3306,master_password='test123',master_log_file='binarylog.000003',master_log_pos=2645011;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

查看状态，确认没问题
mysql> show slave status\G;

然后在主库的t1表中，继续添加数据
mysql> insert into t1(id,name) values(5,"test5");
Query OK, 1 row affected (0.00 sec)

切换到从库中，查看是否自动备份
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.25.11
                  Master_User: test
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binarylog.000003
          Read_Master_Log_Pos: 2647526
               Relay_Log_File: mysql-slave-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: binarylog.000003
             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: 1146
                   Last_Error: Error executing row event: 'Table 'test.t1' doesn't exist'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2647264
              Relay_Log_Space: 795
              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: 1146
               Last_SQL_Error: Error executing row event: 'Table 'test.t1' doesn't exist'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: cd262711-c528-11ea-9399-000c29a68d4e
             Master_Info_File: /data/3307/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: 200713 20:07:58
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

提示数据库不存在，那就手动创建数据库test

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

重启slave后，依然报错，再手动创建t1表
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql>  CREATE TABLE `t1` (
    ->   `id` int(11) DEFAULT NULL,
    ->   `name` varchar(10) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.03 sec)

再次重启slave

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;  
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.25.11
                  Master_User: test
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binarylog.000003
          Read_Master_Log_Pos: 2647526
               Relay_Log_File: mysql-slave-relay-bin.000004
                Relay_Log_Pos: 320
        Relay_Master_Log_File: binarylog.000003
             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: 2647526
              Relay_Log_Space: 699
              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: 0
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: 2
                  Master_UUID: cd262711-c528-11ea-9399-000c29a68d4e
             Master_Info_File: /data/3307/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

一切正常，查看备库表数据
mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    5 | test5 |
+------+-------+
1 row in set (0.01 sec)

表中数据只有id=5的

在主库再次插入一条试试

mysql> insert into t1(id,name) values(6,"test6"); 
Query OK, 1 row affected (0.01 sec)

从库查看；
mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    5 | test5 |
|    6 | test6 |
+------+-------+
2 rows in set (0.00 sec)

汇总：

做主从复制的时候，一定要获取到之前的binlog和pos才可以，或者导入全库后再开始进行主从复制