MySQL双写情况下复制update/delete的同步问题
背景
架构介绍：
机房1主库 |
数据流向 |
机房2主库 |
备注 |
M1 |
——> |
M2 |
M2单向同步了M1的数据 |
为了完成机房1到机房2的迁移，主库写入点切换到M2，但是还不能一把切，只能两边都有数据流量（也就是双写），需要测试什么情况下会造成数据不一致或者同步中断的情况。
测试环境：
MySQL版本 |
5.7.26 |
binlog_format |
row |
GTID_MODE |
on |
第一种情况：表结构中带有主键id的情况
CREATE TABLE `test` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',`ustatus` int(10) unsigned NOT NULL DEFAULT '0',PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
测试一【测试update的执行】：
m2单向同步m1的数据（m1的数据同步到m2，这里m2是从库）
update之前：
| id | ustatus |+----+---------+| 1 | 0 || 3 | 1 || 5 | 2 || 7 | 3 |
m2上执行（从库先执行update）：update test set ustatus=5 where id=1【将之前id=1 ustatus=0 更新为ustatus=5】
## UPDATE `br_test`.`test`## WHERE## @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */## @2=0 /* INT meta=0 nullable=0 is_null=0 */## SET## @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */## @2=5 /* INT meta=0 nullable=0 is_null=0 */
m1上执行（主库后执行）：update test set ustatus=4 where id=1【将之前id=1 ustatus=0 更新为ustatus=4】
主库上的binlog：
### UPDATE `br_test`.`test`## WHERE## @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */## @2=0 /* INT meta=0 nullable=0 is_null=0 */【带有本实例的原值】## SET## @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */## @2=4 /* INT meta=0 nullable=0 is_null=0 */
m2从库上的binlog显示：
update test set ustatus=4 where id=1## UPDATE `br_test`.`test`## WHERE## @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */## @2=5 /* INT meta=0 nullable=0 is_null=0 */【带有本实例的原值】## SET## @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */## @2=4 /* INT meta=0 nullable=0 is_null=0 */+----+---------+| id | ustatus |+----+---------+| 1 | 4 || 3 | 1 || 5 | 2 || 7 | 3 |
如上的执行，最终数据是一致的，数据更新是按照原条件更新的，和原有数据行主从的数据是否一致没关系
此时如果将m1的主库指向m2上，则m1上的数据会被m2上之前的update test set ustatus=5 where id=1覆盖， 覆盖掉id=1 ustatus=4这个记录
主库记录：
+----+---------+| id | ustatus |+----+---------+| 1 | 5 || 3 | 1 || 5 | 2 || 7 | 3 |
从库：
+----+---------+| id | ustatus |+----+---------+| 1 | 4 || 3 | 1 || 5 | 2 || 7 | 3 |
导致主从数据不一致
测试步骤和数据显示：
m2（从库） |
m1（主库） |
状态 |
|
m2：update test set ustatus=5 where id=1 |
1，5 |
1，0 |
主从数据不一致 |
m1：update test set ustatus=4 where id=1 |
1，4（同步了m1的update） |
1，4 |
主从数据一致 |
m1同步m2的数据：m1执行change master to m2 |
1，4 |
1，5 |
主从数据不一致 |
测试二：【测试delete】
#初始化主从数据+----+---------+| id | ustatus |+----+---------+| 1 | 11 || 3 | 1 || 5 | 2 || 7 | 3 |
步骤一：从库执行：update test set ustatus=10 where ustatus=11;
+----+---------+| id | ustatus |+----+---------+| 1 | 10 || 3 | 1 || 5 | 2 || 7 | 3 |
主库数据：
+----+---------+| id | ustatus |+----+---------+| 1 | 11 || 3 | 1 || 5 | 2 || 7 | 3 |
步骤二：主库执行：delete from test where id=1;
从库上binlog：
### DELETE FROM `br_test`.`test`## WHERE## @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */## @2=10 /* INT meta=0 nullable=0 is_null=0 */
主库上binlog：
## DELETE FROM `br_test`.`test`## WHERE## @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */## @2=11 /* INT meta=0 nullable=0 is_null=0 */
此时主从数据是一致的， id=1的记录被删除了
#主从数据显示
+----+---------+| id | ustatus |+----+---------+| 3 | 1 || 5 | 2 || 7 | 3 |+----+---------+
步骤三：将m1的主库指向m2上
则m1上会执行m2上之前执行的语句update test set ustatus=10 where ustatus=11;
但是此时m1上的id=1 ustatus=11的数据早被删除了 则m1没法正常的同步；
会出现报错：br_test.test; Can't find record in 'test', Error_code: 1032;
测试步骤和数据显示：
m2（从库） |
m1（主库） |
状态 |
|
m2： update test set ustatus=10 where ustatus=11; |
1，10 |
1，11 |
主从数据不一致 |
m1：delete from test where id=1; |
id=1的数据被删除 |
id=1的数据被删除 |
主从数据一致 |
m1同步m2的数据：m1执行change master to m2 |
br_test.test; Can't find record in 'test', Error_code: 1032; |
主从数据还是一致，但是同步中断，没法执行update |
第二种情况：表结构中不带有主键的情况
CREATE TABLE `test2` (`id` bigint(20) unsigned NOT NULL,`ustatus` int(10) unsigned NOT NULL DEFAULT '0') ENGINE=InnoDB DEFAULT CHARSET=utf8+----+---------+| id | ustatus |+----+---------+| 1 | 1 || 2 | 2 || 3 | 3 |
第一步：从库上执行update test2 set ustatus=10 where ustatus=1;
## DELETE FROM `br_test`.`test2`## WHERE## @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */## @2=1 /* INT meta=0 nullable=0 is_null=0 */
从库：
+----+---------+| id | ustatus |+----+---------+| 1 | 10 || 2 | 2 || 3 | 3 |
主库：
+----+---------+| id | ustatus |+----+---------+| 1 | 1 || 2 | 2 || 3 | 3 |
第二步：主库上执行delete from test2 where id=1;
此时从库同步停止br_test.test2; Can't find record in 'test2', Error_code: 1032
没有主键的情况，从库上操作直接比对的是全部列的数据
测试步骤和数据显示：
m2（从库） |
m1（主库） |
状态 |
|
m2： update test2 set ustatus=10 where ustatus=1; |
1，10 |
1，1 |
主从数据不一致 |
m1：delete from test2 where id=1; |
同步中断 |
id=1的数据被删除 |
主从数据不一致 |
结论：
任何情况下，不要在从库直接执行数据行的变更，否则会导致不可预知的错误，数据一致性很可能被破坏。如果非要在从库执行事务，就要想好办法应对下面的问题。
第一：当出现同步中断的时候，如果主从数据是一致的，那么就可以执行skip，如上面的带有主键的delete，M1上的同步中断是可以跳过的，最终数据一致。
第二：当没有出现主从同步断开的情况，这时也不能保证数据是一致的，需要校验主从数据一致性。
所以，正常情况下，对于主从数据一致性要求极高的库来说，不建议从库执行事务，在主库维护需要切换主从并且有双写的情况下，一定要校验双写期间的数据变更次序（因为如稳重带有主键id的update，虽然没有出现主从同步中断的情况，但是数据一致性已经遭到破坏）