MySQL主从数据不一致问题

目录

一、主从复制

二、主从一致性问题校验

pt-table-sync工具恢复数据

---

一、主从复制

MySQL数据库复制操作大致可以分成三个步骤：

1. 主服务器将数据的改变记录到二进制日志（binary log）中。

2. 从服务器将主服务器的binary log events 复制到它的中继日志（relay log）中。

3. 从服务器重做中继日志中的事件，将数据的改变与从服务器保持同步。

首先，主服务器会记录二进制日志，每个事务更新数据完成之前，主服务器将这些操作的信息记录在二进制日志里面在事件写入二进制日志完成后主服务器通知 存储引擎提交事务。

准备： 了解binlog日志，MySQL用户-权限 mysql服务器配置复制不难，但是因为场景不同可能会存在一定的差异化，总的来说分为一下几步：

1. 在服务器上创建复制账号。

2. 通知备库连接到主库并从主库复制数据。

二、主从一致性问题校验

在理想情况下，备库和主库的数据应该是完全一样的。但事实上备库可能发生错误并导致数据不一致。即使没有明显的错误，备库同样可能因为MySQL自身的特性导致数据不一致，例如MySQL的Bug感、网络中断、服务器崩溃，非正常关闭或者其他一些错误。 按照我们的经验来看，主备一致应该是一种规范，而不是例外，也就是说，检查你的主备库一致性应该是一个日常工作，特别是当使用备库来做备份时尤为重要，因为肯定不希望从一个已经损坏的备库里获得备份数据。

产生原因：

1、网络中断

2、服务器产生了问题

3、mysql自带bug

4、从库进行了非正当的操作（比如从库进行了添加，删除，修改，主库就会断开）

percona-toolkit官网：https://www.percona.com/doc/percona-toolkit/LATEST/installation.html

我们可以使用percona-toolkit工具做校验，而该工具包含

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

2. pt-table-sync负责挡住从数据不一致时修复数据，让他们保存数据的一致性

3. pt-heartbeat 负责监控MySQL主从同步延迟

一致性校验思路：

1、确定校验的主库，根据需要校验的表，分段获取数据

2、与从库进行校验（根据id）

3、校验的过程发现数据不一致的时候

4、在主库创建一个表 记录校验不一致的数据

5、恢复只需要读取这个表

安装

非docker容器安装
yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl perl-DBI -y

yum install percona-toolkit-3.0.3-1.el6.x86_64.rpm

yum list | grep percona-toolkit

percona-toolkit.x86_64 3.0.3-1.el6 installed
percona-toolkit.noarch 2.2.20-1 percona-release-noarch
percona-toolkit.x86_64 3.1.0-2.el7 percona-release-x86_64
percona-toolkit-debuginfo.x86_64 3.0.13-1.el7 percona-release-x86_64




docker容器中安装
apt-get update 

apt-get install percona-toolkit


pt-table-checksum --help

pt-table-checksum使用
pt-table-checksum [options] [dsn]

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

1、准备实战模拟一下数据的不一致，首先在主库中创建一个数据库，创建数据表，然后添加一些数据

create database mytest;

use mytest;

create table user(
    id int auto_increment not null primary key,
    name varchar(20) 
)engine=InnoDB charset=utf8;


show tables;

insert into user values (1,'aa');

insert into user values (2,'bb');

insert into user values (3,'cc');

select * from user;

2、主库中进行检测数据是否一致

注意常用的参数解释：
--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= ：端口

检测
root@71399784f284:/# pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --databases=mytest --tables=user --user=root --password=123456

Checking if all tables can be checksummed ...
Starting checksum ...
Replica 00847056d2fa 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.

1)上面的错误信息主要是因为，检测主库与从库的binlog日志的模式 - 通常来说可以不用改binlog添加 --no-check-binlog-format 跳过检测,但是可能也会出现如下的问题

root@71399784f284:/# pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --databases=mytest --no-check-binlog-format --tables=user --user=root --password=123456

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

2)问题原因是没有找到从库的地址，MySQL在做主从的时候可能会因为环境配置等因素，让pt-table-checksum没有很好地找到从库的地址 检测的方式：
1. 是否是指定在主库运行进行校验
2. 就是配置--recursion-method参数，然后在从库中指定好对应的地址


正确情况下：
root@71399784f284:/# pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --databases=mytest --no-check-binlog-format --tables=user --user=root --password=123456

Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
05-14T09:48:45      0      0        3          0       1       0   0.030 mytest.user

从库中配置

report_port=3306（由于我是使用docker来做的，因此此处配置端口为33070）
report_host=172.17.0.3
注意！！！ 不要有空格不然可能启动不了

检测：
 pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --no-check-binlog-format --databases=mytest --tables=user --user=root --password=123456


   TS              ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED  TIME     TABLE
05-14T10:26:46      0      0        3          0       1       0      0.018    mytest.user

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

那些信息数据会记录在checksums表中

mysql> use check_data;
Database changed
mysql> select * from checksums\G;
*************************** 1. row ***************************
            db: mytest
           tbl: user
         chunk: 1
    chunk_time: 0.003503
   chunk_index: NULL
lower_boundary: NULL
upper_boundary: NULL
      this_crc: d8588834
      this_cnt: 3
    master_crc: d8588834
    master_cnt: 3
            ts: 2020-05-14 10:26:46
1 row in set (0.00 sec)

