Mysql 主从数据库一致性检查与修复

利用pt-table-checksum 检查主从的一致性,pt-table-sync实现主从数据一致性修复

一.percona-toolkit的下载安装:
需要先安装其它依赖环境包...
shell> perl -MCPAN -e ‘install DBI‘
shell> perl -MCPAN -e ‘install DBD::mysql‘
shell> perl -MCPAN -e ‘install Term::ReadKey‘
顺便说一下，我在安装某些Perl模块的时候，出现类似下面的错误提示：
Can’t locate object method “install” via package “…”
如果你也遇到了类似的问题，可以进入到Perl命令行安装：
yum install YAML
shell> perl -MCPAN -e shell
cpan> install ...

下载地址:wget http://www.percona.com/downloads/percona-toolkit/2.2.14/tarball/percona-toolkit-2.2.14.tar.gz
安装方法:perl Makefile.PL;make;make install

二.在master上做授权操作:(IP为主库地址)
mysql> grant select ,process,super,replication slave on *.* to ‘user‘@‘192.168.9.140‘ identified by ‘userpw‘; 
mysql> flush privileges; 
  注:select:查看所有库的表；process:执行show processlist ；super:设置binlog_format=‘statement’ 
         replication slave:show slavehosts 
数据库主从结构:
主:192.168.9.140 (binlog_format=mixed)
从:192.168.9.142 (binlog_format=mixed)

三.主从库执行数据一致检查
[root@DB1:/]# pt-table-checksum h=‘192.168.9.140‘,u=‘user‘,p=‘userpw‘,P=3306 --databases db1 --tables t1  --max-load="Threads_running=25" --nocheck-replication-filters --create-replicate-table --replicate=test.checksums --no-check-binlog-format --no-check-slave-tables
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
05-13T15:42:58      0      1        8       1       0   0.030 db1.t1
查询结果显示 diffs =1 表示db1.t1表主从库数据存在异常.

执行结果显示参数意义: 
 TS        :完成检查的时间. 
ERRORS     :检查时候发生错误和警告的数量. 
DIFFS      :0表示一致，1表示不一致.当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息. 
ROWS       :表的行数. 
CHUNKS     :被划分到表中的块的数目. 
SKIPPED    :由于错误或警告或过大，则跳过块的数目. 
TIME       :执行的时间. 
TABLE      :被检查的表名

参数详解:
[root@DB1:/]# pt-table-checksum --help   查看所有的参数信息.
  h=‘192.168.9.140‘,u=‘user‘,p=‘userpw‘,P=3306  连接主数据库的IP,用户名与密码,端口.
  --create-replicate-table:这个参数只在第一次运行时添加就可以,用于建立checksums表;后续不需要添加此参数,如果添加了会重新创建checksums表. 
  --nocheck-replication-filters:不检查复制过滤器(即参数文件里设置的repliacte-do-waild-table等规则) 
  --no-check-binlog-format :不检查复制的binlog格式（这个参数在，binlog_format=row时，一定要加，不然报错） 
  --replicate=kz.checksums:把checksum的信息写入指定库，指定表，建议直接写到被检查的库里.
  --ignore-tables=mysql.user:对某个表忽略检查
  --tables 指定被检查的表名;可以多个表,按逗号分开. 例如:--tables t1,t2
  ----databases 指定被检查的库名,如果没有此参数将检查所有库;也可以指定多个库名,例如:--databases db1,db2

更多参数使用方法:
  --recursion-method               processlist,hosts

要是在执行命令的过程遇到找不到从服务器的错误:
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的值;当--recursion-method=hosts 时会取show slave hosts 找到host的值.
使用--recursion-method=hosts 参数也同步可以解决,因从库比较多,而是跨机房从库检查慢的问题.可指定其中一个从库进行检查.
使用解决方法:
在从库的配置文件里加: report_host = 192.168.9.142   #设置成从库本机IP地址,并重启数据库
然后在主库上执行 show slave hosts

>show slave hosts;
+-----------+----------------+------+----------+------+-------------------+-----------+
| Server_id | Host           | User | Password | Port | Rpl_recovery_rank | Master_id |
+-----------+----------------+------+----------+------+-------------------+-----------+
|         2 | 192.168.9.142  |      |          | 3306 |                 0 |        140|
+-----------+----------------+------+----------+------+-------------------+-----------+
1 row in set (0.00 sec)

最后再执行以上命令（多加--recursion-method=hosts 参数)
pt-table-checksum --recursion-method=hosts h=‘192.168.9.140‘,u=‘user‘,p=‘userpw‘,P=3306 --databases db1 --tables t1 --max-load="Threads_running=25" --nocheck-replication-filters --replicate=test.checksums --no-check-binlog-format --no-check-slave-tables

四.对主从数据不一致性进行修复
语法结构: pt-table-sync [OPTIONS] DSN [DSN]
pt-table-sync: 高效的同步MySQL表之间的数据，他可以做单向和双向同步的表数据.他可以同步单个表，也可以同步整个库.它不同步表结构、索引、或任何其他模式对象.所以在修复一致性之前需要保证他们表存在.
pt-table-sync --help 查看详细参数.
  1.根据pt-table-checksum 检查的结果数据库进行合并. 主要指定--replicate=test.checksums
  2.直接进行表数据合并,从主库上面同步到从库. (从库不存在主库上面的表)

推荐根据pt-table-checksum 检查的结果数据库进行合并.
接着上面的复制情况，主和从的test1数据不一致，需要修复，要是有中文的则需要加上:--charset=utf8，防止乱码.
1>指定库,表进行数据合并:
pt-table-sync --recursion-method=hosts --replicate test.checksums --databases=db1 --tables=t1 --sync-to-master h=192.168.9.142,P=3306,u=user,p=userpw --charset=utf8 --print;   //打印主从存在异常的数据
pt-table-sync --recursion-method=hosts --replicate test.checksums --databases=db1 --tables=t1 --sync-to-master h=192.168.9.142,P=3306,u=user,p=userpw --charset=utf8 --execute; //执行数据一致性同步
2> 只根据test.checksums的结果进行数据合并.
pt-table-sync --replicate test.checksums --sync-to-master h=192.168.9.142,P=3306,u=user,p=userpw --charset=utf8 --print;  //打印主从存在异常的数据 
pt-table-sync --replicate test.checksums --sync-to-master h=192.168.9.142,P=3306,u=user,p=userpw --charset=utf8 --execute;//执行数据一致性同步

pt-table-sync --replicate=test.checksums h=192.168.9.140 --user=user --password=userpw h=192.168.9.142,u=user,p=‘userpw‘ --charset=utf8 --print //打印主从存在异常的数据
pt-table-sync --replicate=test.checksums h=192.168.9.140 --user=user --password=userpw h=192.168.9.142,u=user,p=‘userpw‘ --charset=utf8 --excute //执行数据一致性同步

将主的test数据库同步到192.168.9.142,使从上具有一样的数据
pt-table-sync --execute --sync-to-master --user=user --password=userpw  h=192.168.9.142 --database test

只同步指定的表(aaa 表)
pt-table-sync --execute --sync-to-master --user=user --password=userpw  h=192.168.9.142 D=test,t=aaa

参数的解释:
--replicate=  :指定通过pt-table-checksum得到的表.
--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     :执行命令.

更多的参数请见官网
https://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html
https://www.percona.com/doc/percona-toolkit/2.2/

本文出自 “旋木的技术博客” 博客，请务必保留此出处http://mrxiong.blog.51cto.com/287318/1651349