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

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:13:58 username[username] @ [host] 40 55 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query SELECT CONCAT(@@hostname, @@port)
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query SELECT CRC32('test-string')
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query SELECT CRC32('a')
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query SELECT CRC32('a')
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 SHOW DATABASES
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query SHOW /*!50002 FULL*/ TABLES FROM `dcf_loan`
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query USE `dcf_loan`
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query SHOW CREATE TABLE `dcf_loan`.`t_loan_application`
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query EXPLAIN SELECT * FROM `dcf_loan`.`t_loan_application` WHERE 1=1
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query SELECT /*!40001 SQL_NO_CACHE */ `loan_application_id` FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) ORDER BY `loan_application_id` LIMIT 1 /*first lower boundary*/
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query SELECT /*!40001 SQL_NO_CACHE */ `loan_application_id` FROM `dcf_loan`.`t_loan_application` FORCE INDEX (`PRIMARY`) WHERE `loan_application_id` IS NOT NULL ORDER BY `loan_application_id` LIMIT 1 /*key_len*/
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ * FROM `dcf_loan`.`t_loan_application` FORCE INDEX (`PRIMARY`) WHERE `loan_application_id` >= 'LA120140520161304489' /*key_len*/
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 DELETE FROM `percona`.`checksums` WHERE db = 'dcf_loan' AND tbl = 't_loan_application'
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query USE `dcf_loan`
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `loan_application_id` FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA120140520161304489')) ORDER BY `loan_application_id` LIMIT 999, 2 /*next chunk boundary*/
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query SELECT /*!40001 SQL_NO_CACHE */ `loan_application_id` FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA120140520161304489')) ORDER BY `loan_application_id` LIMIT 999, 2 /*next chunk boundary*/
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, UNIX_TIMESTAMP(`apply_start_time`), UNIX_TIMESTAMP(`apply_end_time`), UNIX_TIMESTAMP(`apply_loan_due_date`), UNIX_TIMESTAMP(`apply_ar_due_date`), UNIX_TIMESTAMP(`apply_buyback_due_date`), `digital_sign`, `digital_sign_version`, `digital_by`, UNIX_TIMESTAMP(`expected_loan_date`), UNIX_TIMESTAMP(`apply_date`), `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, UNIX_TIMESTAMP(`create_time`), `created_by`, UNIX_TIMESTAMP(`update_time`), `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA120140520161304489')) AND ((`loan_application_id` <= 'LA120140623154518472')) /*explain checksum chunk*/
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'dcf_loan', 't_loan_application', '1', 'PRIMARY', 'LA120140520161304489', 'LA120140623154518472', COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, UNIX_TIMESTAMP(`apply_start_time`), UNIX_TIMESTAMP(`apply_end_time`), UNIX_TIMESTAMP(`apply_loan_due_date`), UNIX_TIMESTAMP(`apply_ar_due_date`), UNIX_TIMESTAMP(`apply_buyback_due_date`), `digital_sign`, `digital_sign_version`, `digital_by`, UNIX_TIMESTAMP(`expected_loan_date`), UNIX_TIMESTAMP(`apply_date`), `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, UNIX_TIMESTAMP(`create_time`), `created_by`, UNIX_TIMESTAMP(`update_time`), `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA120140520161304489')) AND ((`loan_application_id` <= 'LA120140623154518472')) /*checksum chunk*/
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query SHOW WARNINGS
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'dcf_loan' AND tbl = 't_loan_application' AND chunk = '1'
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query UPDATE `percona`.`checksums` SET chunk_time = '0.028912', master_crc = '989b5d09', master_cnt = '1000' WHERE db = 'dcf_loan' AND tbl = 't_loan_application' AND chunk = '1'
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `loan_application_id` FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA120140623154521327')) ORDER BY `loan_application_id` LIMIT 17292, 2 /*next chunk boundary*/
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query SELECT /*!40001 SQL_NO_CACHE */ `loan_application_id` FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA120140623154521327')) ORDER BY `loan_application_id` LIMIT 17292, 2 /*next chunk boundary*/
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, UNIX_TIMESTAMP(`apply_start_time`), UNIX_TIMESTAMP(`apply_end_time`), UNIX_TIMESTAMP(`apply_loan_due_date`), UNIX_TIMESTAMP(`apply_ar_due_date`), UNIX_TIMESTAMP(`apply_buyback_due_date`), `digital_sign`, `digital_sign_version`, `digital_by`, UNIX_TIMESTAMP(`expected_loan_date`), UNIX_TIMESTAMP(`apply_date`), `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, UNIX_TIMESTAMP(`create_time`), `created_by`, UNIX_TIMESTAMP(`update_time`), `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA120140623154521327')) AND ((`loan_application_id` <= 'LA120150215233222930')) /*explain checksum chunk*/
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'dcf_loan', 't_loan_application', '2', 'PRIMARY', 'LA120140623154521327', 'LA120150215233222930', COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, UNIX_TIMESTAMP(`apply_start_time`), UNIX_TIMESTAMP(`apply_end_time`), UNIX_TIMESTAMP(`apply_loan_due_date`), UNIX_TIMESTAMP(`apply_ar_due_date`), UNIX_TIMESTAMP(`apply_buyback_due_date`), `digital_sign`, `digital_sign_version`, `digital_by`, UNIX_TIMESTAMP(`expected_loan_date`), UNIX_TIMESTAMP(`apply_date`), `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, UNIX_TIMESTAMP(`create_time`), `created_by`, UNIX_TIMESTAMP(`update_time`), `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA120140623154521327')) AND ((`loan_application_id` <= 'LA120150215233222930')) /*checksum chunk*/
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query SHOW WARNINGS
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'dcf_loan' AND tbl = 't_loan_application' AND chunk = '2'
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query UPDATE `percona`.`checksums` SET chunk_time = '0.469923', master_crc = '39bb33a2', master_cnt = '17293' WHERE db = 'dcf_loan' AND tbl = 't_loan_application' AND chunk = '2'
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `loan_application_id` FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA120150215233258303')) ORDER BY `loan_application_id` LIMIT 18350, 2 /*next chunk boundary*/
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query SELECT /*!40001 SQL_NO_CACHE */ `loan_application_id` FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA120150215233258303')) ORDER BY `loan_application_id` LIMIT 18350, 2 /*next chunk boundary*/
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, UNIX_TIMESTAMP(`apply_start_time`), UNIX_TIMESTAMP(`apply_end_time`), UNIX_TIMESTAMP(`apply_loan_due_date`), UNIX_TIMESTAMP(`apply_ar_due_date`), UNIX_TIMESTAMP(`apply_buyback_due_date`), `digital_sign`, `digital_sign_version`, `digital_by`, UNIX_TIMESTAMP(`expected_loan_date`), UNIX_TIMESTAMP(`apply_date`), `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, UNIX_TIMESTAMP(`create_time`), `created_by`, UNIX_TIMESTAMP(`update_time`), `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA120150215233258303')) AND ((`loan_application_id` <= 'LA120150731021959182')) /*explain checksum chunk*/
2015-10-16 11:13:58 username[username] @ [host] 40 55 Query REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'dcf_loan', 't_loan_application', '3', 'PRIMARY', 'LA120150215233258303', 'LA120150731021959182', COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, UNIX_TIMESTAMP(`apply_start_time`), UNIX_TIMESTAMP(`apply_end_time`), UNIX_TIMESTAMP(`apply_loan_due_date`), UNIX_TIMESTAMP(`apply_ar_due_date`), UNIX_TIMESTAMP(`apply_buyback_due_date`), `digital_sign`, `digital_sign_version`, `digital_by`, UNIX_TIMESTAMP(`expected_loan_date`), UNIX_TIMESTAMP(`apply_date`), `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, UNIX_TIMESTAMP(`create_time`), `created_by`, UNIX_TIMESTAMP(`update_time`), `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA120150215233258303')) AND ((`loan_application_id` <= 'LA120150731021959182')) /*checksum chunk*/
2015-10-16 11:13:59 username[username] @ [host] 40 55 Query SHOW WARNINGS
2015-10-16 11:13:59 username[username] @ [host] 40 55 Query SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'dcf_loan' AND tbl = 't_loan_application' AND chunk = '3'
2015-10-16 11:13:59 username[username] @ [host] 40 55 Query UPDATE `percona`.`checksums` SET chunk_time = '0.554547', master_crc = 'b6275fa7', master_cnt = '18351' WHERE db = 'dcf_loan' AND tbl = 't_loan_application' AND chunk = '3'
2015-10-16 11:13:59 username[username] @ [host] 40 55 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
2015-10-16 11:13:59 username[username] @ [host] 40 55 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `loan_application_id` FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA120150731070038802')) ORDER BY `loan_application_id` LIMIT 17265, 2 /*next chunk boundary*/
2015-10-16 11:13:59 username[username] @ [host] 40 55 Query SELECT /*!40001 SQL_NO_CACHE */ `loan_application_id` FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA120150731070038802')) ORDER BY `loan_application_id` LIMIT 17265, 2 /*next chunk boundary*/
2015-10-16 11:13:59 username[username] @ [host] 40 55 Query EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, UNIX_TIMESTAMP(`apply_start_time`), UNIX_TIMESTAMP(`apply_end_time`), UNIX_TIMESTAMP(`apply_loan_due_date`), UNIX_TIMESTAMP(`apply_ar_due_date`), UNIX_TIMESTAMP(`apply_buyback_due_date`), `digital_sign`, `digital_sign_version`, `digital_by`, UNIX_TIMESTAMP(`expected_loan_date`), UNIX_TIMESTAMP(`apply_date`), `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, UNIX_TIMESTAMP(`create_time`), `created_by`, UNIX_TIMESTAMP(`update_time`), `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA120150731070038802')) AND ((`loan_application_id` <= 'LA120150829085718336')) /*explain checksum chunk*/
2015-10-16 11:13:59 username[username] @ [host] 40 55 Query REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'dcf_loan', 't_loan_application', '4', 'PRIMARY', 'LA120150731070038802', 'LA120150829085718336', COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, UNIX_TIMESTAMP(`apply_start_time`), UNIX_TIMESTAMP(`apply_end_time`), UNIX_TIMESTAMP(`apply_loan_due_date`), UNIX_TIMESTAMP(`apply_ar_due_date`), UNIX_TIMESTAMP(`apply_buyback_due_date`), `digital_sign`, `digital_sign_version`, `digital_by`, UNIX_TIMESTAMP(`expected_loan_date`), UNIX_TIMESTAMP(`apply_date`), `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, UNIX_TIMESTAMP(`create_time`), `created_by`, UNIX_TIMESTAMP(`update_time`), `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA120150731070038802')) AND ((`loan_application_id` <= 'LA120150829085718336')) /*checksum chunk*/
2015-10-16 11:14:00 username[username] @ [host] 40 55 Query SHOW WARNINGS
2015-10-16 11:14:00 username[username] @ [host] 40 55 Query SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'dcf_loan' AND tbl = 't_loan_application' AND chunk = '4'
2015-10-16 11:14:00 username[username] @ [host] 40 55 Query UPDATE `percona`.`checksums` SET chunk_time = '0.523829', master_crc = '4d282a49', master_cnt = '17266' WHERE db = 'dcf_loan' AND tbl = 't_loan_application' AND chunk = '4'
2015-10-16 11:14:00 username[username] @ [host] 40 55 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
2015-10-16 11:14:00 username[username] @ [host] 40 55 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `loan_application_id` FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA120150829085718530')) ORDER BY `loan_application_id` LIMIT 16927, 2 /*next chunk boundary*/
2015-10-16 11:14:00 username[username] @ [host] 40 55 Query SELECT /*!40001 SQL_NO_CACHE */ `loan_application_id` FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA120150829085718530')) ORDER BY `loan_application_id` LIMIT 16927, 2 /*next chunk boundary*/
2015-10-16 11:14:00 username[username] @ [host] 40 55 Query EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, UNIX_TIMESTAMP(`apply_start_time`), UNIX_TIMESTAMP(`apply_end_time`), UNIX_TIMESTAMP(`apply_loan_due_date`), UNIX_TIMESTAMP(`apply_ar_due_date`), UNIX_TIMESTAMP(`apply_buyback_due_date`), `digital_sign`, `digital_sign_version`, `digital_by`, UNIX_TIMESTAMP(`expected_loan_date`), UNIX_TIMESTAMP(`apply_date`), `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, UNIX_TIMESTAMP(`create_time`), `created_by`, UNIX_TIMESTAMP(`update_time`), `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA120150829085718530')) AND ((`loan_application_id` <= 'LA220150121150455557')) /*explain checksum chunk*/
2015-10-16 11:14:00 username[username] @ [host] 40 55 Query REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'dcf_loan', 't_loan_application', '5', 'PRIMARY', 'LA120150829085718530', 'LA220150121150455557', COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, UNIX_TIMESTAMP(`apply_start_time`), UNIX_TIMESTAMP(`apply_end_time`), UNIX_TIMESTAMP(`apply_loan_due_date`), UNIX_TIMESTAMP(`apply_ar_due_date`), UNIX_TIMESTAMP(`apply_buyback_due_date`), `digital_sign`, `digital_sign_version`, `digital_by`, UNIX_TIMESTAMP(`expected_loan_date`), UNIX_TIMESTAMP(`apply_date`), `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, UNIX_TIMESTAMP(`create_time`), `created_by`, UNIX_TIMESTAMP(`update_time`), `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA120150829085718530')) AND ((`loan_application_id` <= 'LA220150121150455557')) /*checksum chunk*/
2015-10-16 11:14:00 username[username] @ [host] 40 55 Query SHOW WARNINGS
2015-10-16 11:14:00 username[username] @ [host] 40 55 Query SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'dcf_loan' AND tbl = 't_loan_application' AND chunk = '5'
2015-10-16 11:14:00 username[username] @ [host] 40 55 Query UPDATE `percona`.`checksums` SET chunk_time = '0.496078', master_crc = '59d8dc67', master_cnt = '16928' WHERE db = 'dcf_loan' AND tbl = 't_loan_application' AND chunk = '5'
2015-10-16 11:14:00 username[username] @ [host] 40 55 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
2015-10-16 11:14:00 username[username] @ [host] 40 55 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `loan_application_id` FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA220150121150455585')) ORDER BY `loan_application_id` LIMIT 16974, 2 /*next chunk boundary*/
2015-10-16 11:14:00 username[username] @ [host] 40 55 Query SELECT /*!40001 SQL_NO_CACHE */ `loan_application_id` FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA220150121150455585')) ORDER BY `loan_application_id` LIMIT 16974, 2 /*next chunk boundary*/
2015-10-16 11:14:00 username[username] @ [host] 40 55 Query EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, UNIX_TIMESTAMP(`apply_start_time`), UNIX_TIMESTAMP(`apply_end_time`), UNIX_TIMESTAMP(`apply_loan_due_date`), UNIX_TIMESTAMP(`apply_ar_due_date`), UNIX_TIMESTAMP(`apply_buyback_due_date`), `digital_sign`, `digital_sign_version`, `digital_by`, UNIX_TIMESTAMP(`expected_loan_date`), UNIX_TIMESTAMP(`apply_date`), `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, UNIX_TIMESTAMP(`create_time`), `created_by`, UNIX_TIMESTAMP(`update_time`), `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA220150121150455585')) AND ((`loan_application_id` <= 'LA220150707092922092')) /*explain checksum chunk*/
2015-10-16 11:14:00 username[username] @ [host] 40 55 Query REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'dcf_loan', 't_loan_application', '6', 'PRIMARY', 'LA220150121150455585', 'LA220150707092922092', COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, UNIX_TIMESTAMP(`apply_start_time`), UNIX_TIMESTAMP(`apply_end_time`), UNIX_TIMESTAMP(`apply_loan_due_date`), UNIX_TIMESTAMP(`apply_ar_due_date`), UNIX_TIMESTAMP(`apply_buyback_due_date`), `digital_sign`, `digital_sign_version`, `digital_by`, UNIX_TIMESTAMP(`expected_loan_date`), UNIX_TIMESTAMP(`apply_date`), `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, UNIX_TIMESTAMP(`create_time`), `created_by`, UNIX_TIMESTAMP(`update_time`), `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA220150121150455585')) AND ((`loan_application_id` <= 'LA220150707092922092')) /*checksum chunk*/
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query SHOW WARNINGS
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'dcf_loan' AND tbl = 't_loan_application' AND chunk = '6'
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query UPDATE `percona`.`checksums` SET chunk_time = '0.487775', master_crc = '2409c873', master_cnt = '16975' WHERE db = 'dcf_loan' AND tbl = 't_loan_application' AND chunk = '6'
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `loan_application_id` FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA220150707092922144')) ORDER BY `loan_application_id` LIMIT 17108, 2 /*next chunk boundary*/
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query SELECT /*!40001 SQL_NO_CACHE */ `loan_application_id` FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA220150707092922144')) ORDER BY `loan_application_id` LIMIT 17108, 2 /*next chunk boundary*/
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query SELECT /*!40001 SQL_NO_CACHE */ `loan_application_id` FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) ORDER BY `loan_application_id` DESC LIMIT 1 /*last upper boundary*/
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, UNIX_TIMESTAMP(`apply_start_time`), UNIX_TIMESTAMP(`apply_end_time`), UNIX_TIMESTAMP(`apply_loan_due_date`), UNIX_TIMESTAMP(`apply_ar_due_date`), UNIX_TIMESTAMP(`apply_buyback_due_date`), `digital_sign`, `digital_sign_version`, `digital_by`, UNIX_TIMESTAMP(`expected_loan_date`), UNIX_TIMESTAMP(`apply_date`), `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, UNIX_TIMESTAMP(`create_time`), `created_by`, UNIX_TIMESTAMP(`update_time`), `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA220150707092922144')) AND ((`loan_application_id` <= 'LA220150722184051820')) /*explain checksum chunk*/
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'dcf_loan', 't_loan_application', '7', 'PRIMARY', 'LA220150707092922144', 'LA220150722184051820', COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, UNIX_TIMESTAMP(`apply_start_time`), UNIX_TIMESTAMP(`apply_end_time`), UNIX_TIMESTAMP(`apply_loan_due_date`), UNIX_TIMESTAMP(`apply_ar_due_date`), UNIX_TIMESTAMP(`apply_buyback_due_date`), `digital_sign`, `digital_sign_version`, `digital_by`, UNIX_TIMESTAMP(`expected_loan_date`), UNIX_TIMESTAMP(`apply_date`), `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, UNIX_TIMESTAMP(`create_time`), `created_by`, UNIX_TIMESTAMP(`update_time`), `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` >= 'LA220150707092922144')) AND ((`loan_application_id` <= 'LA220150722184051820')) /*checksum chunk*/
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query SHOW WARNINGS
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'dcf_loan' AND tbl = 't_loan_application' AND chunk = '7'
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query UPDATE `percona`.`checksums` SET chunk_time = '0.050861', master_crc = 'dd5d3c3', master_cnt = '1585' WHERE db = 'dcf_loan' AND tbl = 't_loan_application' AND chunk = '7'
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query EXPLAIN SELECT COUNT(*), '0' FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` < 'LA120140520161304489')) ORDER BY `loan_application_id` /*explain past lower chunk*/
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'dcf_loan', 't_loan_application', '8', 'PRIMARY', NULL, 'LA120140520161304489', COUNT(*), '0' FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` < 'LA120140520161304489')) ORDER BY `loan_application_id` /*past lower chunk*/
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query SHOW WARNINGS
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'dcf_loan' AND tbl = 't_loan_application' AND chunk = '8'
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query UPDATE `percona`.`checksums` SET chunk_time = '0.001877', master_crc = '0', master_cnt = '0' WHERE db = 'dcf_loan' AND tbl = 't_loan_application' AND chunk = '8'
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query EXPLAIN SELECT COUNT(*), '0' FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` > 'LA220150722184051820')) ORDER BY `loan_application_id` /*explain past upper chunk*/
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'dcf_loan', 't_loan_application', '9', 'PRIMARY', 'LA220150722184051820', NULL, COUNT(*), '0' FROM `dcf_loan`.`t_loan_application` FORCE INDEX(`PRIMARY`) WHERE ((`loan_application_id` > 'LA220150722184051820')) ORDER BY `loan_application_id` /*past upper chunk*/
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query SHOW WARNINGS
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'dcf_loan' AND tbl = 't_loan_application' AND chunk = '9'
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query UPDATE `percona`.`checksums` SET chunk_time = '0.001531', master_crc = '0', master_cnt = '0' WHERE db = 'dcf_loan' AND tbl = 't_loan_application' AND chunk = '9'
2015-10-16 11:14:01 username[username] @ [host] 40 55 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
2015-10-16 11:14:12 username[username] @ [host] 40 55 Quit

