pt-table-checksum使用实践

在工作中接触最多的就是mysql replication,由于现在公司也还在使用mysql 5.1.x版本,在复制方面还是比较多的问题,比如主库宕机或者从库宕机都会导致复制中断,通常我们需要进行人为修复(mysql 5.5版本解决大部分问题),或者很多时候需要把一个从库提升为主库,但对从库和主库的数据一致性不能保证一样,所以就利用 pt-table-checksum 工作来检查主从的一致性,以及通过 pt-table-sync 如何修复这些不一致的数据。当然如果你数据量小,slave只是当做一个备份使用,那么出现数据不一致完全可以重做,或者通过其他方法解决。如果数据量非常大,重做就是非常蛋碎的一件事情了。^_^

工具安装:

1.软件下载:

[root@MySQL-01 ~]# wget http://www.percona.com/downloads/percona-toolkit/LATEST/RPM/percona-toolkit-2.2.7-1.noarch.rpm

2.安装该工具依赖的软件包:

[root@MySQL-01 ~]# yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes -y

3.软件安装:

[root@MySQL-01 ~]# rpm -ivh percona-toolkit-2.2.7-1.noarch.rpm 
Preparing...                ########################################### [100%]
   1:percona-toolkit        ########################################### [100%]
[root@MySQL-02 ~]# 

使用方法:

pt-table-checksum [OPTIONS] [DSN]

pt-table-checksum在主(master)上通过执行校验的查询对复制的一致性进行检查,对比主从的校验值,从而产生结果。DSN指向的是主的地址,该工具的退出状态不为零,如果发现有任何差别,或者如果出现任何警告或错误,更多信息请查看官方资料。

下面通过实际的例子来解释该工具如何使用:

主库:

mysql> select *  from yayun.t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | yayun |
|  2 | atlas |
|  3 | mysql |
+----+-------+
3 rows in set (0.00 sec)

mysql> 

备库:

mysql> select * from yayun.t1;
+----+----------+
| id | name     |
+----+----------+
|  1 | yayun    |
|  2 | atlas    |
|  3 | mysql    |
|  4 | dengyy   |
|  5 | love sql |
+----+----------+
5 rows in set (0.00 sec)

mysql> 

很明显主从数据不一致,那么我们使用工具来检测:

[root@MySQL-01 ~]# pt-table-checksum --nocheck-replication-filters --replicate=yayun.checksums --databases=yayun --tables=t1 h=127.0.0.1,u=root,p=123456,P=3306
Replica MySQL-02 has binlog_format MIXED which could cause pt-table-checksum to break replication.  Please read "Replicas using row-based replication" in the LIMITATIONS section of the tool's documentation.  If you understand the risks, specify --no-check-binlog-format to disable this check.
[root@MySQL-01 ~]# 

从错误信息得出,要是不改binlog模式的话,则执行上面的命令时候要指定:
--no-check-binlog-format,即:

[root@MySQL-01 ~]# pt-table-checksum --nocheck-replication-filters  --no-check-binlog-format --replicate=yayun.checksums --databases=yayun --tables=t1 h=127.0.0.1,u=root,p=123456,P=3306
Cannot connect to P=3306,h=192.168.0.20,p=...,u=root
Diffs cannot be detected because no slaves were found.  Please read the --recursion-method documentation for information.
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
04-13T15:44:23      0      0        3       1       0   0.044 yayun.t1
[root@MySQL-01 ~]# 

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

这个报错又是为什么呢?很多文章都没有说到,我真搞不清楚那文章怎么写出来的,囧。。。。

上面的提示信息很清楚,因为找不到从,所以执行失败。用参数--recursion-method 可以指定模式解决,关于--recursion-method参数的设置有:

METHOD       USES
===========  =============================================
processlist  SHOW PROCESSLIST
hosts        SHOW SLAVE HOSTS
cluster      SHOW STATUS LIKE 'wsrep\_incoming\_addresses'
dsn=DSN      DSNs from a table
none         Do not find slaves

默认是通过show processlist 找到host的值或show slave hosts 找到host的值。

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 3
   User: slave
   Host: 192.168.0.20:52352
     db: NULL
Command: Binlog Dump
   Time: 4164
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
*************************** 2. row ***************************
     Id: 33
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
2 rows in set (0.00 sec)

还有一种方法是show slave hosts;前提从库配置文件里面已经配置自己的地址和端口:

[root@MySQL-02 ~]# grep 'report' /etc/my.cnf 
report_host = 192.168.0.20
report_port = 3306
[root@MySQL-02 ~]# 
mysql> show slave hosts;
+-----------+--------------+------+-----------+
| Server_id | Host         | Port | Master_id |
+-----------+--------------+------+-----------+
|         2 | 192.168.0.20 | 3306 |         1 |
+-----------+--------------+------+-----------+
1 row in set (0.00 sec)

mysql> 

所以找不到从服务器时。在从库配置文件添加

report_host=slave_ip

report_port=slave_port

最重要的一点是我们需要在从库上授权,能让主库访问。很多文章没提,却能正常进行,不得不说大牛就是牛B

注意:

1、  根据测试,需要一个即能登录主库,也能登录从库的账号;

2、  只能指定一个host,必须为主库的IP;

3、  在检查时会向表加S锁;

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

mysql> GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'root'@'192.168.0.10' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> 

现在我们再来检测数据一致性:

[root@MySQL-01 ~]# pt-table-checksum --nocheck-replication-filters  --no-check-binlog-format --replicate=yayun.checksums --databases=yayun --tables=t1 h=127.0.0.1,u=root,p=123456,P=3306
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
04-13T15:59:31      0      1        3       1       0   0.080 yayun.t1
[root@MySQL-01 ~]# 
TS            :完成检查的时间。
ERRORS        :检查时候发生错误和警告的数量。
DIFFS         :0表示一致,1表示不一致。当指定--no-replicate-check时,会一直为0,当指定--replicate-check-only会显示不同的信息。
ROWS          :表的行数。
CHUNKS        :被划分到表中的块的数目。
SKIPPED       :由于错误或警告或过大,则跳过块的数目。
TIME          :执行的时间。
TABLE         :被检查的表名。

参数的意思:

--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         :端口

好了,命令以及常用参数都介绍了,一起解释下上面执行的效果:
通过DIFFS 是1 就可以看出主从的表数据不一致。怎么不一致呢? 通过指定--replicate=yayun.checksums 参数,就说明把检查信息都写到了checksums表中。
进入SLAVE相应的库中查看checksums表的信息:

mysql> select * from checksums\G
*************************** 1. row ***************************
            db: yayun
           tbl: t1
         chunk: 1
    chunk_time: 0.010735
   chunk_index: NULL
lower_boundary: NULL
upper_boundary: NULL
      this_crc: babf1dc0    #slave
      this_cnt: 5           #slave
    master_crc: 8727436a    #master
    master_cnt: 3           #master                 可以发现表t1中从库比主库多2条记录
            ts: 2014-04-13 16:05:16
1 row in set (0.00 sec)

mysql> 

通过上面找到了这些不一致的数据表,如何同步数据呢?即如何修复MySQL主从不一致的数据,让他们保持一致性呢?利用另外一个工具 pt-table-sync
使用方法:

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

pt-table-sync高效的同步MySQL表之间的数据,他可以做单向和双向同步的表数据。他可以同步单个表,也可以同步整个库。它不同步表结构、索引、或任何其他模式对象。所以在修复一致性之前需要保证他们表存在。

接着上面的复制情况,主和从的t1表数据不一致,需要修复,
执行

先master的ip,用户,密码,然后是slave的ip,用户,密码

[root@MySQL-01 ~]# pt-table-sync --replicate=yayun.checksums h=127.0.0.1,u=root,p=123456 h=192.168.0.20,u=root,p=123456 --print
DELETE FROM `yayun`.`t1` WHERE `id`='4' LIMIT 1 /*percona-toolkit src_db:yayun src_tbl:t1 src_dsn:h=127.0.0.1,p=...,u=root dst_db:yayun dst_tbl:t1 dst_dsn:h=192.168.0.20,p=...,u=root lock:1 transaction:1 changing_src:yayun.checksums replicate:yayun.checksums bidirectional:0 pid:2190 user:root host:MySQL-01*/;
DELETE FROM `yayun`.`t1` WHERE `id`='5' LIMIT 1 /*percona-toolkit src_db:yayun src_tbl:t1 src_dsn:h=127.0.0.1,p=...,u=root dst_db:yayun dst_tbl:t1 dst_dsn:h=192.168.0.20,p=...,u=root lock:1 transaction:1 changing_src:yayun.checksums replicate:yayun.checksums bidirectional:0 pid:2190 user:root host:MySQL-01*/;
[root@MySQL-01 ~]# 

参数的意义:

--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     :执行命令。

命令介绍完了,一起解释下执行的效果:通过(--print)打印出来了修复数据的sql语句,可以手动的去从行执行,让他们数据保持一致性。那能否直接执行?当然可以,通过(--execute)

[root@MySQL-01 ~]# pt-table-sync --replicate=yayun.checksums h=127.0.0.1,u=root,p=123456 h=192.168.0.20,u=root,p=123456 --execute

没发现任何异常,然后检查主从数据的一致性:

[root@MySQL-01 ~]# pt-table-checksum --nocheck-replication-filters  --no-check-binlog-format --replicate=yayun.checksums --databases=yayun --tables=t1 h=127.0.0.1,u=root,p=123456,P=3306
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
04-13T16:27:28      0      0        3       1       0   0.097 yayun.t1
[root@MySQL-01 ~]# 

主库:

mysql> select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | yayun |
|  2 | atlas |
|  3 | mysql |
+----+-------+
3 rows in set (0.00 sec)

mysql> 

备库:

mysql> select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | yayun |
|  2 | atlas |
|  3 | mysql |
+----+-------+
3 rows in set (0.00 sec)

mysql> 

OK,数据已经保持一致了。不过建议还是用--print 打印出来的好,这样就可以知道那些数据有问题,可以人为的干预下。不然直接执行了,出现问题之后更不好处理。总之还是在处理之前做好数据的备份工作。

注意:要是表中没有唯一索引或则主键则会报错:

Can't make changes on the master because no unique index exists at /usr/local/bin/pt-table-sync line 10591.

总结:

工具很给力,工作中常常在使用。注意使用该工具需要授权,一般SELECT, PROCESS, SUPER, REPLICATION SLAVE等权限就已经足够了。

 

参考资料:

http://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html

posted @ 2014-04-13 16:35  yayun  阅读(15159)  评论(2编辑  收藏  举报