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 ![資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)