pt-table-checksum对比数据测试（dsns方式）

1.1、工作原理

pt-table-checksum会对校验的表数据进行数据块(数据块大小根据服务负载情况调整)划分，并对数据块数据进行checksun处理；存储在建立的表中。
在主库中执行基于statemnet的sql语句，生成数据块的checksum;在从库执行相同的sql语句，获取到数据块的checksum和主库的checksum进行比较。

1.2、执行过程

1. master设置binlog格式为语句。/ *！50108 SET @@ binlog_format：='STATEMENT'* /
2. master设置RR的隔离级别。RR具有静态数据，因此不需要锁定。SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READREAD<
3. master计算块数据校验和。checksums table：
Use REPLACE INTO select to set this_cnt, this_crc (传递给从属设备，实际上是为每个SLAVE设置cnt，crc).
该算法来自：COALESCE（LOWER（CONV（BIT_XOR（CAST（CRC32（主键）AS UNSIGNED）），10,16）），0）
4.Slave 当同步完成时，开始运行REPLACE INTO select and set cnt,crc for each chunk of the slave 。
5.master checksums table：更新master_cnt，master_crc。这是为master的每个块设置cnt，crc。
6.slave 当同步完成时，开始运行update master_cnt，master_crc并为master的每个块设置cnt，crc。
接下来，我们只需要在校验和表中找到master_cnt <> this_cnt或OR master_crc <> this_crc记录。

  dns是个特殊的方式；服务器连接参数不是自动发现，而是通过一个保存DSNs信息的表中获取。DSN方式指定table方式为dsn=h=host,D=percona,t=dsns

  库中写入以下库和表：

CREATE TABLE percona.`dsns` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`dsn` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
);

insert into percona.dsns select 1,1,'h=127.0.0.1,u=rt,p=cc.123,P=18601';

insert into percona.dsns select 2,2,'h=127.0.0.1,u=rt,p=cc.123,P=18602';

　　

 测试数据：

18601和18602，是一个实例下配置的多端口主从

--mysql8.0测试
pt-table-checksum --nocheck-binlog-format --replicate=percona.checksums --recursion-method=dsn=h=10.0.0.1,D=td1,t=dsns --set-vars innodb_lock_wait_timeout=120 -urt -pcc.123 -h10.0.0.1 -P18601 --databases=test --quiet

--判断到底哪个从库和主库数据不一致
pt-table-checksum --nocheck-binlog-format --replicate=percona.checksums --recursion-method=dsn=h=10.0.0.1,D=td1,t=dsns --set-vars innodb_lock_wait_timeout=120  -urt -pcc.123 -h10.0.0.1 -P18601 --databases=test  --replicate-check-only

--UAT测试
pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --replicate=percona.checksums --recursion-method=dsn=h=10.0.0.1,D=percona,t=dsns --set-vars innodb_lock_wait_timeout=120 -urt -pcc.123 -h10.0.0.1 -P18601 --databases=alid --quiets说明

   ERRORS ：检查时候发生错误和警告的数量。 DIFFS ：0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息。 ROWS ：表的行数。 CHUNKS ：被划分到表中的块的数目。 SKIPPED ：由于错误或警告或过大，则跳过块的数目。 TIME ：执行的时间。 TABLE ：被检查的表名。

  说明：  

    1，加上参数–replicate-check-only，只显示有差异的结果，就可以显示出所有从库的差异,

          结果会显示：Differences on VM_27_211_centos， 不同来自哪个机器，这里如果单实例，多端口，没法显示哪个端口从库。

    2，指定--quiet时只输出errors，warnings和主从数据存在不一致的相关信息(在表非常多时该选项很有用)

    3，--no-check-binlog-format 不对binlog的格式进行检查

    4，--replicate= ：把checksum的信息写入到指定表中，建议直接写到被检查的数据库当中。

    5，--databases=，-d：要检查的数据库，逗号分隔。 --databases-regex 正则匹配要检测的数据库，--ignore-databases[-regex]忽略检查的库。Filter选项。

    6，--tables=，-t：要检查的表，逗号分隔。如果要检查的表分布在不同的db中，可以用--tables=dbname1.table1,dbnamd2.table2的形式。

 修复数据：   

--修复从库数据18602是从库实例
--打印修复数据
 pt-table-sync  --replicate=percona.checksums --sync-to-master h=10.0.0.1,P=18602,u=rt,p=cc.123 --databases=test  --print