表被分成的CHUNK以及处理的结果可从percona.checksums中方便的查出。

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 |
+----------+--------------------+-------+------------+-------------+----------------------+----------------------+----------+----------+------------+------------+---------------------+
| dcf_loan | t_loan_application |     1 |   0.028912 | PRIMARY     | LA120140520161304489 | LA120140623154518472 | 1bad6aff |      999 | 989b5d09   |       1000 | 2015-10-16 11:13:58 |
| dcf_loan | t_loan_application |     2 |   0.469923 | PRIMARY     | LA120140623154521327 | LA120150215233222930 | fdd8658c |    17292 | 39bb33a2   |      17293 | 2015-10-16 11:13:58 |
| dcf_loan | t_loan_application |     3 |   0.554547 | PRIMARY     | LA120150215233258303 | LA120150731021959182 | b6275fa7 |    18351 | b6275fa7 |      18351 | 2015-10-16 11:13:59 |
| dcf_loan | t_loan_application |     4 |   0.523829 | PRIMARY     | LA120150731070038802 | LA120150829085718336 | 4d282a49 |    17266 | 4d282a49   |      17266 | 2015-10-16 11:14:00 |
| dcf_loan | t_loan_application |     5 |   0.496078 | PRIMARY     | LA120150829085718530 | LA220150121150455557 | 59d8dc67 |    16928 | 59d8dc67   |      16928 | 2015-10-16 11:14:00 |
| dcf_loan | t_loan_application |     6 |   0.487775 | PRIMARY     | LA220150121150455585 | LA220150707092922092 | 2409c873 |    16975 | 2409c873   |      16975 | 2015-10-16 11:14:01 |
| dcf_loan | t_loan_application |     7 |   0.050861 | PRIMARY     | LA220150707092922144 | LA220150722184051820 | 2f490750 |     1584 | dd5d3c3 |       1585 | 2015-10-16 11:14:01 |
| dcf_loan | t_loan_application |     8 |   0.001877 | PRIMARY     | NULL                 | LA120140520161304489 | 0        |        0 | 0          |          0 | 2015-10-16 11:14:01 |
| dcf_loan | t_loan_application |     9 |   0.001531 | PRIMARY     | LA220150722184051820 | NULL                 | 0        |        0 | 0          |          0 | 2015-10-16 11:14:01 |
+----------+--------------------+-------+------------+-------------+----------------------+----------------------+----------+----------+------------+------------+---------------------+
9 rows in set (0.00 sec)

