mysql主从一致性检查修复 之 percona-toolkit安装检查修复

说明:
通过percona-toolkit工具可以进行mysql数据库主从数据复制的一致性修复。pt-table-checksum在MASTER上校验指定库、表,将结果存在一个库表里,复制进程将检验sql传递到slave上再执行一次。通过比较M/S的检验值确定数据是否一致。利用主从复制做检验,不需要在检验期间对主从数据库同时锁表,可以控制校验的数据和速度,不影响到正常服务。

实现:
一、percona-toolkit安装

1.percona-toolkit是perl写的,因此需要安装perl相应库来处理mysql
wget http://dl.gaingreat.com/percona-toolkit/TermReadKey-2.14.tar.gz
wget http://dl.gaingreat.com/percona-toolkit/Test-Deep-0.112.tar.gz
wget http://dl.gaingreat.com/percona-toolkit/Test-NoWarnings-1.04.tar.gz
wget http://dl.gaingreat.com/percona-toolkit/Test-Simple-1.001003.tar.gz
wget http://dl.gaingreat.com/percona-toolkit/Test-Tester-0.109.tar.gz
wget http://dl.gaingreat.com/percona-toolkit/DBD-mysql-4.027.tar.gz
wget http://dl.gaingreat.com/percona-toolkit/DBI-1.616.tar.gz
wget http://www.percona.com/redir/downloads/percona-toolkit/2.2.4/percona-toolkit-2.2.4.tar.gz

 
2.安装perl相应库。请先安装缩进内的,如Test::Tester就要比Test-Deep先安装。因为Test-Deep依赖Test::Tester

Test-Deep
perl Makefile.PL 
make && make install

	Test::Tester
        perl Makefile.PL 
        make && make install

	Test::Builder,即Test-Simple
        perl Makefile.PL 
        make && make install

	Test::NoWarnings
        perl Makefile.PL 
        make && make install

Term::ReadKey
perl Makefile.PL 
make && make install

DBI
perl Makefile.PL 
make && make install

DBD-mysql
perl Makefile.PL 
make && make install

 
3.安装percona-toolkit

# cd /usr/local/src
# tar zxvf percona-toolkit-2.2.4.tar.gz 
# cd percona-toolkit-2.2.4
# perl Makefile.PL 
# make
# make install

 
二、percona-toolkit工具pt-tables-sum检查mysql主从复制不一致
pt-table-checksum [OPTIONS] [DSN]。pt-table-checksum:在主上通过执行校验的查询对复制的一致性进行检查,对比主从的校验值,从而产生结果。DSN指向的是主的地址,该工具的退出状态不为零,如果发现有任何差别,或者如果出现任何警告或错误,更多信息请见官网。不指定任何参数,会直接对本地的所有数据库的表进行检查。如pt-table-checksum u=root,p=123456
#主库:

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  4 | dd   |
|  5 | ee   |
+----+------+
5 rows in set (0.00 sec)

#从库:

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  4 | dd   |
+----+------+
4 rows in set (0.00 sec)

 
1、测试需要一个既能登录主库,也能登录从库,而且还能同步数据库的账号;
2、只能指定一个host,必须为主库的IP;
3、在检查时会向表加S锁;
4、运行之前需要从库的同步IO和SQL进程是YES状态。mysql> GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO ‘checksums’@’x.x.x.x’ IDENTIFIED BY ‘xxxx’; //可以用该语句授权用户,这里为了方便直接用root用户了

MASTER上执行检测

# pt-table-checksum --nocheck-replication-filters --replicate=test.checksum --databases=test h=192.168.68.235,u=root,p=123 --empty-replicate-table --create-replicate-table
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
09-18T12:03:16      0      1        5       1       0   0.018 test.t1

参数说明:
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可以看出主从的表数据不一致。通过查看从库上的test.checksum表可以看到主从库的检验信息。

mysql> select * from checksum\G;
*************************** 1. row ***************************
            db: test
           tbl: t1
         chunk: 1
    chunk_time: 0.001604
   chunk_index: NULL
lower_boundary: NULL
upper_boundary: NULL
      this_crc: 13fa7d9d    #从的校验值
      this_cnt: 4            #从的行数
    master_crc: aa7a56c3    #主的校验值
    master_cnt: 5            #主的行数
            ts: 2013-09-18 12:03:16
1 row in set (0.00 sec)

通过上面的 this_crc <> master_crc 更能清楚的看出他们的不一致了,通过chunk知道是这个张表的哪个块上的记录出现不一致。要是主的binlog模式是Row 则会报错:
Replica db2 has binlog_format ROW 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.

