pt-table-checksum
原理分析
命令
pt-table-checksum h=xxx,u=xxx,p=xxx,P=6011 -d sakila -t actor --nocheck-replication-filters --replicate=test.checksums --recursion-method=processlist
主库上的全日志
140224 14:02:40 39 Connect superdba@10.55xxx on 39 Query set autocommit=1 39 Query SELECT @@SQL_MODE 39 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO'*/ 39 Query SET wait_timeout=10000 39 Query SELECT @@hostname, @@server_id 39 Query SELECT VERSION() 39 Query SELECT @@binlog_format 39 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 39 Query SET SESSION innodb_lock_wait_timeout=1 39 Query SELECT @@SERVER_ID 39 Query SHOW GRANTS FOR CURRENT_USER() 39 Query SHOW PROCESSLIST 39 Query SHOW DATABASES LIKE 'test' 39 Query USE `test` 39 Query SHOW TABLES FROM `test` LIKE 'checksums' 39 Query SHOW TABLES FROM `test` LIKE 'checksums' 39 Query SHOW FULL COLUMNS FROM `test`.`checksums` 39 Query DELETE FROM `test`.`checksums` LIMIT 0 39 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 39 Query SELECT CRC32('test-string') 39 Query SELECT CRC32('a') 39 Query SELECT CRC32('a') 39 Query SHOW DATABASES 39 Query SHOW /*!50002 FULL*/ TABLES FROM `sakila` 39 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */ 39 Query USE `sakila` 39 Query SHOW CREATE TABLE `sakila`.`actor` 39 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */ 39 Query EXPLAIN SELECT * FROM `sakila`.`actor` WHERE 1=1 39 Query USE `test` 39 Query DELETE FROM `test`.`checksums` WHERE db = 'sakila' AND tbl = 'actor' 39 Query USE `sakila` 39 Query EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `actor_id`, `first_name`, `last_name`, `last_update` + 0)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `sakila`.`actor` /*explain checksum table*/ 39 Query REPLACE INTO `test`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'sakila', 'actor', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `actor_id`, `first_name`, `last_name`, `last_update` + 0)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `sakila`.`actor` /*checksum table*/ 39 Query SHOW WARNINGS 39 Query SELECT this_crc, this_cnt FROM `test`.`checksums` WHERE db = 'sakila' AND tbl = 'actor' AND chunk = '1' 39 Query UPDATE `test`.`checksums` SET chunk_time = '0.002081', master_crc = '160cf2ec', master_cnt = '200' WHERE db = 'sakila' AND tbl = 'actor' AND chunk = '1' 39 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 39 Quit
1、如果表checksums已经存在的话，不会创建，将对应的数据删除即可
2、通过explain获取总的行数，然后划分为多个chunk
3、通过replace...select加上读锁，保证chunk内的数据不会被更新，生成chunk对应的校验值，然后这样的SQL会同步到从库上
4、this_crc,this_cnt代表各个库上产生的校验值
从库上的日志
140224 14:02:40 27 Connect superdba@xxxxon 27 Query set autocommit=1 27 Query SELECT @@SQL_MODE 27 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO'*/ 27 Query SET wait_timeout=10000 27 Query SELECT @@SERVER_ID 27 Query SELECT @@hostname, @@server_id 27 Query SELECT VERSION() 27 Query SHOW GRANTS FOR CURRENT_USER() 27 Query SHOW PROCESSLIST 27 Query SHOW SLAVE HOSTS 27 Query SHOW TABLES FROM `test` LIKE 'checksums' 27 Query EXPLAIN SELECT * FROM `sakila`.`actor` WHERE 1=1 4 Query BEGIN 4 Query DELETE FROM `test`.`checksums` WHERE db = 'sakila' AND tbl = 'actor' 4 Query COMMIT /* implicit, from Xid_log_event */ 4 Query BEGIN 4 Query REPLACE INTO `test`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'sakila', 'actor', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `actor_id`, `first_name`, `last_name`, `last_update` + 0)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `sakila`.`actor` /*checksum table*/ 4 Query COMMIT /* implicit, from Xid_log_event */ 4 Query BEGIN 4 Query UPDATE `test`.`checksums` SET chunk_time = '0.002081', master_crc = '160cf2ec', master_cnt = '200' WHERE db = 'sakila' AND tbl = 'actor' AND chunk = '1' 4 Query COMMIT /* implicit, from Xid_log_event */ 27 Query SHOW SLAVE STATUS 27 Query SELECT MAX(chunk) FROM `test`.`checksums` WHERE db='sakila' AND tbl='actor' AND master_crc IS NOT NULL 27 Query SELECT CONCAT(db, '.', tbl) AS `table`, chunk, chunk_index, lower_boundary, upper_boundary, COALESCE(this_cnt-master_cnt, 0) AS cnt_diff, COALESCE(this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc), 0) AS crc_diff, this_cnt, master_cnt, this_crc, master_crc FROM `test`.`checksums` WHERE (master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)) AND (db='sakila' AND tbl='actor') 27 Quit
(thread_id为4表示SQL线程对应的id，属于同步过来的SQL)
5、从库同步replace...select也产生自己chunk的校验值，插入checksums表中
6、将主库的校验值和从库的校验值进行比较得到是否一致的判断。
http://nettedfish.sinaapp.com/blog/2013/06/04/check-replication-consistency-by-pt-table-checksum/
