在线校验两个 tidb 系统的数据一致性

作者： sustyle 原文来源：https://tidb.net/blog/09e2fdf0

需求介绍

    业务有一套 tidb 需要从 2.1.8 升级到 4.0.13，因采用数据迁移这种保守的升级方案，在数据迁移完毕后，为了业务更放心的迁移到新集群，所以需要做一次数据校验。在查阅 tidb 官方文档发现，tidb 并不支持在线校验（增量数据一直在同步），所以需要另寻方案。

tidb 官方提供的数据校验工具：https://docs.pingcap.com/zh/tidb/v4.0/sync-diff-inspector-overview

一、前言

    本文参考的是 pt-table-checksum 的校验逻辑，基于数据块去遍历每个表，然后比对 checksum 的值判断该块是否一致，本文主要是想聊聊我在实现数据校验脚本过程中遇到的问题以及解决思路，希望对大家有帮助。

二、开发工具遇到的问题

1、解决复杂的联合主键问题

（1）查询索引失效，或者查询报错问题

    熟悉 pt-table-checksum 的朋友应该都知道，该工具是基于主键 (非空唯一键) 进行扫描数据行，其实这个逻辑针对整型单列主键实现起来很简单，但是如果是联合主键且是字符型，好像就没那么简单了，有兴趣的可以思考一下。下面我先说一下大致的逻辑：

• 第一步：判断 _min_rowid 是否为空，为空就取该表的第一行，并记作 _min_rowid=xxx。

• 第二步：根据 _min_rowid=xxx 作为条件进行扫描该表，取下一个数据块的数据，记录数据块的最后一行数据的主键值，记录 checksum 的值，并记下 _min_rowid=xxx。

• 第三步：判断 _min_rowid 是否为空，非空重复第二步，为空退出检查。

    通过上述三个步骤可以看到，如果是单列整型的主键，实现起来很简单，但是问题来了，业务的表的主键五花八门，有的是联合主键，有的是字符型的联合主键，还有整型 + 字符型的联合主键，那么上述的实现方式显然是有问题的。所以实现起来需要多考虑几个问题：

• 需要考虑主键是否是联合主键。

如果是联合主键，在取数据块的时候查询条件就是 where pk1 > xxx and pk2 > yyy

• 需要考虑主键字段的数据类型是否是整型或字符型。

如果主键字段是字符型，在取数据块的时候查询条件就是 where pk > ‘xxx’，否则查询将不会使用到索引。

    鉴于存在上述两个问题，可以参考如下实现逻辑：

• 获取主键字段列表，放在数组里

• 根据主键字段名获取字段的数据类型，放在关联数组里

• 根据字段的数据类型，如果是字符型就需要做特殊处理

这步的作用是说在取每个块的数据，需要记录 _min_rowid 的时候会根据主键的类型记录不一样的值，比如 :

• 整型就记录成 _min_rowid=1

• 字符型就记录成 _min_rowid=‘1’

• 整型 + 字符型的联合主键就记录成 _min_rowid=1,‘1’

• 字符型的联合主键就记录成 _min_rowid=‘1’,‘2’

这样在每次取数据块的时候 where 后面的条件既能正确的使用索引，也不至于因为是非整型而没有带上引号而报错。

另外，经过测试，对使用联合主键的表，可以通过 tidb rowid 进行遍历表，但是为了兼容 mysql，所以没考虑这个方案。

（2）如何界定每个数据块的左区间的边界

    假如有这么一个联合主键字段 primary key(a,b,c) 都是整型，该如何编写遍历 sql 呢？起初我的想法很简单，具体如下：

_min_rowid=(xxx,yyy,zzz)select * from where 1 = 1 and a >= xxx and b >= yyy and c > zzz order by a,b,c limit checksize

乍一看好像逻辑没问题，但是实际跑脚本的时候发现这个逻辑不能完全扫完全表，后来经过多次测试校验，得出下面的逻辑 sql

_min_rowid=(xxx,yyy,zzz)select * from where 1 = 1 and ((a > xxx) or (a = xxx and b > yyy) or (a = xxx and b = yyy and c > zzz)) order by a,b,c limit checksize

    至此在编写校验脚本过程遇到的两个问题就算告一段落了，剩下的就是各种逻辑处理了，不过多赘述，有兴趣的可以自行阅读脚本文件。

