percona-toolkit使用方法

一、percona-toolkit

Mysql主从数据的检查可以使用percona-toolkit工具，该工具包括很多命令，其中pt-table-checksum命令用于检查Mysql主从数据是否一致，pt-table-sync命令用于修复不一致的数据

 

二、percona-toolkit的安装

1、由于percona-toolkit工具是用Perl语言写的，所以需要安装一些Perl用到的模块

yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey

2、下载percona-toolkit：

wget https://www.percona.com/downloads/percona-toolkit/2.2.17/RPM/percona-toolkit-2.2.17-1.noarch.rpm

3、安装：

rpm -ivh percona-toolkit-2.2.17-1.noarch.rpm

 

三、pt-table-checksum

1、功能介绍

检查mysql复制的一致性

2、用法介绍

Usage: pt-table-checksum [OPTION...] [DSN]

工作原理：pt-table-checksum在主上执行检查语句在线检查mysql复制的一致性，生成replace语句，然后通过复制传递到从，再通过update更新master_src的值。通过检测从上this_src和master_src的值从而判断复制是否一致。

注意：使用的时候选择业务低峰的时候运行，因为运行的时候会造成表的部分记录锁定。使用--max-load来指定最大的负载情况，如果达到那个负载这个暂停运行。

通过 -explain参数再结合二进制日志就可以看出脚本的工作原理，如我的test库有一个名字为xiaoml的表，我们通过抓取二进制日志来查看脚本的原理：

REPLACE INTO `test`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'test', 'xiaoml', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `name`, CONCAT(ISNULL(`name`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `test`.`xiaoml` /*checksum table*/;

UPDATE `test`.`checksums` SET chunk_time = '0.000563', master_crc = '31012777', master_cnt = '4' WHERE db = 'test' AND tbl = 'xiaoml' AND chunk = '1'

3、使用示例：比较gamedb_xiaoml数据库同步是否一致，结果显示所有的表

[root@xiaoml_pro ~]# pt-table-checksum h='127.0.0.1',u='xiaoml',p='123456',P=3306 -d gamedb_xiaoml --nocheck-replication-filters --create-replicate-table --replicate=gamedb_xiaoml.checksums  --recursion-method=hosts

            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE

03-17T19:34:08      0      1        3       1       0   0.008 gamedb_xiaoml._8864

03-17T19:34:08      0      0        3       1       0   0.008 gamedb_xiaoml.lk

命令结果各字段的说明：

TS             ：完成检查的时间。

ERRORS        ：检查时候发生错误和警告的数量。

DIFFS          ：0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息。

ROWS          ：表的行数。

CHUNKS        ：被划分到表中的块的数目。

SKIPPED        ：由于错误或警告或过大，则跳过块的数目。

TIME           ：执行的时间。

TABLE          ：被检查的表名。

从结果中，我们可以看到gamedb_xiaoml._8864表的DIFFS不为0，那么就是这两个表不同步了。

4、参数说明：第一次运行的时候需要添加--create-replicate-table参数，如果不加这个就需要手工运行添加表结构的SQL,表结构SQL如下：

mysql> show create table gamedb_xiaoml.checksums\G

*************************** 1. row ***************************

       Table: checksums

Create Table: CREATE TABLE `checksums` (

  `db` char(64) COLLATE utf8mb4_unicode_ci NOT NULL,

  `tbl` char(64) COLLATE utf8mb4_unicode_ci NOT NULL,

  `chunk` int(11) NOT NULL,

  `chunk_time` float DEFAULT NULL,

  `chunk_index` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

  `lower_boundary` text COLLATE utf8mb4_unicode_ci,

  `upper_boundary` text COLLATE utf8mb4_unicode_ci,

  `this_crc` char(40) COLLATE utf8mb4_unicode_ci NOT NULL,

  `this_cnt` int(11) NOT NULL,

  `master_crc` char(40) COLLATE utf8mb4_unicode_ci 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=utf8mb4 COLLATE=utf8mb4_unicode_ci

1 row in set (0.00 sec)

之所以使用--nocheck-replication-filters参数是因为我的my.cnf配置了replicate-ignore-db和replicate-wild-do-table等参数。另外需要特别注意执行的checksums所在的数据库必须是同步的数据库。

5、pt-table-checksum其他参数说明：

--nocheck-replication-filters：不检查复制过滤器，建议启用。后面可以用--databases来指定需要检查的数据库。

--no-check-binlog-format：不检查复制的binlog模式，要是binlog模式是ROW，则会报错。

--replicate-check-only：只显示不同步的信息。(注意：要谨慎使用，此参数不会生成新的checksums数据，只会根据checksums表已经有的数据来显示。)

--replicate=：把checksum的信息写入到指定表中，建议直接写到被检查的数据库当中。 

--databases=：指定需要被检查的数据库，多个则用逗号隔开。

--tables=：指定需要被检查的表，多个用逗号隔开

--recursion-method：指定复制检查的方式,默认情况下使用SHOW PROCESSLIST,如果非标准的3306端口,就使用SHOW SLAVE HOSTS的方式

h=127.0.0.1 ：Master的地址

u=xiaoml：用户名

p=123456：密码

P=3306：端口

6、校验结束后可以在每个从库上执行如下sql查看是否有主从不一致的情况：

mysql> select * from gamedb_xiaoml.checksums \G

*************************** 1. row ***************************

            db: gamedb_xiaoml

           tbl: _8864

         chunk: 1

    chunk_time: 0.00134

   chunk_index: NULL

lower_boundary: NULL

upper_boundary: NULL

      this_crc: 709a8dc------->该数据库该表的crc值

      this_cnt: 4---------------->该数据库该表的count值

    master_crc: 435a7b4f---->主数据库该表的crcr值

    master_cnt: 3--------------->主数据库该表的count值

            ts: 2016-03-17 19:34:08

*************************** 2. row ***************************

            db: gamedb_xiaoml

           tbl: linekong

         chunk: 1

    chunk_time: 0.001512

   chunk_index: NULL

lower_boundary: NULL

upper_boundary: NULL

      this_crc: 435a7b4f

      this_cnt: 3

    master_crc: 435a7b4f

    master_cnt: 3

            ts: 2016-03-17 19:34:08

2 rows in set (0.00 sec)

7、其他注意事项

(1).测试需要一个既能登录主库，也能登录从库，而且还能同步数据库的账号，需要有以下权限：

SELECT,PROCESS,SUPER,REPLICATION SLAVE,CREATE TABLE

(2).只能指定一个host，必须为主库的IP；

(3).在检查时会向表加锁；

(4).运行之前需要从库的同步IO和SQL进程是YES状态。

(5).执行的checksums所在的数据库必须是同步的数据库，否则在从库上找不到该表，会报如下错误，一直循环：

Waiting for the --replicate table to replicate to XXX

(6).主从如果在同一台服务器上，那么要在从库的my.cnf配置文件里写明从库的地址：report_host = $slave_IPADDRESS，不然会因为找不到从库报如下错误：

Diffs cannot be detected because no slaves were found.  Please read the --recursion-method documentation for information.

(7).如果my.cnf配置了replicate-ignore-db和replicate-wild-do-table等参数，那么需要加参数--nocheck-replication-filters，不然会报如下错误：

Replication filters are set on these hosts Please read the --check-replication-filters documentation .....

 

四、pt-table-sync

1、功能介绍

高效同步mysql表的数据

2、用法介绍

Usage: pt-table-sync [OPTIONS] DSN [DSN]

原理：总是在主上执行数据的更改，再同步到从上，不会直接更改成从的数据，在主上执行更改是基于主上现在的数据，不会更改主上的数据。注意使用之前先备份你的数据，避免造成数据的丢失.执行execute之前最好先换成--print查看一下会变更哪些数据。

3、使用示例

查看gamedb_xiaoml库的_8864表有哪些数据不一致

[root@xiaoml_pro ~]# pt-table-sync --print  h=127.0.0.1,P=3306,u=xiaoml,p=123456 h=127.0.0.1,P=3307,u=xiaoml,p=123456 --database=gamedb_xiaoml --tables=_8864 

DELETE FROM `gamedb_xiaoml`.`_8864` WHERE `id`='4' LIMIT 1 /*percona-toolkit src_db:gamedb_xiaoml src_tbl:_8864 src_dsn:P=3306,h=127.0.0.1,p=...,u=xiaoml dst_db:gamedb_xiaoml dst_tbl:_8864 dst_dsn:P=3307,h=127.0.0.1,p=...,u=xiaoml lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:7514 user:root host:xiaoml_pro*/;

修复gamedb_xiaoml库_8864表不一致的数据：

执行修复前查看_8864b表存在不一致的情况：

[root@xiaoml_pro ~]# pt-table-checksum h='127.0.0.1',u='xiaoml',p='123456',P=3306 -d gamedb_xiaoml -t _8864 --nocheck-replication-filters --replicate=gamedb_xiaoml.checksums  --recursion-method=hosts

            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE

03-17T20:23:23      0      1        3       1       0   0.008 gamedb_xiaoml._8864

执行修复命令：

[root@xiaoml_pro ~]# pt-table-sync --execute h=127.0.0.1,P=3306,u=xiaoml,p=123456 h=127.0.0.1,P=3307,u=xiaoml,p=123456 --database=gamedb_xiaoml --tables=_8864

再次查看：

[root@xiaoml_pro ~]# pt-table-checksum h='127.0.0.1',u='xiaoml',p='123456',P=3306 -d gamedb_xiaoml -t _8864 --nocheck-replication-filters --replicate=gamedb_xiaoml.checksums  --recursion-method=hosts

            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE

03-17T20:23:23      0      0        3       1       0   0.008 gamedb_xiaoml._8864

_8864表diffs字段已为0，数据已保持一致