MySQL主从数据一致性校验：pt-table-checksum实战与踩坑总结

说到底MySQL主从数据一致性这事儿绝大多数人一开始都会觉得“只要复制没断数据就肯定一样”。我以前也是这么想的直到线上因为一个漏加主键的同步脚本主从数据悄悄分裂了一个多月才被发现当时配合业务对账花了整整两天两夜。从那之后我就把pt-table-checksum列进了日常巡检的必带工具。这篇文章不绕弯子直接讲清楚pt-table-checksum到底怎么用、为什么它能当主从一致性的鉴定标准以及我在真实环境里跑出来的经验和踩过的坑。文章里所有命令和参数都是我自己实测过的有些地方还附了当时的排查过程和结果希望对你有实际帮助。1. 主从复制延迟久了数据一致性究竟怎么查1.1 我遇到的主从不一致典型场景先还原一下实际场景。我们当时有一张订单明细表每天增量大概50万行线上主库一直在写入从库供报表和运营团队查询。某天开发往一个历史表里补数据写了一个批量UPDATE的脚本直接在从库上执行了——是的在从库上写数据。当时从库的read_only设置被运维临时关掉了因为要跑一个异步任务。结果就是这台从库上部分记录被改成了“新值”而主库上还是“旧值”复制没有中断但数据和主库已经完全对不上。你以为从库会自动报错并不会。MySQL复制只是把主库的binlog按顺序回放从库本地被改过的行主库只要没有新的UPDATE覆盖它就永远保留这个对不上的状态。最坑的是Seconds_Behind_Master显示0因为从库SQL线程正常没有任何延迟。所以仅靠SHOW SLAVE STATUS你根本无法判断数据是否一致。这就是必须引入第三方校验工具的根本原因。1.2 为什么SHOW STATUS里的参数不能证明一致性很多人会看这些所谓“一致性”指标Slave_IO_Running: YesSlave_SQL_Running: YesSeconds_Behind_Master: 0。这三个参数同时正常只能说明复制链路是通的SQL线程在执行binlog且当前没有积压。但复制线程只关心“执行了哪些事件”并不会验证执行后库里的数据是否和主库一致。尤其当从库自己发生过数据修改或者SQL线程因为错误被跳过例如SQL_SLAVE_SKIP_COUNTER跳过了某个事务哪怕跳过的是一条无关紧要的语句从库也会偏离主库。除非业务查询恰好命中不一致的数据否则这个问题可能会存在很久且毫无知觉。所以“复制状态正常”和“数据一致性”是两个完全不同的概念后者需要主动校验而pt-table-checksum就是干这个的。1.3 pt-table-checksum能做什么、不能做什么pt-table-checksum是Percona Toolkit里的核心校验工具。它的基本思路是在主库上对每一张目标表按主键切分成多个chunk分别计算校验值再把这些校验值写入一个名为checksums的表中这个表本身会通过主从复制同步到从库接着工具连接到从库执行相同的校验计算最后对比两边产生的校验值是否一致。如果某一行不一致checksums表里对应chunk的校验值就会不同工具会明确输出DIFFS列告诉你哪张表有差异。它不能做什么也必须搞清楚。它不会自动修复数据只能定位到哪张表、哪个chunk有问题修复需要配合pt-table-sync或者人工写SQL。此外它不会校验所有MySQL对象比如视图、存储过程、事件这类非行数据它一概不关心。它的核心价值是把“全量数据对账”这件原本需要写复杂脚本的活儿做得又快又安全。2. 校验工具的工作原理chunk切分与校验和计算2.1 基于主键的chunk切分逻辑如果把几千万行的表一次性算出一个checksum不仅耗时长还会在主库上产生大事务造成锁和数据延迟。pt-table-checksum的聪明之处在于chunk化。它通过SHOW CREATE TABLE拿到表结构然后用优化器在索引信息中找到一个可用于范围扫描的索引优先主键再把这个表按主键值的区间切成多个小块。每个chunk默认包含大约1000行数据这个行数不是硬性的工具会根据表大小动态调整。比如一个订单表主键id从1到1000万工具会尝试切出10000个chunk。对每个chunk它在主库执行类似这样的计算SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CRC32(CONCAT_WS(#, id, order_no, status, amount))), 10, 16)), 0) AS checksum FROM db.tbl FORCE INDEX (PRIMARY) WHERE id 1 AND id 1000;注意这里用了BIT_XOR和CRC32对整个chunk的每一行计算一个哈希再把所有哈希做异或得到一个紧凑的校验值。COUNT(*)用来判断行数是否一致。这样一条SQL只扫描小范围事务非常短不会长时间持有锁。2.2 REPLACE INTO checksums表与主从传播每个chunk计算完成之后工具会把结果写入Percona设计的checksums表写入方式是REPLACE INTO。这张表通常建在percona库下默认库名是percona也可以通过--replicate指定。关键点来了这条REPLACE语句会作为正常的DML语句进入binlog然后复制到从库在从库上同样执行一遍。为什么这能校验一致性因为主库上checksums表里的值来自“主库目标表的数据”而从库上checksums表里的值来自“复制过来的主库目标表的数据”。但工具在从库上做校验时并不是直接读checksums表而是会重新连接从库然后在从库上对目标表执行同样的chunk计算SQL再和主库写入的checksums表记录做对比。假如从库上的数据和主库不同那么从库执行这段SQL得到的checksum和主库记录里的checksum就不一致工具立刻能发现。这里隐含了一个非常重要的依赖主库的binlog_format必须是STATEMENT或MIXED。因为在ROW格式下REPLACE INTO checksums表时binlog记录的是“主库上产生的最终行值”从库回放时只是把这一行直接应用进去而不会重新执行从库上的checksum计算。如果从库数据已经和主库不同从库checksums表却会被强制改成和主库一样的值这样对比就失效了。所以工具默认会检查binlog_format如果发现是ROW会报错。后面我会讲怎么合规处理。2.3 为什么工具不会拖垮线上业务很多人对pt-table-checksum有顾虑“在一个几亿行的表上做校验会不会把主库打挂”这其实是工具设计上最出色的地方。它提供了多种限流手段默认会读取SHOW PROCESSLIST里的Threads_running如果这个值超过了--max-load设定的阈值它会暂停等待。同时--chunk-time控制每个chunk的执行时间上限工具会根据历史执行时间自动调整chunk大小让每个chunk尽量在0.5秒内完成而不是机械地固定1000行。另外它对每个chunk执行完后会先连接从库收集结果再继续下一个chunk不是持续压着主库不放。配合--pause-file甚至可以随时暂停。我在一台高峰期QPS 5000的主库上跑过1亿行的大表校验主库的慢查询数量几乎没变化CPU增量也在5%以内。当然前提是你把参数调对了比如把--max-load设为Threads_running10--critical-load设为Threads_running20等。3. 安装与准备工作从零搭建可用环境3.1 安装Percona Toolkit的几种方式pt-table-checksum是Percona Toolkit中的一个工具安装整个工具包即可。如果你的服务器是CentOS/RHEL系统可以启用Percona官方仓库后直接yum安装yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm percona-release enable tools release yum install percona-toolkitUbuntu/Debian用户则用wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb dpkg -i percona-release_latest.generic_all.deb apt-get update apt-get install percona-toolkit如果不想碰系统仓库也可以从Percona官网下载对应的tar包解压后把bin目录下的pt-table-checksum和pt-table-sync拷贝到/usr/local/bin即可它依赖Perl以及若干核心模块一般操作系统自带的Perl都能满足缺模块时用cpan装一下就行。这里我建议生产环境优先使用yum/apt安装方便后续升级。3.2 授权账号与最小权限模型工具需要连主库也需要连从库。官方文档推荐的权限如下但我们可以做得更精简。可以创建一个专用账号GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO cksum% IDENTIFIED BY your_password;SELECT是必须的因为要读取目标表数据。PROCESS用于读取线程信息实现限流。SUPER用于设置binlog_format等会话变量在ROW格式下可能需要在会话级临时改我不建议在全局改。REPLICATION SLAVE是用于在从库上执行SHOW SLAVE STATUS如果你的工具连的是从库那么这个权限需要在从库上也授权。还有一点容易被忽略工具需要创建checksums表的权限。默认它会尝试在percona库下建表所以需要给percona库的CREATE、INSERT、UPDATE、DELETE权限。我在测试环境遇到过工具因为没权限建表而报错退出添加授权后就好了。GRANT CREATE, INSERT, UPDATE, DELETE ON percona.* TO cksum%;如果你的MySQL版本启用了所有库下的临时表可能还需要CREATE TEMPORARY TABLES权限不过多数情况下上面的授权足够。3.3 避免影响主库的配置建议在实际跑校验之前先确认主库的binlog_format。这里我不建议在全局把ROW改成STATEMENT或MIXED因为生产环境用ROW格式通常是为了避免误操作和保证更好的复制安全。更稳妥的做法是维持生产全局binlog_formatROW但让pt-table-checksum自己处理——它默认会检查并在需要时通过会话级SET来切换。工具启动时如果检测到全局是ROW且存在复制过滤或触发器它会拒绝执行没有过滤和触发器时它会在会话里用SET SESSION binlog_formatSTATEMENT为了让checksum计算语句走statement复制。为了确保主库不被拖垮我还习惯在命令里加--max-load Threads_running10--critical-load Threads_running20--lock-wait-timeout60--set-vars wait_timeout600这样就可以保证网络抖动、任务堆积时工具主动退让不至于成为新的事故源。另外建议维护窗口至少避开业务高峰哪怕工具限流做得再好大表的全量校验仍然会产生大量的读IO。4. 实测一条命令跑完全库校验4.1 最常用的pt-table-checksum命令拆解下面这条命令是我在项目里最常用的全库校验命令pt-table-checksum \ h主库IP,ucksum,pyour_password,P3306 \ --databasestest_db \ --tablesorders,order_items \ --replicatepercona.checksums \ --nocheck-replication-filters \ --no-check-binlog-format \ --max-load Threads_running10 \ --critical-load Threads_running20 \ --chunk-time0.5 \ --lock-wait-timeout60 \ --set-vars wait_timeout600 \ --empty-replicate-table \ --create-replicate-table拆开来看h主库IP是主库地址工具会先连主库采集信息再自动发现从库。如果有多个从库它也会逐个校验。--databasestest_db指定要校验的库不加则全库校验。--tables可以指定多个表这里只校验orders和order_items。--replicatepercona.checksums指定存储校验结果的库表不指定默认是percona.checksums。--nocheck-replication-filters如果从库配置了replicate-wild-do-table之类的过滤规则不校验这些规则照常执行。如果过滤规则会导致某些表不复制建议结合实际情况使用否则会出现大量false positive。--no-check-binlog-format因为我全局binlog_formatROW工具默认会报错所以加上这个跳过检查。前面说过在没有复制过滤和触发器的前提下它会在会话级切到STATEMENT因此可以安全使用。--max-load和--critical-load限流关键参数超过阈值自动暂停或退出。--chunk-time0.5尽量让每个chunk在0.5秒内完成工具会自适应调整行数。--empty-replicate-table --create-replicate-table每次跑之前先清空并重新创建checksums表避免残留数据干扰。执行过程中终端会实时打印进度类似TS PID DB TABLE CHUNK CNT DIFF RNTS SKIP ...如果最终没有输出任何表的DIFFS不为0就说明这些表的主从数据是一致的。4.2 校验结果表各字段含义命令执行完后可以查看percona.checksums表里面每一条记录代表一个chunk的结果。核心字段有db、tbl库名和表名。chunkchunk序号从0开始。chunk_time该chunk执行耗时单位秒。chunk_index切分chunk所用的索引名。lower_boundary、upper_boundary该chunk的索引范围。this_cnt主库上该chunk的总行数。this_crc主库上该chunk的校验值。master_cnt、master_crc主库记录在checksums表里的行数和校验值。ts执行时间。当工具在从库上重新计算并对比后如果某张表某些chunk的行数或CRC不一致就会在终端报告同时checksums表里对应记录会产生差异。此时不要慌张先确认是不是从库真正有问题因为某些SQL导致binlog格式异常时也可能产生误报。我们在下一节讲诊断和修复。4.3 如何按库、按表、按比例抽样校验如果你不想一上来就全库跑或者表实在太大可以只针对重点表校验pt-table-checksum --databasestest_db --tablesusers --replicatepercona.checksums ...如果想做抽样校验比如只校验where条件里的部分数据可以用--where参数pt-table-checksum h主库IP,ucksum,pyour_password \ --databasestest_db --tablesorders \ --wherecreate_time 2024-01-01 AND create_time 2024-02-01 \ --replicatepercona.checksums ...注意这个where条件会加在每一个chunk的检索上因此通常只适用于有明确业务范围的数据。这个功能在做新老数据迁移后的比对时非常方便比如只校验最近三个月的数据减少扫描量。另外--chunk-size是动态调整的行数目标但--chunk-time优先级更高。如果希望每个chunk固定1000行不自动调整可以同时用--chunk-size1000 --chunk-time0。不过我不推荐固定因为不同表的行宽差异很大动态调整更平衡。5. 从checksum结果定位不一致并修复5.1 解读差异数据DIFFS列与Mismatch执行pt-table-checksum后的终端输出列非常关键。每一行输出对应一张表DIFF如果为1表示该表存在至少一个chunk的主从校验值不一致。RNTSremaining number of rows to check剩余未检测行数。SKIP因为某些原因跳过的chunk数。如果一张表DIFF1你需要进一步定位是哪几行不一致。工具只精确到chunk范围因此下一步需要缩小范围。常用的方法是使用pt-table-sync的dry-run模式只检测并输出差异不执行变更pt-table-sync \ h主库IP,ucksum,pyour_password \ h从库IP,ucksum,pyour_password \ --databasestest_db --tablesorders \ --replicatepercona.checksums \ --dry-run这个工具会读取checksums表里的chunk边界针对有差异的chunk逐行对比然后打印出期望的修复SQL但不会执行。5.2 结合pt-table-sync生成修复SQL如果确认从库数据有问题需要把从库修复成和主库一致直接执行pt-table-sync的--execute模式pt-table-sync \ h主库IP,ucksum,pyour_password \ h从库IP,ucksum,pyour_password \ --databasestest_db --tablesorders \ --replicatepercona.checksums \ --execute需要注意的是这个命令会把有差异的行从主库拉取数据然后发到从库执行REPLACE或UPDATE。如果数据量很大建议先在其中的一个库上小范围测试比如加--where限定主键范围必要时用--print先看SQL。因为它是直接改写从库数据的任何误操作都可能扩大问题。我个人的习惯是先在测试环境跑一遍--dry-run把输出的SQL发给DBA同学review确认没有批量UPDATE全部行这种离谱情况再在维护窗口执行。生产环境第一次用务必加上--chunk-size限制和--max-load限流避免从库被写请求压垮。5.3 修复后的复检与持续监控修复完不等于结束。我在实践中的标准流程是修复后立即再跑一次pt-table-checksum仅针对刚才那一张表或那一个分区确认DIFF列全部为0。这一步能保证修复SQL真正把数据拉齐了并且不会产生新的不一致。然后建议把pt-table-checksum接入定时任务。比如每周日凌晨2点跑一次全库校验并把输出重定向到日志文件0 2 * * 0 /usr/local/bin/pt-table-checksum --replicatepercona.checksums ... /var/log/pt-table-checksum.log 21再配合脚本扫描日志里DIFF1的行有差异就触发告警。这样就从被动救火变成了主动发现。我甚至见过有人用pt-table-checksum做秒级的准实时校验但一般场景不需要每天或每周一次足够覆盖绝大多数主从漂移问题。6. 真实踩坑记录权限、字符集、GTID与性能问题6.1 权限不足导致工具静默跳过表的排查第一次使用pt-table-checksum时我遇到一个诡异的现象跑了十几张表最后终端输出里少了一张关键表没有报错也没有DIFF列就是“跳过”了。后来排查发现那张表在另外一个库下而授权账号对那个库没有SELECT权限。工具在获取表结构时被拒绝由于它不是核心操作竟然不会中断主流程只是默认忽略这张表。这个坑非常隐蔽。解决方法是先执行--explain模式让工具对指定的表输出执行计划而不是真正跑数据pt-table-checksum h主库IP,ucksum,pyour_password \ --databasestest_db --tablesorders \ --explain如果权限有问题这里就会明确报错。另外强烈建议在启动命令里加--recursion-methodprocesslist因为工具需要自动发现从库默认方式可能因为找不到从库而不检查任何从库只默默生成checksums表。从库列表发现失败是另一个常见问题必要时用--host 从库IP显式指定要检查的从库。6.2 字符集不一致引发的误报还有一个让我记忆犹新的大坑某张表里有中文地址字段主库的连接字符集是utf8从库的默认连接字符集是utf8mb4导致同一个字符串在master和slave上计算CRC32时结果不同。pt-table-checksum在计算时会把所有字段拼接成字符串如果字符集不同即使数据实际一致校验值也会不一致产生大量误报。解决办法是统一连接的字符集。在命令中显式加--charsetutf8或者更准确--set-vars NAMESutf8mb4。实际操作中最好统一主从两边的character_set_server和character_set_database并在工具连接参数里加上--default-character-set。我后来做了一次全面排查把所有库表的默认字符集都统一成utf8mb4这类误报就再也没有出现过。6.3 没有主键或唯一键的表怎么处理pt-table-checksum默认要求校验的表必须有主键或唯一键否则它无法安全切分chunk。对于没有主键的表工具会直接跳过并在输出中标为SKIP。我在一个旧系统中遇到不少这样的日志表当时query速度还可以但就是不能校验。处理思路有两个一是给表添加主键或者唯一键通常用自增id来实现这也是最佳方案毕竟没有主键的表在复制出现问题时连基本定位都困难。二是对于无法加主键的临时表暂时用--nocheck-unique-key强行校验。但请注意这个参数只绕过了“是否有唯一键”的检查如果表没有可用的索引工具仍然可能因为无法分块而失败。更稳妥的方式是结合--where手动指定一个范围条件来缩小单次校验的数据集。比如一张无主键的日志表只有create_time可以直接pt-table-checksum ... --databaseslog_db --tablesop_log --whereid 1 AND id 100000前提是你手工知道一段可以切分的区间。对于没有连续的整数列的表还是老老实实加主键更省心。6.4 校验大表时的性能调优技巧最后聊一下大表的性能。我这边最大的单表超过10亿行第一次跑全库校验时直接把业务库的buffer pool命中率拉到95%以下以至于出现了几十秒的TPS抖动。后来我调整了几个参数效果立竿见影把--chunk-time从0.5调成1.0减少chunk数量降低上下文切换。使用--max-load Threads_running8 --critical-load Threads_running15更严格地限制主库并发。加上--retries10遇到锁等待可以安全重试。设置--config文件把校验时间放在凌晨低峰期。如果表分区数量很多先通过--databases加--tables限定范围别一股脑全库扫。此外使用--parallel设置为2或4可以让多个chunk并行处理但并行会放大对主库的读压力我通常只在从库压力很低或者专门有一台校验从库时才开。综合来看单线程配合限流永远是最安全的选择。生产环境最重要的不是跑得快而是跑得稳、不误伤业务。最后按我个人的运维习惯每次跑完大表校验后都会顺手清理下percona.checksums表避免表越来越大。通常用TRUNCATE percona.checksums反正下次校验会重新生成留着旧数据反而可能干扰检查。这个动作虽然小但能减少很多后来排查“为什么上次结果还在”的疑惑。