从错误信息得出,要是不改binlog模式的话,则在执行上面的命令时候要指定:–no-check-binlog-format,即:
pt-table-checksum –nocheck-replication-filters –no-check-binlog-format –replicate-check-only –replicate=test.checksum –databases=test –tables=t1 h=127.0.0.1,u=root,p=123,P=3306
指定–replicate-check-only参数会在前一次pt-table-checksum检验的数据之上比较(不会再执行计算),显示出数据不一致的SLAVE主机名:

# pt-table-checksum  --nocheck-replication-filters --no-check-binlog-format --replicate-check-only --replicate=test.checksum --databases=test --tables=t1  h=127.0.0.1,u=root,p=123456,P=3306
# pt-table-checksum --nocheck-replication-filters --replicate=test.checksum --databases=test h=192.168.68.235,u=root,p=123 --empty-replicate-table --create-replicate-table --replicate-check-only 
Differences on host122
TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY
test.t1 1 -1 1

 
三、数据不一致的SLAVE和表都找出来了,下面就用pt-table-sync修复从库不一致的数据
pt-table-sync [OPTIONS] DSN [DSN]。pt-table-sync: 高效的同步MySQL表之间的数据,他可以做单向和双向同步的表数据。他可以同步单个表,也可以同步整个库。它不同步表结构、索引、或任何其他模式对象。所以在修复一致性之前需要保证他们表存在。

继续上面的复制环境,主和从的t1表数据不一致,需要修复,

# pt-table-sync --print --replicate=test.checksum h=192.168.68.235,u=root,p=123,P=3306 h=192.168.68.232,u=root,p=123,P=3306  //先MASTER的IP,再SLAVE的IP
REPLACE INTO `test`.`t1`(`id`, `name`) VALUES ('5', 'ee') 
/*percona-toolkit src_db:test src_tbl:t1 src_dsn:P=3306,h=192.168.68.235,p=...,u=root dst_db:test dst_tbl:t1 dst_dsn:P=3306,h=192.168.68.232,p=...,u=root lock:1 transaction:1 changing_src:test.checksum replicate:test.checksum bidirectional:0 pid:24763 user:root host:host125*/;

参数的意义:

--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 --print --sync-to-master h=192.168.68.232,u=root,p=123,P=3306 --databases test --tables t1  //用一个IP (SLAVE)就可以了。
REPLACE INTO `test`.`t1`(`id`, `name`) VALUES ('5', 'ee') /*percona-toolkit src_db:test src_tbl:t1 src_dsn:P=3306,h=192.168.68.235,p=...,u=root dst_db:test dst_tbl:t1 dst_dsn:P=3306,h=192.168.68.232,p=...,u=root lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:24798 user:root host:host125*/;

还可以让它自己执行修复数据的SQL语句,但是这样就没有输出了:

# pt-table-sync --execute --sync-to-master h=192.168.68.232,u=root,p=123,P=3306 --databases test --tables t1  //数据已经修复完成了:

 

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  4 | dd   |
|  5 | ee   |
+----+------+
5 rows in set (0.00 sec)

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

附录
附录1.要是表中没有唯一索引或则主键则会报错:
Can’t make changes on the master because no unique index exists at /usr/local/bin/pt-table-sync line 10591.

附录2.要是从库有的数据,而主库没有,那这个数据怎么处理?会给出删除SLAVE多余数据,和修复SLAVE缺失数据的SQL语句。

附录3.如果在shell窗口不想显示输入密码则可以添加:–ask-pass 参数,如:
# pt-table-sync –print –ask-pass –sync-to-master h=192.168.68.232,u=root,P=3306 –databases test –tables t1
Enter password for 192.168.68.232:

如果使用–ask-pass,报错:
Cannot read response; is Term::ReadKey installed? Can’t locate Term/ReadKey.pm in @INC
则需要安装Term/ReadKey.pm模块:
# perl -MCPAN -e “shell”
cpan[1]> install Term::ReadKey

附录4.该工具执行检查表动作,检查连接的帐号需要有很高的权限,在一般权限上需要加SELECT, PROCESS, SUPER, REPLICATION SLAVE等权限。pt-table-checksm 配合pt-table-sync使用,在执行pt-table-sync数据同步之前,一定要执行pt-table-checksm命令检查。

附录5. 手动执行pt-table-checksum时会出现Diffs cannot be detected because no slaves were found. Please read the –recursion-method documentation for information.
从字面意思上看是,主库找不到从数据库。只需要在从库配置文件/具体目录/my.cnf中添加
report_host=slave_ip
report_port=slave_port
即可。

默认是通过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;前提从库配置文件里面已经配置自己的地址和端口:

# grep 'report' /etc/my.cnf 
report_host = 192.168.0.20
report_port = 3306
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主从一致性检查修复 之 percona-toolkit安装检查修复”的一个回复

发表评论

邮箱地址不会被公开。 必填项已用*标注