Mysql 一主二从实现主从复制以及数据一致性校验

1.一主二从实现主从复制

准备好服务器分配，以及mysql部署安装，一下是我配置好的服务器（虚拟机）

角色	IP	操作系统	mysql版本	端口	复制账号	密码
主Master	192.168.24.131	CentOS7.6.1810	mysql8.0.21	3306	slave	root
从slave1	192.168.24.133	CentOS7.6.1810	mysql8.0.21	3306	...	...
从slave2	192.168.24.134	CentOS7.6.1810	mysql8.0.21	3306	...	...

 

查看131主库是都开启binlog日志

 

为主库创建测试账号

mysql> create user `slave`@`%` identified by "root";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    24
Current database: *** NONE ***

Query OK, 0 rows affected (0.02 sec)

mysql> grant all  on *.* to `slave`@`%` with grant option;
Query OK, 0 rows affected (0.08 sec)

接下来就是开启从库133 134mysql终端

 

 

说明配置成功。。。

接下来就是设置131主库的数据库，看是否都能同步数据库。。。

 

查看133 134 是否一致 看截图

 

数据库一致，说明主从配置很ok。。接下来就是设置主库131数据

 

mysql> use mytest;
Database changed
mysql> show tables;
Empty set (0.03 sec)

mysql> create table t (
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.15 sec)

mysql> insert into t values(1,'1');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values(2,'2');
Query OK, 1 row affected (0.02 sec)

mysql> insert into t values(3,'3');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
+----+------+
3 rows in set (0.00 sec)
 

 

设置从库133数据新增（故意造成数据不一致问题）

 

134从库数据不变

 

2.主从复制数据一致性校验

在理想情况下，备库和主库的数据应该是完全一样的。但事实上备库可能发生错误并导致数据不一致。即使没有明显的错误，备库同样可能因为MySQL自身的特性导致数据不一致，例如MySQL的Bug感、网络中断、服务器崩溃，非正常关闭或者其他一些错误。

按照我们的经验来看，主备一致应该是一种规范，而不是例外，也就是说，检查你的主备库一致性应该是一个日常工作，特别是当使用备库来做备份时尤为重要，因为肯定不希望从一个已经损坏的备库里获得备份数据。

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

1. pt-table-checksum 负责检测MySQL主从数据一致性
2. pt-table-sync负责挡住从数据不一致时修复数据，让他们保存数据的一致性
3. pt-heartbeat 负责监控MySQL主从同步延迟

安装

[root@localhost ~]# yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl perl-DBI -y
[root@localhost home]# yum localinstall percona-toolkit-3.2.1-1.el7.x86_64.rpm
[root@localhost home]# yum list | grep percona-toolkit
percona-toolkit.x86_64                      3.2.1-1.el7                @/percona-toolkit-3.2.1-1.el7.x86_64
[root@localhost home]# pt-table-checksum --help

 

从上面可以看到实际上数据是不同步的，也就是主库的数据少于从库的数据

使用工具检测

注意常用的参数解释：

--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@localhost home]# pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --databases=mytest --tables=t --user=slave --password=root
Checking if all tables can be checksummed ...
Starting checksum ...
Replica localhost.localdomain 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.
Replica localhost.localdomain 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日志的模式 - 通常来说可以不用改binlog添加 --no-check-binlog-format 跳过检测

 

上面的错误信息主要是因为，检测主库与从库的binlog日志的模式 - 通常来说可以不用改binlog添加 --no-check-binlog-format 跳过检测

[root@localhost home]# pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --no-check-binlog-format --databases=mytest --tables=t --user=slave --password=root
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
11-11T23:27:10      0      1        3          1       1       0   0.064 mytest.t

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

 

 

 

3.pt-table-sync工具恢复数据

我们可以通过使用另一个工具pt-table-sync进行数据的同步

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

在主库中执行

root@localhost home]# pt-table-sync --sync-to-master h=192.168.24.133,u=slave,p=root,P=3306 --databases=mytest --tables=t --print
DELETE FROM `mytest`.`t` WHERE `id`='4' LIMIT 1 /*percona-toolkit src_db:mytest src_tbl:t src_dsn:P=3306,h=192.168.24.131,p=...,u=slave dst_db:mytest dst_tbl:t dst_dsn:P=3306,h=192.168.24.133,p=...,u=slave lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:2550 user:root host:localhost.localdomain*/;

如上的操作解释：

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

该工具先maseter的信息， 然后再是从库上的信息；参数建议

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

建议:

1. 修复数据的时候，用--print打印出来，这样就可以知道那些数据有问题
2. 修复数据之前一定要备份数据库 ； 然后再 手动执行或者 添加 --execute

 

vi /home/pt-check-sync.sh

#!/usr/bin/env bash

NUM=`pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --no-check-binlog-format --databases=mytest --tables=t --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.24.133,u=slave,p=root,P=3306 --databases=mytest --print
pt-table-sync --sync-to-master h=192.168.24.133,u=slave,p=root,P=3306 --databases=mytest --execute
pt-table-sync --sync-to-master h=192.168.24.134,u=slave,p=root,P=3306 --databases=mytest --print
pt-table-sync --sync-to-master h=192.168.24.134,u=slave,p=root,P=3306 --databases=mytest --execute
fi                   

数据不一致恢复成功啦。。。

然后可以通过编辑crontab -e 定时执行这个脚本就好

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

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