可以看到，表被根据主键（有时候会是唯一索引）被分成了九个部分（既没有主键又无唯一索引且表很大时可能导致表无法检测），每个部分的序号、处理时间、上下边界、在本机和主机上计算所得的crc值、行数等信息均可清晰的获取到。

需要从表中查看那些快存在不一致的话也很容易，只需在slave上执行，且指明this_crc<> master_crc的条件即可。实际上更准确的条件为 WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)，更通用的查询为：

SELECT db, tbl, 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 percona.checksums WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)）
mysql> select * from percona.checksums where this_crc<> master_crc;
+----------+--------------------+-------+------------+-------------+----------------------+----------------------+----------+----------+------------+------------+---------------------+
| db | tbl | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts |
+----------+--------------------+-------+------------+-------------+----------------------+----------------------+----------+----------+------------+------------+---------------------+
| dcf_loan | t_loan_application |     1 |   0.028912 | PRIMARY     | LA120140520161304489 | LA120140623154518472 | 1bad6aff |      999 | 989b5d09   |       1000 | 2015-10-16 11:13:58 |
| dcf_loan | t_loan_application |     2 |   0.469923 | PRIMARY     | LA120140623154521327 | LA120150215233222930 | fdd8658c |    17292 | 39bb33a2   |      17293 | 2015-10-16 11:13:58 |
| dcf_loan | t_loan_application |     7 |   0.050861 | PRIMARY     | LA220150707092922144 | LA220150722184051820 | 2f490750 |     1584 | dd5d3c3 |       1585 | 2015-10-16 11:14:01 |
+----------+--------------------+-------+------------+-------------+----------------------+----------------------+----------+----------+------------+------------+---------------------+
3 rows in set (0.00 sec)

2.3 pt-table-sync测试
主从存在不一致的话这个时候就需要使用pt-table-sync来修复，我们可以使用上述同样的测试流程来观察pt-table-sync的行为

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

这里=只清空general_log表而保留percona.checksums表，因为pt-table-sync要依据此表中的信息来修复主从表的不一致

pt-table-sync --sync-to-master --replicate=percona.checksums --charset=utf8 h=t3-2,u=username,p=password,P=3306 -d dcf_loan -t t_loan_application --check-triggers --foreign-key-checks --unique-checks --print

其中 –sync-to-master指明与master同步（可自动发现主从复制关系），也可不使用该参数而分别使用前后两个连接串来表明主从连接信息。–replicate指明修复时需要参照的pt-check-sum生成的表。character指明字符集，h、u、p、P指明从库连接信息，-d和-t分别用于指明要修复的库表。 –check-triggers、 –foreign-key-checks、–unique-checks用于检测触发器、外键、唯一索引等情况，–print指明只是打印出将要执行的语句而非真正执行。若要实际执行需使用–execute替换–print。可以看到打印出了三条REPLACE INTO语句（出于篇幅部分字段的内容被省略掉了）

REPLACE INTO `dcf_loan`.`t_loan_application`(`loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, `apply_start_time`, `apply_end_time`, `apply_loan_due_date`, `apply_ar_due_date`, `apply_buyback_due_date`, `digital_sign`, `digital_sign_version`, `digital_by`, `expected_loan_date`, `apply_date`, `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, `create_time`, `created_by`, `update_time`, `updated_by`, `delete_flag`) VALUES ('LA120140520161304489', ...) /*percona-toolkit src_db:dcf_loan src_tbl:t_loan_application src_dsn:A=utf8,P=3306,h=t3.dcfservice.com,p=...,u=username dst_db:dcf_loan dst_tbl:t_loan_application dst_dsn:A=utf8,P=3306,h=t3-2,p=...,u=username lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:29649 user:username host:t3-2.dcfservice.com*/;
REPLACE INTO `dcf_loan`.`t_loan_application`(`loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, `apply_start_time`, `apply_end_time`, `apply_loan_due_date`, `apply_ar_due_date`, `apply_buyback_due_date`, `digital_sign`, `digital_sign_version`, `digital_by`, `expected_loan_date`, `apply_date`, `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, `create_time`, `created_by`, `update_time`, `updated_by`, `delete_flag`) VALUES ('LA120140624175053075', ...) /*percona-toolkit src_db:dcf_loan src_tbl:t_loan_application src_dsn:A=utf8,P=3306,h=t3.dcfservice.com,p=...,u=username dst_db:dcf_loan dst_tbl:t_loan_application dst_dsn:A=utf8,P=3306,h=t3-2,p=...,u=username lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:29649 user:username host:t3-2.dcfservice.com*/;
REPLACE INTO `dcf_loan`.`t_loan_application`(`loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, `apply_start_time`, `apply_end_time`, `apply_loan_due_date`, `apply_ar_due_date`, `apply_buyback_due_date`, `digital_sign`, `digital_sign_version`, `digital_by`, `expected_loan_date`, `apply_date`, `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, `create_time`, `created_by`, `update_time`, `updated_by`, `delete_flag`) VALUES ('LA220150722184051820', ...) /*percona-toolkit src_db:dcf_loan src_tbl:t_loan_application src_dsn:A=utf8,P=3306,h=t3.dcfservice.com,p=...,u=username dst_db:dcf_loan dst_tbl:t_loan_application dst_dsn:A=utf8,P=3306,h=t3-2,p=...,u=username lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:29649 user:username host:t3-2.dcfservice.com*/;

slave上查看执行pt-table-sync后的general log

