一、简介

在mysql的replication中，有时候会遇到因为master的断电、网络等原因，导致binlog读取有问题，在生成reply log的时候，reply log有问题。报出1594错误。

二、现象

在出现1594错误是，我们通过' show slave status\G’命令，可以发现以下的报错：

mysql>show slave status\G

Master_Log_File: mysql_binlog.004814

Read_Master_Log_Pos: 14241847
Relay_Log_File: mysql_relay.018501
Relay_Log_Pos: 236752629
  Relay_Master_Log_File: mysql_binlog.004734
Slave_IO_Running: Yes
Slave_SQL_Running: No
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: 236752486
Relay_Log_Space: 21598620582
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.

通过上面的信息，我们可以知道，

1.Master_Log_File: mysql_binlog.004814 当前的slave已经读取了master的binlog文件为： mysql_binlog.004814

2.Read_Master_Log_Pos: 14241847 当前的slave读取的master binlog---mysql_binlog.004814的位置是14241847

3.Slave_SQL_Running: No 当前slave的sql线程停止，表示，master的变化，可以读取到slave，但是slave不能应用这些变换

4.Last_Errno: 1594 当前slave发生了1594错误

5.Last_Error 1594错误的具体信息在laster_error中显示

6.Relay_Master_Log_File: mysql_binlog.004734 当前slave应用的最新的master的binlog是：mysql_binlog.004734。这就意味着，如果我们想重新启用slave，同步的起点应该是从mysql_binlog.004734这个文件开始

7.Exec_Master_Log_Pos: 236752486 当前slave应用的最新的binlog的位置是：236752486 。这就意味着，同步的起点文件的位置是：236752486

三、解决办法

1. 重新创建slave。

使用mysqldump命令，重新备份数据库。然后在slave中导入，然后使用change master to命令重建slave
注：在备份时，加入参数--single-transaction --master-data=2，减少对线上业务的影响，把当前备份的binglog的信息记录到备份中。

2. 直接使用change master to 命令，根据Relay_Master_Log_File、Exec_Master_Log_Pos的值，重建slave。命令如下：

change master to master_host='192.168.11.24',master_user='replication',master_password='1234545',master_port=3306,master_log_file='mysql_binlog.004734',master_log_pos=236752486;

start slave;

show slave status\G

ITPub博客

mysql replication 之 1594错误

最新文章