pt-table-checksum是percona公司提供的一個用於在線比對主從數據一致性的工具。
實現原理
將一張大表分成多個chunk，每次針對一個chunk進行校驗，同時將校驗的結果通過REPLACE INTO語句寫入到percona.checksums表中，然後該語句通過主從復制，在SLAVE中同樣執行一次，校驗的結果同樣是寫入到percona.checksums表中，最後，通過查詢percona.checksums來獲取主從不一致的信息。
常見用法
1. 基本用法
# pt-table-checksum -h192.168.244.10 -umonitor -pmonitor123
其中，monitor的最小權限如下（第二個權限是針對percona.checksums的）：
GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'monitor'@'192.168.244.10';
GRANT ALL PRIVILEGES ON `percona`.* TO 'monitor'@'192.168.244.10';
2. pt-table-checksum默認是運行在statement下，如果是其它日志格式，需加--no-check-binlog-format參數
# pt-table-checksum -h192.168.244.10 -umonitor -pmonitor123 --no-check-binlog-format
3. 如果主從復制中加了過濾條件，譬如binlog_ignore_db或replicate_do_db之類的參數，需加--no-check-replication-filters參數
# pt-table-checksum -h192.168.244.10 -umonitor -pmonitor123 --no-check-binlog-format --no-check-replication-filters
如果在對被過濾表進行校驗時，命令hang住了，可加--replicate-database參數。
4. 基於指定庫的校驗
# pt-table-checksum -h192.168.244.10 -umonitor -pmonitor123 --no-check-binlog-format --databases=test,test1
5. 基於指定表的校驗
# pt-table-checksum -h192.168.244.10 -umonitor -pmonitor123 --no-check-binlog-format --tables=test2.test
其它具體用法，可參考另外一篇博客：pt-table-checksum參數詳解
通過打開general_log來看看其具體的執行過程，注意，測試表是test.test，共1000000條記錄。
17 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
查看Threads_running變量是為了查看當前系統的負載情況
17 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
設置會話變量
17 Query USE `test` 17 Query SHOW CREATE TABLE `test`.`test`
查看test表的表結構，選取分片鍵，一般為主鍵或唯一索引 17 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */ 17 Query EXPLAIN SELECT * FROM `test`.`test` WHERE 1=1
查看test表的大概數量 17 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `test`.`test` FORCE INDEX(`PRIMARY`) ORDER BY `id` LIMIT 1 /*first lower boundary*/
選擇第一個chunk的下標，即id的最小值 17 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `test`.`test` FORCE INDEX (`PRIMARY`) WHERE `id` IS NOT NULL ORDER BY `id` LIMIT 1 /*key_len*/
查看索引的長度 17 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`.`test` FORCE INDEX (`PRIMARY`) WHERE `id` >= '1' /*key_len*/
查看實際使用的索引的長度，這個針對聯合索引的場景。 17 Query USE `percona` 17 Query DELETE FROM `percona`.`checksums` WHERE db = 'test' AND tbl = 'test'
從percona.checksums表中刪除之前的校驗記錄 17 Query USE `test` 17 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `test`.`test` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) ORDER BY `id` LIMIT 2171, 2 /*next chunk boundary*/
17 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `test`.`test` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) ORDER BY `id` LIMIT 2171, 2 /*next chunk boundary*/ 確認本chunk的上限，以及下一個chunk的下限。
17 Query EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `ip`, `no`, CONCAT(ISNULL(`ip`), ISNULL(`no`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `test`.`test` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) AND ((`id` <= '2172')) /*explain checksum chunk*/ 查看對本次chunk執行checksum操作的執行計劃，確認讀取的行數是否合理，選擇的索引是否合適
17 Query REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'test', 'test', '1', 'PRIMARY', '1', '2172', COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `ip`, `no`, CONCAT(ISNULL(`ip`), ISNULL(`no`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `test`.`test` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) AND ((`id` <= '2172')) /*checksum chunk*/ 關鍵操作，對本次chunk執行checksum操作，並將結果更新到percona.checksums表中。
17 Query SHOW WARNINGS 17 Query SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'test' AND tbl = 'test' AND chunk = '1'
查看本次操作校驗的行數和校驗和 17 Query UPDATE `percona`.`checksums` SET chunk_time = '0.059603', master_crc = '568c1ba3', master_cnt = '2172' WHERE db = 'test' AND tbl = 'test' AND chunk = '1' 將上面那個查詢得到的行數和校驗和更新到master_cnt和master_crc中。這樣的話，主庫的校驗和在從庫執行replace操作時被覆蓋。
下面是針對第二個chunk執行的操作。 17 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 17 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `test`.`test` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '2173')) ORDER BY `id` LIMIT 18219, 2 /*next chunk boundary*/ 17 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `test`.`test` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '2173')) ORDER BY `id` LIMIT 18219, 2 /*next chunk boundary*/ 17 Query EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `ip`, `no`, CONCAT(ISNULL(`ip`), ISNULL(`no`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `test`.`test` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '2173')) AND ((`id` <= '20392')) /*explain checksum chunk*/ 17 Query REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'test', 'test', '2', 'PRIMARY', '2173', '20392', COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `ip`, `no`, CONCAT(ISNULL(`ip`), ISNULL(`no`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `test`.`test` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '2173')) AND ((`id` <= '20392')) /*checksum chunk*/ 17 Query SHOW WARNINGS 17 Query SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'test' AND tbl = 'test' AND chunk = '2' 17 Query UPDATE `percona`.`checksums` SET chunk_time = '0.022960', master_crc = '83371365', master_cnt = '18220' WHERE db = 'test' AND tbl = 'test' AND chunk = '2' ... 下面的校驗和上面的並不相同，上述id值的范圍是1~1000000，下面兩個chunk的范圍是<1和>1000000,為什麼要這麼做呢?
主要是考慮到從庫有可能存在上述兩個范圍的數據。
17 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 17 Query EXPLAIN SELECT COUNT(*), '0' FROM `test`.`test` FORCE INDEX(`PRIMARY`) WHERE ((`id` < '1')) ORDER BY `id` /*explain past lower chunk*/ 17 Query REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'test', 'test', '9', 'PRIMARY', NULL, '1', COUNT(*), '0' FROM `test`.`test` FORCE INDEX(`PRIMARY`) WHERE ((`id` < '1')) ORDER BY `id` /*past lower chunk*/ 17 Query SHOW WARNINGS 17 Query SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'test' AND tbl = 'test' AND chunk = '9' 17 Query UPDATE `percona`.`checksums` SET chunk_time = '0.004492', master_crc = '0', master_cnt = '0' WHERE db = 'test' AND tbl = 'test' AND chunk = '9' 17 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 17 Query EXPLAIN SELECT COUNT(*), '0' FROM `test`.`test` FORCE INDEX(`PRIMARY`) WHERE ((`id` > '1000000')) ORDER BY `id` /*explain past upper chunk*/ 17 Query REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'test', 'test', '10', 'PRIMARY', '1000000', NULL, COUNT(*), '0' FROM `test`.`test` FORCE INDEX(`PRIMARY`) WHERE ((`id` > '1000000')) ORDER BY `id` /*past upper chunk*/ 17 Query SHOW WARNINGS 17 Query SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'test' AND tbl = 'test' AND chunk = '10' 17 Query UPDATE `percona`.`checksums` SET chunk_time = '0.058622', master_crc = '0', master_cnt = '0' WHERE db = 'test' AND tbl = 'test' AND chunk = '10'
輸出結果說明
# pt-table-checksum -h 192.168.244.10 -umonitor -pmonitor123 --no-check-binlog-format
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 11-09T21:19:01 0 0 0 1 0 2.768 h2.h3 11-09T21:19:06 0 0 0 1 0 3.903 hello.h1 11-09T21:19:06 0 0 0 1 0 0.620 hello.h2 11-09T21:19:10 0 0 0 1 0 1.725 mysql.columns_priv 11-09T21:19:10 0 1 4 1 0 0.457 mysql.db 11-09T21:19:11 0 0 0 1 0 0.306 mysql.event 11-09T21:19:12 0 0 0 1 0 0.721 mysql.func 11-09T21:19:13 0 0 40 1 0 0.649 mysql.help_category 11-09T21:19:14 0 0 611 6 0 1.313 mysql.help_keyword 11-09T21:19:18 0 0 1218 4 0 3.432 mysql.help_relation 11-09T21:19:20 0 0 583 1 0 1.632 mysql.help_topic 11-09T21:19:21 0 0 0 1 0 0.501 mysql.ndb_binlog_index 11-09T21:19:21 0 0 0 1 0 0.319 mysql.plugin 11-09T21:19:22 0 0 0 1 0 0.624 mysql.proc 11-09T21:19:22 0 0 0 1 0 0.626 mysql.procs_priv 11-09T21:19:23 0 0 2 1 0 0.375 mysql.proxies_priv 11-09T21:19:24 0 0 0 1 0 0.806 mysql.servers 11-09T21:19:24 0 1 3 1 0 0.292 mysql.tables_priv 11-09T21:19:24 0 0 0 1 0 0.382 mysql.time_zone 11-09T21:19:25 0 0 0 1 0 0.398 mysql.time_zone_leap_second 11-09T21:19:25 0 0 0 1 0 0.386 mysql.time_zone_name 11-09T21:19:25 0 0 0 1 0 0.393 mysql.time_zone_transition 11-09T21:19:26 0 0 0 1 0 0.313 mysql.time_zone_transition_type 11-09T21:19:26 0 1 18 1 0 0.393 mysql.user 11-09T21:19:27 0 0 1 1 0 0.642 percona.dsns 11-09T21:19:30 0 1 41 1 0 2.613 test.checksum 11-09T21:19:31 0 0 0 1 0 0.669 test.ta 11-09T21:20:13 0 1 1000000 7 0 42.009 test.test 11-09T21:20:15 0 0 5 1 0 1.818 test1.test 11-09T21:20:16 0 0 5 1 0 0.546 test1.test1 11-09T21:20:17 0 1 12 1 0 0.508 test2.test 11-09T21:20:17 0 1 0 1 0 0.387 test2.test2 11-09T21:20:18 0 1 1000 1 0 0.707 test3.test3
TS：校驗完表後的時間戳
ERRORS：校驗過程中出現的errors和warnings的次數。
DIFFS：所有SLAVE中checksum值不相同的chunk的數量，如一主兩從中，SLAVE1的chunk1與MASTER的checksum不同，SLAVE2的chunk1和chunk2不相同，則DIFFS的值為2。如果SLAVE2的chunk2和chunk3不相同，則DIFFS為3。
ROWS：表中校驗的記錄數。通常情況下為表的總行數。如果指定了--where選項，則為符合條件的記錄數。
CHUNKS：表被分割為多個chunk後，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參數下的輸出結果說明
如果指定了--replicate-check-only參數，則意味著不會校驗任何表，直接獲取上次校驗的結果。
# pt-table-checksum -h 192.168.244.10 -umonitor -pmonitor123 --no-check-binlog-format --replicate-check-only
Differences on hbase TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY mysql.db 1 1 1 mysql.tables_priv 1 -3 1 mysql.user 1 -4 1 test.checksum 1 0 1 Differences on test TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY mysql.db 1 -1 1 mysql.tables_priv 1 -1 1 mysql.user 1 -2 1 test.checksum 1 0 1 test.test 7 1000000 0 PRIMARY 1000000 test2.test 1 -10 1 test2.test2 1 1 1 test3.test3 1 2000 1
可以看出，它分別輸出了不同SLAVE中的差異部分。
TABLE：校驗的表名。
CHUNK：checksum值不相同的chunk的數量。
CNT_DIFF：The number of chunk rows on the replica minus the number of chunk rows on the master.即SLAVE中被校驗的記錄數減去MASTER中的記錄數。
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.如果校驗值相同，則CRC_DIFF為0，否則為1。
上述test.test中CRC_DIFF中crc為0的原因是SLAVE比MASTER多1000000條記錄，且這1000000條記錄正好又是在最後一個chunk中（如下所示）。注：master中id最大值為1000000。
319 Query REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'test', 'test', '7', 'PRIMARY', '1000000', NULL, COUNT(*), '0' FROM `test`.`test` FORCE INDEX(`PRIMARY`) WHERE ((`id` > '1000000')) ORDER BY `id` /*past upper chunk*/
CHUNK_INDEX：用於將table切割成chunk的索引。
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.
上述兩個參數可用來定位具有不同checksum值的chunk對應的索引的上限和下限。
總結
1. pt-table-checksum對表進行校驗時，並不需要表上面有任何索引。這時候，整張表即是一個chunk。
REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'test2', 't1', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `name`, CONCAT(ISNULL(`id`), ISNULL(`name`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `test2`.`t1` /*checksum table*/
如果表的行過多的話，它會報如下錯誤：
# pt-table-checksum -h192.168.244.10 -umonitor -pmonitor123 --chunk-size-limit=100 --no-check-binlog-format --tables=test2.t1
11-17T11:41:53 Cannot checksum table test2.t1: There is no good index and the table is oversized. at /usr/local/bin/pt-table-checksum line 6528.
解決方法：
調整chunk-size-limit的值
# pt-table-checksum -h192.168.244.10 -umonitor -pmonitor123 --chunk-size-limit=1000 --no-check-binlog-format --tables=test2.t1
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 11-17T11:41:59 0 0 100004 1 0 0.600 test2.t1
