mysql 1594_mysql replication 之 1594错误

一、简介

在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