mysql数据迁移数据一致性检校验

 

1.数据迁移场景描述

以下只列举几个特殊场景:

scala 那边的表字段名和现在微服务中的字段名大部分不一致

scala 项目 A 表中的大部分字段需要迁移到微服务中的 A’ 表中，但是有一小部分字段需要和 B 表中的所有字段关联后迁移到微服务的 B’ 表中

scala 项目中的一些枚举与现在微服务中重新规定的枚举有一些差异

待迁移表中有一个特别大的待迁移字段：

`value` varchar(10000) NOT NULL COMMENT '属性值';

术语约定：

​ 源表：scala 中待迁移到微服务中的表

​ 目的表：微服务中的表

2、验证函数选择

网上介绍更多的方法是借助 pt-table-chum 工具进行验证，下面是其原理介绍：

pt-table-checksum 是 Percona-Toolkit 的组件之一，用于检测MySQL主、从库的数据是否一致。其原理是在主库执行基于statement的sql语句来生成主库数据块的checksum，把相同的sql语句传递到从库执行，并在从库上计算相同数据块的checksum，最后，比较主从库上相同数据块的chec七墨博客ksum值，由此判断主从数据是否一致…

根据其原理发现这个工具基于当前特殊场景好像不是很适合，但是还是很有借鉴意义的，比如原理中提到的比较主从库上相同数据块的 checksum 值，那我们这边也可以对源表与目的表分别进行分组并取 checksum 值，最后对比相同数据块的checksum值就可以了。所以搜索下 checksum 是如何计算的，找到如下内容：

计算checksum值时的函数，默认是CRC32，其他还有FNV1A_64, MURMUR_HASH, SHA1, MD5等

通过测试发现 MySQL 目前仅支持：CRC32、SHA1、MD5这三个函数。通过翻阅资料，这三个函数的算法的性能对比文章还是很多的，可以参考博客，结论：MD5的计算速度明显优于另外两个函数。经查询资料，发现md5意外冲突的概率为2 ^ -128，针对本文这种验证情况，可忽略不计。故选择MD5进七墨博客行数据一致性验证。

3、一致性验证流程

通过 count 函数验证迁移前后数据条数是否一致

这个比较简单，其实就是通过数据迁移时的数据规划，确定哪些数据该迁移到哪个表，然后对源表和目的表分别取count值进行对比即可。

通过 md5 函数验证迁移前后数据内容是否一致

首先使用 MySQL 提供的contact函数来合并字段的值，但是假如CONCAT中含有NULL值，那么就会导致最终的结果为NULL，所以我们要先用IFNULL函数来替换NULL值，比如：

CONCAT(IFNULL(pname,''),IFNULL(dname,''))

https://qimok.cn

简单拼接下sql：

SELECT

id, MD5(CONCAT(

id,

IFNULL(pname,''),

IFNULL(dname,''))

) AS md5_value

FROM sub

order by id;

我们对源表与目的表都执行上面的sql，然后把结果拿到软件beyond compare中对比下，很容易就能找出不相同的行以及主键 ID。

解决特殊场景：手写sql是很容易解决上文提到的特殊场景1～3。对与特殊场景4，其实单独对特别长的字段先取一次md5值，就变为32个字节了，也得到了解决。

但是数据量小一点还行，假如数据量特别大(千万级别)，那么执行的结果集也是相当大的，千万级别的数据量对比一整天也够呛能搞完呀！

此时，可以尝试缩小结果集，可以将多行记录的MD5值合并起来再求MD5值，如果最后MD5的值相同，说明这些行也是相同的，如果不同的话肯定是有差异的，我们再对比这些行就可以了。

如果需要将分言七墨组后的结果合并，需要使用GROUP_CONCAT函数，注意在GROUP_CONCAT函数中添加排序保证合并数据的顺序， SQL如下：

SELECT Min(id) AS min_id,

Max(id) AS max_id,

Count(id) AS row_count,

md5(group_concat(

md5(concat(id, pname, dname))

ORDER BY id

)) AS md5_value

