MySql数据库主从复制一致性的校验方案

主从复制往往会存在主从数据库数据不一致的问题，这会导致与客户端显示的数据不一样。那么本文章主要就是对主从复制的一致性问题给出一些操作方案。

 

一、准备工作

1. 使用虚拟机搭建了一主多从的主从结构（主从搭建参考我之前的文章“搭建MySql主从复制”）
2. 主（192.168.119.149），数据库帐号slave
3. 从1（192.168.119.150），数据库帐号slave
4. 从2（192.168.119.151），数据库帐号slave
5. 相关工具：percona-toolkit-3.3.1_x86_64.tar.gz
6. CentOS Linux release 7.9.2009 (Core)
7. mysql版本：8.0.21

 

二、安装percona-toolkit

官网下载地址：https://downloads.percona.com/downloads/percona-toolkit/3.3.1/binary/tarball/percona-toolkit-3.3.1_x86_64.tar.gz

我使用wget下载有点慢，就直接下载到本地后FTP上传到服务器上。

#解压
tar -zxvf percona-toolkit-3.3.1_x86_64.tar.gz

#进入解压后的文件夹
cd percona-toolkit-3.3.1

percona-toolkit-3.3.1文件夹内的结构如下图：

 

安装percona-toolkit：

#如果没有安装prel要先安装prel
yum install perl-devel

#安装好prel后再安装percona-toolkit，PREFIX是指定安装路径，输入命令后结果如下图
perl Makefile.PL PREFIX=/usr/local/percona-toolkit-3.3.1

从上图中可以看见我们还缺少一些依赖没有安装。

#安装依赖
yum install -y perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-DBI perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest perl-Digest-MD5 perl-TermReadKey

 

安装成功后，我们再次安装percona-toolkit，输入命令后结果如下图，没有Warning了。

#再安装percona-toolkit
perl Makefile.PL PREFIX=/usr/local/percona-toolkit-3.3.1

输入安装命令，进行安装

make && make install

 

进入安装目录/usr/local/percona-toolkit-3.3.1/bin测试是否安装成功，结构如下图：

#进入安装目录下的bin目录
cd /usr/local/percona-toolkit-3.3.1/bin

#测试是否安装成功，查看pt版本
./pt-table-checksum --version

 

如果你想随处都可以执行pt命令，那就添加环境变量。

#打开配置文件
vim /etc/profile

#最后一行写入以下内容，冒号后面是percona-toolkit的安装目录下的bin目录
export PATH=$PATH:/usr/local/percona-toolkit-3.3.1/bin

#保存文件后刷新内容
source /etc/profile

 

三、主从数据一致校验

使用 pt-table-checksum 负责检测MySQL主从数据一致性

#命令
pt-table-checksum [options] [dsn]

#常用的参数解释
--nocheck-replication-filters    不检查复制过滤器，建议启用。后面可以用--databases来指定需要检查的数据库。
--no-check-binlog-format         不检查复制的binlog模式，要是binlog模式是ROW，则会报错。
--replicate-check-only           只显示不同步的信息。
--replicate=                     把checksum的信息写入到指定表中，建议直接写到被检查的数据库当中。
--databases=                     指定需要被检查的数据库，多个则用逗号隔开。
--tables=                        指定需要被检查的表，多个用逗号隔开
--host | h=                      Master的地址
--user | u=                      用户名
--passwork | p=                  密码
--Post | P=                      端口

 

先查看三台数据库原有的数据，通过下面三张图可以发现，目前数据都是统一的。

在主库通过 pt-table-checksum 命令检测结果如下图，也是数据一致的。DIFFS等于0就表示没有不一样的数据。

 

主从数据一致性的测试：主从复制都是主写入数据，同步的从，从只负责读数据。现在我们就在从2（192.168.119.151）中先插入一条数据，使得主从数据不一样。

 

现在从2（192.168.119.151）与主、从1数据内容不一致了。我们再次使用 pt-table-checksum 命令来检测，看看是否会检测出主从不一致。

