复制错误案例分享（一）

àì夳堔傛蜴生んèń 2022-04-17 02:36 239阅读 0赞

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）上创建测试表格，并插入测试数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70][]

*  在从库（192.168.1.57）上检查数据以及复制状态

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 1][]

*  接着在从库（192.168.1.57）上执行语句更新数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 2][]

*  在主库（192.168.1.36）上执行语句更新数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 3][]

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 4][]

**现象**

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

**故障分析**

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 5][]

从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）上创建测试表

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 6][]

*  在从库（192.168.1.57）上检查数据以及复制状态

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 7][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 8][]

*  在主库（192.168.1.36）上将id字段指定为允许为空

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 9][]

*  在从库（192.168.1.57）上检查复制状态，发现SQL线程报了1171的复制错误。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 10][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 11][]

**现象**

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

**故障分析**

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

![20181112100756804.png][]

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

![20181112100819695.png][]

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

### **| 作者简介** ###

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

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70]: /images/20220417/953015d5ad444270aadf52147267893c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 1]: /images/20220417/9d0b9e72e42149e69e675e0c1e9b7ea3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 2]: /images/20220417/e6b7c513a93949bcb03f3f85a6a650be.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 3]: /images/20220417/9b79b8786e49423e8300f6cbf23b0e4c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 4]: /images/20220417/d5cb224565df477d8b4e7486c25f631d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 5]: /images/20220417/1bb575667db54d52b6a26fa27f1e8ecd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 6]: /images/20220417/ac4776867b0648eba2116811c8f5888e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 7]: /images/20220417/801dd558b07442f1a58c50673d9796d3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 8]: /images/20220417/0230e53149fa475c80f8ddfe91cce1bf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 9]: /images/20220417/db4013265b154eea977db2fa284740e9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 10]: /images/20220417/3767d573c2e14d659c9bb29c2da4538b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 11]: /images/20220417/8bbcfaeee42c422c96a396a393854013.png
[20181112100756804.png]: /images/20220417/14bad74c3bc84a6384f6e8440364de83.png
[20181112100819695.png]: /images/20220417/7503c2ec365e45549e55719f095b1756.png