2015-10-16 15:22:35 [username] @  [host] 2882 56   Connect   username@host on
2015-10-16 15:22:35 username[username] @  [host]  2882 56   Query     set autocommit=0
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SELECT @@SQL_MODE
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query /*!40101 SET NAMES "utf8"*/
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SHOW VARIABLES LIKE 'wait\\_timeout'
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SET SESSION wait_timeout=10000
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'*/
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query /*!40101 SET @@SQL_MODE := CONCAT(@@SQL_MODE, ',NO_AUTO_VALUE_ON_ZERO')*/
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SHOW VARIABLES LIKE 'version%'
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SHOW ENGINES
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SHOW VARIABLES LIKE 'innodb_version'
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SELECT @@binlog_format
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query /*!50108 SET @@binlog_format := 'STATEMENT'*/
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SHOW SLAVE STATUS
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SHOW SLAVE STATUS
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SELECT CONCAT(@@hostname, @@port)
2015-10-16 15:22:35 [username] @ [host] 2883 56 Connect username@host on
2015-10-16 15:22:35 username[username] @ [host] 2883 56 Query set autocommit=0
2015-10-16 15:22:35 username[username] @ [host] 2883 56 Query SELECT @@SQL_MODE
2015-10-16 15:22:35 username[username] @ [host] 2883 56 Query /*!40101 SET NAMES "utf8"*/
2015-10-16 15:22:35 username[username] @ [host] 2883 56 Query SHOW VARIABLES LIKE 'wait\\_timeout'
2015-10-16 15:22:35 username[username] @ [host] 2883 56 Query SET SESSION wait_timeout=10000
2015-10-16 15:22:35 username[username] @ [host] 2883 56 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'*/
2015-10-16 15:22:35 username[username] @ [host] 2883 56 Query /*!40101 SET @@SQL_MODE := CONCAT(@@SQL_MODE, ',NO_AUTO_VALUE_ON_ZERO')*/
2015-10-16 15:22:35 username[username] @ [host] 2883 56 Query SHOW VARIABLES LIKE 'version%'
2015-10-16 15:22:35 username[username] @ [host] 2883 56 Query SHOW ENGINES
2015-10-16 15:22:35 username[username] @ [host] 2883 56 Query SHOW VARIABLES LIKE 'innodb_version'
2015-10-16 15:22:35 username[username] @ [host] 2883 56 Query SELECT @@binlog_format
2015-10-16 15:22:35 username[username] @ [host] 2883 56 Query /*!50108 SET @@binlog_format := 'STATEMENT'*/
2015-10-16 15:22:35 username[username] @ [host] 2883 56 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SELECT db, tbl, 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 percona.checksums WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SHOW TABLES FROM `dcf_loan` LIKE 't\\_loan\\_application'
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SHOW VARIABLES LIKE 'version%'
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SHOW ENGINES
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SHOW VARIABLES LIKE 'innodb_version'
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SHOW TRIGGERS FROM `dcf_loan`
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2015-10-16 15:22:35 [] @ [] 1 56 Query DROP TABLE IF EXISTS `__maatkit_char_chunking_map` /* generated by server */
2015-10-16 15:22:35 [] @ [] 1 56 Query BEGIN
2015-10-16 15:22:35 [] @ [] 1 56 Query CREATE TEMPORARY TABLE `dcf_loan`.`__maatkit_char_chunking_map` ( `loan_application_id` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '融资申请ID') ENGINE=MEMORY
2015-10-16 15:22:35 [] @ [] 1 56 Query COMMIT
2015-10-16 15:22:35 [] @ [] 1 56 Query BEGIN
2015-10-16 15:22:35 [] @ [] 1 56 Query INSERT INTO `dcf_loan`.`__maatkit_char_chunking_map` VALUES (CHAR('76'))
2015-10-16 15:22:35 [] @ [] 1 56 Query COMMIT
2015-10-16 15:22:35 [] @ [] 1 56 Query DROP TEMPORARY TABLE `__maatkit_char_chunking_map` /* generated by server */
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SELECT CRC32('test-string')
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query USE `dcf_loan`
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SET @crc := '', @cnt := 0
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query commit
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SELECT MASTER_POS_WAIT('t3-bin.000009', 97042179, 60)
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SELECT /*dcf_loan.t_loan_application:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, `apply_start_time` + 0, `apply_end_time` + 0, `apply_loan_due_date` + 0, `apply_ar_due_date` + 0, `apply_buyback_due_date` + 0, `digital_sign`, `digital_sign_version`, `digital_by`, `expected_loan_date` + 0, `apply_date` + 0, `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, `create_time` + 0, `created_by`, `update_time` + 0, `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX (`PRIMARY`) WHERE (1=1) AND ((((`loan_application_id` >= 'LA120140520161304489')) AND ((`loan_application_id` <= 'LA120140623154518472')))) LOCK IN SHARE MODE
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SET @crc := '', @cnt := 0
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SELECT /*rows in chunk*/ `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, `apply_start_time` + 0 AS `apply_start_time`, `apply_end_time` + 0 AS `apply_end_time`, `apply_loan_due_date` + 0 AS `apply_loan_due_date`, `apply_ar_due_date` + 0 AS `apply_ar_due_date`, `apply_buyback_due_date` + 0 AS `apply_buyback_due_date`, `digital_sign`, `digital_sign_version`, `digital_by`, `expected_loan_date` + 0 AS `expected_loan_date`, `apply_date` + 0 AS `apply_date`, `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, `create_time` + 0 AS `create_time`, `created_by`, `update_time` + 0 AS `update_time`, `updated_by`, `delete_flag`, CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, `apply_start_time` + 0, `apply_end_time` + 0, `apply_loan_due_date` + 0, `apply_ar_due_date` + 0, `apply_buyback_due_date` + 0, `digital_sign`, `digital_sign_version`, `digital_by`, `expected_loan_date` + 0, `apply_date` + 0, `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, `create_time` + 0, `created_by`, `update_time` + 0, `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS __crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX (`PRIMARY`) WHERE (1=1) AND (((`loan_application_id` >= 'LA120140520161304489')) AND ((`loan_application_id` <= 'LA120140623154518472'))) ORDER BY `loan_application_id` LOCK IN SHARE MODE
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query commit
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SHOW TABLES FROM `dcf_loan` LIKE 't\\_loan\\_application'
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SHOW TRIGGERS FROM `dcf_loan`
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2015-10-16 15:22:35 [] @ [] 1 56 Query DROP TABLE IF EXISTS `__maatkit_char_chunking_map` /* generated by server */
2015-10-16 15:22:35 [] @ [] 1 56 Query BEGIN
2015-10-16 15:22:35 [] @ [] 1 56 Query CREATE TEMPORARY TABLE `dcf_loan`.`__maatkit_char_chunking_map` ( `loan_application_id` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '融资申请ID') ENGINE=MEMORY
2015-10-16 15:22:35 [] @ [] 1 56 Query COMMIT
2015-10-16 15:22:35 [] @ [] 1 56 Query BEGIN
2015-10-16 15:22:35 [] @ [] 1 56 Query INSERT INTO `dcf_loan`.`__maatkit_char_chunking_map` VALUES (CHAR('76'))
2015-10-16 15:22:35 [] @ [] 1 56 Query COMMIT
2015-10-16 15:22:35 [] @ [] 1 56 Query DROP TEMPORARY TABLE `__maatkit_char_chunking_map` /* generated by server */
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SELECT CRC32('test-string')
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query USE `dcf_loan`
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SET @crc := '', @cnt := 0
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query commit
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SELECT MASTER_POS_WAIT('t3-bin.000009', 97043127, 60)
2015-10-16 15:22:35 username[username] @ [host] 2882 56 Query SELECT /*dcf_loan.t_loan_application:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, `apply_start_time` + 0, `apply_end_time` + 0, `apply_loan_due_date` + 0, `apply_ar_due_date` + 0, `apply_buyback_due_date` + 0, `digital_sign`, `digital_sign_version`, `digital_by`, `expected_loan_date` + 0, `apply_date` + 0, `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, `create_time` + 0, `created_by`, `update_time` + 0, `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX (`PRIMARY`) WHERE (1=1) AND ((((`loan_application_id` >= 'LA120140623154521327')) AND ((`loan_application_id` <= 'LA120141222101815314')))) LOCK IN SHARE MODE
2015-10-16 15:22:36 username[username] @ [host] 2882 56 Query SET @crc := '', @cnt := 0
2015-10-16 15:22:36 username[username] @ [host] 2882 56 Query SELECT /*rows in chunk*/ `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, `apply_start_time` + 0 AS `apply_start_time`, `apply_end_time` + 0 AS `apply_end_time`, `apply_loan_due_date` + 0 AS `apply_loan_due_date`, `apply_ar_due_date` + 0 AS `apply_ar_due_date`, `apply_buyback_due_date` + 0 AS `apply_buyback_due_date`, `digital_sign`, `digital_sign_version`, `digital_by`, `expected_loan_date` + 0 AS `expected_loan_date`, `apply_date` + 0 AS `apply_date`, `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, `create_time` + 0 AS `create_time`, `created_by`, `update_time` + 0 AS `update_time`, `updated_by`, `delete_flag`, CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, `apply_start_time` + 0, `apply_end_time` + 0, `apply_loan_due_date` + 0, `apply_ar_due_date` + 0, `apply_buyback_due_date` + 0, `digital_sign`, `digital_sign_version`, `digital_by`, `expected_loan_date` + 0, `apply_date` + 0, `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, `create_time` + 0, `created_by`, `update_time` + 0, `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS __crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX (`PRIMARY`) WHERE (1=1) AND (((`loan_application_id` >= 'LA120140623154521327')) AND ((`loan_application_id` <= 'LA120141222101815314'))) ORDER BY `loan_application_id` LOCK IN SHARE MODE
2015-10-16 15:22:37 username[username] @ [host] 2882 56 Query commit
2015-10-16 15:22:37 username[username] @ [host] 2882 56 Query SHOW TABLES FROM `dcf_loan` LIKE 't\\_loan\\_application'
2015-10-16 15:22:37 username[username] @ [host] 2882 56 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2015-10-16 15:22:37 username[username] @ [host] 2882 56 Query SHOW TRIGGERS FROM `dcf_loan`
2015-10-16 15:22:37 username[username] @ [host] 2882 56 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2015-10-16 15:22:37 [] @ [] 1 56 Query DROP TABLE IF EXISTS `__maatkit_char_chunking_map` /* generated by server */
2015-10-16 15:22:37 [] @ [] 1 56 Query BEGIN
2015-10-16 15:22:37 [] @ [] 1 56 Query CREATE TEMPORARY TABLE `dcf_loan`.`__maatkit_char_chunking_map` ( `loan_application_id` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '融资申请ID') ENGINE=MEMORY
2015-10-16 15:22:37 [] @ [] 1 56 Query COMMIT
2015-10-16 15:22:37 [] @ [] 1 56 Query BEGIN
2015-10-16 15:22:37 [] @ [] 1 56 Query INSERT INTO `dcf_loan`.`__maatkit_char_chunking_map` VALUES (CHAR('76'))
2015-10-16 15:22:37 [] @ [] 1 56 Query COMMIT
2015-10-16 15:22:37 [] @ [] 1 56 Query DROP TEMPORARY TABLE `__maatkit_char_chunking_map` /* generated by server */
2015-10-16 15:22:37 username[username] @ [host] 2882 56 Query SELECT CRC32('test-string')
2015-10-16 15:22:37 username[username] @ [host] 2882 56 Query USE `dcf_loan`
2015-10-16 15:22:37 username[username] @ [host] 2882 56 Query SET @crc := '', @cnt := 0
2015-10-16 15:22:37 username[username] @ [host] 2882 56 Query commit
2015-10-16 15:22:38 username[username] @ [host] 2882 56 Query SELECT MASTER_POS_WAIT('t3-bin.000009', 97044075, 60)
2015-10-16 15:22:38 username[username] @ [host] 2882 56 Query SELECT /*dcf_loan.t_loan_application:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, `apply_start_time` + 0, `apply_end_time` + 0, `apply_loan_due_date` + 0, `apply_ar_due_date` + 0, `apply_buyback_due_date` + 0, `digital_sign`, `digital_sign_version`, `digital_by`, `expected_loan_date` + 0, `apply_date` + 0, `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, `create_time` + 0, `created_by`, `update_time` + 0, `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX (`PRIMARY`) WHERE (1=1) AND ((((`loan_application_id` >= 'LA220150413152201183')) AND ((`loan_application_id` <= 'LA220150722184051820')))) LOCK IN SHARE MODE
2015-10-16 15:22:38 username[username] @ [host] 2882 56 Query SET @crc := '', @cnt := 0
2015-10-16 15:22:38 username[username] @ [host] 2882 56 Query SELECT /*rows in chunk*/ `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, `apply_start_time` + 0 AS `apply_start_time`, `apply_end_time` + 0 AS `apply_end_time`, `apply_loan_due_date` + 0 AS `apply_loan_due_date`, `apply_ar_due_date` + 0 AS `apply_ar_due_date`, `apply_buyback_due_date` + 0 AS `apply_buyback_due_date`, `digital_sign`, `digital_sign_version`, `digital_by`, `expected_loan_date` + 0 AS `expected_loan_date`, `apply_date` + 0 AS `apply_date`, `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, `create_time` + 0 AS `create_time`, `created_by`, `update_time` + 0 AS `update_time`, `updated_by`, `delete_flag`, CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, `apply_start_time` + 0, `apply_end_time` + 0, `apply_loan_due_date` + 0, `apply_ar_due_date` + 0, `apply_buyback_due_date` + 0, `digital_sign`, `digital_sign_version`, `digital_by`, `expected_loan_date` + 0, `apply_date` + 0, `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, `create_time` + 0, `created_by`, `update_time` + 0, `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS __crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX (`PRIMARY`) WHERE (1=1) AND (((`loan_application_id` >= 'LA220150413152201183')) AND ((`loan_application_id` <= 'LA220150722184051820'))) ORDER BY `loan_application_id` LOCK IN SHARE MODE
2015-10-16 15:22:40 username[username] @ [host] 2882 56 Query commit
2015-10-16 15:22:40 username[username] @ [host] 2882 56 Query commit
2015-10-16 15:22:40 username[username] @ [host] 2882 56 Quit
2015-10-16 15:22:40 username[username] @ [host] 2883 56 Query commit
2015-10-16 15:22:40 username[username] @ [host] 2883 56 Quit

