Mysql复制数据一致性检查2

修复数据不一致

使用 pt-table-sync 来进行数据不一致修复

pt-table-sync synchronizes data efficiently between MySQL tables. This tool changes data, so for maximum safety, you should back up your data before using it . When synchronizing a server that is a replication slave with the –replicate or –sync-to-master methods, it always makes the changes on the replication master, never the replication slave directly . This is in general the only safe way to bring a replica back in sync with its master; changes to the replica are usually the source of the problems in the first place. However, the changes it makes on the master should be no-op changes that set the data to their current values, and actually affect only the replica.

pt-table-sync 是有可能改变数据的，所以要非常慎重，三思而后行

它并非在slave上执行操作，修改不一致数据，而是在master上重新插入当前值，通过同步而影响slave，从而解决数据的不一致(指定用户需要足读写权限)

Note: pt-table-sync is mature, proven in the real world, and well tested, but if used improperly it can have adverse consequences. Always test syncing first with --dry-run and --print.

[mysql@replication-check-vm ~]$ pt-table-sync --replicate test.checksum  h=slave-vm,u=root --ask-pass  --sync-to-master --databases=abc_test_db  --tables=users  --print > /tmp/users.sql
Enter password for slave-vm:       
[mysql@replication-check-vm ~]$ 

一般我都会使用 --print 将结果导出到一个文本中，查看修改的内容进行确认，然后使用--execute自动执行，或手动执行其中部分SQL

执行完修改后，再重复上面的操作检查一次

这个工具好在，业务在线时可以执行，不会对系统造成很大影响(会产生一定量的读IO，但不会产生有明显业务影响的锁)，特别是在大表，核心表数据一致性检查时太能解渴了

原文地址