三、数据校验工具做了哪些改动

1、取消 for update

    本着最低程度影响业务，所以取消加锁逻辑。但是又要保证该数据块的数据一致性，如果这个数据块是个热数据，当前正在变更，那么校验的时候难免会不一致。所以只能通过多次校验实现，默认是校验 20 次，其中有一次校验结果是一致，就认为是一致的，如果前 5 次校验过程中，这个数据块的数据没有变化，也视为不一致（可能是因为延迟，也可能是真的不一致）。

另外 checksum 状态是写到临时文件而非写到业务数据库，这样也避免了直接对线上数据库做修改。

2、支持表结构校验

    pt-table-checksum 不校验表结构，改写时添加表结构的校验。

3、支持基于表的并行校验

    可以基于表的并行校验，可由用户指定并行数，但是脚本有个安全机制，如果用户指定的并行数大于当前 cpu 空闲核心数，就会按当前（空闲核心数 -1）作为并行数。

4、支持网络监控

    添加网络监控，由用户指定网络上限百分比，当网卡流量超过这个百分比就暂停任务，等待网卡流量低于阈值才会继续任务。这个主要是在做校验时触发了网络流量告警（遍历数据的时候 tidb 到 tikv 的网络流量比较大），所以添加了该功能。

5、支持定时任务功能

    支持定时任务功能，用户可以使用这个功能规避业务高峰，仅在业务低峰进行数据校验。

指定了时间段执行校验任务，如果当天没校验完成，等到次日会继续校验。

6、支持任意两个节点的校验

    不仅限于 tidb，也不限于 MySQL 主从节点的校验，只要目标对象支持 MySQL 的标准 SQL 语法就能做数据校验。

7、添加超时机制及自杀机制

    校验逻辑是通过 SQL 采集目标节点的数据库，如果目标数据库系统当前存在异常，无疑是雪上加霜，将会触发未知问题，所以添加超时机制，单次取数据块的阈值是 5s，超过 5 秒就放弃等待重试。测试发现，有时候即便触发超时了，但是 SQL 任务还是会在目标数据库的 processlist 中能看到，所以又添加了一个 kill 机制，超时后会触发一个 kill processlist id 的动作。另外为了避免 kill 错，在每个 SQL 对象添加了一个 32 位的 md5 值，每次 kill 的时候会校验这个 md5 值。

保留 threads_running 的监控，如果 threads_running 过大就会暂停校验，这部分监控逻辑是跟网络监控一起

四、数据校验工具使用介绍

    本工具借鉴 pt-table-checksum 工具思路改写，可以检查随意两支持 mysql sql 语法的数据库节点的数据一致性。

本工具仅供学习使用，如需检查线上的数据，请充分测试

1、校验逻辑

    基于主键以一个块遍历数据表，比对 checksum 的值，块的大小可通过参数指定。

• （1）获取该表的第一个数据块的查询 SQL。

• （2）将两个目标节点的数据块的 checksum 的值，记录到临时文件，file1 file2。

• （3）比对 file1 file2 是否一致。

• 不一致 : 重复（2）的操作，至多连续 20 次，还不一致会将该 SQL 记录到 table 目录

• 一致 : 跳到（4）

• file1 为空 : 表示该表遍历完成，直接跳到（5）

• （4）获取该表的下一个数据块的查询 SQL。

• （5）检查通过就跳到（7），检查不通过调到（6）。

• （6）读取 table 目录校验不通过的 SQL 进行再次校验。

• 本次校验通过也视为数据一致

• 如果校验不通过，会将不一致的部分记录到 diff 目录

• （7）该表校验任务结束。

2、功能介绍

• 检查随意两个几点的数据一致性

• 支持表结构的校验

• 支持并发检查，基于表的并发

• 支持指定时间，可以规避业务高峰期

• 支持网络监控，如果网络超过阈值可以暂停校验

• 不支持无主键（非空唯一键）的表

• 不支持联合主键达到四个字段及以上的表

• 
  

• 
  

3、安装教程

（1）下载

