使用pt-table-checksum和pt-table-sync工具验证和修复主从数据不一致问题

一、环境准备
DBD源码下载：http://www.cpan.org/modules/by-module/DBD/DBI-1.636.tar.gz.
cd /data/tool/
tar -zxvf DBI-1.636.tar.gz
cd DBI-1.636
perl Makefile.PL
make
make test
make install

 

DBD源码下载：http://www.cpan.org/modules/by-module/DBD/DBD-mysql-4.033.tar.gz
cd /data/tool/
tar -zxvf DBD-mysql-4.033.tar.gz
cd DBD-mysql-4.033
perl Makefile.PL --mysql_config=/usr/bin/mysql_config
make
make test
make install

查看主库机器是否有安装DBI和DBD模块：
[mysql@mvxl0783 ~]$ perl -MDBD::mysql -e "print\"module installed\n\""
module installed

pt-table工具授权：
[root@mvxl0783 bin]# chown -R mysql:mysql /data/tool/percona-toolkit-2.2.10

创建mysql 检查账号：

在主库建立checksum的账号，checksums,需要的权限有SELECT, PROCESS, SUPER, REPLICATION SLAVE，

grant SELECT, PROCESS, SUPER, REPLICATION SLAVE on *.* to pc_checksum@'%' identified by "percona";

grant all on test.* to pc_checksum@'%'; -- 以便能创建test.checksums表

flush privileges;

二、pt-table-checksum检查主从是否一致

在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=10.16.24.109,u=pc_checksum,p=percona,P=3376");
检查命令：
cd /data/tool/percona-toolkit-2.2.10/bin
perl pt-table-checksum --no-check-binlog-format --nocheck-replication-filters --ignore-databases=mysql,\
performance_schema,information_schema,log,common_schema --replicate=test.checksums --host=127.0.0.1 \
--port 3376 -upc_checksum -ppercona --engines=InnoDB,MyISAM --recurse=1 --chunk-size-limit=0 --recursion-method=dsn=D=host,D=test,t=dsns
检查操作已经完成，查看输出结果，是否有不一致的记录，
[mysql@mvxl0783 bin]$ perl pt-table-checksum --no-check-binlog-format --nocheck-replication-filters --ignore-databases=mysql,\
> performance_schema,information_schema,log,common_schema --replicate=test.checksums --host=127.0.0.1 \
> --port 3376 -upc_checksum -ppercona --engines=InnoDB,MyISAM --recurse=1 --chunk-size-limit=0 --recursion-method=dsn=D=host,D=test,t=dsns
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
05-08T23:23:18      0      0        9       1       0   0.320 lots.t1
05-08T23:23:35      0      0  1872178      25       0  17.221 lots.t_order
05-08T23:23:35      0      1        4       1       0   0.280 lots.test
05-08T23:23:36      0      1      999       1       0   0.285 lots.z
05-08T23:23:36      0      0      200       1       0   0.287 sakila.actor
05-08T23:23:36      0      0      603       1       0   0.283 sakila.address
05-08T23:23:36      0      0       16       1       0   0.023 sakila.category
05-08T23:23:36      0      0      600       1       0   0.278 sakila.city
05-08T23:23:37      0      0      109       1       0   0.272 sakila.country
05-08T23:23:37      0      0      599       1       0   0.271 sakila.customer
05-08T23:23:37      0      0     1000       1       0   0.280 sakila.film
05-08T23:23:38      0      0     5462       1       0   0.293 sakila.film_actor
05-08T23:23:38      0      0     1000       1       0   0.273 sakila.film_category
05-08T23:23:38      0      0     1000       1       0   0.013 sakila.film_text
05-08T23:23:38      0      0     4581       1       0   0.278 sakila.inventory
05-08T23:23:38      0      0        6       1       0   0.262 sakila.language
05-08T23:23:39      0      0    16049       1       0   0.334 sakila.payment
05-08T23:23:39 Cannot checksum table sakila.payment2: There is no good index and the table is oversized. at pt-table-checksum line 6417.

