pt-table-checksum 校验mysql主从数据一致性——筑梦之路

                    pt-table-checksum 校验mysql主从数据一致性

实现原理：
将一张大表分成多个chunk，每次针对一个chunk进行校验，同时将校验的结果通过REPLACE INTO语句写入到percona.checksum表中，
然后该语句通过主从复制，在SLAVE中同样执行一次，校验的结果同样是写入到percona.checksum表中，
最后，通过查询percona.checksums来获取主从不一致的信息

数据库主从不一致的情形：
    Master端使用了不确定的语句(如:CURRENT_USER(), UUID())
    不正确的故障转移(failover)流程
    误操作或直接在Slave进行DML操作
    持续的升级更新(Rolling upgrades)
    混合使用事务引擎和非事务引擎的表
    跳过了复制事件 (SET GLOBAL SQL_SLAVE_SKIP_COUNTER = N)
    使用临时表(Temporary Tables)
    复制过滤(Replication Filters)
    使用含LIMIT且没有order by的更新语句(update/delete with LIMIT clause without order by)


环境和注意事项说明
1. 操作系统：centos 7
2.数据库版本：mariadb  mysql均可
3.数据库主从复制集群已经搭建，并均正常运行
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
4.端口默认都是3306
5.创建账户既能登录主库，也能登录从库，授予权限SELECT, PROCESS, SUPER, REPLICATION SLAVE即可
6.只能指定一个host，必须为主库的IP
7.在检查时会向表加S锁
8.如果master和slave的binlog日志不是STATEMENT格式，要用--no-check-binlog-format选项
9.表要有主键索引或唯一键索引

工具安装使用
1.添加yum源
yum install https://mirrors.tuna.tsinghua.edu.cn/percona/yum/percona-release-1.0-27.noarch.rpm

2.安装toolkit
yum install percona-toolkit 

3.验证安装
rpm -qa | grep percona

4.参数说明
#pt-table-checksum参数
--nocheck-replication-filters ：不检查复制过滤器，建议启用。后面可以用--databases来指定需要检查的数据库。
--no-check-binlog-format      : 不检查复制的binlog模式，要是binlog模式是ROW，则会报错。
--replicate-check-only        :只显示不同步的信息。
--replicate=   ：把checksum的信息写入到指定表中，建议直接写到被检查的数据库当中。 
--databases=   ：指定需要被检查的数据库，多个则用逗号隔开。
--tables=      ：指定需要被检查的表，多个用逗号隔开
h=127.0.0.1    ：Master的地址
u=root         ：用户名
p=123456       ：密码
P=3306         ：端口

#pt-table-sync参数
--replicate=  ：指定通过pt-table-checksum得到的表，这2个工具差不多都会一直用。
--databases=  : 指定执行同步的数据库，多个用逗号隔开。
--tables=     ：指定执行同步的表，多个用逗号隔开。
--sync-to-master ：指定一个DSN，即从的IP，他会通过show processlist或show slave status 去自动的找主。
h=127.0.0.1   ：服务器地址，命令里有2个ip，第一次出现的是Master的地址，第2次是Slave的地址。
u=root        ：帐号。
p=123456      ：密码。
--print       ：打印，但不执行命令。
--execute     ：执行命令。

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

校验步骤说明和实际案例

mysql-master: 192.168.47.10
mysql-slave:  192.168.47.11

1.在主从库上都创建用户
#这里主从做了所有库的同步，因此这里只需要在主库上执行sql语句，若排除了mysql用户表的同步则需要在从库上也执行
GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE , REPLICATION CLIENT  ON *.* TO 'checksum'@'192.168.47.%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON `percona`.* TO 'checksum'@'192.168.47.%';
flush privileges;

2.创建数据库和表 存储不一致的信息（主库上执行）
create database percona;
use percona;
CREATE TABLE `checksum` (
  `db` char(64) NOT NULL,
  `tbl` char(64) NOT NULL,
  `chunk` int(11) NOT NULL,
  `chunk_time` float DEFAULT NULL,
  `chunk_index` varchar(200) DEFAULT NULL,
  `lower_boundary` text DEFAULT NULL,
  `upper_boundary` text DEFAULT NULL,
  `this_crc` char(40) NOT NULL,
  `this_cnt` int(11) NOT NULL,
  `master_crc` char(40) DEFAULT NULL,
  `master_cnt` int(11) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`db`,`tbl`,`chunk`),
  KEY `ts_db_tbl` (`ts`,`db`,`tbl`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#创建测试的表
CREATE TABLE `t` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `ename` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

#插入数据
insert into t(ename) values('Leshami'),('Henry'),('Jack'),('Mary'),('Lisa');

#查询一下
select * from t;

#模拟数据不一致的情况，从库上删除数据
delete from t where id=3 or id=5;

3.pt-table-checksum检查数据一致性
pt-table-checksum --nocheck-replication-filters  --no-check-binlog-format --replicate=percona.checksum --databases=percona --tables=t h=192.168.47.10,u=checksum,p=123456,P=3306
#从结果中看到diff_rows值为2
#从库checksum表查看信息
use percona;
select * from checksum;
#this_cnt  master_cnt两个字段的值不同，差2

4.pt-table-sync修复从库不一致的数据
#打印输出不一致的数据信息
pt-table-sync --replicate=percona.checksum h=192.168.47.10,u=root,p=123456 h=192.168.47.11,u=root,p=123456 --print

#同步不一致的数据
pt-table-sync --replicate=percona.checksum h=192.168.47.10,u=root,p=123456 h=192.168.47.11,u=root,p=123456 --execute

#从库检查
select * from t;
#可以看到被删除的id 为3 5的两条数据已经同步

#再次校验数据
执行上面的第3步即可

5.其他用法参考
#基本用法
pt-table-checksum -h192.168.47.10 -uchecksum -p123456

#基于指定库的校验
pt-table-checksum -h192.168.47.10 -uchecksum -p123456 --no-check-binlog-format --databases=test,test1

#基于指定表的校验
pt-table-checksum -h192.168.47.10 -ucheksum -p123456 --no-check-binlog-format --tables=test2.test

#表过多时的校验,调整参数chunk-size-limit
pt-table-checksum -h192.168.47.10 -uchecksum -p123456 --chunk-size-limit=1000 --no-check-binlog-format --tables=test2.t1