git clone https://gitee.com/mo-shan/check_data_for_mysql.gitcd check_data_for_mysql

（2）配置

• 编辑配置文件

请结合实际情况根据注释提示进行相关配置

• 修改工作路径

4、使用说明

（1）目录介绍

moshan /data/git/check_data_for_mysql > tree -L 2.├── conf│   └── check.conf├── func│   ├── f_check_diff_for_mysql.sh│   ├── f_check_diff_for_row.sh│   ├── f_logging.sh│   └── f_switch_time.sh├── log├── manager.sh├── README.en.md├── README.md└── start.sh
3 directories, 9 filesmoshan /data/git/check_data_for_mysql >

• conf 配置文件的目录，check.conf 是配置文件

• log 日志目录

• start.sh 主程序

• manager.sh 网络监控脚本，任务状态的管理脚本

• func 目录是存放脚本的目录

• f_check_diff_for_mysql.sh 校验数据块的脚本

• f_check_diff_for_row.sh 校验数据行，这个脚本是将 f_check_diff_for_mysql.sh 校验不通过的结果做进一步校验

（2）帮助手册

• 主程序

可以根据需求进行参数使用，如需规避业务高峰期在低峰执行校验任务，请使用 -l 参数指定执行时间 ，如 ’-l 1-5’ 表示凌晨 1 点到 5 点执行校验任务，如果当天六点前没校验完成，会等到次日凌晨 1 点继续校验

• 任务管理脚本

可以根据实际网卡信息针对该网卡进行监控，当流量达到指定的阈值就会暂时暂停数据校验。这个脚本主要是针对使用了中间件，比如 mycat（mysql 到 mycat）。或者是 tidb（tikv 到 tidb），这种情况下会占用较多网络带宽。

• 该脚本必须要求在整个工具的家目录下执行

• 
  

（3）常用命令参考

• 管理脚本相关

• bash manager.sh -a start -t eth0 -n 30 启动 eth0 网卡的流量监控，流量达到 30% 就暂停数据校验

• bash manager.sh -a pause 暂停监控及暂停数据校验任务

• bash manager.sh -a continue 继续监控及继续数据校验

• bash manager.sh -a stop 停止监控及停止数据校验

• 主程序相关

• bash start.sh -f true -d dbatest -t test1 -l 0-5 仅校验 dbatest 库下的 test 表，且在 0 点到 5 点执行校验任务

（4）测试用例 - 校验通过场景

• 每次执行校验任务的时候强制要求情况 log 目录，请做好校验结果得备份

• 执行校验任务的时候强烈建议开启 screen

• 有网卡监控需求，执行网卡监控时也强烈建议开启 screen

    第一步：先开启一个 screen 监控网络

moshan /data/git/check_data_for_mysql > screen -S check_net_3306moshan /data/git/check_data_for_mysql > bash manager.sh -a start[ 2022-01-18 11:55:34 ] [ 1000 Mb/s ] [ RX : 2    MB/S ]  [ TX : 2    MB/S ][ 2022-01-18 11:55:35 ] [ 1000 Mb/s ] [ RX : 2    MB/S ]  [ TX : 4    MB/S ][ 2022-01-18 11:55:36 ] [ 1000 Mb/s ] [ RX : 2    MB/S ]  [ TX : 2    MB/S ][ 2022-01-18 11:55:37 ] [ 1000 Mb/s ] [ RX : 2    MB/S ]  [ TX : 3    MB/S ][ 2022-01-18 11:55:38 ] [ 1000 Mb/s ] [ RX : 1    MB/S ]  [ TX : 2    MB/S ][ 2022-01-18 11:55:39 ] [ 1000 Mb/s ] [ RX : 1    MB/S ]  [ TX : 2    MB/S ][ 2022-01-18 11:55:41 ] [ 1000 Mb/s ] [ RX : 1    MB/S ]  [ TX : 2    MB/S ][ 2022-01-18 11:55:42 ] [ 1000 Mb/s ] [ RX : 2    MB/S ]  [ TX : 8    MB/S ]

    第二步：新开启一个 screen 执行校验任务

