对比MySQL表数据内容方式汇总

一. mysql自带的checksum命令: 可在不同服务器通过checksum值对比两张表是否一致, 加上EXTENDED参数表示逐行扫描, 结果更为可靠; 执行过程会给表加上System lock, 参考:https://dev.mysql.com/doc/refman/5.7/en/checksum-table.html

mysql> checksum table item_sku EXTENDED;
+---------------+------------+
| Table        | Checksum   |
+---------------+------------+
| test.item_sku | 3046185714 |
+---------------+------------+
1 row in set (15.53 sec)

mysql> select count(1) from item_sku;
+----------+
| count(1) |
+----------+
| 5912619  |
+----------+
1 row in set (3.04 sec)

可通过对比checksum值来判断表是否一致; 粒度比较粗, 不能具体知道哪些数据不一致. 其优点是速度较快, 可先用checksum判断表是否一致, 然后再用其他工具进一步检查数据内容.

二. 常用pt工具能高效地检查主从一致性, 但前提是有主从关系
主: *.*.*.39
从: *.*.*.43

pt-table-checksum \
    -h*.*.*.39 \
    -uroot \
    -p123456 \
    --databases=test \
    --tables=item_sku \    
    --no-check-binlog-format \
--nocheck-replication-filters   

pt-table-sync \
    --replicate=percona.checksums \
    h=*.*.*.39,u=root,p=123456,D=test,t=item_sku \
    h=*.*.*.43,u=root,p=123456 \
    --charset=utf8 \
    --print

--print 即可生成修复脚本; 该工具效率比较高, 每次仅对chunk加读锁, 能生成修补数据的脚本.
pt工具足够强大不多说, 但以下情形不适合使用:
1)两台服务器无主从关系
2)有主从关系, 但只想检查主从数据差异, 不希望数据开始复制带来负面影响
3)有主从关系, 但有复制延迟或主库压力过大

三. 常见图形工具SQLyog提供了Data Comparison, 配置好源和目标的连接, 选好需要对比的表, 就可以开始对比了. 对比好后, 可选出数据不一致的内容, 然后生成相应的修复脚本

注意: 当发现一个数据不同的时候, 会停止运行, 需要点击Next Difference才能把脚本完全整理出来, 不太方便.
图形工具SQLyog效率非常高, 仅次于pt工具. 但有限制: 当主键大小写敏感的时候, 不能比较, 出现如下错误; 且该工具为图形界面, 不提供命令行支持, 所以认为其通用性低

四. Red-gate提供了很多SQL Server的工具, 发现它的MySQL Data Compare也是可以对比的. 没有用过SQL Server会比较陌生, 地址:http://www.red-gate.com/products/mysql/mysql-data-compare/
选好想要对比的两个数据库:

选择需要对比的表:

开始对比:

在对比的同时, 去mysql观察其原理: 把表拉回本地, 程序内部对数据逐行对比, 对比过程会执行以下语句.

会看到相当多的时间耗在Writing to net, 可增大max_allowed_packet 值提高网络传输效率; 对比结束后, 可以看到如下结果

然后点击Deployment Wizard就可以生成修复数据的脚本了

用了几次之后, 会卡在Planning Deployment几个小时才出结果; 毕竟是免费版本, 按官方步骤操作没发序列号过来. 官方称: The MySQL tools now benefit from a free edition for non-commercial use. We are, however, unable to provide support for this edition. 介绍该工具只为多掌握一种检查数据的方法, 仅供参考

五. mysqldbcompare来自mysql官方工具utilities, 但实际上各方面几乎最差, 是笔者最不推荐的一个:

原理: 在给定的两台服务器上, 分别串行分组校验, 注意到是一台服务器校验完, 再到下一台服务器校验, 效率极其低下, 但效率最低; 而且只是2G的表, 对比过程中就莫名其妙的被kill掉, 而且不能指定检查某张表

下面是5种对比表数据内容工具的总结:

转自: http://120.76.165.139/WordPress-master/index.php/2017/02/17/mysql-data-comparison/