做 MySQL 数据差异对比这件事平时用不上一用就是救命的场景。主从复制跑得好好的突然某天看监控发现从库和主库的行数对不上或者业务反馈从库查出来的数据少了这时候你必须快速回答三个问题差了多少、差在哪里、怎么补回去。这篇文章就围绕 MySQL 数据差异对比 修复这条主线把从定位差异到完成修复的完整流程拆成五步适合正在维护主从架构、经常被数据一致性问题折磨的 DBA 和偏运维的后端开发新手看完也能照着手动执行一遍。我会把每一步的验证逻辑、执行顺序和我踩过的坑都写出来尤其是那些文档里不会写、只有现场才会暴露的问题。1. 先搞清楚差异是怎么冒出来的数据不会无缘无故不一致。不把根因摸清楚就算这次把数据修平了过几周又会爆出来。所以五步里面理解差异来源是第一步也是最容易被跳过的一步。1.1 复制链路里的“假同步”最常见的差异来源是主从复制没有真正追平。你在从库上执行SHOW SLAVE STATUS看到Seconds_Behind_Master是 0就以为两边数据一致了实际上这个值只是代表 SQL 线程当前没有在执行的 relay log 积压并不代表已经执行到的位置和主库 binlog 完全对齐。只要从库的 I/O 线程或者 SQL 线程曾经报过错有人手动执行了SET GLOBAL SQL_SLAVE_SKIP_COUNTER 1跳过事务差异就已经埋下了。还有一种情况是 binlog 格式的问题。如果主库用的是STATEMENT格式某些非确定性函数比如UUID()、NOW()配合不走主键的更新语句在从库执行出来的结果和主库完全不同。这种差异非常隐蔽表面上SHOW SLAVE STATUS一切正常数据却越差越远。注意主从复制只保证 binlog 里的语句被执行不保证执行结果和主库一致。binlog_format和事务隔离级别都会影响最终一致性。1.2 人为误操作和业务写入越权从库被写入是新手最容易踩的坑。很多团队给从库设了read_only 1但read_only只对普通账号生效拥有SUPER权限的账号照样能写。如果监控账号、备份账号或者某个共用账号带了这个权限业务代码一旦连错库写入照样成功主从差异立刻出现。另外人工在从库上执行“临时修复”也是一大来源。比如某个数据需要改一下DBA 图省事直接在主库改了同时又在从库上手动跑了一部分两边改的范围不一样后续复制一追平要么报主键冲突要么默默覆盖了从库的修改。这类操作我在现场见过不止一次。1.3 崩溃恢复和历史欠账还有一个容易被忽略的来源历史欠账。比如做物理备份恢复时备份文件本来就不一致或者曾经有过一次强杀进程InnoDB 崩溃恢复后主库和从库的回放进度不同再比如从库磁盘满导致 relay log 写不进去SQL 线程停了半天等磁盘清理后追数据中间恰好有事务没接上。这些历史问题不会在恢复的当下暴露往往过了一两个星期后做一次行数对比才发现整个表差了十几万行。2. 对比方案的选型决定了后面省不省事知道差异是怎么来的接下来要考虑怎么对比。对比方案选不好后面修数据的时候会加倍难受。2.1 全量对比和抽样对比怎么选小表小于几十万行直接全量对比没毛病。大表上来就跑全量逐行比对会把数据库的 I/O 和 CPU 直接打满。我一般的判断标准是行数在 100 万以内的核心表全量对比一次性确认千万级以上的大表先用抽样对比做冒烟如果抽样都发现差异直接进入修复流程定期巡检用抽样重大变更前后用全量两者搭配使用。抽样不是随便LIMIT 1000捞一行而是按主键哈希分段采样保证散落在各个区间否则只抽到热点数据冷数据区的差异永远发现不了。2.2 用什么字段做校验为什么不能整行拼接很多人做对比习惯把整行的所有字段CONCAT起来算一个 MD5然后两边比对。这个做法有两个坑第一字段顺序不一样拼接结果完全不同而两个环境的表结构很可能不完全一致第二NULL值参与拼接时CONCAT直接返回NULL导致本来一致的行算出来不一样产生大量“假差异”。我的做法是挑业务主键加核心业务字段拼一个带固定分隔符的串再做CRC32。选字段的原则是能反映这条记录唯一性和核心状态比如订单表的订单号、状态、金额、更新时间像create_time这种两边可能差几毫秒的字段如果业务上允许微小时差就不要拿来参与差异判断否则全是无效告警。2.3 工具选型与自研脚本的取舍社区里有一款很出名的 MySQL 校验工具原理就是分块计算校验值再对比还支持输出差异 SQL功能非常成熟。但它要求目标库有特定账号权限而且校验大表时临时表数量和锁策略都要调优不是拿来就能跑。我个人的经验是生产环境能装开源工具就用它省心没法装或表结构特殊的时候就写一个按主键分块的CRC32校验脚本几十行也能解决 80% 的需求不管是工具还是自研脚本都必须支持“只读对比不锁表”和“分块限速”这两个基本能力否则一跑起来就出事。3. 五步实操全流程从发现差异到修复完成下面是核心部分一步一步照着做。整个流程假设你已经有一主一从两套环境并且判断主库是基准库。3.1 第一步圈定范围和基准不要上来就全库扫描。先确认这次要对比哪些库表以哪个库为准。正常情况下以主库为基准但要注意如果这个主库本身也刚恢复过或者之前有过误操作那就得再往上找——比如某个时间点的物理备份或者二主架构里切换前的另一个节点。圈定范围时顺便记录一个 binlog 位点从库执行SHOW SLAVE STATUS拿Master_Log_File和Read_Master_Log_Pos主库执行SHOW MASTER STATUS两个位点的意义不一样但都记下来。这样修复完成后可以反推对比期间是否有新写入进来避免修完又被新数据“污染”。3.2 第二步生成校验快照这一步不直接对比先把主库的数据生成一份校验快照。快照不是拷数据而是生成“主键 CRC 值”的映射表。具体做法按主键范围把表切成若干块每块 5000 到 10000 行分别执行类似这样的 SQLSELECT FLOOR(id / 5000) AS chunk_id, COUNT(*) AS row_count, BIT_XOR(CRC32(CONCAT_WS(#, id, order_no, status, amount))) AS row_crc, MAX(update_time) AS max_update_time FROM order_table GROUP BY chunk_id;这里有几个要点用BIT_XOR而不是SUM因为SUM遇到整数溢出或者符号问题容易失真CONCAT_WS里的分隔符要固定字段里不可能出现否则多个字段边界会黏连如果主键不是数字比如用了UUID分块就不能用FLOOR(id/5000)得改成按SUBSTRING取哈希值分段。把主库的快照结果存到一个临时表或者导出成文件这一步基本不占业务资源因为都是实际数据行的读操作。3.3 第三步逐表比对并生成差异清单用同样的 SQL 在从库上跑一遍得到从库的“分块 CRC 快照”然后和主库快照对比。分块一致的行跳过分块不一致的进入下一步把这个分块里的行逐条拉出来做行级对比。行级对比不要用JOIN一次大查而是把主库这个分块的数据临时导成 INSERT 语句文件再从文件导入一个临时比对表用NOT IN或者LEFT JOIN找出差异-- 找出从库缺失的记录 SELECT id, order_no, status, amount FROM temp_main_block WHERE id NOT IN ( SELECT id FROM slave_check_table );这里我不建议直接SELECT *全字段输出因为差异清单要的是“能定位和能修复”的最小集主键加关键字段就够了。逐表跑完后汇总成一张差异清单清单里明确标注每一条是缺失行、多余行、还是值不一致行。3.4 第四步分类型制定修复方案差异就三种从库缺失、从库多数据、从库有数据但值不对。修复策略要分开缺失行从主库导出这几行生成INSERT语句插到从库多余行确认主库确实没有这些记录后在从库生成DELETE语句值不一致以主库当前值为准生成UPDATE语句。很多人在这一步图省事直接拿主库的整表覆盖从库。这是最危险的做法等于把所有数据重新灌一遍耗时不仅长而且很容易因为外键约束把整个从库锁住。在执行任何写操作前先看差异清单里有没有“从库有数据但主库没有”的情况这类数据要格外警惕。如果主库这些数据是被正常业务删除的那从库的确该删但如果是主库曾经丢失过一段 binlog 导致数据没了而从库还留着这时候直接从库删数据就会永久丢失。正确做法是先确认 binlog 的连续性确认主库删除了对应记录再执行从库删除。3.5 第五步执行修复与二次回归单条执行差异 SQL 是最稳的但差异量大的时候不现实所以要按块提交。我的习惯是每 500 行一个事务每个事务执行完睡眠几百毫秒给从库复制线程喘口气。执行前先改从库STOP SLAVE SQL_THREAD; SET GLOBAL SQL_SLAVE_SKIP_COUNTER 0;这里只停 SQL 线程不停 I/O 线程这样 relay log 还能继续收。如果直接STOP SLAVE主库的东西收不进来修复完再启动时可能积压一大堆更容易出问题。修复执行期间每跑完一批可以顺带在主库和从库执行相同的COUNT(*)验证行数是否追平。全部修完后跑一次第二步的 CRC 快照对比确认所有分块一致然后重新看一次SHOW SLAVE STATUS确认 SQL 线程没有报错、位点能持续推进。注意修复 SQL 在从库执行时如果从库的 binlog 是开着的这些修数语句会一并写进从库自己的 binlog。一旦发生主从切换这些“补丁”会变成新主库的写入源头后续配置需要提前考虑清楚。4. 修复过程中最容易踩的坑这一节是我自己摔出来的经验每一条都对应过一次线上事故。4.1 修复语句引发的复制风暴数据差异如果已经累积了十几万行你在从库上直接跑一个大事务的 UPDATE 或 INSERT同步线程会把这个大事务当作一个整体回放期间其他正常事务全部排队等待。一旦这个操作执行了十几分钟延迟会瞬间飙高等到事务提交复制线程才一下子恢复但积压的其他事务又会继续排队形成恶性循环。解决方法是强制分批同时把max_allowed_packet和slave_net_timeout调到一个合理的值避免一条超大 SQL 直接把网络层卡死。4.2 大表扫描拖垮生产我有一次在业务高峰期跑对比校验 SQL 写得太野一个GROUP BY没有走主键直接全表扫描加上 filesort把从库的 CPU 冲到 100%然后主库的写入照样进来从库越来越追不上。后来我养成了两个习惯第一所有对比 SQL 提前用EXPLAIN看执行计划确认走的是主键范围或索引范围第二利用pt-schema-change那类工具的限流思想自己给脚本加一个“每秒最大读取行数”的阈值比如默认每秒读 2 万行跑完一块睡一下。对比不是越快越好稳定不中断才是前提。4.3 自增列和外键约束连环炸修复缺失行的时候如果表有自增主键而且主库在这些记录插入之后又有大量新数据插入主库的自增值已经跳得很远了。这时你直接生成 INSERT 语句补到从库反而可能把从库的自增值拉高或者和后续复制过来的 INSERT 语句发生主键冲突SQL 线程直接停下。规范做法是修复前先对齐自增值在从库执行ALTER TABLE ... AUTO_INCREMENT n或者干脆在 INSERT 语句里显式写入主键值不用自增。对于外键修复顺序必须按照“先子表后父表”或“先删子表再删父表”的依赖关系来否则一个外键约束错误就会让整个修复脚本中断。4.4 伪装成差异的字符集问题比对结果出现大量假差异时先别急着修复。我踩过最隐蔽的一个坑是两个环境的表字符集不一致主库是utf8mb4从库是utf8同一个 emoji 字符在两边存储的字节完全不同CRC32 算出来当然不一样。还有排序规则collation不一致导致的重复键陷阱比如utf8mb4_general_ci和utf8mb4_bin对大小写和重音符号的处理不同明明看起来一样的数据唯一索引却认为不重复。所以跑差异对比前先花两分钟检查两边的表结构、字符集、排序规则、列是否允许NULL这几个配置没对齐后面所有的比对结果都不可信。5. 可复用的 SQL 片段和巡检建议最后分享几个我一直在用的脚本片段直接复制改库名表名就能跑。5.1 分块 CRC 校验 SQL 通用模板-- 参数库名、表名、主键字段名 SET tbl order_table; SET pk id; SET sql CONCAT( SELECT FLOOR(, pk, / 1000) chunk_id, COUNT(*) cnt,, BIT_XOR(CRC32(CONCAT_WS(#, , pk, , status, amount))) row_crc , FROM , tbl, GROUP BY chunk_id ); PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;上面的模板只用于单字段数字主键。如果是联合主键优先用CONCAT_WS拼出虚拟主键列再分块。注意BIT_XOR只能处理整数所以CRC32是必须的不能偷懒直接BIT_XOR原始字符串。5.2 生成修复 SQL 的脚本逻辑我的思路是先不直接在从库执行修复而是生成一个 .sql 文件人工 review 一遍再执行。逻辑用任意语言写都行但核心流程是一致的连接主库查询差异清单中对应主键的数据判断主键是否在从库存在在从库也查一次存在且值不一致生成UPDATE只更新值不一致的字段不存在生成INSERT显式带主键值主库不存在但从库存在生成DELETE并且单独输出到一个“高危待确认”文件。这个脚本里的每一步都要留操作日志包括操作时间、影响行数、对应的 binlog 位点。修数据这件事最怕的就是“修完了不知道改了什么”后面一旦还要二次回溯日志比记忆可靠得多。5.3 巡检脚本的落地建议数据一致性巡检我建议做成定时任务频率按业务重要程度来核心库每天一次一般库每周一次。巡检内容不只是跑 CRC 快照还包括# 主库和从库分别执行 mysql -h主机 -P端口 -u账号 -p密码 -e SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN (mysql,information_schema,performance_schema); 再配合检查从库状态SHOW SLAVE STATUS\G SELECT * FROM performance_schema.replication_applier_status_by_worker;重点看有没有报错、有没有 worker 线程卡住。巡检的产出是一张格式化的差异报表有差异才告警没有差异就静默结束不要每天给团队发一堆“一切正常”的邮件轰炸。最后一个建议把上面整套五步流程固化成团队的运维手册。遇到数据差异先跑一遍巡检脚本拿报表再按差异清单分类然后按修复方案分批执行不要每次都临时写脚本。我在实际维护中体会很深的一点是修数据最大的风险从来不是不会修而是修的时候不够稳。把每一步都变成可追溯、可回滚、可验证的操作比任何技巧都重要。