moshan /data/git/check_data_for_mysql > screen -S check_data_3306moshan /data/git/check_data_for_mysql > bash start.sh -d dba -t dbatest1 -f true [ 2022-01-17 20:32:19 ] [ 成功 ] [ 192.168.1.1 ] [ start.sh/start.sh ] [ f_prepare:130 ] [ 本次数据一致性检查开始 ][ 2022-01-17 20:32:19 ] [ 警告 ] [ 192.168.1.1 ] [ start.sh/start.sh ] [ f_main:185 ] [ 本次数据一致性检查将检查如下库 : [dba] ][ 2022-01-17 20:32:19 ] [ 成功 ] [ 192.168.1.1 ] [ start.sh/start.sh ] [ f_main:203 ] [ 正在检查dba库 ]
[ 2022-01-17 20:32:19 ] [ 成功 ] [ 192.168.1.1 ] [ func/f_check_diff_for_mysql.sh ] [ f_check_diff_for_mysql:249 ] [ dba.dbatest1 ] [ 表结构一致 ]
[ 2022-01-17 20:32:19 ] [ 成功 ] [ 192.168.1.1 ] [ func/f_check_diff_for_mysql.sh ] [ f_check_diff_for_mysql:491 ] [ dba.dbatest1 ] [ 1,1 ] [ 00 d 00 h 00 m 00 s ] [ 9.09%, (0:0)/1 ] [ 数据一致 ][ 2022-01-17 20:32:19 ] [ 成功 ] [ 192.168.1.1 ] [ func/f_check_diff_for_mysql.sh ] [ f_check_diff_for_mysql:491 ] [ dba.dbatest1 ] [ 2,11 ] [ 00 d 00 h 00 m 00 s ] [ 100.00%, (0:0)/1 ] [ 数据一致 ][ 2022-01-17 20:32:19 ] [ 成功 ] [ 192.168.1.1 ] [ func/f_check_diff_for_mysql.sh ] [ f_check_diff_for_mysql:504 ] [ dba.dbatest1 ] [ 检查完毕 ]
[ 2022-01-17 20:32:19 ] [ 成功 ] [ 192.168.1.1 ] [ start.sh/start.sh ] [ f_main:242 ] [ 本次数据一致性检查完成 ] [ 通过 ]
moshan /data/git/check_data_for_mysql > 

检查结束后会提示检查通过，否则就是检查不通过，如下面的用例。

（5）测试用例 - 校验不通过场景

• 执行校验任务的时候强烈建议开启 screen

5、测试结果解读

moshan /data/git/check_data_for_mysql > ls -ltotal 444-rw-r--r-- 1 root root 450389 Jan 18 11:56 info.logdrwxr-xr-x 2 root root    194 Jan 18 11:56 listdrwxr-xr-x 2 root root      6 Jan 18 11:56 md5drwxr-xr-x 6 root root     72 Jan 18 11:56 pridrwxr-xr-x 5 root root     42 Jan 18 11:52 res-rw-r--r-- 1 root root     65 Jan 18 11:56 skip.logmoshan /data/git/check_data_for_mysql > 

• （1）info.log 文件

校验的日志，会将数据库的数据是否一致一一记录，如下是一行日志记录。

[ 2022-01-17 20:32:19 ] [ 成功 ] [ 192.168.1.1 ] [ func/f_check_diff_for_mysql.sh ] [ f_check_diff_for_mysql:491 ] [ dba.dbatest1 ] [ 2,11 ] [ 00 d 00 h 00 m 00 s ] [ 100.00%, (0:0)/1 ] [ 数据一致 ]

• [ 2022-01-17 20:32:19 ] 第一段是记录日志的时间

• [ 成功 ] 第二段是日志状态

• [ 192.168.1.1 ] 第三段是产生日志的机器 ip

• [ func/f_check_diff_for_mysql.sh ] 第四段是哪个文件产生的日志

• [ f_check_diff_for_mysql:491 ] 第五段是哪个函数: 行号产生的日志

• [ dba.dbatest1 ] 第六段是针对哪个 db 哪个表产生的日志

• [ 2,11 ] 第七段是数据块的左右闭区间

• [ 00 d 00 h 00 m 00 s ] 第八段是针对该表的数据校验总执行的时间

