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
pt-table-checksum 校验mysql主从数据一致性——筑梦之路
最新推荐文章于 2024-07-30 04:00:41 发布