【MySQL】Last_SQL_Errno: 1594Relay log read failure: Could not parse relay log event entry...问题总结处理
备库报错：
**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 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才可以，或者导入全库后再开始进行主从复制
手机扫一扫
移动阅读更方便
你可能感兴趣的文章