pt-table-checksum原理

mysql code tracer 2021-04-25

1824

导读

最近由于个人原因比较忙，公众号更新也比较慢，抱歉了各位。今天主要介绍的是pt-table-checksum的原理，相信使用过主从的同学对pt-table-checksum工具都不会陌生，可以说pt-table-checksum是主从校验使用最广泛的工具，帮助DBA同学完成主从数据的一致性校验。

实验过程

我们同样还是通过general_log的方式观察输出来看整个原理基于DSN方式的主从一致性校验

CREATE TABLE `dsns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `dsn` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO percona.dsns(dsn) VALUES ("h=127.0.0.1,P=3308,u=xucl,p=xuclxucl");

执行检查

pt-table-checksum h=127.0.0.1,u=xucl,p=xuclxucl,P=3307 -d sbtest --nocheck-replication-filters --replicate=pecona.checksums --no-check-binlog-format --recursion-method dsn=h=127.0.0.1,D=percona,t=dsns --chunk-time=2

检查结果输出：

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-16T10:36:23 0 0 2000 1 0 0.282 sbtest.sbtest1

master端输出

2019-05-16T02:36:23.477862Z 78 Connect xucl@127.0.0.1 on using TCP/IP
2019-05-16T02:36:23.478199Z 78 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
2019-05-16T02:36:23.480193Z 78 Query SET SESSION innodb_lock_wait_timeout=1
2019-05-16T02:36:23.480348Z 78 Query SHOW VARIABLES LIKE 'wait\_timeout'
2019-05-16T02:36:23.481635Z 78 Query SET SESSION wait_timeout=10000
2019-05-16T02:36:23.481780Z 78 Query SELECT @@SQL_MODE
2019-05-16T02:36:23.481892Z 78 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
2019-05-16T02:36:23.481997Z 78 Query SELECT @@server_id /*!50038 , @@hostname*/
2019-05-16T02:36:23.482100Z 78 Query SELECT @@SQL_MODE
2019-05-16T02:36:23.482179Z 78 Query SET SQL_MODE=',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
2019-05-16T02:36:23.482241Z 78 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2019-05-16T02:36:23.482404Z 78 Query SHOW VARIABLES LIKE 'wsrep_on'
2019-05-16T02:36:23.484356Z 79 Connect xucl@127.0.0.1 on percona using TCP/IP
2019-05-16T02:36:23.484552Z 79 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
2019-05-16T02:36:23.487098Z 79 Query SET SESSION innodb_lock_wait_timeout=1
2019-05-16T02:36:23.487209Z 79 Query SHOW VARIABLES LIKE 'wait\_timeout'
2019-05-16T02:36:23.488512Z 79 Query SET SESSION wait_timeout=10000
2019-05-16T02:36:23.488609Z 79 Query SELECT @@SQL_MODE
2019-05-16T02:36:23.488727Z 79 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
2019-05-16T02:36:23.488839Z 79 Query SELECT @@server_id /*!50038 , @@hostname*/
2019-05-16T02:36:23.488954Z 79 Query SHOW VARIABLES LIKE 'wsrep_on'
2019-05-16T02:36:23.490111Z 79 Query SELECT dsn FROM `percona`.`dsns` ORDER BY id
2019-05-16T02:36:23.496518Z 78 Query SHOW VARIABLES LIKE 'wsrep_on'
2019-05-16T02:36:23.498199Z 79 Quit
2019-05-16T02:36:23.498339Z 78 Query SELECT @@SERVER_ID
2019-05-16T02:36:23.499940Z 78 Query SHOW VARIABLES LIKE 'wsrep_on'
2019-05-16T02:36:23.501486Z 78 Query SELECT @@SERVER_ID
2019-05-16T02:36:23.503262Z 78 Query SHOW DATABASES LIKE 'pecona'
2019-05-16T02:36:23.503514Z 78 Query CREATE DATABASE IF NOT EXISTS `pecona` /* pt-table-checksum */
2019-05-16T02:36:23.507237Z 78 Query USE `pecona`
2019-05-16T02:36:23.507398Z 78 Query SHOW TABLES FROM `pecona` LIKE 'checksums'
2019-05-16T02:36:23.509290Z 78 Query CREATE TABLE IF NOT EXISTS `pecona`.`checksums` (
     db CHAR(64) NOT NULL,
     tbl CHAR(64) NOT NULL,
     chunk INT NOT NULL,
     chunk_time FLOAT NULL,
     chunk_index VARCHAR(200) NULL,
     lower_boundary TEXT NULL,
     upper_boundary TEXT NULL,
     this_crc CHAR(40) NOT NULL,
     this_cnt INT NOT NULL,
     master_crc CHAR(40) NULL,
     master_cnt INT NULL,
     ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     PRIMARY KEY (db, tbl, chunk),
     INDEX ts_db_tbl (ts, db, tbl)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
2019-05-16T02:36:23.512051Z 78 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
2019-05-16T02:36:23.513684Z 78 Query SELECT CONCAT(@@hostname, @@port)
2019-05-16T02:36:23.514134Z 78 Query SELECT CRC32('test-string')
2019-05-16T02:36:23.514242Z 78 Query SELECT CRC32('a')
2019-05-16T02:36:23.514338Z 78 Query SELECT CRC32('a')
2019-05-16T02:36:23.514483Z 78 Query SHOW VARIABLES LIKE 'wsrep_on'
2019-05-16T02:36:23.516169Z 78 Query SHOW DATABASES
2019-05-16T02:36:23.516513Z 78 Query SHOW /*!50002 FULL*/ TABLES FROM `sbtest`
2019-05-16T02:36:23.516758Z 78 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2019-05-16T02:36:23.516856Z 78 Query USE `sbtest`
2019-05-16T02:36:23.516959Z 78 Query SHOW CREATE TABLE `sbtest`.`sbtest1`
2019-05-16T02:36:23.517175Z 78 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2019-05-16T02:36:23.517798Z 78 Query EXPLAIN SELECT * FROM `sbtest`.`sbtest1` WHERE 1=1
2019-05-16T02:36:23.519522Z 78 Query USE `pecona`
2019-05-16T02:36:23.519610Z 78 Query DELETE FROM `pecona`.`checksums` WHERE db = 'sbtest' AND tbl = 'sbtest1'
2019-05-16T02:36:23.528668Z 78 Query USE `sbtest`
2019-05-16T02:36:23.529010Z 78 Query EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `k`, convert(`c` using utf8mb4), convert(`pad` using utf8mb4))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `sbtest`.`sbtest1` /*explain checksum table*/
2019-05-16T02:36:23.529512Z 78 Query REPLACE INTO `pecona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'sbtest', 'sbtest1', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `k`, convert(`c` using utf8mb4), convert(`pad` using utf8mb4))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `sbtest`.`sbtest1` /*checksum table*/
2019-05-16T02:36:23.539748Z 78 Query SHOW WARNINGS
2019-05-16T02:36:23.540106Z 78 Query SELECT this_crc, this_cnt FROM `pecona`.`checksums` WHERE db = 'sbtest' AND tbl = 'sbtest1' AND chunk = '1'
2019-05-16T02:36:23.540352Z 78 Query UPDATE `pecona`.`checksums` SET chunk_time = '0.009998', master_crc = '4fcda2f4', master_cnt = '2000' WHERE db = 'sbtest' AND tbl = 'sbtest1' AND chunk = '1'
2019-05-16T02:36:23.545813Z 78 Query SHOW GLOBAL STATUS LIKE 'Threads_running'

