pt-table-checksum数据一致性校验工具和pt-table-sync数据同步工具解析

本文链接：https://blog.youkuaiyun.com/zyz511919766/article/details/49307727

本文介绍了Percona Toolkit中的pt-table-checksum和pt-table-sync工具，用于MySQL主从数据库的数据一致性校验和修复。pt-table-checksum通过计算主从数据库的校验和来检测不一致，pt-table-sync则用于修复数据不一致。文章详细描述了这两个工具的使用方法、工作原理和注意事项，包括如何设置参数、如何处理大型表以及在存在不一致时的修复策略。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.背景

我的MySQL主从数据库运行了很长时间了，我现在不知道主从数据库中的数据是否一致？我该怎么办？该怎么验证主从库中的数据是否一致？如果我明确的知道主从数据库由于某次故障或者误操作已经存在数据不一致，那么我该怎么修复呢？需要使用主库数据重新搭一遍复制么？或者需要重新开始某个库或者表的复制么？相信很多MySQL DBA都提出过这些问题，或者自己动手实现过检测和恢复的功能。实际上大名鼎鼎的Percona-Toolkit工具套件中已经有两个特别重要的工具pt-table-checksum和pt-table-sync，两个工具分别用于实现主从复制关系中的主从库数据一致性验证和主从库数据不一致时的数据修复。这篇文章就来讲讲两个工具的使用方法，工作原理和一些重要的注意事项。

2.测试

2.1环境准备
首先搭建测试工具所需的主从复制环境
master :t3
slave :t3-2

slave上安装percona-toolkit
(其实安装在哪台机器上都无所谓，只要能连上指定的MySQL服务器即可)

yum install perl-IO-Socket-SSL
yum install perl-TermReadKey
yum install perl-DBD-mysql
yum install perl-Time-HiRes
rpm -ivh percona-toolkit-2.2.15-1.noarch.rpm

在主从数据库中均开启general log以观察使用一致性验证和修复工具时数据库中究竟会做一些什么动作

mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.00 sec)

2.2 pt-table-checksum工具测试
连接到主库t3执行验证：

pt-table-checksum h='t3',u='username',p='password',P=3306 -d mysql -t user --replicate=percona.checksums

Replica t3-2.dcfservice.com has binlog_format MIXED 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

其中h、u、p、P指定数据库连接信息-d指定要检测的数据库-t指定要检测的表，为了便于探索工具的使用方法和工作原理这里仅指定检测mysql.user表，–replicate指定用于存储检测结果的数据库和表。

看到工具报错，说t3-2.dcfservice.com上的MySQL使用了MIXED格式的binlog，可能导致复制中断，如果了解了其中的风险的话可以通过指定–no-check-binlog-format来跳过这个检测

查看t3中general log

| 2015-10-15 15:16:27 | [username] @  [host]     |        32 |        55 | Connect      | username@host on                                                                                                     |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | set autocommit=1                                                                                                         |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SELECT @@SQL_MODE                                                                                                        |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'                                                                          |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SET SESSION innodb_lock_wait_timeout=1                                                                                   |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SHOW VARIABLES LIKE 'wait\_timeout'                                                                                      |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SET SESSION wait_timeout=10000                                                                                           |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'*/ |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SELECT @@server_id /*!50038 , @@hostname*/                                                                               |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SELECT @@SQL_MODE                                                                                                        |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'                                          |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SHOW VARIABLES LIKE 'version%'                                                                                           |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SHOW ENGINES                                                                                                             |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SHOW VARIABLES LIKE 'innodb_version'                                                                                     |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SELECT @@binlog_format                                                                                                   |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | /*!50108 SET @@binlog_format := 'STATEMENT'*/                                                                            |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ                                                                  |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SHOW VARIABLES LIKE 'wsrep_on'                                                                                           |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SELECT @@SERVER_ID                                                                                                       |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SHOW GRANTS FOR CURRENT_USER()                                                                                           |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SHOW PROCESSLIST                                                                                                         |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SHOW VARIABLES LIKE 'wsrep_on'                                                                                           |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SELECT @@SERVER_ID                                                                                                       |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SHOW VARIABLES LIKE 'wsrep_on'                                                                                           |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SELECT @@SERVER_ID                                                                                                       |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SHOW VARIABLES LIKE 'version%'                                                                                           |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SHOW ENGINES                                                                                                             |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SHOW VARIABLES LIKE 'innodb_version'                                                                                     |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Query        | SELECT @@binlog_format                                                                                                   |
| 2015-10-15 15:16:27 | username[username] @  [host] |        32 |        55 | Quit         |