可以看到多了一些额外的命令：如设置字符编码，查看salve状态，查询存在不一致的表、CHUNK，查看触发器等。另外，这里将SESSION级别的自动提交禁用掉了，改用手工提交的方式，并且在执行修复前会执行SELECT MASTER_POS_WAIT(‘t3-bin.000009’, 97042179, 60)以阻塞后续的操作直至salve到达函数中指定的binlog位置，该位置可从show slave status或者show master status中获取。过程中还创建了内存临时表 dcf_loan.__maatkit_char_chunking_map ( loan_application_id varchar(50) COLLATE utf8_bin NOT NULL COMMENT ‘融资申请ID’) ENGINE=MEMORY 包含待检测的表的主键。因为检测到三处不一致，所以pt-table-sync会分别在存在不一致的CHUNK的上下边界区间内以只读模式锁定这些记录，逐行比较以发现存在不一致的行并试图进行修复和校验。关于这个阶段为什么执行这两条SQL我们暂不去讨论，先看一下正式执行修复时会发生些什么。

同样，先清空general_log

truncate table general_log;

这次把–print换成–execute也就是真正的执行修复

pt-table-sync --sync-to-master --replicate=percona.checksums --charset=utf8 h=t3-2,u=username,p=password,P=3306 -d dcf_loan -t t_loan_application --check-triggers --foreign-key-checks --unique-checks --execute

