mysql pttablechecksum_pt-table-checksum/pt-table-sync原理以及使用

一、pt-table-checksum校验数据一致性

主从数据不一致一直是DBA比较头疼的问题，偶尔被业务投诉主从数据不一致，或者几个从库之间的数据不一致。遇到这种情况，如果采取重建数据库的方法，虽然可以最终达到数据一致，但是代价非常大。因此，我们需要合适的工具，来检测并且修复不一致的数据。

pt-table-checksum简介

pt-table-checksum是著名的percona-toolkit工具集的其中一种工具。它是一个在线验证主从数据一致性的工具，主要用于以下场景：

1、数据迁移前后，进行数据一致性检查

2、当主从复制出现问题，待修复完成后，对主从数据进行一致性检查

3、把从库当成主库，进行数据更新，产生了“脏数据”

pt-table-checksum原理

1)、单行数据checksum值的计算

Pt工具先检查表的结构，并且获取表中每一列的数据类型，把所有数据类型都转化为字符串，然后使用concat_ws()函数进行连接，然后使用crc32计算出该行的checksum值。

2)、数据块checksum值的计算

如果一行一行去计算checksum值，再去和从库比较，效率会很低。pt-table-checksum可以利用表中的索引，将表的数据split成一个个chunk，计算的时候也是以chunk为单位。pt-table-checksum引入了聚合函数BIT_XOR()。它的功能可以理解为将这个chun

k内的所有行数据拼接起来，再计算CRC32的值，就得到了这个chunk的checksum值。

3)、pt-table-checksum通过在主服务器上执行检查语句，在线检查MySQL复制的一致性，生成replace语句，然后传递到从服务器，再通过update更新master_src的值。通过检测从服务器的this_src和master_src的值从而判断复制是否一致。

pt-table-checksum如何保证checksum的数据一致性？

当pt工具在计算主库上某一个chunk的checksum值时，主库可能还在更新，为了保证checksum的是同一份数据，需要对该chunk加for

update锁【for

update锁需要在事务内才有效，对于不支持事务的MyISAM表，pt工具select加的是全表锁】。

校验例子：

1.在主库上创建用于校验数据的用户

GRANT SELECT, INSERT,

UPDATE, DELETE, CREATE, DROP,SUPER ON *.* to

'checkdata'@'A1.A2.A3.A4' identified by

'checkdata8s';

#【A1.A2.A3.A4为主库IP】

2、在从库上授予主库校验数据的权限

GRANT SELECT, INSERT,

UPDATE, DELETE, CREATE, DROP,SUPER ON *.* to

'checkdata'@'A1.A2.A3.A4'

identified by 'checkdata8s';

#【B1.B2.B3.B4为从库】

3.在主库上创建percona库，并且创建一个slave表

create database

percona;

use

percona;

CREATE TABLE `slave`

(

`id` int(11) NOT NULL

AUTO_INCREMENT,

`parent_id` int(11) DEFAULT

NULL,

`dsn` varchar(255) NOT

NULL,

PRIMARY KEY

(`id`)

) ENGINE=InnoDB DEFAULT

CHARSET=utf8;

4.insert记录到slave表，该记录表明要检测那个slave

insert into

slave(`id`,`dsn`) values(1,'h=B1.B2.B3.B4,P=6301');

5.开始pt-table-checksum【在主库上执行】

pt-table-checksum

--nocheck-replication-filter --no-check-binlog-format

--databases=ymore --create-replicate-table

--replicate=percona.checksums --recursion-method=dsn=h=A1.A2.A3.A4,P=6301,D=percona,t=slave

--host=A1.A2.A3.A4 --port=6301

--user=checkdata

--password=checkdata8s

【第一次checksum，需要加上--create-replicate-table】

命令参数解释：

--nocheck-replication-filters

:不检查复制过滤规则(replicate-ignore-db、replicate-wild-do-table)。另外需要特别注意执行checksum所在的数据库必须是同步的数据库；

--no-check-binlog-format

：不检查复制的binlog模式。如果binlog模式为ROW，则需要启用—no-check-binlog-format，否则会报错；

--databases=ymore

：指定要checksum的数据库；

--create-replicate-table

：第一次checksum需要启用该参数。表示创建checksum表，用于存放checksum的结果；

--replicate=percona.checksums

：存放checksum结果的表；

--recursion-method=dsn=h=A1.A2.A3.A4,P=6301,D=percona,t=slave

：使用dsn的方式，指定要checksum的从库。在percona.slave表里面有记录表明要对哪个从库进行checksum；

以下是checksum的结果：

TS ERRORS

DIFFS ROWS CHUNKS

SKIPPED TIME

TABLE

05-14T12:11:04 0 1 272330 10 0 4.938

ymore.User

05-14T12:11:09

0 0 274494 9 0 4.755

ymore.User_xianshang

TS :完成检查的时间

ERRORS ：检查时发生的错误和警告的数量

DIFFS ：0表示一致，1表示不一致

ROWS ：表的行数

CHUNKS ：表中被划分的块的数目

