pt-table-checksum使用总结

1、pt-table-checksum概述

    pt-table-checksum是Percona-Toolkit工具集中的一个组件，是一个检查主从复制一致性的工具，通过在主库上执行检查语句可以在线检查MySQL主从数据的一致性。它通过指定选项连接指定服务器实例，可以通过指定条件过滤库级别或表级别，每次检查一张表，所以不需要大量的内存和多余操作。即使是非常大的表也可以运行良好，这主要是因为pt-table-checksum可以将表划分为多个chunk，根据checksum query运行的时间来调整chunk的大小，减少了pt-table-checksum运行对主从复制延迟和主机负载的影响。为了保证对数据库主机运行尽可能少的影响，pt-table-checksum会自动探测从库并连接，如果连接失败，可以通过选项--recursion-method选定从库连接方式。

2、pt-table-checksum使用方法

语法：pt-table-checksum [OPTIONS] [DSN]

2.1  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分块操作，默认情况下会选择最优的索引，使用force index可以指定索引。

--chunk-size：默认值1000，指定chunk大小，每个chunk需要校验的行数，单位可以为k、m或者g。使用这个参数后，该工具就不会动态的调整chunk的大小，以便chunk的校验在--chunk-time规定的时间内完成。大多数情况下不使用该参数，而是用--chunk-time。

--chunk-size-limit：默认值2，指定chunk的行数最多可以超过--chunk-size规定行数的多少倍，最小值为1倍，即不超过--chunk-size规定的行数。由于行数是由explain估算的，可能与实际行数不相等，因此不建议设置为1。当参数值为0时，则不会检查是否超过指定的行数。

--chunk-time：默认值0.5，动态调整chunk的大小使得cunk内的行数可以在指定的时间执行完。如果设置为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;

注意: lower_boundary and upper_boundary的数据类型可以是BLOB。因为选项“--[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，如果没有严重错误（如lock wait timeout or the query being killed），指定重复校验chunk的次数。

--run-time：指定校验运行时间，默认校验完所有的表。

--set-vars：进行校验时指定参数，多个参数用“,”隔开。

--socket -S：指定socket文件。

--tables -t：指定需要校验哪些表，多个表之间用“,”隔开。

--databases -d：指定需要校验哪些库，多个库之间用“,”隔开。

--host，-h：指定连接的数据库IP地址。

--port，-P：指定连接的数据库Port端口。

--user，-u：指定连接的数据库用户。

--password，-p：指定连接的数据库用户密码。

--progress：打印工具执行过程的进度提示到STDERR。选项值有两部分组成，用逗号进行分隔，第一部分为百分比，时间和迭代。第二部分为根据第一部分数据更新频率，也分为百分比，时间和迭代。

--quiet，-q：不打印工具执行过程的信息到STDOUT(禁用'--progress')。但错误和警告还是打印到STDERR。

--recurse：指定递归搜寻从库的层级，默认无限级。

--recursion-method：默认值包括processlist、hosts，指定获取从库的方式。该工具在校验期间会多次执行“REPLICA CHECKS”。如果没有获取到从库，可以换一种recursion method，或者使用dsn指定需要校验的从库。

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及更新版本，例如Percona XtraDB Cluster versions 5.5.29 and above。

dsn：这种方法指定表dsns存放从库的信息（"h=replica_host,u=repl_user,p=repl_pass"），该工具只会对表中的从库进行校验。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`)
);

2.2 DSN选项

    dsn选项为“optipn=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

2.3 使用案例

 

#在主库上创建percona_schema库和dsns表，插入从库的host，port，user，passwd
> create database if not exists percona_schema;
> use percona_schema;
> CREATE TABLE if not exists dsns (
    -> id int(11) NOT NULL AUTO_INCREMENT,
    -> parent_id int(11) DEFAULT NULL,
    -> dsn varchar(255) NOT NULL,
    -> PRIMARY KEY (id)
    -> );

> show tables;
> desc dsns;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| parent_id | int(11)      | YES  |     | NULL    |                |
| dsn       | varchar(255) | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

#将从库的dsn信息写入dsns表
> insert into percona_schema.dsns(dsn) VALUES ("h=10.192.168.52,P=3306,u=checksum,p=123456");
> select * from percona_schema.dsns;

#校验单表
pt-table-checksum h=10.192.168.51,u=checksum,p='123456',P=3306  --tables=test.t1 --replicate=percona_schema.checksums --no-check-binlog-format --nocheck-replication-filters --recursion-method dsn=t=percona_schema.dsns,h=10.192.168.52,u=checksum,p='123456',P=3306

#校验全库
pt-table-checksum h=10.192.168.51,u=checksum,p='123456',P=3306 --databases=test --replicate=percona_schema.checksums --nocheck-replication-filters --no-check-binlog-format  --chunk-size-limit=2000 --recursion-method dsn=t=percona_schema.dsns,h=10.192.168.52,u=checksum,p='123456',P=3306

说明：
-chunk-size-limit=2000(无索引的大表需要使用)