Percona-Toolkit 之 pt-table-checksum 总结
2019-04-04 10:31 H_Johnny 阅读(4633) 评论(0) 编辑 收藏 举报pt-table-checksum - Verify MySQL replication integrity.
pt-table-checksum performs an online replication consistency check by executing checksum queries on the master, which produces different results on replicas that are inconsistent with the master.
pt-table-checksum是Percona-Toolkit工具集中的一个组件，是一个检查主从复制数据库数据一致性的工具，通过在主库上执行检查语句可以在线检查MySQL主从数据库数据的一致性。
pt-table-checksum通过指定选项连接指定服务器实例，可以通过指定条件过滤到库级别和表级别。每次检查一张表，所以并不需要大量的内存和多余的操作，即使是非常大的表也可以良好运行。根据官方文档的描述，之所以在检查大表上也能很好运行，主要是因为pt-table-checksum将表划分多个chunk，根据checksum query运行时间来调整chunk的大小，这样就减少pt-table-checksum的运行对主从复制延迟和主机负载的影响。为了保证对数据库主机运行尽可能少的影响，pt-table-checksum会自动探测从库并进行连接，如果连接失败，可以通过选项--recursion-method指定从库连接方式。
本文是关于之前有关pt-table-checksum工具使用的学习笔记进行重新整理，使用最新版本的工具同时也进行原理知识的梳理。
关于获取和安装Percona-Toolkit工具集可以参考我另一篇博文：Percona-Toolkit 之 pt-online-schema-change 总结中的安装部分。
基本说明
pt-table-checksum [OPTIONS] DSN
常用选项(OPTIONS)
--ask-pass
命令行提示密码输入，保护密码安全，前提需安装模块perl-TermReadKey。
--[no]check-binlog-format
默认值：yes
指定检查所有服务器上的binlog_format系统参数是否相同。
--check-interval
默认值：1s
指定因为选项'--max-lag'检查之间休眠时间。
--[no]check-replication-filters
默认值：yes
指定检测主从复制是否有设置复制过滤器。默认如果有设置复制过滤器，则工具不进行检查校验操作。
--check-slave-lag
指定主从复制延迟大于选项'--max-lag'指定的值之后暂停检查校验操作。默认情况下，工具会检查所有的从库，但该选项只作用于指定的从库(通过DSN连接方式)。
--chunk-index
指定使用哪个索引对表进行chunk分块操作。默认情况下会选择最优的索引，工具会在SQL语句中添加FORCE INDEX子句。
--chunk-index-columns
指定使用选项'--chunk-index'的索引使用最左前缀几个索引字段，只适用于复合索引。
--chunk-size
默认值：1000
指定表分块的chunk大小，每个chunk需要校验的表行数，允许的后缀单位为k、M、G。
当指定了这个选项会覆盖工具默认动态调整chunk块大小以便在选项'--chunk-time'指定时间内完成行的校验。所以在大多数情况下不建议使用该选项，应该使用选项'--chunk-time'来调整。
--chunk-size-limit
默认值：2.0
指定chunk的行数最多可以超过选项'--chunk-size'指定的行数的多少倍。最小值是1，表示chunk的行数不能超过选项'--chunk-size'指定的值。由于行数是通过explain估算的，所以不建议指定为1。当参数值为0时，则不会检查是否超过指定的行数。
--chunk-time
默认值：0.5
动态调整每个chunk的大小使相应的表行数都在指定的时间内完成校验操作。
如果该选项值设置为0，则不会动态调整chunk的大小，就有可能造成每次校验操作的时间不同，但每个chunk大小还是一致的。
--columns，-c
指定只需要校验的字段，如有多个则用','(逗号)隔开。该选项一般只针对检验一张表时有效，除非有多张表具有相同的字段。
--[no]create-replicate-table
默认值：yes
创建选项'--replicate'指定的数据库和表。表结构与选项'--replicate'指定的结构相同。
--replicate
默认值：percona.checksums
指定保存校验结果的表。创建表的结构如下：
'
CREATE TABLE checksums (
db CHAR(64) NOT NULL,
tbl CHAR(64) NOT NULL,
chunk INT NOT NULL,
chunk_time FLOAT NULL,
chunk_index VARCHAR(200) NULL,
lower_boundary TEXT NULL,
upper_boundary TEXT NULL,
this_crc CHAR(40) NOT NULL,
this_cnt INT NOT NULL,
master_crc CHAR(40) NULL,
master_cnt INT NULL,
ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (db, tbl, chunk),
INDEX ts_db_tbl (ts, db, tbl)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
'
因为选项'--[no]create-replicate-table'默认值为true，所以默认情况下如果数据库和表之前不存在则会自动创建percona库和checksums表。除非另有选项指定。选项'--replicate'指定的表不会进行校验操作，指定的表默认加入选项'--ignore-tables'。
--[no]replicate-check
默认值：yes
指定在校验完每张表后检查主从当前表是否出现不一致。工具通过连接到从库执行简单的SELECT语句查询校验结果与主库的校验结果进行差异对比，结果显示在输出中的DIFF字段中。
--replicate-check-only
指定仅仅执行检查主从数据是否一致而不进执行真正的校验操作(主要通过查询之前保留的校验结果)。该选项只适用于同时指定选项'--no-replicate-check'。
--replicate-check-retries
默认值：1
指定当校验出主从数据不一致重试校验的次数。
--replicate-database
指定工具在执行校验操作时在哪个数据库下进行，相当于执行了语句USE [DB_NAME]。
--resume
指定从最后完成校验的chunk开始恢复校验。适用于还未完成所有表的校验就工具就中断的情况。
--retries
默认值：2
指定当出现非严重性错误时重复校验一个块的次数。非严重性错误指的是如锁等待超时或长查询被kill的情况。
--run-time
指定校验操作运行的时间。默认情况需要校验完所有的表数据后停止，可以指定时间单位为：s(秒)、m(分钟)、h(小时)、d(天)。
--skip-check-slave-lag
DSN类型，可重复使用
指定DSN连接从库时跳过主从延迟检查，可以指定多个从库检查。
--set-vars
默认：
wait_timeout=10000
innodb_lock_wait_timeout=1
lock_wait_timeout=60
运行检查时指定参数值，如有多个用','(逗号)分隔。如'--set-vars=wait_timeout=5000'。
--[no]empty-replicate-table
默认值：yes
指定进行当前校验之前删除之前每张表的校验记录。该选项并不是对保存校验结果的表进行truncate，而是在校验每张表之前删除当前表之前的校验结果，因此当校验操作过早停止，则有可能还有表没有校验数据，如果是从之前校验操作恢复，也不会清空保存校验结果的表。如果想清空保存校验结果的表，则在校验操作进行之前手动对表执行truncate操作。
--databases，-d
指定只需要校验的数据库，如有多个则用','(逗号)隔开。
--engines，-e
指定只需要校验的指定存储引擎类型的表。
--explain
指定显示校验查询语句，但不执行真正的校验操作。该选项会禁用选项'--[no]empty-replicate-table'，如果指定两次，则工具实际使用的是迭代的chunk算法，打印出每个块的上边界和下边界值，但不执行真正的校验。
--fail-on-stopped-replication
指定当主从复制停止时，校验中止操作并提示错误而不是等待主从复制恢复之后再进行。
--function
默认值：CRC32
指定校验操作使用的哈希函数。可选函数有SHA1、MD5等。
--ignore-columns
指定需要忽略校验的字段，如有多个则用','(逗号)隔开。
--ignore-databases
指定需要忽略校验的数据库，如有多个则用','(逗号)隔开。
--ignore-databases-regex
指定采用正则表达式匹配忽略校验的数据库。
--ignore-engines
默认值：FEDERATED，MRG_MyISAM
指定需要忽略校验的存储引擎类型的表，如有多个则用','(逗号)隔开。
--ignore-tables
指定需要忽略校验的表，如有多个则用','(逗号)隔开。表名称可以使用数据库名加以限定。
--ignore-tables-regex
指定采用正则表达式匹配忽略校验的表。
--max-lag
默认值：1s
指定允许主从复制延迟时长的最大值，单位秒。如果在每次校验查询之后主从延迟超过指定的值，则校验操作将暂停执行，暂停休眠时间为选项'--check-interval'指定的值。待休眠时间结束之后再次检查主从延迟时长，检查方法是通过从库查询的'Seconds_Behind_Master'值来确定。如果主从复制延迟一直大于该参数指定值或者从库停止复制，则操作将一直等待直到从库重新启动并且延迟小于该参数指定值。
--max-load
数组类型，默认值：Threads_running = 25
在校验要询完每个chunk数据之后，运行SHOW GLOBAL STATUS检查所指定变量值高于该参数指定变量的阈值时将暂停校验操作。如果有多个变量阈值，可以用','(逗号)进行分隔，参数指定形式可以为变量名=MAX_VALUE或变量名:MAX_VALUE。
如果只是指定变量名，没有为其指定阈值，则检查当前值并增加20%作为阈值。如：
--max-load=Threads_running：没有指定具体值，以当前查询值增加20%作为阈值，如当前为100，阈值为120；
--max-load=Threads_running:10：以当前指定值为阈值。
--host，-h
指定连接的数据库IP地址。
--port，-P
指定连接的数据库Port端口。
--user，-u
指定连接的数据库用户。
--password，-p
指定连接的数据库用户密码。
--database，-d
指定连接的数据库。
--socket，-S
指定使用SOCKET文件连接。
--progress
打印工具执行过程的进度提示到STDERR。选项值有两部分组成，用逗号进行分隔，第一部分为百分比，时间和迭代。第二部分为根据第一部分数据更新频率，也分为百分比，时间和迭代。
--quiet，-q
不打印工具执行过程的信息到STDOUT(禁用'--progress')。但错误和警告还是打印到STDERR。
--recurse
指定搜寻从库的层级，默认无限级。
--recursion-method
默认值：processlist，hosts
指定获取从库的方式。pt-table-checksum在执行校验操作时会执行多次REPLICA CHECKS操作。
METHOD USES
=========== =============================================
processlist SHOW PROCESSLIST
hosts SHOW SLAVE HOSTS
cluster SHOW STATUS LIKE 'wsrep\_incoming\_addresses'
dsn=DSN DSNs from a table
none Do not find slaves
==========================================================
processlist：通过SHOW PROCESSLIST方式找到slave，为默认方式，当SHOW SLAVE HOSTS不可用时。一旦实例运行在非3306端口上时，hosts方式就会变为默认方式；
hosts：通过SHOW SLAVE HOSTS方式找到slave，hosts方式要求从库配置'--report_host'和'--report_port'这两个参数；
cluster：基于集群版本Galera 23.7.3及更新版本；
dsn：通过读取表中从库的DSN信息进行连接。
--tables，-t
指定只需要校验的表，如有多个则用','(逗号)隔开。表名称可以使用数据库名加以限定。
--tables-regex
指定采用正则表达式匹配校验的表。
--where
指定通过where条件确定表中需要校验的数据。
--truncate-replicate-table
指定在执行校验操作之前对保存校验结果的表执行truncate操作。注意与选项'--[no]empty-replicate-table'的区分。
--version
显示工具的版本并退出。
--[no]version-check
默认值：yes
检查Percona Toolkit、MySQL和其他程序的最新版本。
DSN选项(DSN)
可以使用DSN方式来连接数据库，DSN选项为key=value方式，在等号的两侧不能有空格出现，并且区分大小写，多个选项之前以','(逗号)隔开，主要选项如下：
- A
指定字符集 - D
指定DSN表所在数据库 - t
指定DSN表 - h
指定要连接的HOST - P
指定要连接的PORT - S
指定连接所使用的SOCKET文件(Unix systems) - u
指定连接的用户名 - p
指定连接的用户名密码
示例：
h=192.168.58.3,P=3306,D=employees,t=employees
从库检查(REPLICA CHECKS)
默认情况，pt-table-checksum工具会对所有连接到的从库进行检查，从库连接策略由选项--recursion-method和--recurse控制，检查项目如下：
- --[no]check-replication-filters
工具检查每个从库上是否有存在主从复制过滤器，如果发现有复制过滤器，则校验操作会中止，工具会退出。
- --replicate指定的表
工具检查每个从库上是否都存在选项--replicate指定保存校验结果的表。如果有从库没有这张表时，校验操作有可能导致主从复制中断。这项检查无法被关闭，如果从库不存在表，则工具会一直等待直到从库存在该表。
- 单个chunk大小
工具检查主库上表的是否可以在单个chunk范围内进行校验。具体说明可以参考官方说明：REPLICA CHECKS。
- 主从复制延迟
工具在每次校验完一个chunk之后都会检查每个从库是否有延迟，或者通过选项--check-slave-lag指定需要检查的从库。
- 校验块
工具在主库校验完每张表之后都会等待每个从库最后一个校验块的完成，再执行选项--[no]replicate-check指定的操作。
使用限制
- pt-table-checksum工具要求主从复制是基于STATEMENT格式进行的复制，并且会在主库执行
binlog_format=STATEMENT语句。由于MySQL的限制，这一设置并不会传递到从库进行。因此当从库的binlog格式是ROW时，无法直接对从库的从库进行校验操作。工具在进行校验操作时都会检查所有从库的binlog_format，可以使用选项--[no]check-binlog-format进行控制； - 工具假设主从数据库中SCHEMA和表结构都是一致的。如果从库上不存在主库的SCHEMA，或者是从库上表结构与主库不一致，则有可能导致主从复制中断。
用法示例
环境与数据准备
MySQL：5.7.24
MASTER：192.168.58.3:3306
SLAVE：192.168.58.5:3306
本文基于MySQL官方示例数据库employee：Example Databases进行测试。
创建测试表employees_ptchksum：
-- 表employees_ptchksum信息
mysql admin@192.168.58.3:employees> create table employees_ptchksum as select * from employees;
Query OK, 300024 rows affected
Time: 2.688s
mysql admin@192.168.58.3:employees> show create table employees_ptchksum\G;
***************************[ 1. row ]***************************
Table | employees_ptchksum
Create Table | CREATE TABLE `employees_ptchksum` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set
Time: 0.007s
-- 从库更新部分数据，使得主从出现数据不一致
mysql admin@192.168.58.5:employees> set sql_log_bin = 0;
Query OK, 0 rows affected
Time: 0.002s
mysql admin@192.168.58.5:employees> update employees_ptchksum set first_name = 'Georgi_ptchk' where first_name = 'Georgi';
Query OK, 252 rows affected
Time: 0.235s
mysql admin@192.168.58.5:employees> set sql_log_bin = 1;
Query OK, 0 rows affected
Time: 0.002s
执行一致性校验
在主库上执行：
# pt-table-checksum h=192.168.58.3,P=3306,u=admin --ask-pass --tables=employees_ptchksum
执行以上命令会报如下错误：
Replica dbabd has binlog_format ROW which could cause pt-table-checksum to break replication. Please read "Replicas using row-based replication" in the LIMITATIONS section of the tool's documentation. If you understand the risks, specify --no-check-binlog-format to disable this check.
根据错误信息需指定选项--no-check-binlog-format，加上选项再次执行：
# pt-table-checksum h=192.168.58.3,P=3306,u=admin --ask-pass --tables=employees_ptchksum --no-check-binlog-format
执行以上命令报如下错误：
04-02T15:45:40 Cannot checksum table employees.employees_ptchksum: There is no good index and the table is oversized. at /usr/bin/pt-table-checksum line 6726.
根据错误信息提示表employees_ptchksum没有合适的索引进行chunk操作，为表添加主键后再次执行校验：
-- 添加主键
mysql admin@192.168.58.3:employees> alter table employees_ptchksum add primary key(emp_no);
Query OK, 0 rows affected
Time: 2.271s
-- 再次执行校验
# pt-table-checksum h=192.168.58.3,P=3306,u=admin --ask-pass --tables=employees.employees_ptchksum --no-check-binlog-format
Enter MySQL password:
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
04-02T15:56:21 0 3 300024 0 5 0 0.845 employees.employees_ptchksum
输出字段解析
- TS：校验完成的时间戳(没有年份显示)；
- ERRORS： 校验报错的数量；
- DIFFS：主从之间chunk不同的数量，如果指定
--no-replicate-check，则该值总是为0，如果指定--replicate-check-only，则只有校验结果不同的表会显示； - 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输出字段如下：
Checking if all tables can be checksummed ...
Starting checksum ...
Differences on dbabd
TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY
employees.employees_ptchksum 1 0 1 PRIMARY 10001 11000
employees.employees_ptchksum 2 0 1 PRIMARY 11001 35858
employees.employees_ptchksum 3 0 1 PRIMARY 35859 491954
employees.employees_ptchksum 4 0 1 PRIMARY 491955 499999
- TABLE：与主库数据不一致的表；
- CHUNK：与主库相应表不同的chunk；
- CNT_DIFF：从库上chunk行数减去主库上chunk行数值；
- CRC_DIFF：如果从库上对应的chunk与主库上不同，则为1，否则为0；
- CHUNK_INDEX：表使用哪个索引用来进行chunk；
- LOWER_BOUNDARY：chunk下边界对应的索引值；
- UPPER_BOUNDARY：chunk上边界对应的索引值。
获取校验结果
在从库上执行：
mysql admin@192.168.58.5:employees> 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;
+-----------+--------------------+------------+--------+
| db | tbl | total_rows | chunks |
+-----------+--------------------+------------+--------+
| employees | employees_ptchksum | 300024 | 4 |
+-----------+--------------------+------------+--------+
1 row in set
Time: 0.021s
工具其他选项
- 主库为标准端口(3306)，从库为非标准端口(非3306)
修改从库端口为3307：
mysql admin@192.168.58.5:employees> show global variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3307 |
+---------------+-------+
1 row in set
Time: 0.027s
执行一致性校验，会报出如下错误：
# pt-table-checksum h=192.168.58.3,P=3306,u=admin --ask-pass --databases=employees --tables=employees_ptchksum --no-check-binlog-format
Enter MySQL password:
Checking if all tables can be checksummed ...
Starting checksum ...
Cannot connect to P=3306,h=192.168.58.5,p=...,u=admin
Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information.
根据报错信息看出，工具找不到从库，因为现在从库运行在3307端口上，而工具默认连接的是3306端口，所以需要指定选项--recursion-method，关于选项的使用参考前面说明。
因为主库运行在标准端口，而从库在非标准端口，所以选项--recursion-method值只能为hosts或dsn，以下分两种情况讨论：
-- 指定--recursion-method=hosts，从库必须配置参数report_host和report_port
mysql admin@192.168.58.5:employees> show global variables like '%report%';
+-----------------+--------------+
| Variable_name | Value |
+-----------------+--------------+
| report_host | 192.168.58.5 |
| report_password | |
| report_port | 3307 |
| report_user | |
+-----------------+--------------+
4 rows in set
Time: 0.035s
-- 执行一致性校验
# pt-table-checksum h=192.168.58.3,P=3306,u=admin --ask-pass --databases=employees --tables=employees_ptchksum --no-check-binlog-format --recursion-method hosts
------------------------------------------------------------------
-- 指定--recursion-method=dsn，主库必须先创建dsns表，表结构如下
mysql admin@192.168.58.3:percona> show create table dsns;
+-------+-------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------+
| dsns | CREATE TABLE `dsns` ( |
| | `id` int(11) NOT NULL AUTO_INCREMENT, |
| | `parent_id` int(11) DEFAULT NULL, |
| | `dsn` varchar(255) NOT NULL, |
| | PRIMARY KEY (`id`) |
| | ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------+
1 row in set
Time: 0.037s
-- 表中插入从库dsn信息
mysql admin@192.168.58.3:percona> select * from dsns;
+----+-----------+-----------------------+
| id | parent_id | dsn |
+----+-----------+-----------------------+
| 1 | 1 | h=192.168.58.5,P=3307 |
+----+-----------+-----------------------+
1 row in set
Time: 0.022s
-- 执行一致性校验
# pt-table-checksum h=192.168.58.3,P=3306,u=admin --ask-pass --databases=employees --tables=employees_ptchksum --no-check-binlog-format --recursion-method dsn=D=percona,t=dsns
- 主库和从库都为非标准端口(非3306)
修改主库端口为3307：
mysql admin@192.168.58.3:employees> show global variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3307 |
+---------------+-------+
1 row in set
Time: 0.021s
同样，如果指定选项--recursion-method hosts，则从库必须配置参数report_host和report_port，这里不再赘述。或者选项--recursion-method值为processlist或dsn。
-- 指定--recursion-method processlist
# pt-table-checksum h=192.168.58.3,P=3307,u=admin --ask-pass --databases=employees --tables=employees_ptchksum --no-check-binlog-format --recursion-method processlist
-- 指定--recursion-method=dsn
# pt-table-checksum h=192.168.58.3,P=3307,u=admin --ask-pass --databases=employees --tables=employees_ptchksum --no-check-binlog-format --recursion-method dsn=D=percona,t=dsns
关于主从端口的总结(选项--recursion-method值的设置)：
- 对于所有类型的端口设置，dsn方式都可以使用，最为方便，也便于进行管理；
- 如果要使用hosts方式，则从库必须配置参数
report_host和report_port；- 主库如果是标准端口(3306)，则只能使用hosts或dsn方式；
- 主库如果是非标准端口(非3306)，则根据从库是否配置参数
report_host和report_port来决定：
有配置可选择hosts方式；
没有配置则只能选择processlist或dsn方式。
- 执行整个数据库校验
# pt-table-checksum h=192.168.58.3,P=3306,u=admin --ask-pass --databases=employees --no-check-binlog-format --recursion-method dsn=D=percona,t=dsns
- 执行表某个字段校验
这里以employees_ptchksum表的first_name字段为例。
# pt-table-checksum h=192.168.58.3,P=3306,u=admin --ask-pass --databases=employees --tables=employees_ptchksum --columns=first_name --no-check-binlog-format --recursion-method dsn=D=percona,t=dsns
- 只打印校验查询语句
指定选项--explain。
# pt-table-checksum h=192.168.58.3,P=3306,u=admin --ask-pass --databases=employees --tables=employees_ptchksum --no-check-binlog-format --recursion-method dsn=D=percona,t=dsns --explain
Enter MySQL password:
Checking if all tables can be checksummed ...
Starting checksum ...
--
-- employees.employees_ptchksum
--
REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT ?, ?, ?, ?, ?, ?, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, convert(`first_name` using utf8mb4), convert(`last_name` using utf8mb4), convert(`gender` using utf8mb4), `hire_date`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= ?)) AND ((`emp_no` <= ?)) /*checksum chunk*/
REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT ?, ?, ?, ?, ?, ?, COUNT(*), '0' FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` < ?)) ORDER BY `emp_no` /*past lower chunk*/
REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT ?, ?, ?, ?, ?, ?, COUNT(*), '0' FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` > ?)) ORDER BY `emp_no` /*past upper chunk*/
SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= ?)) ORDER BY `emp_no` LIMIT ?, 2 /*next chunk boundary*/
- 指定校验分块(chunk)大小
会覆盖工具动态调整chunk大小的行为。
# pt-table-checksum h=192.168.58.3,P=3306,u=admin --ask-pass --databases=employees --tables=employees_ptchksum --no-check-binlog-format --recursion-method dsn=D=percona,t=dsns --chunk-size=66666
- 指定每个分块(chunk)校验时间
动态调整chunk大小，使得校验操作可以在指定的时间内完成。
# pt-table-checksum h=192.168.58.3,P=3306,u=admin --ask-pass --databases=employees --tables=employees_ptchksum --no-check-binlog-format --recursion-method dsn=D=percona,t=dsns --chunk-time=2
工作流程
通过general log来了解pt-table-checksum工具如何进行校验，以及如何进行校验语句查询的，以下以校验employees_ptchksum表为例。
-- 初始的一些检查数据库参数、负载信息这里不再细说
51 Connect admin@dbabd1 on using TCP/IP
51 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
51 Query SET SESSION innodb_lock_wait_timeout=1
51 Query SHOW VARIABLES LIKE 'wait\_timeout'
51 Query SET SESSION wait_timeout=10000
51 Query SELECT @@SQL_MODE
51 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
51 Query SELECT @@server_id /*!50038 , @@hostname*/
51 Query SELECT @@SQL_MODE
51 Query SET SQL_MODE=',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
51 Query SHOW VARIABLES LIKE 'version%'
51 Query SHOW ENGINES
51 Query SHOW VARIABLES LIKE 'innodb_version'
-- 设置会话级binlog row格式为STATEMENT
51 Query SELECT @@binlog_format
51 Query /*!50108 SET @@binlog_format := 'STATEMENT'*/
-- 设置会话级隔离级别为RR(REPEATABLE READ)
51 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
51 Query SHOW /*!40103 GLOBAL*/ VARIABLES
51 Query SELECT VERSION()
51 Query SHOW ENGINES
51 Query SHOW VARIABLES LIKE 'wsrep_on'
52 Connect admin@dbabd1 on percona using TCP/IP
52 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
52 Query SET SESSION innodb_lock_wait_timeout=1
52 Query SHOW VARIABLES LIKE 'wait\_timeout'
52 Query SET SESSION wait_timeout=10000
52 Query SELECT @@SQL_MODE
52 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
52 Query SELECT @@server_id /*!50038 , @@hostname*/
52 Query SHOW VARIABLES LIKE 'wsrep_on'
52 Query SELECT dsn FROM `percona`.`dsns` ORDER BY id
52 Quit
51 Query SHOW VARIABLES LIKE 'wsrep_on'
51 Query SELECT @@SERVER_ID
51 Query SHOW VARIABLES LIKE 'wsrep_on'
51 Query SELECT @@SERVER_ID
-- 创建存储校验结果表percona.checksums
51 Query SHOW DATABASES LIKE 'percona'
51 Query CREATE DATABASE IF NOT EXISTS `percona` /* pt-table-checksum */
51 Query USE `percona`
51 Query SHOW TABLES FROM `percona` LIKE 'checksums'
51 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 DEFAULT CHARSET=utf8
-- 检查数据库服务器运行状态
51 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
51 Query SELECT CONCAT(@@hostname, @@port)
51 Query SELECT CRC32('test-string')
51 Query SELECT CRC32('a')
51 Query SELECT CRC32('a')
51 Query SHOW VARIABLES LIKE 'wsrep_on'
51 Query SHOW DATABASES
51 Query SHOW /*!50002 FULL*/ TABLES FROM `employees`
51 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
-- 开始对表employees_ptchksum进行分析
51 Query USE `employees`
51 Query SHOW CREATE TABLE `employees`.`employees_ptchksum`
51 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
-- 获取表信息，通过主键或唯一索引，获取校验第一个chunk下边界起点，这里为emp_no = 10001
51 Query EXPLAIN SELECT * FROM `employees`.`employees_ptchksum` WHERE 1=1
51 Query SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) ORDER BY `emp_no` LIMIT 1 /*first lower boundary*/
-- 通过索引获取校验表行数
51 Query SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees_ptchksum` FORCE INDEX (`PRIMARY`) WHERE `emp_no` IS NOT NULL ORDER BY `emp_no` LIMIT 1 /*key_len*/
51 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ * FROM `employees`.`employees_ptchksum` FORCE INDEX (`PRIMARY`) WHERE `emp_no` >= '10001' /*key_len*/
-- 清除表percona.checksums中有关employees_ptchksum表的校验结果信息
51 Query USE `percona`
51 Query DELETE FROM `percona`.`checksums` WHERE db = 'employees' AND tbl = 'employees_ptchksum'
-- 确定每个chunk包含的行数，首个块默认为1000行，可以根据系统状态调整，调整每个块校验完成默认时间为0.5s
51 Query USE `employees`
51 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '10001')) ORDER BY `emp_no` LIMIT 999, 2 /*next chunk boundary*/
51 Query SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '10001')) ORDER BY `emp_no` LIMIT 999, 2 /*next chunk boundary*/
-- 确定本次chunk校验查询的执行计划，并通过replace into方式写入校验结果表
51 Query EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, convert(`first_name` using utf8mb4), convert(`last_name` using utf8mb4), convert(`gender` using utf8mb4), `hire_date`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '10001')) AND ((`emp_no` <= '11000')) /*explain checksum chunk*/
51 Query REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'employees', 'employees_ptchksum', '1', 'PRIMARY', '10001', '11000', COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, convert(`first_name` using utf8mb4), convert(`last_name` using utf8mb4), convert(`gender` using utf8mb4), `hire_date`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '10001')) AND ((`emp_no` <= '11000')) /*checksum chunk*/
-- 再次查看校验chunk的行数和校验结果，并更新校验结果表
51 Query SHOW WARNINGS
51 Query SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'employees' AND tbl = 'employees_ptchksum' AND chunk = '1'
51 Query UPDATE `percona`.`checksums` SET chunk_time = '0.007819', master_crc = '4f6eb3dc', master_cnt = '1000' WHERE db = 'employees' AND tbl = 'employees_ptchksum' AND chunk = '1'
-- 查看数据库服务器状态，并进行下一个chunk的检查校验
51 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
51 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '11001')) ORDER BY `emp_no` LIMIT 63946, 2 /*next chunk boundary*/
51 Query SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '11001')) ORDER BY `emp_no` LIMIT 63946, 2 /*next chunk boundary*/
51 Query EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, convert(`first_name` using utf8mb4), convert(`last_name` using utf8mb4), convert(`gender` using utf8mb4), `hire_date`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '11001')) AND ((`emp_no` <= '74947')) /*explain checksum chunk*/
51 Query REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'employees', 'employees_ptchksum', '2', 'PRIMARY', '11001', '74947', COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, convert(`first_name` using utf8mb4), convert(`last_name` using utf8mb4), convert(`gender` using utf8mb4), `hire_date`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '11001')) AND ((`emp_no` <= '74947')) /*checksum chunk*/
51 Query SHOW WARNINGS
51 Query SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'employees' AND tbl = 'employees_ptchksum' AND chunk = '2'
51 Query UPDATE `percona`.`checksums` SET chunk_time = '0.148209', master_crc = '48c0faee', master_cnt = '63947' WHERE db = 'employees' AND tbl = 'employees_ptchksum' AND chunk = '2'
……省略……
51 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
51 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '474878')) ORDER BY `emp_no` LIMIT 291764, 2 /*next chunk boundary*/
51 Query SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '474878')) ORDER BY `emp_no` LIMIT 291764, 2 /*next chunk boundary*/
51 Query SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) ORDER BY `emp_no` DESC LIMIT 1 /*last upper boundary*/
51 Query EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, convert(`first_name` using utf8mb4), convert(`last_name` using utf8mb4), convert(`gender` using utf8mb4), `hire_date`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '474878')) AND ((`emp_no` <= '499999')) /*explain checksum chunk*/
51 Query REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'employees', 'employees_ptchksum', '4', 'PRIMARY', '474878', '499999', COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, convert(`first_name` using utf8mb4), convert(`last_name` using utf8mb4), convert(`gender` using utf8mb4), `hire_date`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '474878')) AND ((`emp_no` <= '499999')) /*checksum chunk*/
51 Query SHOW WARNINGS
51 Query SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'employees' AND tbl = 'employees_ptchksum' AND chunk = '4'
51 Query UPDATE `percona`.`checksums` SET chunk_time = '0.036433', master_crc = '6ca4b1c9', master_cnt = '25122' WHERE db = 'employees' AND tbl = 'employees_ptchksum' AND chunk = '4'
-- 上述校验到chunk = 4其实已经包含整张表的数据，以下的chunk范围为emp_no < 10001和emp_no > 4999999，这样做的意图很明显，因为工具并不能确认从库在这两个chunk所对应的数据范围内没有数据存在，为了保证检查数据的完整性，所以进行这样的检查操作，包括了所有的可能性。
'对应emp_no < 10001'
51 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
51 Query EXPLAIN SELECT COUNT(*), '0' FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` < '10001')) ORDER BY `emp_no` /*explain past lower chunk*/
51 Query REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'employees', 'employees_ptchksum', '5', 'PRIMARY', NULL, '10001', COUNT(*), '0' FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` < '10001')) ORDER BY `emp_no` /*past lower chunk*/
51 Query SHOW WARNINGS
51 Query SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'employees' AND tbl = 'employees_ptchksum' AND chunk = '5'
51 Query UPDATE `percona`.`checksums` SET chunk_time = '0.062096', master_crc = '0', master_cnt = '0' WHERE db = 'employees' AND tbl = 'employees_ptchksum' AND chunk = '5'
'对应emp_no > 499999'
51 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
51 Query EXPLAIN SELECT COUNT(*), '0' FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` > '499999')) ORDER BY `emp_no` /*explain past upper chunk*/
51 Query REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'employees', 'employees_ptchksum', '6', 'PRIMARY', '499999', NULL, COUNT(*), '0' FROM `employees`.`employees_ptchksum` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` > '499999')) ORDER BY `emp_no` /*past upper chunk*/
51 Query SHOW WARNINGS
51 Query SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'employees' AND tbl = 'employees_ptchksum' AND chunk = '6'
51 Query UPDATE `percona`.`checksums` SET chunk_time = '0.002064', master_crc = '0', master_cnt = '0' WHERE db = 'employees' AND tbl = 'employees_ptchksum' AND chunk = '6'
51 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
51 Query SHOW MASTER STATUS
51 Quit
根据
general log可以看出，pt-table-checksum主要工作的流程可以总结如下：
- 连接主库和从库，查询当前数据库服务器信息，包括参数设置，负载信息等；
- 根据工具选项设置会话级别参数，设置会话级
binlog row format为STATEMENT；- 根据工具选项创建校验结果表(默认为percona.checksums表)，查看当前数据库服务器运行状态；
- 获取校验的数据库和表，逐张顺序进行检查校验；
- 开始分析表，根据表的索引(如有)，将表分成多个chunk，每个chunk包含多行，默认为1000，chunk对应的表行数可以根据数据库性能状态动态调整；
- 根据以上分析生成表的校验语句，并检查表结构；
- 开始进行表的校验分析，为了保证一致性，这个阶段会将当前chuck所包含的行加上行锁，并将校验的结果以
replace into方式存入校验结果表；- 再次查询校验结果表，并更新master_crc、master_cnt的值(主库)，如果是从库则是this_crc、this_cnt；
- 根据数据库运行状态调整下一个chunk所包含行数；
- 继续下一个chunk的校验检查，直到表中所有的chunk校验完成；
- 从库运行完校验检查，汇总这张表的结果；
- 循环完成所有需要校验的表直到完成所有的表的校验操作。
总结
- pt-table-checksum是当前MySQL主从数据库数据一致性校验比较好的工具，但也会对服务器性能造成影响，尽管影响的范围有限，在进行每个chunk检查校验时会对chunk中涉及的表行加锁，所以，检验还是尽量在业务低峰期进行操作；
- pt-table-checksum工具无法对主从表结构不一致的表进行校验；
- 校验结果只能看出哪些表出现不一致性，并无法得出具体出现多少行数据不一致，哪些数据行不一致等。
参考
https://www.percona.com/doc/percona-toolkit/LATEST/pt-table-checksum.html
http://seanlook.com/2015/12/29/mysql_replica_pt-table-checksum/
http://keithlan.github.io/2016/05/25/pt_table_checksum/
☆〖本人水平有限，文中如有错误还请留言批评指正！〗☆
