前言

在搭建主从的时候，默认是一个异步的过程，所以难免出现数据延迟。

虽然用同步复制（参考下文链接），可以解决数据延迟问题，但是牺牲了一些性能，并不利于生产环境的需求。

所以，对于主从复制的属于不一致问题，我们尽可能的使用高可用的方法和方案，去做到一个更高的方案。

下面介绍一款工具：

Percona Toolkit 简称 pt 工具

参考文献

Percona Toolkit安装：https://blog.mailjob.net/posts/1022633166.html

主从原理（同步复制）：https://blog.mailjob.net/posts/3006260634.html

优秀博文：https://www.cnblogs.com/kevingrace/p/6261091.html

mysql 主从存在的一些问题

异步同步复制延迟
MySQL的Bug感
网络中断
服务器崩溃
非正常关闭
等其他一些错误。

PT 工具校验

接下来我用以下3个工具做数据校验

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

在mysql主从实战从，我搭建了一组主从，如下

容器名称 版本 IP 端口 root账号密码 slave账号密码
mysql1（主） 5.7 172.17.0.2 33061->3306 root root slave slave
mysql2（从） 5.7 172.17.0.3 33062->3306 root root

现在我在从库（mysql2）中添加一些数据，使其形成数据不一致

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `pt_test` (
  `pt_id` int(11) NOT NULL AUTO_INCREMENT,
  `pt_name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`pt_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of pt_test
-- ----------------------------
INSERT INTO `pt_test` VALUES ('1', 'qwer');
INSERT INTO `pt_test` VALUES ('2', 'zxcvbn');
INSERT INTO `pt_test` VALUES ('3', '159852');

这里我添加的是从库，如果是主库的话，数据会被主从同步过去，没法进行接下来的演示了

所以当前的数据就是不一致的了，从库的数据多于主库

PT 检测数据不一致

在 mysql 主从实战搭建中，我曾经创建了一个 blog_db 数据库，然后里面创建了一个 user 表

我现在在 slave库（从）中添加一条数据，这样就会导致主从不一致，便于接下来的测试

1
INSERT INTO `user` VALUES ('3', '9');

在主库服务器执行命令查验

1
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums --recursion-method=hosts --databases=blog_db h=127.0.0.1,u=root,p=root,P=33061

返回结果如下

1
2
3
4
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
02-19T01:47:05      0      1        2          1       1       0   0.040 blog_db.user

返回参数说明

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

信息数据会记录在check_data表中

1
2
3
4
5
6
7
8
9
mysql> use check_data;

mysql> show tables;
+----------------------+
| Tables_in_check_data |
+----------------------+
| checksums            |
+----------------------+
1 row in set (0.00 sec)

问题：

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

这个是由于，master库（主）找不到slave库（从）导致的问题。在主库查看从库的 hosts 信息发现是空的：

1
show slave hosts;

image-20210219000953186

a、把slave库（从）的账户连接权限对主库开放

我这里是图方便，授权了 root 账户，实际搭建中确不能这么做，存在安全隐患

建议在，搭建主从的时候，开发和主授权给从，同样的账号和密码，然后再使用 pt 工具中，就可以使用这个账号和密码进行数据检查和同步了

1
2
mysql> GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'root'@'172.17.0.2' IDENTIFIED BY 'root';
mysql> flush privileges;

b、更改slave库（从）的配置文件 my.cnf ，添加从的ip和端口信息

1
2
3
[mysqld]
report_host=IP_INFO
report_port=3306

再次在 master库（主）查看从库的 hosts 信息发现ok了

PT 恢复数据

在主库服务器执行以下命令查验

1
pt-table-sync --replicate=check_data.checksums h=127.0.0.1,u=root,p=root,P=33061 h=127.0.0.1,u=root,p=root,P=33062 --print

先master的ip，用户，密码，然后是slave的ip，用户，密码

–sync-to-master ：指定一个DSN，即从的IP，他会通过show processlist或show slave status 去自动的找主。
–print ：打印，但不执行命令。
–execute ：执行命令。

返回结果

1
DELETE FROM `blog_db`.`user` WHERE `id`='3' LIMIT 1 /*percona-toolkit src_db:blog_db src_tbl:user src_dsn:P=33061,h=127.0.0.1,p=...,u=root dst_db:blog_db dst_tbl:user dst_dsn:P=3306,h=172.17.0.3,p=...,u=root lock:1 transaction:1 changing_src:check_data.checksums replicate:check_data.checksums bidirectional:0 pid:27104 user:root host:VM-0-15-centos*/;

数据恢复同步命令

1
pt-table-sync --replicate=check_data.checksums h=127.0.0.1,u=root,p=root,P=33061 h=127.0.0.1,u=root,p=root,P=33062 --execute

再次查看，是否存在问题，结果ok

image-20210219020225574

pt-heartbeat 监控 mysql 延迟

对于MySQL数据库主从复制延迟的监控，可以借助percona的有力武器 pt-heartbeat 来实现。
pt-heartbeat的工作原理通过使用时间戳方式在主库上更新特定表，然后在从库上读取被更新的时间戳然后与本地系统时间对比来得出其延迟。

具体流程：
1）在主库上创建一张heartbeat表，按照一定的时间频率更新该表的字段（把时间更新进去）。监控操作运行后，heartbeat 表能促使主从同步！
2）连接到从库上检查复制的时间记录，和从库的当前系统时间进行比较，得出时间的差异。

master库（主）上创建一个 hearbeat 表：

1
2
3
4
5
6
7
8
9
use check_data;
CREATE TABLE heartbeat (
    ts VARCHAR (26) NOT NULL,
    server_id INT UNSIGNED NOT NULL PRIMARY KEY,
    file VARCHAR (255) DEFAULT NULL, -- SHOW MASTER STATUS
    position bigint unsigned DEFAULT NULL, -- SHOW MASTER STATUS
    relay_master_log_file varchar(255) DEFAULT NULL, -- SHOW SLAVE STATUS
    exec_master_log_pos bigint unsigned DEFAULT NULL -- SHOW SLAVE STATUS
);

更新主库（master）上的 heartbeat (注意这个启动操作要在主库服务器上执行)

--interval=1 表示 1秒钟 更新一次

1
2
3
4
5
6
7
8
9
10
11
# 命令执行
[root@VM-0-15-centos src]# pt-heartbeat --ask-pass --user=root --host=127.0.0.1 --port=33061 --create-table --database blog_db --interval=1 --interval=1 --update --replace --daemonize
Enter password: 

# Tips：
我这里检测的库只有 --database blog_db ，建议是生产环境中不要加入这个。这样可以监测全部数据库

# 查看是否启动
[root@VM-0-15-centos src]# ps -ef|grep pt-heartbeat
root      6306     1  0 03:01 ?        00:00:00 perl /usr/bin/pt-heartbeat --ask-pass --user=root --host=127.0.0.1 --port=33061 --create-table --database blog_db --interval=1 --interval=1 --update --replace --daemonize
root      6667  5386  0 03:02 pts/0    00:00:00 grep --color=auto pt-heartbeat

查看监测结果

1
2
3
4
5
[root@VM-0-15-centos src]# pt-heartbeat --database blog_db --table=heartbeat --monitor --host=127.0.0.1 --port=33061 --user=root --password=root --master-server-id=1
0.00s [  0.00s,  0.00s,  0.00s ]
0.02s [  0.00s,  0.00s,  0.00s ]
0.00s [  0.00s,  0.00s,  0.00s ]
0.00s [  0.00s,  0.00s,  0.00s ]

这其中 0.02s 表示延迟了 ，没有延迟是为0 而 [ 0.00s, 0.00s, 0.00s ] 则表示1m,5m,15m的平均值，

而这其中中需要注意的是 --master-server-id 为主服务器的服务id就是在 my.cnf 中配置的 server_id 的值

其他问题

如果想把这个输出结果加入自动化监控，那么可以使用如下命令使监控输出写到文件，然后使用脚本定期过滤文件中的最大值作为预警即可：
注意–log选项必须在有–daemonize参数的时候才会打印到文件中，且这个文件的路径最好在/tmp下，否则可能因为权限问题无法创建

1
2
3
[root@master-server ~]# pt-heartbeat -D huanqiu --table=heartbeat --monitor --host=192.168.1.102 --user=root --password=123456 --log=/opt/master-slave.txt --daemonize
[root@master-server ~]# tail -f /opt/master-slave.txt       //可以测试，在主库上更新数据时，从库上是否及时同步，如不同步，可以在这里看到监控的延迟数据
0.00s [ 0.00s, 0.00s, 0.00s ]

下面是编写的主从同步延迟监控脚本，就是定期过滤–log文件中最大值（此脚本运行的前提是：启动更新主库heartbeat命令以及带上–log的同步延迟检测命令）。如果发生延迟，发送报警邮件

1
[root@master-server ~]# cat /root/check-slave-monit.sh  
1
2
3
4
5
6
7
8
9
10
#!/bin/bash
cat /opt/master-slave.txt > /opt/master_slave.txt
echo > /opt/master-slave.txt
max_time=`cat /opt/master_slave.txt |grep -v '^$' |awk '{print $1}' |sort -k1nr |head -1`
NUM=$(echo "$max_time"|cut -d"s" -f1)
if [ $NUM == "0.00" ];then
   echo "Mysql主从数据一致"
else
   /usr/local/bin/sendEmail -f ops@163.com -t wang@163.com -s smtp.email.cn -u "Mysql主从同步延迟" -o message-content-type=html -o message-charset=utf8 -xu ops@huanqiu.cn -xp WEE78@12l$ -m "Mysql主从数据同步有延迟"
fi

最后总结：
通过pt-heartbeart工具可以很好的弥补默认主从延迟的问题，但需要搞清楚该工具的原理。
默认的Seconds_Behind_Master值是通过将服务器当前的时间戳与二进制日志中的事件时间戳相对比得到的，所以只有在执行事件时才能报告延时。备库复制线程没有运行，也会报延迟null。
还有一种情况：大事务，一个事务更新数据长达一个小时，最后提交。这条更新将比它实际发生时间要晚一个小时才记录到二进制日志中。当备库执行这条语句时，会临时地报告备库延迟为一个小时，执行完后又很快变成0

便捷管理

1、使用 shell 进行定时查验和同步数据

要做的是：创建一个 shell 脚本，定时的去检查数据的一致性，如果发现延迟问题，自动的存储日志和同步数据。

当然，你也可以做到对于延迟的比较厉害的从库进行 linux email 通知运维人员

1
2
3
4
5
6
7
8
9
10
11
#!/usr/bin/env bash
NUM=`pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --no-check-binlog-format --databases=mytest --tables=t --user=mytest --password=rot | 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.29.103,u=mytest,p=rot,P=3306 --databases=mytest --print
pt-table-sync --sync-to-master h=192.168.29.103,u=mytest,p=rot,P=3306 --databases=mytest --execute
pt-table-sync --sync-to-master h=192.168.29.104,u=mytest,p=rot,P=3306 --databases=mytest --print
pt-table-sync --sync-to-master h=192.168.29.104,u=mytest,p=rot,P=3306 --databases=mytest --execute
fi

主从不一致其他解决方案

1、减少锁竞争

如果查询导致大量的表锁定，需要考虑重构查询语句，尽量避免过多的锁。

2、负载均衡

搭建多少slave，并且使用lvs或nginx进行查询负载均衡，可以减少每个slave执行查询的次数和时间，从而将更多的时间用于去处理主从同步。

3、salve较高的机器配置

4、slave调整参数

为了保障较高的数据安全性，配置sync_binlog=1，innodb_flush_log_at_trx_commit=1等设置。而Slave可以关闭binlog，innodb_flush_log_at_trx_commit也可以设置为0来提高sql的执行效率（这两个参数很管用）

5、并行复制

即将单线程的复制改成多线程复制。

从库有两个线程与复制相关：io_thread 负责从主库拿binlog并写到relaylog， sql_thread 负责读relaylog并执行。

多线程的思路就是把sql_thread 变成分发线程，然后由一组worker_thread来负责执行。

几乎所有的并行复制都是这个思路，有不同的，便是sql_thread 的分发策略。

MySQL5.7的真正并行复制 enhanced multi-threaded slave（MTS）很好的解决了主从同步复制的延迟问题。