2015-10-19 14:07:06 [username] @  [host] 8138 56   Connect   username@host on
2015-10-19 14:07:06 username[username] @  [host]  8138 56   Query     set autocommit=0
2015-10-19 14:07:06 username[username] @ [host] 8138 56 Query SELECT @@SQL_MODE
2015-10-19 14:07:06 username[username] @ [host] 8138 56 Query /*!40101 SET NAMES "utf8"*/
2015-10-19 14:07:06 username[username] @ [host] 8138 56 Query SHOW VARIABLES LIKE 'wait\\_timeout'
2015-10-19 14:07:06 username[username] @ [host] 8138 56 Query SET SESSION wait_timeout=10000
2015-10-19 14:07:06 username[username] @ [host] 8138 56 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'*/
2015-10-19 14:07:06 username[username] @ [host] 8138 56 Query /*!40101 SET @@SQL_MODE := CONCAT(@@SQL_MODE, ',NO_AUTO_VALUE_ON_ZERO')*/
2015-10-19 14:07:06 username[username] @ [host] 8138 56 Query SHOW VARIABLES LIKE 'version%'
2015-10-19 14:07:06 username[username] @ [host] 8138 56 Query SHOW ENGINES
2015-10-19 14:07:06 username[username] @ [host] 8138 56 Query SHOW VARIABLES LIKE 'innodb_version'
2015-10-19 14:07:06 username[username] @ [host] 8138 56 Query SELECT @@binlog_format
2015-10-19 14:07:06 username[username] @ [host] 8138 56 Query /*!50108 SET @@binlog_format := 'STATEMENT'*/
2015-10-19 14:07:06 username[username] @ [host] 8138 56 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2015-10-19 14:07:06 username[username] @ [host] 8138 56 Query SHOW SLAVE STATUS
2015-10-19 14:07:06 username[username] @ [host] 8138 56 Query SHOW SLAVE STATUS
2015-10-19 14:07:06 username[username] @ [host] 8138 56 Query SELECT CONCAT(@@hostname, @@port)
2015-10-19 14:07:06 [username] @ [host] 8139 56 Connect username@host on
2015-10-19 14:07:06 username[username] @ [host] 8139 56 Query set autocommit=0
2015-10-19 14:07:06 username[username] @ [host] 8139 56 Query SELECT @@SQL_MODE
2015-10-19 14:07:06 username[username] @ [host] 8139 56 Query /*!40101 SET NAMES "utf8"*/
2015-10-19 14:07:06 username[username] @ [host] 8139 56 Query SHOW VARIABLES LIKE 'wait\\_timeout'
2015-10-19 14:07:06 username[username] @ [host] 8139 56 Query SET SESSION wait_timeout=10000
2015-10-19 14:07:06 username[username] @ [host] 8139 56 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'*/
2015-10-19 14:07:06 username[username] @ [host] 8139 56 Query /*!40101 SET @@SQL_MODE := CONCAT(@@SQL_MODE, ',NO_AUTO_VALUE_ON_ZERO')*/
2015-10-19 14:07:06 username[username] @ [host] 8139 56 Query SHOW VARIABLES LIKE 'version%'
2015-10-19 14:07:06 username[username] @ [host] 8139 56 Query SHOW ENGINES
2015-10-19 14:07:06 username[username] @ [host] 8139 56 Query SHOW VARIABLES LIKE 'innodb_version'
2015-10-19 14:07:06 username[username] @ [host] 8139 56 Query SELECT @@binlog_format
2015-10-19 14:07:06 username[username] @ [host] 8139 56 Query /*!50108 SET @@binlog_format := 'STATEMENT'*/
2015-10-19 14:07:06 username[username] @ [host] 8139 56 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2015-10-19 14:07:06 username[username] @ [host] 8138 56 Query SELECT db, tbl, 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 percona.checksums WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)
2015-10-19 14:07:06 username[username] @ [host] 8138 56 Query SHOW TABLES FROM `dcf_loan` LIKE 't\\_loan\\_application'
2015-10-19 14:07:06 username[username] @ [host] 8138 56 Query SHOW VARIABLES LIKE 'version%'
2015-10-19 14:07:06 username[username] @ [host] 8138 56 Query SHOW ENGINES
2015-10-19 14:07:06 username[username] @ [host] 8138 56 Query SHOW VARIABLES LIKE 'innodb_version'
2015-10-19 14:07:06 username[username] @ [host] 8138 56 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2015-10-19 14:07:06 username[username] @ [host] 8138 56 Query SHOW TRIGGERS FROM `dcf_loan`
2015-10-19 14:07:06 username[username] @ [host] 8138 56 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2015-10-19 06:07:16 [] @ [] 2914 56 Query DROP TABLE IF EXISTS `__maatkit_char_chunking_map` /* generated by server */
2015-10-19 06:07:16 [] @ [] 2914 56 Query BEGIN
2015-10-19 06:07:16 [] @ [] 2914 56 Query CREATE TEMPORARY TABLE `dcf_loan`.`__maatkit_char_chunking_map` ( `loan_application_id` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '融资申请ID') ENGINE=MEMORY
2015-10-19 06:07:16 [] @ [] 2914 56 Query COMMIT
2015-10-19 06:07:16 [] @ [] 2914 56 Query BEGIN
2015-10-19 06:07:16 [] @ [] 2914 56 Query INSERT INTO `dcf_loan`.`__maatkit_char_chunking_map` VALUES (CHAR('76'))
2015-10-19 06:07:16 [] @ [] 2914 56 Query COMMIT
2015-10-19 06:07:16 [] @ [] 2914 56 Query DROP TEMPORARY TABLE `__maatkit_char_chunking_map` /* generated by server */
2015-10-19 14:07:16 username[username] @ [host] 8138 56 Query SELECT CRC32('test-string')
2015-10-19 14:07:16 username[username] @ [host] 8138 56 Query USE `dcf_loan`
2015-10-19 14:07:16 username[username] @ [host] 8138 56 Query SET @crc := '', @cnt := 0
2015-10-19 14:07:16 username[username] @ [host] 8138 56 Query commit
2015-10-19 14:07:16 username[username] @ [host] 8138 56 Query SELECT MASTER_POS_WAIT('t3-bin.000016', 243774410, 60)
2015-10-19 14:07:16 username[username] @ [host] 8138 56 Query SELECT /*dcf_loan.t_loan_application:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, `apply_start_time` + 0, `apply_end_time` + 0, `apply_loan_due_date` + 0, `apply_ar_due_date` + 0, `apply_buyback_due_date` + 0, `digital_sign`, `digital_sign_version`, `digital_by`, `expected_loan_date` + 0, `apply_date` + 0, `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, `create_time` + 0, `created_by`, `update_time` + 0, `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX (`PRIMARY`) WHERE (1=1) AND ((((`loan_application_id` >= 'LA120140520161304489')) AND ((`loan_application_id` <= 'LA120140623154518472')))) LOCK IN SHARE MODE
2015-10-19 14:07:16 username[username] @ [host] 8138 56 Query SET @crc := '', @cnt := 0
2015-10-19 14:07:16 username[username] @ [host] 8138 56 Query SELECT /*rows in chunk*/ `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, `apply_start_time` + 0 AS `apply_start_time`, `apply_end_time` + 0 AS `apply_end_time`, `apply_loan_due_date` + 0 AS `apply_loan_due_date`, `apply_ar_due_date` + 0 AS `apply_ar_due_date`, `apply_buyback_due_date` + 0 AS `apply_buyback_due_date`, `digital_sign`, `digital_sign_version`, `digital_by`, `expected_loan_date` + 0 AS `expected_loan_date`, `apply_date` + 0 AS `apply_date`, `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, `create_time` + 0 AS `create_time`, `created_by`, `update_time` + 0 AS `update_time`, `updated_by`, `delete_flag`, CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, `apply_start_time` + 0, `apply_end_time` + 0, `apply_loan_due_date` + 0, `apply_ar_due_date` + 0, `apply_buyback_due_date` + 0, `digital_sign`, `digital_sign_version`, `digital_by`, `expected_loan_date` + 0, `apply_date` + 0, `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, `create_time` + 0, `created_by`, `update_time` + 0, `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS __crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX (`PRIMARY`) WHERE (1=1) AND (((`loan_application_id` >= 'LA120140520161304489')) AND ((`loan_application_id` <= 'LA120140623154518472'))) ORDER BY `loan_application_id` LOCK IN SHARE MODE
2015-10-19 06:07:16 [] @ [] 2914 56 Query BEGIN
2015-10-19 14:07:16 username[username] @ [host] 8138 56 Query commit
2015-10-19 14:07:16 [] @ [] 2914 56 Query REPLACE INTO `dcf_loan`.`t_loan_application`(`loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, `apply_start_time`, `apply_end_time`, `apply_loan_due_date`, `apply_ar_due_date`, `apply_buyback_due_date`, `digital_sign`, `digital_sign_version`, `digital_by`, `expected_loan_date`, `apply_date`, `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, `create_time`, `created_by`, `update_time`, `updated_by`, `delete_flag`) VALUES (...) /*percona-toolkit src_db:dcf_loan src_tbl:t_loan_application src_dsn:A=utf8,P=3306,h=t3.dcfservice.com,p=...,u=username dst_db:dcf_loan dst_tbl:t_loan_application dst_dsn:A=utf8,P=3306,h=t3-2,p=...,u=username lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:30371 user:username host:t3-2.dcfservice.com*/
2015-10-19 14:07:16 [] @ [] 2914 56 Query COMMIT /* implicit, from Xid_log_event */
2015-10-19 14:07:16 username[username] @ [host] 8138 56 Query SHOW TABLES FROM `dcf_loan` LIKE 't\\_loan\\_application'
2015-10-19 14:07:16 username[username] @ [host] 8138 56 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2015-10-19 14:07:16 username[username] @ [host] 8138 56 Query SHOW TRIGGERS FROM `dcf_loan`
2015-10-19 14:07:16 username[username] @ [host] 8138 56 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2015-10-19 14:07:21 [] @ [] 2914 56 Query DROP TABLE IF EXISTS `__maatkit_char_chunking_map` /* generated by server */
2015-10-19 14:07:21 [] @ [] 2914 56 Query BEGIN
2015-10-19 14:07:21 [] @ [] 2914 56 Query CREATE TEMPORARY TABLE `dcf_loan`.`__maatkit_char_chunking_map` ( `loan_application_id` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '融资申请ID') ENGINE=MEMORY
2015-10-19 14:07:21 [] @ [] 2914 56 Query COMMIT
2015-10-19 14:07:21 [] @ [] 2914 56 Query BEGIN
2015-10-19 14:07:21 [] @ [] 2914 56 Query INSERT INTO `dcf_loan`.`__maatkit_char_chunking_map` VALUES (CHAR('76'))
2015-10-19 14:07:21 [] @ [] 2914 56 Query COMMIT
2015-10-19 14:07:21 [] @ [] 2914 56 Query DROP TEMPORARY TABLE `__maatkit_char_chunking_map` /* generated by server */
2015-10-19 14:07:21 username[username] @ [host] 8138 56 Query SELECT CRC32('test-string')
2015-10-19 14:07:21 username[username] @ [host] 8138 56 Query USE `dcf_loan`
2015-10-19 14:07:21 username[username] @ [host] 8138 56 Query SET @crc := '', @cnt := 0
2015-10-19 14:07:21 username[username] @ [host] 8138 56 Query commit
2015-10-19 14:07:22 username[username] @ [host] 8138 56 Query SELECT MASTER_POS_WAIT('t3-bin.000016', 243790404, 60)
2015-10-19 14:07:22 username[username] @ [host] 8138 56 Query SELECT /*dcf_loan.t_loan_application:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, `apply_start_time` + 0, `apply_end_time` + 0, `apply_loan_due_date` + 0, `apply_ar_due_date` + 0, `apply_buyback_due_date` + 0, `digital_sign`, `digital_sign_version`, `digital_by`, `expected_loan_date` + 0, `apply_date` + 0, `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, `create_time` + 0, `created_by`, `update_time` + 0, `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX (`PRIMARY`) WHERE (1=1) AND ((((`loan_application_id` >= 'LA120140623154521327')) AND ((`loan_application_id` <= 'LA120150216134407217')))) LOCK IN SHARE MODE
2015-10-19 14:07:22 username[username] @ [host] 8138 56 Query SET @crc := '', @cnt := 0
2015-10-19 14:07:22 username[username] @ [host] 8138 56 Query SELECT /*rows in chunk*/ `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, `apply_start_time` + 0 AS `apply_start_time`, `apply_end_time` + 0 AS `apply_end_time`, `apply_loan_due_date` + 0 AS `apply_loan_due_date`, `apply_ar_due_date` + 0 AS `apply_ar_due_date`, `apply_buyback_due_date` + 0 AS `apply_buyback_due_date`, `digital_sign`, `digital_sign_version`, `digital_by`, `expected_loan_date` + 0 AS `expected_loan_date`, `apply_date` + 0 AS `apply_date`, `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, `create_time` + 0 AS `create_time`, `created_by`, `update_time` + 0 AS `update_time`, `updated_by`, `delete_flag`, CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, `apply_start_time` + 0, `apply_end_time` + 0, `apply_loan_due_date` + 0, `apply_ar_due_date` + 0, `apply_buyback_due_date` + 0, `digital_sign`, `digital_sign_version`, `digital_by`, `expected_loan_date` + 0, `apply_date` + 0, `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, `create_time` + 0, `created_by`, `update_time` + 0, `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS __crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX (`PRIMARY`) WHERE (1=1) AND (((`loan_application_id` >= 'LA120140623154521327')) AND ((`loan_application_id` <= 'LA120150216134407217'))) ORDER BY `loan_application_id` LOCK IN SHARE MODE
2015-10-19 14:07:25 [] @ [] 2914 56 Query BEGIN
2015-10-19 14:07:25 username[username] @ [host] 8138 56 Query commit
2015-10-19 14:07:25 [] @ [] 2914 56 Query REPLACE INTO `dcf_loan`.`t_loan_application`(`loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, `apply_start_time`, `apply_end_time`, `apply_loan_due_date`, `apply_ar_due_date`, `apply_buyback_due_date`, `digital_sign`, `digital_sign_version`, `digital_by`, `expected_loan_date`, `apply_date`, `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, `create_time`, `created_by`, `update_time`, `updated_by`, `delete_flag`) VALUES (...) /*percona-toolkit src_db:dcf_loan src_tbl:t_loan_application src_dsn:A=utf8,P=3306,h=t3.dcfservice.com,p=...,u=username dst_db:dcf_loan dst_tbl:t_loan_application dst_dsn:A=utf8,P=3306,h=t3-2,p=...,u=username lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:30371 user:username host:t3-2.dcfservice.com*/
2015-10-19 14:07:25 username[username] @ [host] 8138 56 Query SHOW TABLES FROM `dcf_loan` LIKE 't\\_loan\\_application'
2015-10-19 14:07:25 [] @ [] 2914 56 Query COMMIT /* implicit, from Xid_log_event */
2015-10-19 14:07:25 username[username] @ [host] 8138 56 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2015-10-19 14:07:25 username[username] @ [host] 8138 56 Query SHOW TRIGGERS FROM `dcf_loan`
2015-10-19 14:07:25 username[username] @ [host] 8138 56 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2015-10-19 14:07:30 [] @ [] 2914 56 Query DROP TABLE IF EXISTS `__maatkit_char_chunking_map` /* generated by server */
2015-10-19 14:07:30 [] @ [] 2914 56 Query BEGIN
2015-10-19 14:07:30 [] @ [] 2914 56 Query CREATE TEMPORARY TABLE `dcf_loan`.`__maatkit_char_chunking_map` ( `loan_application_id` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '融资申请ID') ENGINE=MEMORY
2015-10-19 14:07:30 [] @ [] 2914 56 Query COMMIT
2015-10-19 14:07:30 [] @ [] 2914 56 Query BEGIN
2015-10-19 14:07:30 [] @ [] 2914 56 Query INSERT INTO `dcf_loan`.`__maatkit_char_chunking_map` VALUES (CHAR('76'))
2015-10-19 14:07:30 [] @ [] 2914 56 Query COMMIT
2015-10-19 14:07:30 [] @ [] 2914 56 Query DROP TEMPORARY TABLE `__maatkit_char_chunking_map` /* generated by server */
2015-10-19 14:07:30 username[username] @ [host] 8138 56 Query SELECT CRC32('test-string')
2015-10-19 14:07:30 username[username] @ [host] 8138 56 Query USE `dcf_loan`
2015-10-19 14:07:30 username[username] @ [host] 8138 56 Query SET @crc := '', @cnt := 0
2015-10-19 14:07:30 username[username] @ [host] 8138 56 Query commit
2015-10-19 14:07:30 username[username] @ [host] 8138 56 Query SELECT MASTER_POS_WAIT('t3-bin.000016', 243806786, 60)
2015-10-19 14:07:30 username[username] @ [host] 8138 56 Query SELECT /*dcf_loan.t_loan_application:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, `apply_start_time` + 0, `apply_end_time` + 0, `apply_loan_due_date` + 0, `apply_ar_due_date` + 0, `apply_buyback_due_date` + 0, `digital_sign`, `digital_sign_version`, `digital_by`, `expected_loan_date` + 0, `apply_date` + 0, `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, `create_time` + 0, `created_by`, `update_time` + 0, `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX (`PRIMARY`) WHERE (1=1) AND ((((`loan_application_id` >= 'LA220150702131201758')) AND ((`loan_application_id` <= 'LA220150722184051820')))) LOCK IN SHARE MODE
2015-10-19 14:07:30 username[username] @ [host] 8138 56 Query SET @crc := '', @cnt := 0
2015-10-19 14:07:30 username[username] @ [host] 8138 56 Query SELECT /*rows in chunk*/ `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, `apply_start_time` + 0 AS `apply_start_time`, `apply_end_time` + 0 AS `apply_end_time`, `apply_loan_due_date` + 0 AS `apply_loan_due_date`, `apply_ar_due_date` + 0 AS `apply_ar_due_date`, `apply_buyback_due_date` + 0 AS `apply_buyback_due_date`, `digital_sign`, `digital_sign_version`, `digital_by`, `expected_loan_date` + 0 AS `expected_loan_date`, `apply_date` + 0 AS `apply_date`, `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, `create_time` + 0 AS `create_time`, `created_by`, `update_time` + 0 AS `update_time`, `updated_by`, `delete_flag`, CRC32(CONCAT_WS('#', `loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, `apply_start_time` + 0, `apply_end_time` + 0, `apply_loan_due_date` + 0, `apply_ar_due_date` + 0, `apply_buyback_due_date` + 0, `digital_sign`, `digital_sign_version`, `digital_by`, `expected_loan_date` + 0, `apply_date` + 0, `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, `create_time` + 0, `created_by`, `update_time` + 0, `updated_by`, `delete_flag`, CONCAT(ISNULL(`applicant_id`), ISNULL(`contract_id`), ISNULL(`parent_contract_id`), ISNULL(`loan_document_id`), ISNULL(`apply_document_no`), ISNULL(`apply_order_content`), ISNULL(`apply_document_amount`), ISNULL(`apply_amount`), ISNULL(`apply_start_time`), ISNULL(`apply_end_time`), ISNULL(`apply_loan_due_date`), ISNULL(`apply_ar_due_date`), ISNULL(`apply_buyback_due_date`), ISNULL(`digital_sign`), ISNULL(`digital_sign_version`), ISNULL(`digital_by`), ISNULL(`expected_loan_date`), ISNULL(`apply_date`), ISNULL(`loan_application_state`), ISNULL(`micro_contract`), ISNULL(`create_time`), ISNULL(`created_by`), ISNULL(`update_time`), ISNULL(`updated_by`), ISNULL(`delete_flag`)))) AS __crc FROM `dcf_loan`.`t_loan_application` FORCE INDEX (`PRIMARY`) WHERE (1=1) AND (((`loan_application_id` >= 'LA220150702131201758')) AND ((`loan_application_id` <= 'LA220150722184051820'))) ORDER BY `loan_application_id` LOCK IN SHARE MODE
2015-10-19 14:07:30 [] @ [] 2914 56 Query BEGIN
2015-10-19 14:07:30 username[username] @ [host] 8138 56 Query commit
2015-10-19 14:07:30 [] @ [] 2914 56 Query REPLACE INTO `dcf_loan`.`t_loan_application`(`loan_application_id`, `applicant_id`, `contract_id`, `parent_contract_id`, `loan_document_id`, `apply_document_no`, `apply_order_content`, `apply_document_amount`, `apply_amount`, `apply_start_time`, `apply_end_time`, `apply_loan_due_date`, `apply_ar_due_date`, `apply_buyback_due_date`, `digital_sign`, `digital_sign_version`, `digital_by`, `expected_loan_date`, `apply_date`, `loan_application_state`, `applicant_contract`, `benchmark_one_year_lending_rate`, `benchmark_half_year_lending_rate`, `micro_contract`, `create_time`, `created_by`, `update_time`, `updated_by`, `delete_flag`) VALUES (...) /*percona-toolkit src_db:dcf_loan src_tbl:t_loan_application src_dsn:A=utf8,P=3306,h=t3.dcfservice.com,p=...,u=username dst_db:dcf_loan dst_tbl:t_loan_application dst_dsn:A=utf8,P=3306,h=t3-2,p=...,u=username lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:30371 user:username host:t3-2.dcfservice.com*/
2015-10-19 14:07:30 [] @ [] 2914 56 Query COMMIT /* implicit, from Xid_log_event */
2015-10-19 14:07:30 username[username] @ [host] 8138 56 Query commit
2015-10-19 14:07:30 username[username] @ [host] 8138 56 Quit
2015-10-19 14:07:30 username[username] @ [host] 8139 56 Query commit
2015-10-19 14:07:30 username[username] @ [host] 8139 56 Quit

