复制错误案例分享(一)

MySQL Replication是MySQL非常重要的特性。用好了,可以发挥很大的作用,做负载均衡,做读写分离,做备份等等,能在关键时刻救DBA一命;用不好,那就是给DBA自己找麻烦了,处理不尽的故障。所以我这边给大家分享几个关于复制的案例。

作者 沈刚·沃趣科技数据库技术专家
出品 沃趣科技

MySQL Replication是MySQL非常重要的特性。用好了,可以发挥很大的作用,做负载均衡,做读写分离,做备份等等,能在关键时刻救DBA一命;用不好,那就是给DBA自己找麻烦了,处理不尽的故障。所以我这边给大家分享几个关于复制的案例。

| 案例一:binlog_format=MIXED导致的主从数据不一致

环境信息
  • 操作系统 Red Hat 6.7

  • 数据库版本 MySQL5.6.36

  • 主从IP : (1) 主库:192.168.1.36 ; (2) 从库:192.168.1.57

  • 数据库参数配置 sync_binlog=1 传统复制,即非GTID复制

故障重现
  • 将两台数据库搭建成为主从架构(此处省略搭建步骤)

  • 在主库(192.168.1.36)上创建测试表格,并插入测试数据

  • 在从库(192.168.1.57)上检查数据以及复制状态

  • 接着在从库(192.168.1.57)上执行语句更新数据

  • 在主库(192.168.1.36)上执行语句更新数据

  • 在从库(192.168.1.57)上检查数据和复制状态,可以看到主库的操作并没有在从库上生效,并且主从的复制状态也是正常的。

现象

在测试步骤中我们可以看到,在从库更新数据之后,主库上的更新操作在从库上没有生效,但是查看复制状态一切正常。仅从show slave status\G中查看到的信息,我们认为目前主从的复制是正常的,但是考虑实际的数据,主从的数据已经不一致了。

故障分析

看到主库的更新操作没有在从库上应用,首先考虑,这个事务的binlog是否真的被从库接收到。于是检查从库上的relay log,使用mysqlbinlog工具解析relay log

从relay log中可以看到,主库上的更新操作在从库上是接收到了的。接着根据 show slave status\G的信息,也可以确定该事务是被sql线程应用了的。再仔细一看这个 relay log 发现,这个 update 操作是被以STATEMENT的格式保存下来,并复制到从库。所以在从库上只是简单的执行这个语句。并且因为从库上int_b=1的记录已经被修改为 int_b=2,从而在从库上执行这个语句的时候,找不到符合相应条件的记录需要修改。 这个更新操作是执行了的,只是没有找到符合where条件的记录。所以 show slave status\G 查看复制状态也是正常。但是主从数据不一致了。 所以,在复制架构中一定要强调不要随便在从库上执行insert、update、delete等操作,因为极有可能做了相应的操作之后,主从数据不一致,复制状态正常。应用查询数据出现异常,问题很难排查。

| 案例二:主从版本不一致导致的复制错误

环境信息
  • 操作系统 Red Hat 6.7

  • 数据库信息 : (1) 主库IP:192.168.1.36 ; (2) 从库IP:192.168.1.57 ; (3) 主库数据库版本:5.6.36 ; (4) 从库数据库版本:5.7.18

  • 数据库参数配置 sync_binlog=1 传统复制,即非GTID复制

故障重现
  • 主从搭建复制架构,搭建步骤此处省略

  • 在主库(192.168.1.36)上创建测试表

  • 在从库(192.168.1.57)上检查数据以及复制状态


  • 在主库(192.168.1.36)上将id字段指定为允许为空

  • 在从库(192.168.1.57)上检查复制状态,发现SQL线程报了1171的复制错误。


现象

从以上测试步骤中可以看到,在复制正常的情况下,主库上执行DDL提示没有错误,在从库上执行会有一个错误,提示说主键的字段必须非空,如果你要在一个索引中使用NULL属性,那应该使用唯一索引替代主键索引使用。

故障分析

因为主库为5.6.36版本,从库为5.7.18版本,所以很容易考虑说是不是因为主从数据库版本不一致的原因。但是具体是因为5.6和5.7中什么的不同导致的问题,需要接着分析。 可看到我们在主库上执行DDL的语句的时候,执行成功了,但是查看 show create table tt; 语句,可以看到这个DDL语句并没有起作用,所以这个DDL语句在5.6版本中是被忽略了。 我们直接拿这个DDL语句在5.7的数据库上执行,直接就报错了

检查主库上的binlog日志以及从库上的relay log,都能看到DDL语句是被记录了的

可以说明这句DDL语句是被正常复制的,但是该语句在5.6主库上执行的时候,操作被忽略了。DDL语句被复制到5.7从库上执行的时候,因为5.7不允许该操作,所以SQL线程在重放该操作的时候报错,导致SQL线程中断。

| 作者简介

沈 刚·沃趣科技数据库技术专家

熟悉MySQL数据库运行机制,丰富的数据库及复制架构故障诊断、性能调优、数据库备份恢复及迁移经验。

发表评论

电子邮件地址不会被公开。 必填项已用*标注