pt-table-checksum – 检查MySQL主从数据一致性
2,674 total views, 2 views today
pt-table-checksum用于在线检测MySQL主从一致性，其原理是在主库执行checksum查询，然后与从库进行结果的比对，从而得出是否一致性的报告。
pt-table-checksum checksum每张表，然后得出每个从库的一致性报告。pt-table-checksum 工具只关注数据的不一致，修复数据一致性需要用到 pt-table-sync 工具。
用法：
|
1 |
Usage: pt-table-checksum [OPTIONS] [DSN] |
原理：
pt-table-checksum 连接指定的主库，然后查找数据库和表（如果指定了过滤条件，则按过滤条件查找）。它同一时间只checksum 一张表，所以不会消耗过多的内存和资源。对于大数据库的数据库来说，这是一个非常有用的设计，无论数据库有多大，比如一个server中有几百个数据库和表，数万亿（trillions）的行，我们都无需担心，pt-table-checksum都可以胜任。
可以胜任的原因是， pt-table-checksum 将每张表都拆分成行块（chunks of rows），默认是1000行。然后对每个行块使用单独的REPLACE…SELECT 进行checksum 查询。行块（chunks of rows）大小随着设定的–chunk-time 时间（默认0.5秒）动态调节。每次 checksum 查询执行完毕后，下次checksum 查询会根据上次执行的时间调整 行块（chunks of rows）大小，也就是通过学习系统的负载情况来调节行块大小。 如果不设定 –chunk-time ，行块大小则不会自动调整。这样就不会形成一个大的checkum 查询操作，确保了主从复制不会产生大的延迟或者负载。
pt-table-checksum 利用索引来排序，从而将表拆分成多个行块（最好是主键或者唯一键）。如果没有索引，但是表中只有少量数据，pt-table-checksum 可以将该表直接拆分成一个行块进行checksum 查询。建议MySQL中每张表都有主键，如果表很大没有主键、唯一键、索引，那么会产生如下错误
|
1 |
12-20T15:03:51 Cannot checksum table cnail.g1: There is no good index and the table is oversized. at /usr/local/bin/pt-table-checksum line 6559. |
最好创建一个专门用于checksum的用户，它需要有连接到从库的权限。
|
1 2 |
GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'checksum'@'10.%.%.%' IDENTIFIED BY 'checksum'; GRANT ALL PRIVILEGES ON *.* TO 'checksum'@'10.%.%.%' IDENTIFIED BY 'checksum'; |
如果pt-table-checksum 在主库连接不到从库会报错
|
1 2 |
Cannot connect to h=10.9.15.132,p=...,u=root Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information. |
只检查cnail一个库数据是否一致，使用–databases参数
|
1 |
# pt-table-checksum --user=checksum --password=checksum --host=10.9.15.131 --databases=cnail --replicate=cnail.checksums --create-replicate-table --no-check-binlog-format |
检查所有库是否一致
|
1 |
# pt-table-checksum --user=checksum --password=checksum --host=10.9.15.131 --replicate=cnail.checksums --create-replicate-table --no-check-binlog-format |
只打印输出数据不一致的表，会显示具体是哪个从库的哪个表不一致。
|
1 2 3 4 |
# pt-table-checksum --user=checksum --password=checksum --host=10.9.15.131 --replicate=cnail.checksums --create-replicate-table --no-check-binlog-format --max-load='Threads_connected=120' --replicate-check-only <strong><span style="color: #ff0000;">Differences on MySQL02 TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY cnail.max_test 1 -10 1</span> </strong> |
参数说明：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
--replicate pt-table-checksum 数据存放的表，格式schema.table_name Create Table: CREATE TABLE `checksums` ( `db` char(64) NOT NULL, `tbl` char(64) NOT NULL, `chunk` int(11) NOT NULL, `chunk_time` float DEFAULT NULL, `chunk_index` varchar(200) DEFAULT NULL, `lower_boundary` text, `upper_boundary` text, `this_crc` char(40) NOT NULL, `this_cnt` int(11) NOT NULL, `master_crc` char(40) DEFAULT NULL, `master_cnt` int(11) DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`db`,`tbl`,`chunk`), KEY `ts_db_tbl` (`ts`,`db`,`tbl`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 --create-replicate-table 如果checksum结果表不存在，自动创建 --no-check-binlog-format 不检查binlog的格式，由于现在一般都是使用row格式，而pt-table-checksum需要的是语句格式，会报错，所以设置为不检测。 --max-lag 默认1秒，从库延迟大于该值，pt-table-checksum暂停，小于该值后再次启动。减少对主从同步的影响。 --max-load 限制最大负载数，如果超过设定值，pt-table-checksum暂停，小于设定值则恢复，起到保护正常业务的目的。 比如 --max-load='Threads_connected=120', 表示当前thread数量超过120，pt-table-checksum暂停，小于120则继续。 |
结果输出情况
|
1 2 3 4 5 6 7 8 9 10 11 |
# pt-table-checksum --user=checksum --password=checksum --host=10.9.15.131 --replicate=cnail.checksums --create-replicate-table --no-check-binlog-format --max-load='Threads_connected=120' TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 12-20T16:52:13 0 0 8 1 0 0.008 cnail.autoinc1 12-20T16:52:13 0 0 4 1 0 0.009 cnail.autoinc2 12-20T16:52:13 0 1 1001 1 0 0.009 cnail.max_test 12-20T16:52:13 0 0 3 1 0 0.010 cnail.c1 12-20T16:52:13 0 0 3 1 0 0.008 cnail.c2 12-20T16:52:13 0 0 1 1 0 0.009 cnail.heartbeat 12-20T16:52:14 0 0 1 1 0 0.009 cnail.kp 12-20T16:52:14 0 0 0 1 0 0.007 cnail.lag 12-20T16:52:14 0 0 1001 1 0 0.009 cnail.max_test |
输出内容介绍：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
TS 时间戳，不包含年，checksum该表的完成时间戳 ERRORS checksum表时产生的errors和warnnings的个数。 DIFFS 主从不同的行块数量（The number of chunks）。如果指定 --no-replicate-check ,该列的值一致为0。 如果设置 --replicate-check-only，只会打印输出数据不一致的表。 ROWS 表的行数。 CHUNKS 表被拆分成的行块（The number of chunks）的数量。 SKIPPED 由于下列问题，跳过的行块（The number of chunks）数量。 * 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 checksum表花费的时间 TABLE 已经被checksum的数据库和表名称。 |
案例：模拟主从数据不一致
在从库中删除10条记录，使主从不一致
然后进行checksum
方式1：输出所有表，注意观察DIFFS列，如果不为0，则表示数据不一致。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
# pt-table-checksum --user=checksum --password=checksum --host=10.9.15.131 --replicate=cnail.checksums --create-replicate-table --no-check-binlog-format --max-load='Threads_connected=120' TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 12-20T17:07:12 0 0 8 1 0 0.008 cnail.autoinc1 12-20T17:07:12 0 0 4 1 0 0.008 cnail.autoinc2 12-20T17:07:12 0 0 3 1 0 0.006 cnail.c1 12-20T17:07:12 0 0 3 1 0 0.007 cnail.c2 12-20T17:07:12 0 0 1 1 0 0.008 cnail.heartbeat 12-20T17:07:12 0 0 1 1 0 0.006 cnail.kp 12-20T17:07:12 0 0 0 1 0 0.007 cnail.lag 12-20T17:07:12 0 1 1001 1 0 0.011 cnail.max_test 12-20T17:07:13 0 0 1 1 0 0.010 cnail.no_trans |
方式2：推荐！由于表数据量众多，所有表都输出，让人无所适从。还好有 –replicate-check-only 参数，只输出不一致的表的信息。
|
1 2 3 4 5 6 |
# pt-table-checksum --user=checksum --password=checksum --host=10.9.15.131 --replicate=cnail.checksums --create-replicate-table --no-check-binlog-format --max-load='Threads_connected=120' --replicate-check-only Differences on MySQL02 TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY cnail.max_test 1 -10 1 --表示从库上cnail.max_test 比主库少了10行数据 cnail.pk_test 1 -1000 1 PRIMARY 1 102805 |
输出结果说明：
|
1 2 3 4 5 6 7 8 |
Differences on MySQL02 数据不一致的从库信息。 TABLE 数据不一致的表名称 CHUNK 不一致性的行块数,这里是1。 CNT_DIFF 从库表的行数 减去 主库表的行数的差值，-10表示从库比主库少10行数据。 CRC_DIFF 1 if the CRC of the chunk on the replica is different than the CRC of the chunk on the master, else 0. CHUNK_INDEX 用到的索引，如果有主键，则是PRIMARY。 LOWER_BOUNDARY The index values that define the lower boundary of the chunk. UPPER_BOUNDARY The index values that define the upper boundary of the chunk. |
修复一致性，需要用到 pt-table-sync 工具，在下篇文章会介绍。
更多pt-toolkit详细使用方法，请点击这里。