slave端输出

2019-05-16T02:36:23.491096Z 17 Connect xucl@127.0.0.1 on using TCP/IP
2019-05-16T02:36:23.491362Z 17 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
2019-05-16T02:36:23.493172Z 17 Query SET SESSION innodb_lock_wait_timeout=1
2019-05-16T02:36:23.493293Z 17 Query SHOW VARIABLES LIKE 'wait\_timeout'
2019-05-16T02:36:23.494579Z 17 Query SET SESSION wait_timeout=10000
2019-05-16T02:36:23.494714Z 17 Query SELECT @@SQL_MODE
2019-05-16T02:36:23.494840Z 17 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
2019-05-16T02:36:23.494965Z 17 Query SELECT @@server_id /*!50038 , @@hostname*/
2019-05-16T02:36:23.495111Z 17 Query SHOW VARIABLES LIKE 'wsrep_on'
2019-05-16T02:36:23.498482Z 17 Query SHOW VARIABLES LIKE 'wsrep_on'
2019-05-16T02:36:23.499775Z 17 Query SELECT @@SERVER_ID
2019-05-16T02:36:23.501646Z 17 Query SHOW VARIABLES LIKE 'wsrep_on'
2019-05-16T02:36:23.503046Z 17 Query SELECT @@SERVER_ID
2019-05-16T02:36:23.511087Z 7 Query CREATE DATABASE IF NOT EXISTS `pecona` /* pt-table-checksum */
2019-05-16T02:36:23.511646Z 17 Query SHOW TABLES FROM `pecona` LIKE 'checksums'
2019-05-16T02:36:23.513891Z 17 Query SELECT CONCAT(@@hostname, @@port)
2019-05-16T02:36:23.518381Z 17 Query SHOW TABLES FROM `sbtest` LIKE 'sbtest1'
2019-05-16T02:36:23.518588Z 17 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2019-05-16T02:36:23.518683Z 17 Query USE `sbtest`
2019-05-16T02:36:23.518778Z 17 Query SHOW CREATE TABLE `sbtest`.`sbtest1`
2019-05-16T02:36:23.518972Z 17 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2019-05-16T02:36:23.519277Z 17 Query EXPLAIN SELECT * FROM `sbtest`.`sbtest1` WHERE 1=1
2019-05-16T02:36:23.523595Z 7 Query CREATE TABLE IF NOT EXISTS `pecona`.`checksums` (
     db CHAR(64) NOT NULL,
     tbl CHAR(64) NOT NULL,
     chunk INT NOT NULL,
     chunk_time FLOAT NULL,
     chunk_index VARCHAR(200) NULL,
     lower_boundary TEXT NULL,
     upper_boundary TEXT NULL,
     this_crc CHAR(40) NOT NULL,
     this_cnt INT NOT NULL,
     master_crc CHAR(40) NULL,
     master_cnt INT NULL,
     ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     PRIMARY KEY (db, tbl, chunk),
     INDEX ts_db_tbl (ts, db, tbl)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
2019-05-16T02:36:23.528122Z 7 Query BEGIN
2019-05-16T02:36:23.528353Z 7 Query COMMIT /* implicit, from Xid_log_event */
2019-05-16T02:36:23.539094Z 7 Query BEGIN
2019-05-16T02:36:23.539273Z 7 Query COMMIT /* implicit, from Xid_log_event */
2019-05-16T02:36:23.544637Z 7 Query BEGIN
2019-05-16T02:36:23.544841Z 7 Query COMMIT /* implicit, from Xid_log_event */
2019-05-16T02:36:23.545055Z 17 Query SHOW SLAVE STATUS
2019-05-16T02:36:23.547796Z 17 Query SELECT MAX(chunk) FROM `pecona`.`checksums` WHERE db='sbtest' AND tbl='sbtest1' AND master_crc IS NOT NULL
2019-05-16T02:36:23.798654Z 17 Query SELECT MAX(chunk) FROM `pecona`.`checksums` WHERE db='sbtest' AND tbl='sbtest1' AND master_crc IS NOT NULL
2019-05-16T02:36:23.799409Z 17 Query SELECT CONCAT(db, '.', tbl) AS `table`, chunk, chunk_index, lower_boundary, upper_boundary, COALESCE(this_cnt-master_cnt, 0) AS cnt_diff, COALESCE(this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc), 0) AS crc_diff, this_cnt, master_cnt, this_crc, master_crc FROM `pecona`.`checksums` WHERE (master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)) AND (db='sbtest' AND tbl='sbtest1')
2019-05-16T02:36:23.803856Z 17 Quit

