使用pt-table-checksum进行主从数据一致性比对
主从数据库环境:
master db 192.168.17.134 host134 replicate db:bhtest test
slave db 192.168.17.189 host189 replicate db:bhtest test
操作系统:
# cat /etc/issue
CentOS release 5.6 (Final)
x86_64
安装部署
134主库执行:
134、189主库,从库均执行
134主库 //在Master机的test库加入
use test;
CREATE TABLE `dsns` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id`
int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) );
//写入从库信息
INSERT INTO dsns (parent_id,dsn)
values(1,'h=192.168.17.189,u=checksumer,p=123,P=3307');
现在我们构建不一致的表来进行测试
134主库
现在我们针对表来进行比对
134主库
pt-table-checksum --nocheck-replication-filters --replicate=test.checksums --no-check-binlog-format --databases=bhtest --tables=test90 h=192.168.17.134,u=checksumer,p=123,P=3307 --recursion-method=dsn=h=192.168.17.134,D=test,t=dsns
# 2 software updates are available:
# * The current version for MySQL Community Server (GPL) is 5.6.21.
# * The current version for Percona::Toolkit is 2.2.11.
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
11-05T20:45:24 0 1 9 1 0 0.047 bhtest.test90
//DIFFS值非0,表示主从不一致
现在我们比对bhtest库中所有的表
参数的意义:
--replicate= :指定通过pt-table-checksum得到的表,这2个工具差不多都会一直用。
--databases= : 指定执行同步的数据库,多个用逗号隔开。
--tables= :指定执行同步的表,多个用逗号隔开。
--sync-to-master :指定一个DSN,即从的IP,他会通过show processlist或show slave status 去自动的找主。
h=127.0.0.1 :服务器地址,命令里有2个ip,第一次出现的是M的地址,第2次是Slave的地址。
u=root :帐号。
p=123456 :密码。
--print :打印,但不执行命令。
--execute :执行命令。
更多的参数请见官网
# pt-table-sync --sync-to-master h=192.168.17.189,u=checksumer,p=123,P=3307 --databases=bhtest --tables=test90 --print
Can't make changes on the master because no unique index exists at /usr/bin/pt-table-sync line 10666. while doing bhtest.test90 on 192.168.17.189
134主库
alter table bhtest.test90 add primary key(id);
打印差异数据
执行差异数据
# pt-table-sync --sync-to-master h=192.168.17.189,u=checksumer,p=123,P=3307 --databases=bhtest --tables=test90 --execute
[root@host4 percona-toolkit-2.2.5]#
下面,我们再来查看主库和从库中的数据,发现已经一致了。
134主库
主从数据库环境:
master db 192.168.17.134 host134 replicate db:bhtest test
slave db 192.168.17.189 host189 replicate db:bhtest test
操作系统:
# cat /etc/issue
CentOS release 5.6 (Final)
Kernel \r on an \m
CentOS release 5.9 (Final)
# archx86_64
安装部署
134主库执行:
# mv percona-toolkit-2.2.5.tar.gz /usr/local/
# tar xzvf percona-toolkit-2.2.5.tar.gz
# cd percona-toolkit-2.2.5
# perl Makefile.PL
# make;make install
# pt-table- //按Tab补齐键,能够看到我们即将使用的工具pt-table-checksum pt-table-sync
pt-table-checksum pt-table-sync pt-table-usage
赋权
134、189主库,从库均执行
GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'checksumer'@'192.168.17.134' IDENTIFIED BY '123';
GRANT ALL PRIVILEGES ON `bhtest`.* TO 'checksumer'@'192.168.17.134';
GRANT ALL PRIVILEGES ON `test`.* TO 'checksumer'@'192.168.17.134';
创建从库信息
134主库 //在Master机的test库加入
use test;
CREATE TABLE `dsns` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id`
int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) );
//写入从库信息
INSERT INTO dsns (parent_id,dsn)
values(1,'h=192.168.17.189,u=checksumer,p=123,P=3307');
(在134主库测试是否能使用checksumer账号登陆17.189从库:mysql -uchecksumer -p123 -P3307 -h192.168.17.189,要求能正确登陆。否则请检查权限和网络)
//如果有多个从库,就插入多条记录.现在我们构建不一致的表来进行测试
134主库
@test 08:46:15>select * from bhtest.test90;
+-------------+
| id |
+-------------+
| 2147483647 |
| 2147483647 |
| 2147483647 |
| -2147483648 |
| -2147483647 |
| -2147483648 |
| -2147483648 |
| -2147483647 |
| 2147483647 |
+-------------+
9 rows in set (0.00 sec)
189从库
mysql>insert into bhtest.test90 values(20141105);
mysql> select * from bhtest.test90;
+-------------+
| id |
+-------------+
| 2147483647 |
| 2147483647 |
| 2147483647 |
| -2147483648 |
| -2147483647 |
| -2147483648 |
| -2147483648 |
| -2147483647 |
| 2147483647 |
| 20141105 |
+-------------+
10 rows in set (0.00 sec)
现在,bhtest库中的test90表在主从是不一致的,从库比主库中多一条记录
现在我们针对表来进行比对
134主库
pt-table-checksum --nocheck-replication-filters --replicate=test.checksums --no-check-binlog-format --databases=bhtest --tables=test90 h=192.168.17.134,u=checksumer,p=123,P=3307 --recursion-method=dsn=h=192.168.17.134,D=test,t=dsns
# 2 software updates are available:
# * The current version for MySQL Community Server (GPL) is 5.6.21.
# * The current version for Percona::Toolkit is 2.2.11.
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
11-05T20:45:24 0 1 9 1 0 0.047 bhtest.test90
//DIFFS值非0,表示主从不一致
现在我们比对bhtest库中所有的表
pt-table-checksum --nocheck-replication-filters --replicate=bhtest.checksums --no-check-binlog-format --databases=bhtest h=192.168.17.134,u=checksumer,p=123,P=3307 --recursion-method=dsn=h=192.168.17.134,D=test,t=dsns
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
11-05T20:51:05 0 0 1 1 0 0.279 bhtest.accounts
11-05T20:51:05 0 0 92 1 0 0.012 bhtest.audit
11-05T20:51:05 0 0 1 1 0 0.010 bhtest.bh_test2
11-05T20:51:05 0 0 8 1 0 0.013 bhtest.bhtest
11-05T20:51:05 0 0 3 1 0 0.011 bhtest.bhtest1
11-05T20:51:05 0 0 0 1 0 0.009 bhtest.bhtest10
11-05T20:51:05 0 0 0 1 0 0.011 bhtest.com_base_info
11-05T20:51:05 0 0 1 1 0 0.009 bhtest.ms_test
11-05T20:51:05 0 0 0 1 0 0.009 bhtest.prod_product_cat_search
11-05T20:51:05 0 0 3 1 0 0.010 bhtest.test
11-05T20:51:05 0 1 9 1 0 0.009 bhtest.test90
11-05T20:51:05 0 0 7 1 0 0.009 bhtest.test900
11-05T20:51:05 0 0 4 1 0 0.008 bhtest.test9000
检查出表test90存在数据不一致的现象,那么我们通过工具pt-table-sync来进行修复
重新构建环境
134主库
bohai@bhtest 11:29:14>select * from test90;
+----+
| id |
+----+
| 30 |
| 31 |
| 32 |
| 33 |
+----+
4 rows in set (0.00 sec)
189从库
mysql> select * from bhtest.test90;
+------+
| id |
+------+
| 30 |
| 31 |
| 32 |
| 33 |
| 1111 |
+------+
5 rows in set (0.00 sec)
# 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,第一次出现的是M的地址,第2次是Slave的地址。
u=root :帐号。
p=123456 :密码。
--print :打印,但不执行命令。
--execute :执行命令。
更多的参数请见官网
# pt-table-sync --sync-to-master h=192.168.17.189,u=checksumer,p=123,P=3307 --databases=bhtest --tables=test90 --print
Can't make changes on the master because no unique index exists at /usr/bin/pt-table-sync line 10666. while doing bhtest.test90 on 192.168.17.189
134主库
alter table bhtest.test90 add primary key(id);
打印差异数据
# pt-table-sync --sync-to-master h=192.168.17.189,u=checksumer,p=123,P=3307 --databases=bhtest --tables=test90 --print
DELETE FROM `bhtest`.`test90` WHERE `id`='1111' LIMIT 1 /*percona-toolkit src_db:bhtest src_tbl:test90 src_dsn:P=3307,h=192.168.17.134,p=...,u=checksumer dst_db:bhtest dst_tbl:test90 dst_dsn:P=3307,h=192.168.17.189,p=...,u=checksumer lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:18970 user:root host:host4*/;
打印出来了修复数据的sql语句,可以手动的去从行执行,让他们数据保持一致性。那能否直接执行?当然可以,通过(--execute):
执行差异数据
# pt-table-sync --sync-to-master h=192.168.17.189,u=checksumer,p=123,P=3307 --databases=bhtest --tables=test90 --execute
[root@host4 percona-toolkit-2.2.5]#
下面,我们再来查看主库和从库中的数据,发现已经一致了。
134主库
bohai@bhtest 11:29:14>select * from test90;
+----+
| id |
+----+
| 30 |
| 31 |
| 32 |
| 33 |
+----+
4 rows in set (0.00 sec)
189从库
bohai@bhtest 11:29:14>select * from test90;
+----+
| id |
+----+
| 30 |
| 31 |
| 32 |
| 33 |
+----+
4 rows in set (0.00 sec)
遇到的问题汇总
1,mysql module is not installed or not found
# pt-table-checksum --nocheck-replication-filters --replicate=test.checksums --no-check-binlog-format --databases=umc --tables=test90 h=192.168.65.211,u=checksum,p=check123,P=3306 --recursion-method=dsn=h=192.168.65.211,D=test,t=dsns
11-07T11:39:44 Cannot connect to MySQL because the Perl DBD::mysql module is not installed or not found. Run 'perl -MDBD::mysql' to see the directories that Perl searches for DBD::mysql. If DBD::mysql is not installed, try:
Debian/Ubuntu apt-get install libdbd-mysql-perl
RHEL/CentOS yum install perl-DBD-MySQL
OpenSolaris pgk install pkg:/SUNWapu13dbd-mysql
解决
# yum install perl-DBD-MySQL