--执行
pt-table-sync  --replicate=percona.checksums --sync-to-master h=10.0.0.1,P=18602,u=rt,p=cc.123 --databases=test --execute 

　原理：

     pt-table-checksum用来检测主从数据库上的数据一致性，其原理是通过在主库上运行一系列的MySQL函数计算每个表的散列值，并利用主从关系将相同的操作在从服务器上重放(基于statement格式的binlog),从而获取到主从服务器上的散列值然后比较散列值判定主从数据是否一致。

   对于表中的单行数据，先检查每一列的数据类型，然后将所有的数据类型转换为字符串，再使用concat_ws()函数进行连接，然后根据连接后得到的字符串计算出checksum的值，默认的checksum算法为crc32。

    对于数据量较大的表，如果单次计算整表的checksum值，会导致主库性能压力和主从延迟，因此会根据chunk_size和chunk_time等参数来对表进行拆分为多个chunk，计算chunk时，将该chunk中所有行的数据拼接起来进行checksum计算。

  

对比原理

在开始checksum计算时，会先修改会话级别binlog_format为STATEMENT和开启重复读隔离级别，使得主库上进行的计算SQL操作在从库上重放（不是值）

  1，/*!50108 SET @@binlog_format := 'STATEMENT'*/

  2，SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

    修改隔离级别，通过加锁保证在取数据行数(replaceinto select  from)到计算出检验值这段时间数据不会被修改。他采用块，来减少每次锁住数据的行数，这样提高了并发性

    然后对表数据拆分为chunk进行checksum计算，由于使用STATEMENT的二进制格式，主库和从库都会进行分别计算，如果主库数据不一致，则会导致this_cnt和this_crc两列的数据不同。

说明：

如下面对`testdb1`.`tb1001`表的`id` >= '4621' and `id` <= '158655'作为一个chunk进行checksum计算并插入到目标表的语句

REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'td1', 'dsns', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `parent_id`, convert(`dsn` using utf8mb4), CONCAT(ISNULL(`parent_id`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `td1`.`dsns` /*checksum table*/

　　

然后获取主库的上该chunk的计算值this_cnt和this_crc，将这两值更新到master_crc和master_cnt列中  

SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'td1' AND tbl = 'dsns' AND chunk = '1'   主库查出crc结果和条数

UPDATE `percona`.`checksums` SET chunk_time = '0.007034', master_crc = '8bbc9dc2', master_cnt = '2' WHERE db = 'td1' AND tbl = 'dsns' AND chunk = '1'  主库写入master的crc和条数

　   

   关键执行语句：

2021-04-13T02:42:52.788766Z	  364 Query	DELETE FROM `percona`.`checksums` WHERE db = 'td1' AND tbl = 'dsns'
2021-04-13T02:42:52.793008Z	  364 Query	USE `td1`
2021-04-13T02:42:52.793339Z	  364 Query	EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `parent_id`, convert(`dsn` using utf8mb4), CONCAT(ISNULL(`parent_id`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `td1`.`dsns` /*explain checksum table*/
2021-04-13T02:42:52.793804Z	  364 Query	REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'td1', 'dsns', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `parent_id`, convert(`dsn` using utf8mb4), CONCAT(ISNULL(`parent_id`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `td1`.`dsns` /*checksum table*/
2021-04-13T02:42:52.800891Z	  364 Query	SHOW WARNINGS
2021-04-13T02:42:52.801264Z	  364 Query	SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'td1' AND tbl = 'dsns' AND chunk = '1'
2021-04-13T02:42:52.801653Z	  364 Query	UPDATE `percona`.`checksums` SET chunk_time = '0.007034', master_crc = '8bbc9dc2', master_cnt = '2' WHERE db = 'td1' AND tbl = 'dsns' AND chunk = '1'

　　

   上面更新操作传递到从库上后，从库上的`percona`.`checksums`表便拥有主库的checksum值和从库自己的checksum值，

因此在从库上将master_crc和master_cnt列与this_cnt和this_crc两列进行对比即可以判断该chunk段数据是否有差异。

 查看单表差异：  

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 `percona`.`checksums` WHERE (master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc))  AND (db='td1' AND tbl='dsns')

　执行的全部log：xml改成log格式

    https://files.cnblogs.com/files/zping/master.xml

    https://files.cnblogs.com/files/zping/slave.xml