原理解读

整理成时序表格

每一步的过程都已经写在表格当中了，其中比较重要的地方就是以下几步

设置会话隔离级别为RR
设置当前会话binlog格式为statement格式
通过函数COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', id, k, convert(c using utf8mb4),convert(pad using utf8mb4))) AS UNSIGNED)), 10, 16)), 0)计算每个chunk的hash值
由于binlog格式为statement格式，因此binlog同步到slave以后以同样的函数进行计算hash值，如果是row格式的话，直接同步改变的行了，所以这里非常关键，也是这个工具的核心之处了
slave上计算完hash以后，直接比对master和slave上的crc值即可知道主从之间数据是否有差异

pt-table-sync是如何定位到差异数据的？

校验命令

pt-table-sync h=127.0.0.1,P=3307,u=xucl,p=xuclxucl,D=sbtest,t=sbtest1 h=127.0.0.1,P=3308,u=xucl,p=xuclxucl --replicate pecona.checksums --print

general_log输出

2019-05-16T06:48:45.658336Z 10757 Query SELECT @@binlog_format
2019-05-16T06:48:45.658449Z 10757 Query /*!50108 SET @@binlog_format := 'STATEMENT'*/
2019-05-16T06:48:45.658516Z 10757 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2019-05-16T06:48:45.659238Z 10758 Connect xucl@127.0.0.1 on sbtest using TCP/IP
2019-05-16T06:48:45.659363Z 10758 Query set autocommit=0
2019-05-16T06:48:45.659520Z 10758 Query SHOW VARIABLES LIKE 'wait\_timeout'
2019-05-16T06:48:45.661390Z 10758 Query SET SESSION wait_timeout=10000
2019-05-16T06:48:45.661493Z 10758 Query SELECT @@SQL_MODE
2019-05-16T06:48:45.661587Z 10758 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
2019-05-16T06:48:45.661700Z 10758 Query /*!40101 SET @@SQL_MODE := CONCAT(@@SQL_MODE, ',NO_AUTO_VALUE_ON_ZERO')*/
2019-05-16T06:48:45.661856Z 10758 Query SHOW VARIABLES LIKE 'version%'
2019-05-16T06:48:45.663373Z 10758 Query SHOW ENGINES
2019-05-16T06:48:45.663735Z 10758 Query SHOW VARIABLES LIKE 'innodb_version'
2019-05-16T06:48:45.665269Z 10758 Query SELECT @@binlog_format
2019-05-16T06:48:45.665370Z 10758 Query /*!50108 SET @@binlog_format := 'STATEMENT'*/
2019-05-16T06:48:45.665434Z 10758 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2019-05-16T06:48:45.678991Z 10757 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2019-05-16T06:48:45.679182Z 10757 Query USE `sbtest`
2019-05-16T06:48:45.679335Z 10757 Query SHOW CREATE TABLE `sbtest`.`sbtest1`
2019-05-16T06:48:45.679575Z 10757 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2019-05-16T06:48:45.686092Z 10757 Query SELECT MIN(`id`), MAX(`id`) FROM `sbtest`.`sbtest1` FORCE INDEX (`PRIMARY`)
2019-05-16T06:48:45.686469Z 10757 Query EXPLAIN SELECT * FROM `sbtest`.`sbtest1` FORCE INDEX (`PRIMARY`)
2019-05-16T06:48:45.694855Z 10757 Query SELECT CRC32('test-string')
2019-05-16T06:48:45.695271Z 10757 Query SELECT CRC32('a')
2019-05-16T06:48:45.695402Z 10757 Query SELECT CRC32('a')
2019-05-16T06:48:45.695707Z 10757 Query USE `sbtest`
2019-05-16T06:48:45.698193Z 10757 Query SET @crc := '', @cnt := 0
2019-05-16T06:48:45.698540Z 10757 Query commit
2019-05-16T06:48:45.698668Z 10757 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
2019-05-16T06:48:45.698816Z 10757 Query SELECT /*sbtest.sbtest1:1/1926*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `k`, `c`, `pad`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `sbtest`.`sbtest1` FORCE INDEX (`PRIMARY`) WHERE (`id` = 0) FOR UPDATE
2019-05-16T06:48:45.699165Z 10758 Query SHOW MASTER STATUS
2019-05-16T06:48:45.700075Z 10757 Query SET @crc := '', @cnt := 0
2019-05-16T06:48:45.700276Z 10757 Query commit
2019-05-16T06:48:45.700381Z 10757 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
2019-05-16T06:48:45.700476Z 10757 Query SELECT /*sbtest.sbtest1:2/1926*/ 1 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `k`, `c`, `pad`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `sbtest`.`sbtest1` FORCE INDEX (`PRIMARY`) WHERE (`id` > 0 AND `id` < '1042') FOR UPDATE
2019-05-16T06:48:45.705613Z 10758 Query SHOW MASTER STATUS
2019-05-16T06:48:45.711611Z 10757 Query SET @crc := '', @cnt := 0
2019-05-16T06:48:45.711934Z 10757 Query SELECT /*rows in chunk*/ `id`, `k`, `c`, `pad`, CRC32(CONCAT_WS('#', `id`, `k`, `c`, `pad`)) AS __crc FROM `sbtest`.`sbtest1` FORCE INDEX (`PRIMARY`) WHERE (`id` > 0 AND `id` < '1042') ORDER BY `id` FOR UPDATE
2019-05-16T06:48:45.739750Z 10757 Query SET @crc := '', @cnt := 0
2019-05-16T06:48:45.740137Z 10757 Query commit
2019-05-16T06:48:45.740274Z 10757 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
2019-05-16T06:48:45.740418Z 10757 Query SELECT /*sbtest.sbtest1:3/1926*/ 2 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `k`, `c`, `pad`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `sbtest`.`sbtest1` FORCE INDEX (`PRIMARY`) WHERE (`id` >= '1042' AND `id` < '2081') FOR UPDATE
重复同样的动作...