这一次general log打印出的日志与–print是基本一致，最重要的区别是这里多了REPLACE INTO dcf_loan.t_loan_application语句（这里我省略了具体的值）用以修复与主库不一致的行。

修复完成后我们再来执行一下检测

[username@t3-2 ~]# 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-19T14:24:26      0      0    90263       9       0  11.795 dcf_loan.t_loan_application

mysql> SELECT db, tbl, 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 percona.checksums WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc);
Empty set (0.00 sec)

发现不一致已经被修复了。

3.原理以及注意事项

总体来说pt-table-checksum和pt-table-sync的使用还是相对比较简单的。接下来我们结合官方文档相信介绍一下两个工具的原理以及注意事项。

3.1pt-table-checksum
通过前边的实践，我们大体上已经了解到了pt-table-checksum的原理了，也即在master上执行校验查询（具体是把表根据具体情况分成几个CHUNK，对于每个CHUNK将各个行的值拼接起来计算校验和）若master与salve存在不一致则同样的校验查询会在master与salve上得到不同的结果，最后通过存储校验结果的表就可以很容易判断出哪些表的哪些CHUNK内存在不一致。如果你认为pt-table-check只是做了这么点工作而已，那就错了，经过不断的开发迭代，当前的pt-table-checksum工具已经非常智能了。首先工具会连接到指定的MySQL Server找到指定的库表，逐个库、表执行检查，正因如此不会占用过大的内存或者在执行检查前做很多其他额外工作，因此即使对于很大的数据库依然适用。使该工具可以应用于超大表的另外一个原因是他可以根据期望的每个校验查询的执行时间将表动态划分为不同大小的CHUNK分别进行校验，从而保证不会引入过大的复制延迟和服务器负载。
该工具可以追踪服务器执行查询的速度，从而根据服务器的性能调整CHUNK。意味着，当服务器负载很重的时候，工具能够立即“减速”。其中CHUNK的划分使用所谓的“nibbling”技术，将一个表根据主键或者唯一索引分成不同的CHUNK。若无索引表又较小则工具会把整个表划到一个CHUNK中。

除了这些，pt-table-checksum还采取了一些列安全措施来保证不会影响主从服务器的操作。比如会持续监控复制，若从库延迟过大则会暂停以保证从库追赶上主库。若任何从库有错误或者复制停止了工具都会暂停并等待，直到问题解决。工具可以检测容易引起问题的一些设置，如复制过滤设置（不推荐使用复制过滤，容易引起问题）、binlog log格式等，检测到这些问题的时候不会执行校验，除非强制执行。工具还会验证每个CHUNK是不是过大，如果太大则会认为执行校验不安全会跳过这些CHUNK，可通过 –chunk-size-limit控制这个值。若某个表只被划分为了一个CHUNK则工具还会验证该表在从库上是不是过大，以避免主库中表较小，从库中表非常大，主库上很快就能跑完的一个校验查询在从库中要跑很久从而造成复制延迟。还有另外的一些安全措施，比如设置session级别的innodb_lock_wait_timeout，根据服务器上当前正在执行的并发查询量来选择暂停或开启校验。

通常对于其他任务来说，执行主从一致性校验是个优先级相对较低的操作，会让行其他操作。但频繁关闭重启工具也是不能容忍的，因此该工具被设计为能从错误中“恢复”。比如，若通过pt-kill杀掉了长期运行的校验查询则工具会重试被kill掉的查询，若再次失败则会开始校验下一个CHUNK。如果到服务器的连接断开了则工具会进行重连并继续工作。若工具遇到问题完全停止了则可以通过–resume选项恢复。工具会从上一次处理到的CHUNK重新开始。
工具检查完表的所有CHUNK后会暂停并等待所有的从机执行检验查询。一旦完成，会检查所有的从机是否和主库存在不一致（通过指定的存储校验结果的表来判断）并输出结果，若某些步骤比较耗时还会输出处理进度。

手工从校验结果表查存在不一致的表的SQL如下：

SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(
*
) AS chunks
FROM percona.checksums
WHERE (
master_cnt <> this_cnt
OR master_crc <> this_crc
OR ISNULL(master_crc) <> ISNULL(this_crc))
GROUP BY db, tbl;

使用限制
通常，工具适用于binlog格式为statement的复制，因为需要将在master上执行的校验SQL复制到各个slave上重新执行，而基于row格式的复制不能将SQL语句本身由master传递到slave。执行检验前，工具会检查当前复制环境中的binlog格式，若不是statement的格式则会给与提示并退出检查，除非手工指定–no-check-binlog-format。在执行检查前，会在master上设置session级别的binglog_format格式为statement，但是由于当前MySQL的限制，这个设置的变化并不能传递到slave上，若这个salve恰恰又是其他salve的master时，也即级联复制的情况下若下一级复制使用的非statement格式的binlog则会出现问题。
另外，工具假定master与各个slave上的schema与table在结构上是一致的。该工具同样适用于Percona XtraDB Cluster(PXC)

关于工具的输出
这次测试一下dcf_loan下的所有库