SKIPPED ：由于错误或者警告或块过大，跳过的块的数目

TIME ：执行的时长

TABLE ：被检查的表

DIFFS不等于0则表示数据存在不一致。在从库上查看percona.checksums表，比较this_crc和master_crc的值

select * from

percona.checksums where this_crc <>

master_crc\G;

***************************

1. row ***************************

db: ymore

tbl: User

chunk: 1

chunk_time: 0.013446

chunk_index: PRIMARY

lower_boundary:

1008

upper_boundary:

101095

this_crc:

7fcf3811

this_cnt: 999

master_crc:

12664b2c

master_cnt: 1000

ts: 2015-05-17 23:42:59

1 row in set (0.00

sec)

this_crc和master_crc的值不一致，说明确实存在数据不一致的情况。this_cnt:

999、master_cnt:

1000说明主库比从库多出一条记录

二、pt-table-sync修复不一致数据

pt-table-sync原理：

顾名思义，它用来修复多个实例之间数据的不一致，它可以让主从的数据修复到最终一致。

1、

单行数据checksum值的计算

和pt-table-checksum一样，也是先检查表结构，并获取每一列的数据类型，把所有数据类型都转化为字符串，然后用concat_ws()函数进行连接，由此计算出该行的checksum值。Checksum默认采用crc32计算。

2、

数据块checksum值的计算

和pt-table-checksum工具一样，pt-table-sync会将表的数据split成若干个chunk，计算的时候以chunk为单位。可以理解为将chunk内的所有行的数据拼接起来，再计算crc32的值，既可以得到该chunk的checksum值。

3、

数据修复

前面两步，pt-table-sync与pt-table-checksum的算法和原理是一样的。再往下，就开始有所不同了：

pt-table-checksum只是校验，它把checksum结果存储到统计表，然后把执行过的sql语句记录到binlog，任务就算完成。语句级的复制把计算逻辑传递到从库，并且在从库执行相同的计算。

pt-table-sync则不同，它首先要完成chunk的checksum值计算，一旦发现主从上相同的chunk的checksum值不一样，就会深入到该chunk内部，逐行比较并且修复有问题的行。它的计算逻辑描述如下：

1)、对每一个从库，每一个表，循环进行如下校验和修复过程。

2)、对每一个chunk，校验时加上for update锁。一旦获得锁，就记录下当前主库的show master

status值。

3)、在从库上执行select

master_pos_wait()函数，等待从库的sql线程执行到show master

status得到位置。以此保证，主从上关于这个chunk的内容不再改变。【select

master_pos_wait(‘master_log_file’,’master_log_pos’);该函数会阻塞直到从服务器达到指定的日志文件和偏移量。此时从服务器和主服务器就同步了，语句返回值为0】.

4)、对这个chunk执行checksum计算，然后与主库的checksum进行比较。

5)、如果checksum相同，说明主从数据一致，接着就可以继续下一个chunk。

6)、如果checksum值不同，说明该chunk有不一致。就会深入到chunk内部，逐行计算checksum并比较(单行checksum的比较过程与chunk的比较过程一样，单行实际是chunk的size等于1的特例)。

7)、如果发现某行不一致，则标记下来。继续检测剩余行，直到这个chunk结束。

8)、对找到的主从不一致的行，采用replace

into语句，在主库上执行一遍以生成该行全量的binlog，

并同步到从库，这就会以主库数据为基准来修复从库；对于主库有的，而从库没有的行，采用replace

into在主库上插入(注意，不能是insert。这分为两种情况：一是有唯一性主键，如果有唯一性主键或者索引，则insert相同记录会在主库上插入失败；二是没有唯一性主键或者索引，insert相同记录会造成记录重复。故要求pt-table-sync的表必须要有唯一性主键或者索引)。

9)、直到修复该chunk所有不一致的行。继续检查和修复下一个chunk。

10)、直到这个从库上的所有表修复结束。接着继续修复下一个从库。

pt-table-sync使用(在从库执行)：

1)、主库上增加相应的用户，用于给从库数据修复使用

GRANT SELECT, INSERT,

UPDATE, DELETE, CREATE, DROP,SUPER ON *.* TO

'checkdata'@'B1.B2.B3.B4' IDENTIFIED BY

'checkdata8s'

2)、在从库上添加相应的用户，用于修复数据

grant SELECT, INSERT, UPDATE, DELETE, CREATE,DROP,SUPER,PROCESS

ON *.* TO 'checkdata'@'B1.B2.B3.B4' IDENTIFIED BY

'checkdata8s'

3)、pt-table-sync打印出来

pt-table-sync

--sync-to-master h=B1.B2.B3.B4,u=checkdata,p=checkdata8s,P=6301

--databases=ymore  --charset=utf8mb4

–print

注意：pt-table-sync后面接的h=xxx.xxx.xxx.xxx是从库的IP和端口，并不是主库

4)、pt-table-sync修复数据

pt-table-sync

--sync-to-master h=B1.B2.B3.B4,,u=checkdata,p=checkdata8s,P=6301

--databases=ymore --charset=utf8mb4 --print

--execute