FROM sub

GROUP BY (id DIV 32);

解释下id DIV 32：group_concat函数可以拼接的最大长度是1024个字节，md5的值最长占32个字节，1024/32=32。为了防止拼接被截断，故id对32取商进行分组。

执行结果：

min_id max_id row_count md5_value

46527776 46527807 32 b23eb8a7a034e33ae2e270fb4959fd59

46527808 46527839 32 f6b404e432fa0518cea10444947fd719

46527840 46527871 32 5c4838332396b5067b40f31122b06185

46527872 46527903 32 129b297ddc4d1908fe847782cb281c58

46527904 46527935 32 7c88bad5ee173777df5c37f8efc091da

46527936 46527967 32 15d6422ff3f52fc791d8244f5eefb164

46527968 46527990 23 927d5d1c6d1eebcb2e33e7786d3daa6e

46563126 46563130 5 784db028250312c34e0671b6aabaabf9

46563787 46563787 1 b0f83065c2026e4988fa6580e3e9570a

这样效率提升了32倍。

4、拓展

由于生产数据量是千万级别的，即使每32条数据分成一组，那至少也要30多万条的md5值需要对比，工作量也是相当大的，故作出以下拓展：

拓展1：

当生产要求不是很严格的时候，可以在凌晨(用户活跃度很低的情况下)，执行以下语句(扩大group_concat的长度限制，当前会话生效)：

SET SESSION group_concat_max_len = 102400;

此时id对3200取商进行分组，然后再进行md5方式验证，如果担心此种方式对服务器造成很大七墨博客的压力，可参考拓展2。

拓展2：

数据迁移后，找运维人员将源表和目的表都同步到测试数据库，然后扩大 group_concat 的长度限制，比如还是扩大到102400字节，再进行md5验证，然后对比迁移前后的结果，此时对比的效率扩大到初始的3200倍。具体group_concat 可以搞到多大，可以自行参考 MySQL 官方文档。

拓展3：

双写打开后，也可以通过md5的方式验证源表与目的表中双写的新数据是否一致，只需要在where子句中对created进行限制一下即可。

注意：

对于数据迁移不一致的情况，我认为不一致基本都是批量问题，主要还是因为迁移脚本导致迁移前后数据的批量不一致。

​另外，数据迁移前，一定要首先对比一下源表和目的表对应字段的类型是否一致，比如varchar类型的字段，假如目的表中设定的字段类型的长度小于源表中的字段类型长度，迁移时，长字符串有被截断的风险。

5、总结md5方式的验证流程：

1、对源表和目的表中的数据每条都先取md5值(长字段可以单独先取md5值)

2、再统一按n条数据进行分组拼接，即 id DIV n，此sql定义为原sql

3、然后分别计算md5值

4、将源表与目的表的结果全部放在软件beyond compare中

5、比对结果值，如果一致，则迁移前后的数据是一致的

6、如果不一致，从上往下比对每条md5值，找到第m条不一致

7、不一致的数据在id：(m-1)*n ~ mn之间，可以直接定位范围

8、如果范围不大，可以一条条的对比

9、如果范围很大，可以将原sql添加where过滤条件，并对n缩小分组范围验证，仅针对范围内的数据进行md5方式的验证

6、线上验证暴露出的问题

迁移时最好连主键一起迁移，否则需要通过 [SELECT @rownum:=@rownum+1 AS rownum] 生成伪主键，具体使用请自行搜索

校验脚本依赖迁移脚本，业务相关的迁移错误无法验证出来

发布成功后，隔段时间需要再验证下增量数据的正确性(主要验证代码的业务逻辑)

7、总结

本文仅对数据迁移后的一致性校验提供一种简单思路，适合场景：增量数据迁移验证、双写时数据验证、数据迁移后的抽样验证、迁移时最好连主键一起迁移的场景。

参考

相关资源：MySQLReplicaIntegrityCheck:MySQL在线复制一致性检查工具-它通过...