[username@t3-2 ~]# pt-table-checksum h='t3',u='username',p='xxxxxx',P=3306 -d dcf_loan --replicate=percona.checksums --no-check-binlog-format --replicate=percona.checksums;
 TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
10-20T10:35:36      0      0     5487       4       0   0.367 dcf_loan.customer_account
10-20T10:35:37      0      0      278       1       0   0.274 dcf_loan.customer_account_authority
10-20T10:35:37      0      0        0       1       0   0.275 dcf_loan.customer_account_authority_log
10-20T10:35:37      0      0      169       1       0   0.275 dcf_loan.t_acceptance_bank
10-20T10:35:38      0      0       33       1       0   0.269 dcf_loan.t_acceptance_bank_usage
10-20T10:35:41      0      0   427122       7       0   3.604 dcf_loan.t_account_posting_detail
10-20T10:35:41      0      0     3634       1       0   0.302 dcf_loan.t_account_posting_recon
10-20T10:35:46      0      0  1332168       9       0   4.976 dcf_loan.t_activity
10-20T10:35:51      0      0    81031       1       0   4.564 dcf_loan.t_asset_package
10-20T10:35:52      0      0    88316       1       0   0.494 dcf_loan.t_asset_package_loan_application_association
10-20T10:35:52      0      0        1       1       0   0.020 dcf_loan.t_backdoor_tmp
10-20T10:35:52      0      0    16382       1       0   0.403 dcf_loan.t_bank_transaction
10-20T10:35:52      0      0        2       1       0   0.025 dcf_loan.t_base_interest_rate
10-20T10:35:52      0      0      869       1       0   0.275 dcf_loan.t_customer_account_balance
10-20T10:35:54      0      0   526112       4       0   2.021 dcf_loan.t_document_association
10-20T10:35:55      0      0    22317       1       0   0.394 dcf_loan.t_freeze_amount_log
....

可以看到，每个表检测完成后都会立刻打印出对应结果，其中：
TS：对应的是对应的表校验结束的时间
ERRORS：检验过程中发生的错误和警告数（这是该工具的一个特色，遇到错误或者警告不会退出，会重试）
DIFFS：主从存在差异的CHUNK数
ROWS：表中被选择和检验的行数
CHUNKS：表被划分成的CHUNK数
SKIPPED：检测过程中被跳过的CHUNK数，可能由于以下原因导致此种情况：
MySQL not using the –chunk-index
MySQL not using the full chunk index (–[no]check-plan)
Chunk size is greater than –chunk-size * –chunk-size-limit
Lock wait timeout exceeded (–retries)
Checksum query killed (–retries)
TIME ：检验过程消耗的时间
TABLE：对应的表

检测结果还会随指定的参数不同而不同，比如指定了–replicate-check-only的话，会仅仅打印出存在差异的表，每个slave占一个段落。一个比较典型的结果如下：

Differences on h=127.0.0.1,P=12346
TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY
db1.tbl1 1 0 1 PRIMARY 1 100
db1.tbl1 6 0 1 PRIMARY 501 600

Differences on h=127.0.0.1,P=12347
TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY
db1.tbl1 1 0 1 PRIMARY 1 100
db2.tbl2 9 5 0 PRIMARY 101 200

各个字段的含义不言而喻。

工具有三种退出状态：0、255、其他值，分别代表无任何错误、警告、不一致、跳过chunk或table；严重错误，如崩溃退出；其他一般性错误或者有不一致存在或者有跳过的CHUNK/TABLE或者没法发现从机等。具体可以查看参考手册。

工具还支持其他丰富的额外选项，具体可查看帮助文档。
https://www.percona.com/doc/percona-toolkit/2.0/pt-table-checksum.html

3.2 pt-table-sync
pt-table-sync与pt-table-checksum配套使用，用于在检测到主从不一致时同步主从之间的数据。由于该工具会改变数据，所以安全起见使用前最好做好备份。当使用 –replicate 或者 –sync-to-master同步slave的数据使之与master保持一致时，工具都是在master上执行更改然后将执行的语句传递到salve，而非直接在salve上修改。在master上执行的这些更改为 no-op操作，也即只是将值设置为当前值。

几个常见的用法：

将host1上的db.tb1同步到host2对应的库表
pt-table-sync --execute h=host1,D=db,t=tbl h=host2
将host1上所有表同步到host2和host3
pt-table-sync --execute host1 host2 host3
使slave1与其所属的master同步
pt-table-sync --execute --sync-to-master slave1
根据--replicate指定的表（由前期的pt-table-checksum生成）找出master1的各个salve上的不一致并进行同步
pt-table-sync --execute --replicate test.checksum master1
同上，区别在于只找出slave1与master之间的不一致并进行同步
pt-table-sync --execute --replicate test.checksum --sync-to-master slave1
master-master复制模式下将master1上db.tb1的数据同步到master2
pt-table-sync --execute --sync-to-master h=master2,D=db,t=tbl
master-master复制模式下不要使用以下方式，该方式会直接在master2上执行修改然后传到master1
pt-table-sync --execute h=master1,D=db,t=tbl master2

pt-table-sync不会同步表结构、索引等，只同步不一致的数据。
pt-table-sync的工作逻辑：

if DSN has a t part, sync only that table:
     if 1 DSN:
          if --sync-to-master:
               The DSN is a slave. Connect to its master and sync.
     if more than 1 DSN:
          The first DSN is the source. Sync each DSN in turn.
else if --replicate:
     if --sync-to-master:
          The DSN is a slave. Connect to its master, find records
          of differences, and fix.
     else:
          The DSN is the master. Find slaves and connect to each,
     find records of differences, and fix.
else:
     if only 1 DSN and --sync-to-master:
          The DSN is a slave. Connect to its master, find tables and
          filter with --databases etc, and sync each table to the master.
     else:
           find tables, filtering with --databases etc, and sync each
          DSN to the first.

也就是说，pt-table-sync可以以两种方式运行：使用或者不使用–replicate参数。默认情况下不使用，此时工具能根据不同算法自动高效的找出不一致。反之，若使用了该参数工具会使用之前pt-table-checksum的检测结果。无论使用或不使用–replicate参数都需要指定要同步的主机，有两种方式指定，使用–sync-to-mster或者不使用，若指定了该参数则工具需要且只需要指定一个slave的DSN，工具能自动发现其master并同步数据。需要注意的是尽管只指定一个slave，但如果该slave的master上有其他的slave时，其他的slave也会受到来自master的修复不一致的SQL。反之，若不指定–sync-to-master且不指定–replicate则必须至少指定两个DSN，第一个将作为源，后边的将作为目标，需要特别注意的是，这里的目标主机不能和源主机处于同一复制拓扑中，工具若检测到目标主机为slave则会错误退出，因为此时更新会直接发生在slave上，这是极不安全的。若不指定–sync-to-master但制定了–replicate则工具需且只需指定一个mmaster DSN，此时工具能够自动发现slave并同步数据。

工具命令行中的每一个host通过DSN指定，第一个DSN为后续的DSN提供默认值。比如：
pt-table-sync –execute h=host1,u=msandbox,p=msandbox h=host2
这里host2会继承host1的u和p部分，这一点可以通过–explain-hosts参数来观察工具如何解释DSN。

工具输出
若指定了–verbose参数则可以看到表之间的不一致，每个表一行，每个Server一段。（–verbose参数可以多次指定，可以自行测试效果）

pt-table-sync --sync-to-master --replicate=percona.checksums --charset=utf8 h=t3-2,u=username,p=password,P=3306 -d dcf_loan -t t_loan_application --check-triggers --foreign-key-checks --unique-checks --print --verbose
Syncing via replication A=utf8,P=3306,h=t3-2,p=...,u=username
DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE

因为此时已经修复了所有的不一致所以这里没有实际的结果，一个比较典型的结果可能如下：

 Syncing h=host1,D=test,t=test1
DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE
0 0 3 0 Chunk 13:00:00 13:00:17 2 test.test1

这个结果是说host1上的test.test1需要3次insert来同步数据，同步数据使用的算法为Chunk，操作开始于13:00:00耗时17秒

修复数据时也存在特殊情况，比如如下表，a列上为主键索引，b列上为唯一索引，此时便无法只通过update语句来消除不一致，因为这样会导致唯一索引冲突。pt-table-sync采取的做法是将SQL重写为DELETE和REPLACE语句，这个过程是自动的。

+---+---+ +---+---+
| a | b | | a | b |
+---+---+ +---+---+
| 1 | 2 | | 1 | 1 |
| 2 | 1 | | 2 | 2 |
+---+---+ +---+---+

复制的安全性
一般情况下，安全的同步不一致数据的方式为在master上执行SQL然后通过基于语句的复制传递到slave上再次执行。然而，这只工作在可在master的表上执行replacede的情况下，也即存在唯一索引的情况下。此时可以使用–sync-to-master和/或–replicate选项同步slave与master。若无唯一索引则只能直接在slave上执行更改了，pt-table-sync可以检测到这种情况并报错退出除非指定了–no-check-slave，若指定了这一参数将直接在salve执行更改，此时若复制为master-master结构那么更改会被传递到改机器的salve（同时也是他的master），因此这种情况下还要指定–no-bin-log参数。

另外在存在外键约束的情况下使用该工具一定要小心谨慎

同步不一致数据时使用的算法
工具可根据每个表的索引情况、列类型等自动选取最优的发现不一致数据的算法，目前有Chunk、Nibble、GroupBy、Stream几种。（各种算法的描述及优劣可查看参考手册）

双向同步
前边讲解的都是根据master来同步slave数据。pt-table-sync同时还支持双向同步，但是目前存在一定限制：
只允许将数据从某一Server同步到另外的独立的Server(s),不能应用于复制环境
要求表能够使用Chunk算法划分成多个Chunk
每次只能在两台Server间同步
不能处理DELETE变化
目前双向同步使用的范围比较有限，不过进一步的讨论，有兴趣或者需要使用的话可查询参考手册

除了上边讲到的一些列参数外，pt-table-sync还支持其他丰富的选项，具体请查看参考手册
https://www.percona.com/doc/percona-toolkit/2.2/pt-table-sync.html

1.背景

2.测试

3.原理以及注意事项

更多相关文章

随机推荐