欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  科技

如何利用percona-toolkit工具检查MySQL数据库主从一致性以及修复

程序员文章站 2023-12-10 16:25:04
如何利用percona-toolkit工具检查MySQL数据库主从一致性以及修复...

利用percona-toolkit工具检查mysql数据库主从复制数据的一致性,以及修复。

一、pt-table-checksum检查主从库数据的一致性

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

安装:

#依赖包yum  –y  perl-dbi  perl-dbd-mysql  perl-termreadkey#percona-toolkit包wget http://www.percona.com/downloads/percona-toolkit/latest/percona-toolkit-2.2.4.tar.gztar xzvf percona-toolkit-2.2.4.tar.gz ;  cd percona-toolkit-2.2.4 ;  perl makefile.pl && make && make install

使用方法:

pt-table-checksum [options] [dsn]

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

不指定任何参数,会直接对本地的所有数据库的表进行检查。

pt-table-checksum –s /tmp/mysqld.sock 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状态。

可以用该语句授权用户,这里为了方便直接用root用户了

grant select, process, super, replication slave on *.* to ‘checksums’@’x.x.x.x’ identified by ‘xxxx’;

执行检测(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 table09-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: nulllower_boundary: nullupper_boundary: null      this_crc: 13fa7d9d    #从的校验值      this_cnt: 4            #从的行数    master_crc: aa7a56c3    #主的校验值    master_cnt: 5            #主的行数            ts: 2013-09-18 12:03:161 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主机名:

[root@host125 ~]# 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[root@host125 ~]# 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 host122table chunk cnt_diff crc_diff chunk_index lower_boundary upper_boundarytest.t1 1 -1 1

数据不一致的slave和表都找出来了,下面就用pt-table-sync来修补数据。

二、pt-table-sync修复从库不一致的数据

使用方法:

pt-table-sync [options] dsn [dsn]

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

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

执行:

[root@host125 ~]# 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的ipreplace 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     :执行命令。

更多的参数请见官网,上面指出来的是常用的,对该场景够用的参数。

和上面的命令一样效果的命令:

[root@host125 ~]# 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语句,但是这样就没有输出了:

[root@host125 ~]# pt-table-sync --execute --sync-to-master h=192.168.68.232,u=root,p=123,p=3306 --databases test --tables t1

如何利用percona-toolkit工具检查MySQL数据库主从一致性以及修复

数据已经修复完成了:

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

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

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

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

补充:

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

如果在shell窗口不想显示输入密码则可以添加:--ask-pass 参数,如:

[root@host125 ~]# pt-table-sync --print --ask-pass --sync-to-master h=192.168.68.232,u=root,p=3306 --databases test --tables t1enter 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模块:

[root@host125 ~]# perl -mcpan -e "shell"cpan[1]> install term::readkey

总结:

该工具执行检查表动作,检查连接的帐号需要有很高的权限,在一般权限上需要加select, process, super, replication slave等权限。pt-table-checksm 配合pt-table-sync使用,在执行pt-table-sync数据同步之前,一定要执行pt-table-checksm命令检查。

相关标签: MySQL 数据库