percona-toolkit--pt-table-checksum

pt-table-checksum 是 Percona-Toolkit的组件之一，用于检测MySQL主、从库的数据是否一致。其原理是在主库执行基于statement的sql语句来生成主库数据块的checksum，把相同的sql语句传递到从库执行，并在从库上计算相同数据块的checksum，最后，比较主从库上相同数据块的checksum值，由此判断主从数据是否一致。检测过程根据唯一索引将表按row切分为块（chunk），以为单位计算，可以避免锁表。检测时会自动判断复制延迟、 master的负载， 超过阀值后会自动将检测暂停，减小对线上服务的影响。
pt-table-checksum 默认情况下可以应对绝大部分场景，官方说，即使上千个库、上万亿的行，它依然可以很好的工作，这源自于设计很简单，一次检查一个表，不需要太多的内存和多余的操作；必要时，pt-table-checksum 会根据服务器负载动态改变 chunk 大小，减少从库的延迟。

pt-table-checksum要求主从复制是基于STATEMENT格式进行的复制，并且会在主库执行binlog_format=STATEMENT语句。由于MySQL的限制，这一设置并不会传递到从库进行。因此当从库的binlog格式是ROW时，无法直接对从库的从库进行校验操作。工具在进行校验操作时都会检查所有从库的binlog_format，可以使用选项--[no]check-binlog-format进行控制；工具假设主从数据库中SCHEMA和表结构都是一致的。如果从库上不存在主库的SCHEMA，或者是从库上表结构与主库不一致，则有可能导致主从复制中断

为了减少对数据库的干预，pt-table-checksum还会自动侦测并连接到从库，当然如果失败，可以指定--recursion-method

默认情况，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：在主（master）上通过执行校验的查询对复制的一致性进行检查，对比主从的校验值，从而产生结果。DSN指向的是主的地址，该工具的退出状态不为零，如果发现有任何差别，或者如果出现任何警告或错误.


CREATE TABLE `dsns` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) );

INSERT INTO dsns (parent_id,dsn) values(1, "h=192.168.1.129,u=admin,p=123456,P=3307");  

pt-table-checksum  --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums --databases=check_sum  h=192.168.1.128,u=admin,p=123456,P=3306--recursion-method=dsn=h=192.168.1.128,
D=test,t=dsns

在等号的两侧不能有空格出现，并且区分大小写，多个选项之前以','(逗号)隔开，主要选项如下：

A
指定字符集
D
指定DSN表所在数据库
t
指定DSN表
h
指定要连接的HOST
P
指定要连接的PORT
S
指定连接所使用的SOCKET文件(Unix systems)
u
指定连接的用户名
p
指定连接的用户名密码

选项来告诉从库在哪里。它的易用性还体现在，复制若有延迟，在从库 checksum 会暂停直到赶上主库的计算时间点（也通过选项--设定一个可容忍的延迟最大值，超过这个值也认为不一致）。

为了保证主数据库服务的安全，该工具实现了许多保护措施：
    1）自动设置 innodb_lock_wait_timeout 为1s，避免引起
    2）默认当数据库有25个以上的并发查询时，pt-table-checksum会暂停。可以设置 --max-load 选项来设置这个阀值
    3）当用 Ctrl+C 停止任务后，工具会正常的完成当前 chunk 检测，下次使用 --resume 选项启动可以恢复继续下一个 chunk

常用参数解释：

--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和其他程序的最新版本。

最重要的一点就是：
要在主库上授权，能让主库ip访问。这一点不能忘记！（实验证明从库上可以不授权，但最好还是从库也授权）
注意：
1）根据测试，需要一个即能登录主库，也能登录从库的账号；
2）只能指定一个host，必须为主库的IP；
3）在检查时会向表加S锁；
4）运行之前需要从库的同步IO和SQL进程是YES状态。表要有主键索引或唯一键索引

工作过程