可以看到pt-table-checksum工具连接到主库执行了一些列查询和设置命令：包括查询SQL_MODE、服务器版本、InnoDB版本、ENGINES、用户权限、wsrep_on等，设置自动提交、session级别的InnoDB锁等待超时时间、SQL_MODE、SESSION级别的事务隔离级别、SESSION级别的binglog_format等。至于为什么要做这些参数的查询和设置会在后续的基本原理部分说明。由于检测到主从复制使用的日志格式非statement所以退出检测，general log到此为止。

修改命令，添加–no-check-binlog-format参数再次执行

pt-table-checksum h='t3',u='username',p='password',P=3306 -d mysql -t user --replicate=percona.checksums --no-check-binlog-format --replicate=percona.checksums
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
10-15T15:55:43      0      0        5       1       0   0.326 mysql.user

由于一切正常且当前主从不存在不一致所以结果看起来如上。其中TS是当前步骤发生的时间戳、ERRORS显示检测过程中发生的错误数、DIFFS显示是否存在不一致、ROW显示表的行数，CHUNKS显示表被划分的块数，SKIPPED为跳过的错误数，TIME为消耗的时间，TABLE为检测的表。

查看t3上的general log

| 2015-10-15 15:55:41 | [username] @  [host]     |        35 |        55 | Connect      | username@host on                                                                                                                   |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | set autocommit=1                                                                                                                       |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SELECT @@SQL_MODE                                                                                                                      |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'                                                                                        |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SET SESSION innodb_lock_wait_timeout=1                                                                                                 |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SHOW VARIABLES LIKE 'wait\_timeout'                                                                                                    |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SET SESSION wait_timeout=10000                                                                                                         |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'*/               |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SELECT @@server_id /*!50038 , @@hostname*/                                                                                             |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SELECT @@SQL_MODE                                                                                                                      |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'                                            |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SHOW VARIABLES LIKE 'version%'                                                                                                         |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SHOW ENGINES                                                                                                                           |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SHOW VARIABLES LIKE 'innodb_version'                                                                                                   |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SELECT @@binlog_format                                                                                                                 |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | /*!50108 SET @@binlog_format := 'STATEMENT'*/                                                                                          |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ                                                                                |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SHOW VARIABLES LIKE 'wsrep_on'                                                                                                         |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SELECT @@SERVER_ID                                                                                                                     |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SHOW GRANTS FOR CURRENT_USER()                                                                                                         |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SHOW PROCESSLIST                                                                                                                       |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SHOW VARIABLES LIKE 'wsrep_on'                                                                                                         |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SELECT @@SERVER_ID                                                                                                                     |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SHOW VARIABLES LIKE 'wsrep_on'                                                                                                         |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SELECT @@SERVER_ID                                                                                                                     |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SHOW DATABASES LIKE 'percona'                                                                                                          |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | CREATE DATABASE IF NOT EXISTS `percona` /* pt-table-checksum */                                                                        |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | USE `percona`                                                                                                                          |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SHOW TABLES FROM `percona` LIKE 'checksums'                                                                                            |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | CREATE TABLE IF NOT EXISTS `percona`.`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                                                                                                                                                                                                                    |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SHOW GLOBAL STATUS LIKE 'Threads_running'                                                                                              |
| 2015-10-15 15:55:41 | username[username] @  [host] |        35 |        55 | Query        | SELECT CONCAT(@@hostname, @@port)                                                                                                      |
| 2015-10-15 15:55:43 | username[username] @  [host] |        35 |        55 | Query        | SELECT CRC32('test-string')                                                                                                            |
| 2015-10-15 15:55:43 | username[username] @  [host] |        35 |        55 | Query        | SELECT CRC32('a')                                                                                                                      |
| 2015-10-15 15:55:43 | username[username] @  [host] |        35 |        55 | Query        | SELECT CRC32('a')                                                                                                                      |
| 2015-10-15 15:55:43 | username[username] @  [host] |        35 |        55 | Query        | SHOW VARIABLES LIKE 'wsrep_on'                                                                                                         |
| 2015-10-15 15:55:43 | username[username] @  [host] |        35 |        55 | Query        | SHOW DATABASES                                                                                                                         |
| 2015-10-15 15:55:43 | username[username] @  [host] |        35 |        55 | Query        | SHOW /*!50002 FULL*/ TABLES FROM `mysql`                                                                                               |
| 2015-10-15 15:55:43 | username[username] @  [host] |        35 |        55 | Query        | /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */     |
| 2015-10-15 15:55:43 | username[username] @  [host] |        35 |        55 | Query        | USE `mysql`                                                                                                                            |
| 2015-10-15 15:55:43 | username[username] @  [host] |        35 |        55 | Query        | SHOW CREATE TABLE `mysql`.`user`                                                                                                       |
| 2015-10-15 15:55:43 | username[username] @  [host] |        35 |        55 | Query        | /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */                                                     |
| 2015-10-15 15:55:43 | username[username] @  [host] |        35 |        55 | Query        | EXPLAIN SELECT * FROM `mysql`.`user` WHERE 1=1                                                                                         |
| 2015-10-15 15:55:43 | username[username] @  [host] |        35 |        55 | Query        | USE `percona`                                                                                                                          |
| 2015-10-15 15:55:43 | username[username] @  [host] |        35 |        55 | Query        | DELETE FROM `percona`.`checksums` WHERE db = 'mysql' AND tbl = 'user'                                                                  |
| 2015-10-15 15:55:43 | username[username] @  [host] |        35 |        55 | Query        | USE `mysql`                                                                                                                            |
| 2015-10-15 15:55:43 | username[username] @  [host] |        35 |        55 | Query        | EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `host`, `user`, `password`, `select_priv`, `insert_priv`, `update_priv`, `delete_priv`, `create_priv`, `drop_priv`, `reload_priv`, `shutdown_priv`, `process_priv`, `file_priv`, `grant_priv`, `references_priv`, `index_priv`, `alter_priv`, `show_db_priv`, `super_priv`, `create_tmp_table_priv`, `lock_tables_priv`, `execute_priv`, `repl_slave_priv`, `repl_client_priv`, `create_view_priv`, `show_view_priv`, `create_routine_priv`, `alter_routine_priv`, `create_user_priv`, `event_priv`, `trigger_priv`, `create_tablespace_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`, `plugin`, `authentication_string`, CONCAT(ISNULL(`plugin`), ISNULL(`authentication_string`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `mysql`.`user` /*explain checksum table*/                                                                                                        |
| 2015-10-15 15:55:43 | username[username] @  [host] |        35 |        55 | Query        | REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'mysql', 'user', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `host`, `user`, `password`, `select_priv`, `insert_priv`, `update_priv`, `delete_priv`, `create_priv`, `drop_priv`, `reload_priv`, `shutdown_priv`, `process_priv`, `file_priv`, `grant_priv`, `references_priv`, `index_priv`, `alter_priv`, `show_db_priv`, `super_priv`, `create_tmp_table_priv`, `lock_tables_priv`, `execute_priv`, `repl_slave_priv`, `repl_client_priv`, `create_view_priv`, `show_view_priv`, `create_routine_priv`, `alter_routine_priv`, `create_user_priv`, `event_priv`, `trigger_priv`, `create_tablespace_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`, `plugin`, `authentication_string`, CONCAT(ISNULL(`plugin`), ISNULL(`authentication_string`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `mysql`.`user` /*checksum table*/ |
| 2015-10-15 15:55:43 | username[username] @  [host] |        35 |        55 | Query        | SHOW WARNINGS                                                                                                                                               |
| 2015-10-15 15:55:43 | username[username] @  [host] |        35 |        55 | Query        | SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'mysql' AND tbl = 'user' AND chunk = '1'                                                    |
| 2015-10-15 15:55:43 | username[username] @  [host] |        35 |        55 | Query        | UPDATE `percona`.`checksums` SET chunk_time = '0.041358', master_crc = 'f62e5299', master_cnt = '5' WHERE db = 'mysql' AND tbl = 'user' AND chunk = '1'     |
| 2015-10-15 15:55:43 | username[username] @  [host] |        35 |        55 | Query        | SHOW GLOBAL STATUS LIKE 'Threads_running'                                                                                                                   |
| 2015-10-15 15:55:43 | username[username] @  [host] |        35 |        55 | Quit         |

这里除了变量的查看和设置外，还多了其他一些流程：创建数据库percona，并在改库下创建表checksums，表结构如下：

CREATE TABLE IF NOT EXISTS `percona`.`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

在主库和从库上分别查询该表
主库：

mysql> select * from percona.checksums;
+-------+------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| db    | tbl  | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts                  |
+-------+------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| mysql | user |     1 |   0.041358 | NULL        | NULL           | NULL           | f62e5299 |        5 | f62e5299   |          5 | 2015-10-15 15:55:43 |
+-------+------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
1 row in set (0.00 sec)

从库：

mysql> select * from percona.checksums;
+-------+------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| db    | tbl  | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts                  |
+-------+------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| mysql | user |     1 |   0.041358 | NULL        | NULL           | NULL           | f62e5299 |        5 | f62e5299   |          5 | 2015-10-15 15:55:43 |
+-------+------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
1 row in set (0.00 sec)

这里比较重要的是this_crc列和master_crc列，其中this_crc为本机上对应部分的校验码，master_crc为主机上对应部分的校验码，若在从机上两列的值不同则说明主从上对应的部分存在不一致。

除此之外还会查询正在运行的线程数、查询要检测的表的表结构、查看扫描表数据的SQL的执行计划以及生成校验信息的SQL的执行计划、清空原来存储校验信息的表并导入和更新新的校验数据。这里比较重要的是

SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `host`, `user`, `password`, `select_priv`, `insert_priv`, `update_priv`, `delete_priv`, `create_priv`, `drop_priv`, `reload_priv`, `shutdown_priv`, `process_priv`, `file_priv`, `grant_priv`, `references_priv`, `index_priv`, `alter_priv`, `show_db_priv`, `super_priv`, `create_tmp_table_priv`, `lock_tables_priv`, `execute_priv`, `repl_slave_priv`, `repl_client_priv`, `create_view_priv`, `show_view_priv`, `create_routine_priv`, `alter_routine_priv`, `create_user_priv`, `event_priv`, `trigger_priv`, `create_tablespace_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`, `plugin`, `authentication_string`, CONCAT(ISNULL(`plugin`), ISNULL(`authentication_string`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `mysql`.`user`

语句，该语句用于生成表行的一致性校验信息。

如若之前不太了解pt-table-checksum工具的使用和基本原理，那么对于上边说到的某些概念可能会比较含糊，这些会在后续部分加以说明。

我们人为创造一些不一致，选择一张比较大的表dcf_loan.t_loan_application,单表2.2G供89170行。不一致前先检测一次,由于表较大，可以看到pt-table-checksums打印出了处理进度，以便于让用户了解处理过程进行到了哪一步。另外因为表较大，因此被分割为了82个CHUNK来加以处理，而不像之前的小表mysql.user只有一个CHUNK，相应的大表的处理时间也有所增加，为68秒，检测过程并没有发生错误也没有检测出不一致。

pt-table-checksum h='t3',u='username',p='password',P=3306 -d dcf_loan -t t_loan_application --replicate=percona.checksums --no-check-binlog-format --replicate=percona.checksums
Checksumming dcf_loan.t_loan_application:  32% 01:01 remain
Checksumming dcf_loan.t_loan_application:  87% 00:08 remain
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
10-16T10:38:19      0      0    89398      82       0  68.774 dcf_loan.t_loan_application

现在人为制造一些不一致，在slave上dcf_loan.t_loan_application中删除loan_application_id最小和最大的一条记录以及另外一条记录，
LA120140520161304489
LA120140624175053075
LA220150722184051820

delete from dcf_loan.t_loan_application where loan_application_id in ('LA120140520161304489','LA120140624175053075','LA220150722184051820');

在master上清空一下日志表和校验结果表（以便于观察新的检测行为和结果）

mysql> truncate table mysql.general_log;
Query OK, 0 rows affected (0.03 sec)

mysql> truncate table percona.checksums;
Query OK, 0 rows affected (0.10 sec)

开始检测

pt-table-checksum h='t3',u='username',p='password',P=3306 -d dcf_loan -t t_loan_application --replicate=percona.checksums --no-check-binlog-format --replicate=percona.checksums；
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
10-16T11:08:14      0      3    89398       9       0  15.499 dcf_loan.t_loan_application

这次可以看到主从之间存在三处不一致。CHUNKS变化为了9而非上一次的82，说明CHUNK数的是动态计算的，检测时间也相应的缩短了（难道有利用上一次检测缓存下来的信息？），因而也没有打印处理进度。至于general_log中的内容，跟检测mysql.user时除了库和表不一样外并没太大的区别。最主要的区别是是这个表较大，可以从日志中看到工具将表根据主键分成了9个不同的部分，每部分分别加以处理，每一部分的处理过程一致，具体的处理步骤在日志文件中一目了然。

2015-10-16 11:13:58 [username] @  [host] 40   55   Connect   username@host on
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     set autocommit=1
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     SELECT @@SQL_MODE
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     SHOW VARIABLES LIKE 'innodb\\_lock_wait_timeout'
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     SET SESSION innodb_lock_wait_timeout=1
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     SHOW VARIABLES LIKE 'wait\\_timeout'
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     SET SESSION wait_timeout=10000
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'*/
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     SELECT @@server_id /*!50038 , @@hostname*/
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     SELECT @@SQL_MODE
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     SHOW VARIABLES LIKE 'version%'
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     SHOW ENGINES
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     SHOW VARIABLES LIKE 'innodb_version'
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     SELECT @@binlog_format
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     /*!50108 SET @@binlog_format := 'STATEMENT'*/
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     SHOW VARIABLES LIKE 'wsrep_on'
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     SELECT @@SERVER_ID
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     SHOW GRANTS FOR CURRENT_USER()
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     SHOW PROCESSLIST
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     SHOW VARIABLES LIKE 'wsrep_on'
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     SELECT @@SERVER_ID
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     SHOW VARIABLES LIKE 'wsrep_on'
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     SELECT @@SERVER_ID
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     SHOW DATABASES LIKE 'percona'
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     CREATE DATABASE IF NOT EXISTS `percona` /* pt-table-checksum */
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     USE `percona`
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     SHOW TABLES FROM `percona` LIKE 'checksums'
2015-10-16 11:13:58 username[username] @  [host]  40   55   Query     CREATE TABLE IF NOT EXISTS `percona`.`checksums` (\n     db             CHAR(64)     NOT NULL,\n     tbl            CHAR(64)     NOT NULL,\n     chunk          INT          NOT NULL,\n     chunk_time     FLOAT            NULL,\n     chunk_index    VARCHAR(200)     NULL,\n     lower_boundary TEXT             NULL,\n     upper_boundary TEXT             NULL,\n     this_crc       CHAR(40)     NOT NULL,\n     this_cnt       INT          NOT NULL,\n     master_crc     CHAR(40)         NULL,\n     master_cnt     INT              NULL,\n     ts             TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n     PRIMARY KEY (db, tbl, chunk),\n     INDEX ts_db_tbl (ts, db, tbl)\n  ) ENGINE=InnoDB
2015-10-16 11: