pt-table-checksum – 检查MySQL主从数据一致性
2,591 total views, 3 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详细使用方法,请点击这里。