1\. 连接到主库：pt工具连接到主库，然后自动发现主库的所有从库。默认采用show full processlist来查找从库，但是这只有在主从实例端口相同的情况下才有效。
3\. 查找主库或者从库是否有复制过滤规则：这是为了安全而默认检查的选项。你可以关闭这个检查，但是这可能导致checksum的sql语句要么不会同步到从库，要么到了从库发现从库没有要被checksum的表，这都会导致从库同步卡库。
5\. 开始获取表，一个个的计算。
6\. 如果是表的第一个chunk，那么chunk-size一般为1000；如果不是表的第一个chunk，那么采用19步中分析出的结果。
7\. 检查表结构，进行数据类型转换等，生成checksum的sql语句。
8\. 根据表上的索引和数据的分布，选择最合适的split表的方法。
9\. 开始checksum表。
10\. 默认在chunk一个表之前，先删除上次这个表相关的计算结果。除非–resume。
14\. 根据explain的结果，判断chunk的size是否超过了你定义的chunk-size的上限。如果超过了，为了不影响线上性能，这个chunk将被忽略。
15\. 把要checksum的行加上for update锁，并计算。
17-18\. 把计算结果存储到master_crc master_count列中。
19\. 调整下一个chunk的大小。
20\. 等待从库追上主库。如果没有延迟备份的从库在运行，最好检查所有的从库，如果发现延迟最大的从库延迟超过max-lag秒，pt工具在这里将暂停。
21\. 如果发现主库的max-load超过某个阈值，pt工具在这里将暂停。
22\. 继续下一个chunk，直到这个table被chunk完毕。
23-24\. 等待从库执行完checksum，便于生成汇总的统计结果。每个表汇总并统计一次。
25-26\. 循环每个表，直到结束。

校验结束后，在每个从库上，执行如下的sql语句即可看到是否有主从不一致发生：

select * from percona.checksums where master_cnt <> this_cnt OR master_crc <> this_crc OR 
ISNULL(master_crc) <> ISNULL(this_crc) \G

命令：

pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=bim.checksums --create-replicate-table --databases=bim  h=103.32.132.166,u=root,p=0202,P=3306

Cannot connect to P=3306,h=10.2.132.160,p=...,u=root
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
08-03T10:17:52      0      0     1829          0       1       0   0.445 bim.a20200911
08-03T10:17:53      0      0        0          0       1       0   0.358 bim.a_pwd_change
08-03T10:17:53      0      0        0          0       1       0   0.361 bim.act_evt_log
08-03T10:17:53      0      0        0          0       1       0   0.360 bim.act_ge_bytearray
08-03T10:17:54      0      0        3          0       1       0   0.359 bim.act_ge_property
08-03T10:17:54      0      0        0          0       1       0   0.382 bim.act_hi_actinst
08-03T10:17:55      0      0        0          0       1       0   0.356 bim.act_hi_attachment
08-03T10:17:55      0      0        0          0       1       0   0.365 bim.act_hi_comment
08-03T10:17:55      0      0        0          0       1       0   0.392 bim.act_hi_detail
08-03T10:17:56      0      0        0          0       1       0   0.350 bim.act_hi_identitylink
08-03T10:17:56      0      0        0          0       1       0   0.483 bim.act_hi_procinst
08-03T10:17:56      0      0        0          0       1       0   0.310 bim.act_hi_taskinst
08-03T10:17:57      0      0        0          0       1       0   0.316 bim.act_hi_varinst
08-03T10:17:57      0      0        0          0       1       0   0.299 bim.act_re_deployment
08-03T10:17:57      0      0        0          0       1       0   0.376 bim.act_re_model
08-03T10:17:58      0      0        0          0       1       0   0.366 bim.act_re_procdef
08-03T10:17:58      0      0        0          0       1       0   0.352 bim.act_ru_event_subscr
08-03T10:17:59      0      0        0          0       1       0   0.378 bim.act_ru_execution
08-03T10:17:59      0      0        0          0       1       0   0.345 bim.act_ru_identitylink
08-03T10:17:59      0      0        0          0       1       0   0.347 bim.act_ru_job
08-03T10:18:00      0      0        0          0       1       0   0.476 bim.act_ru_task
 

解释：
TS ：完成检查的时间。
ERRORS ：检查时候发生错误和警告的数量。
DIFFS ：0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息。
ROWS ：表的行数。
CHUNKS ：被划分到表中的块的数目。
SKIPPED ：由于错误或警告或过大，则跳过块的数目。
TIME ：执行的时间。
TABLE ：被检查的表名。

在主库里添加pt-table-checksum检查的权限（从库可以不授权）后，进行数据一致性检查操作，会在操作的库（实例中是huanqiu、huanpc）下产生一个checksums表！
这张checksums表是pt-table-checksum检查过程中产生的。这张表一旦产生了，默认是删除不了的，并且这张表所在的库也默认删除不了，删除后过一会儿就又会出来。

要想删除的话，一定要先把pt-table-checksum检查前添加的权限收回！checksums表一旦产生，不仅这张表默认删除不了，连同它所在的库，要是想删除它们，只能如上操作先撤销权限。