结果解读

前面两步，pt-table-sync与pt-table-checksum的算法和原理是一样的。再往下，就开始有所不同了：
pt-table-checksum只是校验，它把checksum结果存储到统计表，然后把执行过的sql语句记录到binlog，任务就算完成。语句级的复制把计算逻辑传递到从库，并且在从库执行相同的计算。
pt-table-sync则不同，它首先要完成chunk的checksum值计算，一旦发现主从上相同的chunk的checksum值不一样，就会深入到该chunk内部，逐行比较并且修复有问题的行。

它的计算逻辑描述如下：

对每一个从库，每一个表，循环进行如下校验和修复过程
对每一个chunk，校验时加上for update锁。一旦获得锁，就记录下当前主库的show master status值。
在从库上执行select masterposwait()函数，等待从库的sql线程执行到show master status得到位置。以此保证，主从上关于这个chunk的内容不再改变。【select masterposwait(‘masterlogfile’,’masterlogpos’，60);该函数会阻塞直到从服务器达到指定的日志文件和偏移量。此时从服务器和主服务器就同步了，语句返回值为0】.
对这个chunk执行checksum计算，然后与主库的checksum进行比较
如果checksum相同，说明主从数据一致，接着就可以继续下一个chunk
如果checksum值不同，说明该chunk有不一致。就会深入到chunk内部，逐行计算checksum并比较（单行checksum的比较过程与chunk的比较过程一样，单行实际是chunk的size等于1的特例）
如果发现某行不一致，则标记下来。继续检测剩余行，直到这个chunk结束。
对找到的主从不一致的行，采用replace into语句，在主库上执行一遍以生成该行全量的binlog，并同步到从库，这就会以主库数据为基准来修复从库；对于主库有的，而从库没有的行，采用replace into在主库上插入（注意，不能是insert。这分为两种情况：一是有唯一性主键，如果有唯一性主键或者索引，则insert相同记录会在主库上插入失败；二是没有唯一性主键或者索引，insert相同记录会造成记录重复。故要求pt-table-sync的表必须要有唯一性主键或者索引）
直到修复该chunk所有不一致的行。继续检查和修复下一个chunk。
直到这个从库上的所有表修复结束。接着继续修复下一个从库

总结

本文主要介绍了pt-table-checksum是如何校验主从数据的，另外也简单介绍了pt-table-sync如何定位到差异数据行的
由于需要设置binlog格式为statement，因此MGR环境无法通过pt-table-checksum工具完成校验

数据库

文章转载自mysql code tracer，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。