• [ 100.00%, (0:0)/1 ] 第九段是执行进度，其中小括号部分表示：(校验通过的表个数: 校验不通过的表个数)/ 总共需要校验的表的个数

• [ 数据一致 ] 第十段是数据一致状态。

• （2）list 目录

这个目录会针对每个 db 记录两个文件，一个是已经校验通过的表，另一个是正在校验的表。

• （3）md5 目录

保存数据块的 checksum 临时目录，可以忽略

• （4）pri 目录

这个目录会针对每个 db 都创建一个目录，然后记录每个表当前校验的数据块的最后一行数据的 pk(pk list) 值

• （5）res 目录

这个目录是记录校验结果的目录，会有三个子目录

drwxr-xr-x 2 root root 6 Jan 18 11:52 diffdrwxr-xr-x 2 root root 6 Jan 18 11:52 rowdrwxr-xr-x 2 root root 6 Jan 18 11:56 table

• table : f_check_diff_for_mysql.sh 脚本会将校验不通过的数据块的 SQL 记录在这里。这个目录会按 db 创建目录，将记录校验不通过的数据块的 SQL 语句格式如下：”table/db/table.log”

• row : f_check_diff_for_row.sh 脚本会读取 table 目录的 SQL 语句进行再次校验，然后产生的临时文件存在 row 目录，可以忽略

• diff : f_check_diff_for_row.sh 脚本会读取 table 目录的 SQL 语句进行再次校验，然后产生的再次校验不通过的部分存记录到这个目录，格式如下：”diff/db/table.num.diff”（该部分不一致的数据由那条 sql 触发的会保存在这个文件 ”diff/db/table.sql”）

    这是 table 目录下记录某个数据块不一致的一个例子

set tx_isolation='REPEATABLE-READ';set innodb_lock_wait_timeout=1;SELECT '127d04065afd91d587bbb19bc16037a6:mobile_bind', COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#',`id`,`uid`,`count`,`score`,`timestamp`,`info`,`mobile_info`,`del`,CONCAT(ISNULL(`id`),ISNULL(`uid`),ISNULL(`count`),ISNULL(`score`),ISNULL(`timestamp`),ISNULL(`info`),ISNULL(`mobile_info`),ISNULL(`del`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM (select * from tdb_spam_oltp.`mobile_bind` where 1 = 1 and id > 667930554 order by id limit 10000 )a

如果校验某个数据块发现两个节点数据不一致会记录这个 SQL

• 一来是方便 f_check_diff_for_row.sh 脚本再次校验

• 二来是方便用户再次确认是真的不一致还是因为这是热数据，在校验的时候正在频繁被修改

    这是 diff 目录下记录某个数据行不一致的一个例子

7974c7974< 667930554  2  1642491495866595584  100  948895134572797275  2022-01-10 16:01:30  2022-01-11 10:45:04  {"dlvBoid":7877725947093058957}  -667930554---> 667930554  1  1642491495866595584  100  948895134572797275  2022-01-10 16:01:30  2022-01-10 16:32:01  {"dlvBoid":7877725947093058957}  -667930554

同一个主键，如果数据不一致会以这样的格式记录到 diff 目录

• （6）skip.log 文件

检查不通过在 log 目录都会生成一个 skip.log 文件, 里面记录了哪些表被跳过检查及跳过原因，如果检查通过就不会有这个文件。

moshan /data/git/check_data_for_mysql > ls -l log/skip.log -rw-r--r-- 1 root root 37 Jan 17 20:35 log/skip.logmoshan /data/git/check_data_for_mysql > cat log/skip.log dbatest1.dbatest:table structure errmoshan /data/git/check_data_for_mysql >

• （7）总结

    总之就一句话，校验结果不通过的话就会生成 log/skip.log，想知道哪部分数据不一致，以及哪条 SQL 的数据块不一致可以分析 diff/db 目录。

当分析结果有不一致的时候，可以手动执行相关的 SQL【diff/db/table.sql】进行人工比对。

五、写在最后

    本工具是参考了 pt-table-checksum 工具的一些思路并结合自身经验进行改写，尚有很多不足之处，仅做学习交流之用，如有线上环境使用需求，请在测试环境充分测试。