#检测命令，在主库中输入命令。--replicate=php.checksums表示会在php数据库中生成一张checksums的表来记录检测信息
pt-table-checksum --nocheck-replication-filters --replicate=php.checksums --no-check-binlog-format --host=192.168.119.149 --databases=php --tables=user --user=slave --password=root

 

从上图的结果中可以看出主从出现了不一致，有一行数据不同，这说明我们的检测结果是正确的。

 

四、 数据恢复 

使用 pt-table-sync 负责当从数据不一致时进行数据修复，让他们保持数据的一致性

#命令
pt-table-sync [OPTIONS] DSN [DSN]

#常用参数解释
--replicate=        指定通过pt-table-checksum得到的表，这2个工具差不多都会一直用。
--databases=        指定执行同步的数据库，多个用逗号隔开。
--tables=           指定执行同步的表，多个用逗号隔开。
--sync-to-master    指定一个DSN，即从的IP，他会通过show processlist或show slave status 去自动的找主。
h=192.168.119.149   服务器地址，命令里有2个ip，第一次出现的是Master的地址，第二次是Slave的地址。
u=slave             帐号。
p=root              密码。
--print             打印，但不执行命令。
--execute           执行命令。

#当指定--sync-to-master参数时，DSN1表示的从库的ip，端口号，用户，密码等，按照下面命令【h=192.168.119.151,u=slave,p=root,P=3306】就表示从库
pt-table-sync --sync-to-master h=192.168.119.151,u=slave,p=root,P=3306 --databases=php --execute

 

在主库使用 pt-table-sync 修复数据，结果如下图。

#命令，此条命令没有--tables参数表示执行全库。因为现在只有【192.168.119.151】库的数据不一样，所以这里只恢复一个库。
#如果【192.168.119.150】数据也不一样，那么在下面命令还要加上【h=192.168.119.150,u=slave,p=root,P=3306】的数据库信息。
pt-table-sync --sync-to-master h=192.168.119.151,u=slave,p=root,P=3306 --databases=php --execute

由上图可见，再次检测的时候DIFFS=0，表示主从数据已经修复统一。

 

具体是否真的数据一致了，我们再来查询三个数据库中的user表，结果如下图：

由上图可见，在从2（192.168.119.151）的数据库中我们之前插入了一条name=rong，age=44的数据，现在查询结果又恢复到插入数据之前了。那条name=rong，age=44的数据不见了。我们再查询主库和从1（结果看下面两张图）看看数据是否有变化。

由上两张图可见，现在主从数据已经一致。试验结果成功。

 

五、 定时检测与恢复 

我们可以把检测与恢复命令写成shell脚本定时去执行，确保数据的一致性。我写的只是针对user表，想要全库就把--tables参数去掉。

#!/usr/bin/env bash
NUM=`pt-table-checksum --nocheck-replication-filters --replicate=php.checksums --no-check-binlog-format --host=192.168.119.149 --databases=php --tables=user --user=slave --password=root | awk 'NR>1{sum+=$3}END{print sum}'`
if [ $NUM -eq 0 ] ;then
echo "Data is ok!"
else
echo "Data is error!"
pt-table-sync --sync-to-master h=192.168.119.150,u=slave,p=root,P=3306 --databases=php --tables=user --execute
pt-table-sync --sync-to-master h=192.168.119.151,u=slave,p=root,P=3306 --databases=php --tables=user --execute
fi

------------------脚本内容不包括此虚线以下的内容--------------------
#也可以把一下两句合并成一句
pt-table-sync --sync-to-master h=192.168.119.150,u=slave,p=root,P=3306 --databases=php --tables=user --execute
pt-table-sync --sync-to-master h=192.168.119.151,u=slave,p=root,P=3306 --databases=php --tables=user --execute
#合并成
pt-table-sync --sync-to-master h=192.168.119.150,u=slave,p=root,P=3306 h=192.168.119.151,u=slave,p=root,P=3306 --databases=php --tables=user --execute