3、如果配置了从属性ip与端口依然解决不了问题，可以替换为dsn的方式进行校验。

什么是dsn？

dsn方法：dsn是参数--recursion-method的一个参数值。注意是dsn，不是dns…

DSN：DSN，即DATA SOURCE NAME，数据源名称。

DSN包含从库的各个连接参数（user、password、port等），由逗号分隔的多个option=value字符串 组成。

如：

h=host,P=3309,u=username,p=password

dsn方法是指将从库的DSN信息存储在表（DSN表）里，然后将该表和DSN信息赋值给dsn，作为--recursion-method的参数值。

格式如：

--recursion-method dsn=D=percona,t=dsns,h=host,P=3309,u=username,p=passwd

 当指定pt-table-checksum的--recursion-method参数值为dsn时，它只会连接和检测这些指定的从库。当从库的MySQL用户名、密码、端口与主库不相同时，也 可以通过dsn的方式来指定。

DSN的部分选项如下：

D:DSN表所在的数据库名。

h:从库的host。

p:小写p，从库的密码。当密码包括逗号（,）时，需要使用反斜杠转义。 

P:大写P，从库的端口。 

S:连接使用的socket文件。 

t:存储DSN信息的DSN表名。

u:从库的MySQL用户名。

每一个选项和其值的形式为option=value，=的前后不能空格，如果选项值有空格，则必须使用引号引起来。 DSN表结构如下：

CREATE TABLE dsns (

   id int(11) NOT NULL AUTO_INCREMENT,

   parent_id int(11) DEFAULT NULL, 

   dsn varchar(255) NOT NULL, PRIMARY KEY (id) 

); 

存储在表中的DSNs以id值排序，但是这里可以忽略id和parent_id列，只需将从库的DSN信息存储在dsn列即可。存储的DSN格式如前面在命令行上指定DSN一样， 如: h=host,P=3309,u=username,p=password。 

创建好表后新增从节点的连接信息：

INSERT INTO mytest.dsns(dsn) VALUES ("h=172.17.0.3,u=root,p=123456,P=3306"); 

#create user 'slave_check'@'172.17.0.%' identified with mysql_native_password by '123456';

#grant all on *.* to 'slave_check'@'172.17.0.%' with grant option;

 

 

检测：

[root@localhost ~]# pt-table-checksum --tables=user  --databases=mytest --user=root --password='123456' --replicate=check_data.checksums --no-check-binlog-format --recursion-method dsn=t=mytest.dsns,h=172.17.0.3,u=slave_check,p=123456,P=3306  
  TS           ERRORS DIFFS  ROWS CHUNKS SKIPPED TIME  TABLE 
05-18T02:39:12  0      0      2     1       0    1.050  mytest.user

如果报以下错误 ，说明从数据库没有关闭防火墙

解决方法：

在从库中，关掉防火墙

#关闭防火墙
systemctl stop firewalld  （centos系统下的操作）
#开启防护墙
systemctl start firewalld 
#查看防火墙状态
systemctl status firewalld


docker中的操作
docker exec -it --privileged=true slave bash 

apt-get install iptables*

iptables -A INPUT -s 172.17.0.0/16 -p tcp -m tcp --dport 3306  -j ACCEPT

 接下来在主库中再次运行检测，如下图

pt-table-sync工具恢复数据

手册地址：https://www.percona.com/doc/percona-toolkit/LATEST/pt-table-sync.html

1、在从数据库中人为添加两条数据，从而让主从数据不一致

2、主库中进行检测数据一致性问题 

3、主库打印恢复数据

pt-table-sync --replicate=rep_test.checksums h=172.17.0.2,u=root,p=123456 h=172.17.0.3,u=slave_check,p=123456 --print

pt-table-sync --replicate=rep_test.checksums h=172.17.0.2,u=root,p=123456 h=172.17.0.3,u=slave_check,p=123456 --execute 

或者
pt-table-sync --sync-to-master h=172.17.0.3,u=slave_check,p=123456,P=3306 --databases=mytest --print

pt-table-sync --sync-to-master h=172.17.0.3,u=slave_check,p=123456,P=3306 --databases=mytest --execute 

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

4、主库执行数据恢复 ，从库中查看数据发现已经恢复。

 

上面操作是手动执行的数据检测与数据恢复，但实际工作中是不可能每天手动这样进行操作的，那么怎么做呢？

接下来我们可以写一个shell脚本来定时执行检测与恢复操作，这样就可以省掉不少麻烦。 

vi /home/pt-check-sync.sh

#!/bin/bash
NUM=`pt-table-checksum --tables=user  --databases=mytest --user=root --password='123456' --replicate=check_data.checksums --no-check-binlog-format --recursion-method dsn=t=mytest.dsns,h=172.17.0.3,u=slave_check,p=123456,P=3306 | 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=172.17.0.3,u=slave_check,p=123456,P=3306 --databases=mytest --print

    pt-table-sync --sync-to-master h=172.17.0.3,u=slave_check,p=123456,P=3306 --databases=mytest --execute 
fi

 执行sh pt-check-sync.sh文件，也可以写定时器执行

apt-get update 

apt-get install -y --no-install-recommends cron

chmod +x ./docker-entrypoint.sh

# 保存环境变量，开启crontab服务
env >> /etc/default/locale
/etc/init.d/cron start

crontab -e 

20 23 * * * /home/pt-check-sync.sh

表示每天晚上23:20运行这个脚本