mysql 主从同步不一致_记录一次MySQL主从不同步解决方法

今同事反映数据有问题，查看了一下MySQL主从不同步，

mysql> show slave status G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.101

Master_User: db_user

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000007

Read_Master_Log_Pos: 22404

Relay_Log_File: relay-bin.000111

Relay_Log_Pos: 23083

Relay_Master_Log_File: mysql-bin.000062

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB: test

...

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: 230837

Relay_Log_Space: 45632

...

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.确认主库的bin-log没有问题，

mysqlbinlog mysql-bin.000062

查看输出的内容有没有错误信息，如果出错，说明主库的bin-log有问题，就只能重置主库，重新同步了；

我的数据库的架构是一主多从，只有这一台slave有问题，说明主库的bin-log没有问题；

2.检测从库

根据错误提示，应该是从库的Relay log出了问题，同样的方式，检测relay-bin

mysqlbinlog mysqld-relay-bin.000056

一般你能看到一些导致同步错误的描述，主库没问题，从库出错，这相对来说是个好消息，只要找到同步停止的位置，重新同步就行了。

为了解决错误，需要弃用从库已经生成的Relay log，重新设置同步点，重新生成Relay log ，那么如何找到从库同步的位置呢，查看上面的错误信息，就是如下的

两个值

Relay_Master_Log_File: mysql-bin.000062

Exec_Master_Log_Pos: 230837

3. 命令操作步骤：

# 1. stop slave

mysql> stop slave;

# 重置slave的复制位置

mysql> reset slave;

# 设置slave新的同步位置

mysql> change master to master_log_file='mysql-bin.000062', master_log_pos=230837;

# 重启同步slave

mysql> start slave;

“reset slave” 会删除master.info, relay-log.info 和所有的relay log,这些文件存储了主从同步的位置和状态信息。

/var/lib/mysql目录的文件不需要修改，等Seconds_Behind_Master为0表示已完成同步，主从的数据一致，后面的恢复正常的同步了。

更详细的命令介绍参考MySQL官方文档

https://dev.mysql.com/doc/refman/5.7/en/reset-slave.html

为了防止从库的误操作，最好设置从库只读，超级用户也设置只读

set global read_only=1;

set global super_read_only =1;

到此问题就解决了！

问题的表面总是千变万化，仔细找出问题根源所在。经常根据网上别人的方法，并不能解决自己的问题，所以一定要详细查看自己的错误信息。

参考：https://www.redips.net/mysql/replication-slave-relay-log-corrupted/