本文共 8934 字，大约阅读时间需要 29 分钟。

备库报错：

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 ;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| 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 changedmysql> show create table t1\G;;*************************** 1. row ***************************       Table: t1Create Table: CREATE TABLE `t1` (  `id` int(11) DEFAULT NULL,  `name` varchar(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)ERROR: No query specifiedERROR: No query specifiedmysql> 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: NULLMaster_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提示数据库不存在，那就手动创建数据库testmysql> 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 changedmysql> 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)再次重启slavemysql> 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: 0Master_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才可以，或者导入全库后再开始进行主从复制

转载地址：https://blog.csdn.net/imliuqun123/article/details/110387320 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！