05-08T23:23:53      0      0  3466584      25       0  14.655 sakila.payment3
05-08T23:23:54      0      0    16044       1       0   0.373 sakila.rental
05-08T23:23:54      0      0        2       1       0   0.273 sakila.staff
05-08T23:23:54      0      0        2       1       0   0.265 sakila.store
05-08T23:23:55      0      0        1       1       0   0.275 test.dsns
05-08T23:23:55      0      0    83659       1       0   0.419 zengxuewen.z

检查到lots.test和 lots.z表各有一行记录不一致，具体查看主从库不一致信息：
主库：
(product)root@localhost [test]> select * from lots.test;

+------+-------+
| id   | name  |
+------+-------+
|    1 | zeng1 |
|    2 | zeng2 |
|    3 | zeng3 |
|    5 | zeng4 |
+------+-------+
4 rows in set (0.02 sec)

(product)root@localhost [test]> select count(*) from lots.z;
+----------+
| count(*) |
+----------+
|      999 |
+----------+
1 row in set (0.02 sec)

从库：
mysql> select * from lots.test;
+------+-------+
| id   | name  |
+------+-------+
|    1 | zeng1 |
|    2 | zeng2 |
|    3 | zeng3 |
|    4 | zeng4 |
+------+-------+
4 rows in set (0.01 sec)
mysql> select count(*) from lots.z;
+----------+
| count(*) |
+----------+
|      998 |
+----------+
1 row in set (0.01 sec

操作完成，等同步修改也做完后，需要把pc_checksum@'%' 给删除了。

下面是一些选项的解释：

a.--nocheck-replication-filters：忽略从库replication-do-db规则
b.如果binlog_format =mixed 需要加上该格式--no-check-binlog-format

c. --ignore-databases 忽略要检查的库

d. --replicate=test.checksums   默认值，默认会创建此表，需要 pc_checksum 账号有test库创建表的权限
在test库下创建checksums表,并将数据写入，这个要注意，会复制到从库，若从库设置了Replicate_Ignore_DB:test 之类的，先手工脚本创建checksums表，否则主库跨库操作checksum表，

会复制报错。

e --replicate-check-only 只输出数据不一致的信息。
f -help

--engines=InnoDB,MyISAM 指定要检查的引擎，避免archive，memory引擎的表检查报错及延迟

--recurse=1 processlist模式下，不需要递归检查，也就是100上，不检查117,118的库

几个比较重要的参数：
-h -u -p -P -S -d 连接信息

--chunk-size，--chunk-size-limit 用于指定检测块的大小。可控性更强。--chunk-size-limit=0 这个对大库是必须的，因为有些表无索引，不设置，会调过trunk，不检查。
--explain 打印checksum实际运行的sql语句
--ignore-databases/tables/column 跳出指定元素的过滤
--lock-wait-timeout innodb 锁的超时设定，默认为1
--max-load 设置最大并发连接数
--recursion-method 指定获取slave hosts的方式：
1.show processlist
2.show slave host
3.从配置表dsns表中读取，此表需单独创建

默认是processlist的所有slave都检查，要想跳过某些库，不检查，只能使用 --recursion-method=dsn=D=test,t=dsns 方法

方法为：

在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=10.20.71.113,u=pc_checksum,p=percona,P=6612"); 有几个想检查的slave，就写几条。

三、pt-table-sync修复主从一致
在从库上建立帐号：
grant all on *.* to pc_checksum@'10.16.24.108' identified by "percona";
flush privileges;

在主库机器上操作：
先打印出在从库上执行的SQL：

[mysql@mvxl0783 bin]$ perl pt-table-sync --charset=utf8 --print --no-check-slave h=localhost,u=pc_checksum,p=percona,S=/tmp/mysql3376.sock

h=10.16.24.109,u=pc_checksum,p=percona,P=3376
DELETE FROM `lots`.`test` WHERE `id`='4' AND `name`='zeng4' LIMIT 1 /*percona-toolkit src_db:lots src_tbl:test

src_dsn:A=utf8,S=/tmp/mysql3376.sock,h=localhost,p=...,u=pc_checksum dst_db:lots dst_tbl:test

dst_dsn:A=utf8,P=3376,S=/tmp/mysql3376.sock,h=10.16.24.109,p=...,u=pc_checksum lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:23650 user:mysql

host:mvxl0783*/;
INSERT INTO `lots`.`test`(`id`, `name`) VALUES ('5', 'zeng4') /*percona-toolkit src_db:lots src_tbl:test

src_dsn:A=utf8,S=/tmp/mysql3376.sock,h=localhost,p=...,u=pc_checksum dst_db:lots dst_tbl:test

dst_dsn:A=utf8,P=3376,S=/tmp/mysql3376.sock,h=10.16.24.109,p=...,u=pc_checksum lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:23650 user:mysql

host:mvxl0783*/;
INSERT INTO `lots`.`z`(`a`, `b`) VALUES ('759', '1000') /*percona-toolkit src_db:lots src_tbl:z src_dsn:A=utf8,S=/tmp/mysql3376.sock,h=localhost,p=...,u=pc_checksum

dst_db:lots dst_tbl:z dst_dsn:A=utf8,P=3376,S=/tmp/mysql3376.sock,h=10.16.24.109,p=...,u=pc_checksum lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0

pid:23650 user:mysql host:mvxl0783*/;
UPDATE `test`.`checksums` SET `chunk_time`=0.024243, `chunk_index`=NULL, `lower_boundary`=NULL, `upper_boundary`=NULL, `this_crc`='694b5c13', `this_cnt`='4',

`master_crc`='694b5c13', `master_cnt`='4', `ts`='2016-05-08 22:15:42' WHERE `db`='lots' AND `tbl`='test' AND `chunk`='1' LIMIT 1 /*percona-toolkit src_db:test

src_tbl:checksums src_dsn:A=utf8,S=/tmp/mysql3376.sock,h=localhost,p=...,u=pc_checksum dst_db:test dst_tbl:checksums

dst_dsn:A=utf8,P=3376,S=/tmp/mysql3376.sock,h=10.16.24.109,p=...,u=pc_checksum lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:23650 user:mysql

host:mvxl0783*/;

--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 ：执行命令。更多的参数请见官网，上面指出来的是常用的，对该场

景够用的参数。
 我的建议是使用--replicate参数和--sync-to-master
 好处是：
 1：使用replicate可以不用自己指定需要同步的表
 2：使用sync-to-master可以不用指定master信息，
 例子如下：
 pt-table-sync --sync-to-master --replicate=test.checksums --charset=utf8 h=192.168.11.64,u=checksums,p=123456,P=3307 --print
 再写一个不用sync-to-master和replicate的例子例子1：pt-table-sync --replicate=test.checksums h=192.168.11.103,u=checksums,p=123456,P=3306

h=192.168.11.64,u=checksums,p=123456,P=3307 --print
 例子2：pt-table-sync --sync-to-master  --databases=test --tables=dic_ind --charset=utf8 h=192.168.11.64,u=checksums,p=123456,P=3307 --print

再执行如下：
perl pt-table-sync --charset=utf8 --execute --no-check-slave h=localhost,u=pc_checksum,p=percona,S=/tmp/mysql3376.sock h=10.16.24.109,u=pc_checksum,p=percona,P=3376

[mysql@mvxl0783 bin]$ perl pt-table-sync --charset=utf8 --execute --no-check-slave h=localhost,u=pc_checksum,p=percona,S=/tmp/mysql3376.sock

h=10.16.24.109,u=pc_checksum,p=percona,P=3376
Triggers are defined on the table at pt-table-sync line 10920.  while doing sakila.customer on 10.16.24.109
Triggers are defined on the table at pt-table-sync line 10920.  while doing sakila.film on 10.16.24.109
Triggers are defined on the table at pt-table-sync line 10920.  while doing sakila.payment on 10.16.24.109
Triggers are defined on the table at pt-table-sync line 10920.  while doing sakila.rental on 10.16.24.109

检查数据是否一致了：

perl pt-table-checksum --no-check-binlog-format --nocheck-replication-filters --ignore-databases=mysql,\
performance_schema,information_schema,log,common_schema --replicate=test.checksums --host=127.0.0.1 \
--port 3376 -upc_checksum -ppercona --engines=InnoDB,MyISAM --recurse=1 --chunk-size-limit=0 --recursion-method=dsn=D=host,D=test,t=dsns --replicate-check-only

没有显示返回记录，说明数据已同步。