库一致的情况下./pt-table-checksum h=‘127.0.0.1‘,u=‘root‘,p=‘dh123‘,P=5331 -d db1 --recursion-method=processlist --no-check-binlog-format --replicate=test.checksumsSELECT db, tbl,lower_boundary,upper_boundary, this_cnt,master_cnt,this_crc,master_crc,ts FROM test.checksums ;+-----+--------+----------------+----------------+----------+------------+----------+------------+---------------------+| db | tbl | lower_boundary | upper_boundary | this_cnt | master_cnt | this_crc | master_crc | ts |+-----+--------+----------------+----------------+----------+------------+----------+------------+---------------------+| db1 | tab1_1 | 0 | 999 | 1000 | 1000 | dbd114dd | dbd114dd | 2013-06-25 13:02:37 || db1 | tab1_1 | 1000 | 9999 | 9000 | 9000 | 0 | 0 | 2013-06-25 13:02:37 || db1 | tab1_1 | NULL | 0 | 0 | 0 | 0 | 0 | 2013-06-25 13:02:37 || db1 | tab1_1 | 9999 | NULL | 0 | 0 | 0 | 0 | 2013-06-25 13:02:37 || db1 | tab1_2 | NULL | NULL | 1000 | 1000 | dbd114dd | dbd114dd | 2013-06-25 13:02:37 |+-----+--------+----------------+----------------+----------+------------+----------+------------+---------------------+
库不一致的情况下slavedelete from tab1_2 ;./pt-table-checksum h=‘127.0.0.1‘,u=‘root‘,p=‘dh123‘,P=5331 -d db1 --recursion-method=processlist --no-check-binlog-format --replicate=test.checksumsSELECT db, tbl,lower_boundary,upper_boundary, this_cnt,master_cnt,this_crc,master_crc,ts FROM test.checksums ;+-----+--------+----------------+----------------+----------+------------+----------+------------+---------------------+| db | tbl | lower_boundary | upper_boundary | this_cnt | master_cnt | this_crc | master_crc | ts |+-----+--------+----------------+----------------+----------+------------+----------+------------+---------------------+| db1 | tab1_1 | 0 | 999 | 1000 | 1000 | dbd114dd | dbd114dd | 2013-06-25 13:05:29 || db1 | tab1_1 | 1000 | 9999 | 9000 | 9000 | 0 | 0 | 2013-06-25 13:05:29 || db1 | tab1_1 | NULL | 0 | 0 | 0 | 0 | 0 | 2013-06-25 13:05:29 || db1 | tab1_1 | 9999 | NULL | 0 | 0 | 0 | 0 | 2013-06-25 13:05:29 || db1 | tab1_2 | NULL | NULL | 0 | 1000 | 0 | dbd114dd | 2013-06-25 13:05:30 |+-----+--------+----------------+----------------+----------+------------+----------+------------+---------------------+
常用的参数解释--check-replication-filters 检查复制中是否设置了过滤条件，如果设置了，程序将退出。可以使用--no-check-replication-filters禁用--recursion-method 发现slave使用的方式 通常使用 processlist ，或者手动指定dsn--max-lag 只有当复制的延迟小于这个范围的时候，才开始检查一致性，否则暂停--explain 只显示checksum的查询语句，但是不执行，对于理解checksum非常有用。--chunk-time 调整chunk的大小，似的每次chunk的执行时间小于这个值--no-check-binlog-format 不对binlog的格式进行检查查询哪些表的结果是不一致的sqlSELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks FROM test.checksums WHERE ( master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)) GROUP BY db, tbl;
常见的错误及其解决方法Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information.是指找不到slave，检查可能会无效。需要指定 --recursion-method=processlistReplica centos-1 has binlog_format ROW which could cause pt-table-checksum to break replication. Please read "Replicas using row-based replication" in the LIMITATIONS section of the tool‘s documentation. If you understand the risks, specify --no-check-binlog-format to disable this check.指定--no-check-binlog-format跳过binlog format的检查06-25T13:01:11 Cannot checksum table test.t: There is no good index and the table is oversized. at ./pt-table-checksum line 6370.表上没有主键或者唯一索引06-25T13:08:44 --replicate table `test`.`checksums` does not exist and --no-create-replicate-table was specified. You need to create the table.replicate 表不存在的情况下，不能指定--no-create-replicate-table。指定了 --replicate-check-only 的情况下，也需要replicate 表存在。
没有复制关系的两个实例之间同步数据./pt-table-sync --execute h=‘127.0.0.1‘,u=‘root‘,p=‘dh123‘,P=5331 h=‘192.168.12.201‘,u=‘root‘,p=‘dh123‘,P=5332 --database db1
连接到master上，同步所有checksums发现的不一致的slave数据pt-table-sync --execute --replicate test.checksums h=‘127.0.0.1‘,u=‘root‘,p=‘dh123‘,P=5331,D=db1,t=tab1_2
连接到slave上，执行同步master上的数据操作[root@centos-1 bin]# ./pt-table-sync --execute --sync-to-master h=‘127.0.0.1‘,u=‘root‘,p=‘dh123‘,P=5331 -d db1 --verbose# Syncing P=5331,h=127.0.0.1,p=...,u=root# DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE# 0 0 0 0 Chunk 14:04:31 14:04:31 0 db1.tab1_1# 0 1000 0 0 Chunk 14:04:31 14:04:32 2 db1.tab1_2重新执行checksum，可以看到数据已经同步过来了。SELECT db, tbl,lower_boundary,upper_boundary, this_cnt,master_cnt,this_crc,master_crc,ts FROM test.checksums ;+-----+--------+----------------+----------------+----------+------------+----------+------------+---------------------+| db | tbl | lower_boundary | upper_boundary | this_cnt | master_cnt | this_crc | master_crc | ts |+-----+--------+----------------+----------------+----------+------------+----------+------------+---------------------+| db1 | tab1_1 | 0 | 999 | 1000 | 1000 | dbd114dd | dbd114dd | 2013-06-25 14:06:38 || db1 | tab1_1 | 1000 | 9999 | 9000 | 9000 | 0 | 0 | 2013-06-25 14:06:38 || db1 | tab1_1 | NULL | 0 | 0 | 0 | 0 | 0 | 2013-06-25 14:06:38 || db1 | tab1_1 | 9999 | NULL | 0 | 0 | 0 | 0 | 2013-06-25 14:06:38 || db1 | tab1_2 | NULL | NULL | 1000 | 1000 | dbd114dd | dbd114dd | 2013-06-25 14:06:38 |+-----+--------+----------------+----------------+----------+------------+----------+------------+---------------------+
常用的参数解释--sync-to-master 将数据和master上保持一致--dry-run 干跑 不实际执行--execute 执行执行--bin-log 记录binlog--replicate
常见的错误及其解决方法[root@centos-0 bin]# ./pt-table-sync --execute h=‘127.0.0.1‘,u=‘root‘,p=‘dh123‘,P=5331,D=db1,t=tab1_1 h=‘192.168.12.201‘,u=‘root‘,p=‘dh123‘,P=5331Can‘t make changes on D=db1,P=5331,h=192.168.12.201,p=...,t=tab1_1,u=root because it‘s a slave. See the documentation section ‘REPLICATION SAFETY‘ for solutions to this problem. at ./pt-table-sync line 10612. while doing db1.tab1_1 on 192.168.12.201两个实例是复制关系 ，为了保证数据的安全性，而报的错
pt-table-checksum 和 pt-table-sync 的使用
原文：http://www.blogjava.net/xiaomage234/archive/2014/11/21/420419.html