1、基本环境:
Mysql版本:5.6.12-log
Percona-toolkit:2.2.18
Linux:centos6.5
2、安装
源码安装:
# 一些依赖包
yum install perl perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL perl-Time-HiRes -y
# 下载安装percona-toolkit
wget https://www.percona.com/downloads/percona-toolkit/2.2.18/tarball/percona-toolkit-2.2.18.tar.gz
tar -xvf percona-toolkit_2.2.18.tar.gz
cd percona-toolkit-2.2.18/
yum install perl-DBD-MySQL -y
perl Makefile.PL
make
make test
make install
cp /usr/local/bin/pt* /bin/
Rpm包安装:
wget https://www.percona.com/downloads/percona-toolkit/2.2.18/RPM/percona-toolkit-2.2.18-1.noarch.rpm
报错记录:
07-21T11:36:38 Error checksumming table business_db.SHOP_BARGAIN_RANGE: DBD::mysql::st execute failed: Unknown column 'db' in 'where clause' [for Statement "DELETE FROM `test`.`dsns` WHERE db = ? AND tbl = ?" with ParamValues: 0='business_db', 1='SHOP_BARGAIN_RANGE'] at /usr/local/bin/pt-table-checksum line 10085.
需要安装perl源组件:
yum -y install perl-devel
yum -y install perl-CPAN
3、建立check账号
赋予权限:
mysql> grant SUPER on *.* to repl@'192.168.%';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> grant PROCESS on *.* to repl@'192.168.%';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> grant select,create,drop,insert,delete,update,alter on percona.* to repl@'192.168.%';
Query OK, 0 rows affected (0.00 sec)
mysql>
在主库建库建表:
# 默认的check库
Create database percona;
# 准备测试库数据
Create database bg;
Use bg;
CREATE TABLE `t1` (
`id` int(1) NOT NULL AUTO_INCREMENT,
`cname` varchar(3) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
insert into t1 select 1,’abc’;
# 然后单独在从库录入数据,那么主从数据不一致了,从库多了一条数据
Insert into t1 select 2,’ab2’;
开始使用pt-table-checksum检查:
# 执行checksum操作
[root@hch_test_dbm1_121_62 mysql]# pt-table-checksum --host=192.168.121.62 --port 3306 --databases=bg -urepl -pPlcc0805@replication --no-check-binlog-format
[root@hch_test_dbm1_121_62 mysql]#
# 没有check出来数据不一致的,why?
去执行select * from checksums;也没有记录。
mysql> select * from percona.checksums;
Empty set (0.00 sec)
mysql>
换个mysql主从环境进行测试:
pt-table-checksum --host=110.251.13.11 --port 3307 --databases=test-u check_user -pPlcc0805@replication --no-check-binlog-format
执行报错:
Diffs cannot be detected because no slaveswere found. Please read the--recursion-method documentation for information.
从字面意思上看是,主库找不到从数据库。只需要在从库配置文件 my.cnf中添加
report_host=slave_ip
report_port=slave_port
即可。
如果slave从库有表t3,master主库没有t3表,那么结果集上不会显示出来说从库多了个表t3,它以主master库为基准来判断验证的。
4、Test库能check,enrolment_db不能check
# 开始检查test库的所有表
[root@azure_zhitian_dbm1_13_11 ~]# pt-table-checksum --host=110.251.13.11 --port 3307 --databases=test --user=check_user --password="Plcc0805@replication" --no-check-binlog-format
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
07-28T16:34:23 0 0 1 1 0 0.358 test.JBPM4_EXECUTION
07-28T16:34:23 0 1 2 1 0 0.038 test.t1
[root@azure_zhitian_dbm1_13_11 ~]#
n 看到test.t1表的DIFFS为1,表示关于test.t1表的主从数据不一致,需要修复
# 再检查enrolment_db库,没有记录,这是为啥?
[root@azure_zhitian_dbm1_13_11 ~]# pt-table-checksum --host=110.251.13.11 --port 3307 --databases=enrolment_db --user=check_user --password="Plcc0805@replication" --chunk-size=100 --no-check-binlog-format
[root@azure_zhitian_dbm1_13_11 ~]#