MySQL-13mysql主从复制问题

2021-10-13 本文已影响0人安晓生

大家好，本片文章看一下主从复制的问题，
1. 主从复制复习
2. 主从复制数据一致性
3. 主从复制延迟问题
4. 主从复制总结

1. 主从复制复习

1.1 什么是主从复制?

MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

主要用途

1. 读写分离
2. 数据实时备份，当系统中某个节点发生故障时，可以方便的故障切换
3. 高可用HA
4. 架构扩展

随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能。

1.2 主从复制原理

MySQL主从复制涉及到三个线程，一个运行在主节点(log dump thread)，其余两个(I/O thread, SQL thread)运行在从节点

主从图.png

1,主节点 binary log dump 线程

当从节点连接主节点时，主节点会创建一个log dump 线程，用于发送bin-log的内容。在读取bin-log中的操作时，此线程会对主节点上的bin-log加锁，当读取完成，甚至在发动给从节点之前，锁会被释放。

1. 从节点I/O线程

当从节点上执行 start slave 命令之后，从节点会创建一个I/O线程用来连接主节点，请求主库中更新的bin-log。I/O线程接收到主节点binlog dump 进程发来的更新之后，保存在本地relay-log中。

1. 从节点SQL线程

SQL线程负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。对于每一个主从连接，都需要三个进程来完成。当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个binary log dump 进程，而每个从节点都有自己的I/O进程，SQL进程。从节点用两个线程将从主库拉取更新和执行分成独立的任务，这样在执行同步数据任务的时候，不会降低读操作的性能。比如，如果从节点没有运行，此时I/O进程可以很快从主节点获取更新，尽管SQL进程还没有执行。如果在SQL进程执行之前从节点服务停止，至少I/O进程已经从主节点拉取到了最新的变更并且保存在本地relay日志中，当服务再次起来之后，就可以完成数据的同步。

主从复制的步骤

1. 主库将所有的写操作记录在binlog日志中，并生成log dump线程，将binlog日志传给从库的I/O线程
2. 从库生成两个线程，一个是I/O线程，另一个是SQL线程
3. I/O线程去请求主库的binlog日志，并将binlog日志中的文件写入relay log(中继日志)中
4. SQL线程会读取relay loy中的内容，并解析成具体的操作，来实现主从的操作一致，达到最终数据一致的目的

配置主从的步骤

1. 确保从数据库与主数据库里的数据一致
2. 在主数据库里创建一个同步账户授权给从数据库使用
3. 配置主数据库(修改配置文件)与开启binlog日志
4. 配置从数据库(修改配置文件) 5. 配置从节点

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

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

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

下面介绍一种工具，不过还有其他的工具，可以自己百度。
我们可以使用percona-toolkit工具做校验，而该工具包含

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

下面我们进行安装一下，也可以自己百度找一下更方便的安装方法。
安装

yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl perl-DBI -y
yum localinstall percona-toolkit-3.2.1-1.el7.x86_64.rpm
yum list | grep percona-toolkit

pt-table-checksum --help

使用

pt-table-checksum [options] [dsn]

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

现在我们可以准备一个动作:来模拟数据不一致的问题，同时需要确保主从是配置好了的 -》思路就是创建一个test的库随便添加一个t表

create database `mytest`; create table t (
id int primary key,
name varchar(20)
);

首先配置的是主库 192.168.22.101 //根据自己的ip来。

mysql> use `mytest`;
mysql> insert  into t  values(1,6);
mysql> insert  into t  values(2,2); 
mysql> insert  into t  values(4,4); 
mysql> select  * from t;
+----+------+ 
| id | name | 
+----+------+
|  1  |  6  |
|  2  |  2  |
|  4  |  4  |
+----+------+
3 rows in set (0.00 sec)

其次是从库 192.168.22.102,此时因为主从复制的原因，在上面主库进行的配置会复制到从库。

mysql> use `mytest`;
mysql> insert  into t values(3,3);
mysql> select  * from t;
+----+------+ 
| id | name | 
+----+------+ 
|  1  |  6  |
|  2  |  2  | 
|  3  |  3  | 
|  4  |  4  | 
+----+------+ 
4 rows in set
(0.02 sec)

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

使用工具检测

注意常用的参数解释:

--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=test --tables=t --user=mytest --password=rot 
Checking if all tables can be checksummed ...
Starting checksum ...
Replica localhost.localdomain has binlog_format MIXED 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 MIXED 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 跳过检测

[root@localhost home]# pt-table-checksum --nocheck-replication-filters -- replicate=check_data.checksums --no-check-binlog-format --databases=mytest --tables=t - -user=mytest --password=rot
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED 11-11T23:27:10 0 1 3 1 1 0
TIME TABLE 0.064 mytest.t
TS :完成检查的时间。
ERRORS :检查时候发生错误和警告的数量。
DIFFS :0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定-- replicate-check-only会显示不同的信息。
ROWS :表的行数。
CHUNKS :被划分到表中的块的数目。
SKIPPED :由于错误或警告或过大，则跳过块的数目。
TIME :执行的时间。
TABLE :被检查的表名。

那些信息数据会记录在checksums表中
执行完毕后，自己可以查看表数据，这里不演示了。

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.29.103,u=mytest,p=rot,P=3306 --databases=mytest --print
DELETE FROM `mytest`.`t` WHERE `id`='3' LIMIT 1 /*percona-toolkit src_db:mytest src_tbl:t src_dsn:P=3306,h=192.168.29.102,p=...,u=mytest dst_db:mytest dst_tbl:t dst_dsn:P=3306,h=192.168.29.103,p=...,u=mytest lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:76531 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 :执行命令。

建议:

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

我们也可以把这个编辑成脚本，定期通过centos定时器定期检查, 对于我们来说我们执行在意的是通过pt- table-checksums 显示信息中的DIFFS信息.

 [root@localhost home]# pt-table-checksum --nocheck-replication-filters -- replicate=check_data.checksums --no-check-binlog-format --databases=mytest --tables=t - -user=mytest --password=rot

对于centos来说我们可以通过 awk 命令获取到 DIFFS 中的值，然后判断这个值是否不等于0;则可以判断是否一致。

[root@localhost home]# 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}'
1

下一步要做的就是编辑shell脚本 - 额外解释，所谓的sh脚本就是可以直接模拟centos执行我们在命令太执行的命令然后根据返回的结果进行相应的逻辑处理，我们可以创建一个pt-table-checksums;注意!!! windows下编辑的sh脚本在linux中执行可能会存在一定的问题，推荐可以直接在xshell中编辑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=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.22.101,u=mytest,p=rot,P=3306 -- databases=mytest --print
pt-table-sync --sync-to-master h=192.168.22.102,u=mytest,p=rot,P=3306 -- databases=mytest --execute
pt-table-sync --sync-to-master h=192.168.22.103,u=mytest,p=rot,P=3306 -- databases=mytest --print
pt-table-sync --sync-to-master h=192.168.22.103,u=mytest,p=rot,P=3306 -- databases=mytest --execute
fi

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

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

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

4. 对于主从延迟的处理

master 服务器和 slave 服务器连接时，创建 Binlog dump thread 以发送 bin log 数据:
1. 一个 Binlog dump thread 对应一个 slave 服务器;
2. Binlog dump thread 从 bin log 获取数据时会加锁，获取到数据后，立即释放锁。

当 slave 服务器收到 START_SLAVE 命令时，会创建 I/O thread 和 SQL thread:
1. I/O thread 以拉的方式，从 master 读取事件，并存储到 slave 服务器的 relay log 中;
2. SQL thread 从 relay log 中读取事件并执行;
3. slave 可以按照自己的节奏读取和更新数据，也可以随意操作复制进程(启动和停止)。

pt-heartbeat

在percona toolkit 产品中也提供了可以对于MySQL主从延时检查的工具pt-heartbeat, pt-heartbeat 的工作原理是通过使用时间戳方式在主库上更新特定表，然后再从库上读取呗更新的时间戳然后与本地系统时间对比来得出其延迟。

具体流程:

在住上创建一张hearteat表，按照一定的时间频率更新该表的数据。监控操作运行后，heartbeat表能促使主从同步
连接到从库上检查复制的时间记录，和从库的当前系统时间进行比较，得出时间的差异。注意在使用的方式就是需要在主库中创建这个表;

use test;
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 );

通过pt-heartbeat可以对于mysql中的heartbeat表每隔多久更新一次(注意这个启动操作要在主库服务器上执行)

[root@localhost home]# pt-heartbeat --user=root --ask-pass --create-table --database mytest --interval=1 --interval=1 --update --replace --daemonize
[root@localhost home]# ps -ef | grep pt-heartbeat
root 76637 1 0 23:52 ? 00:00:00 perl /usr/bin/pt-heartbeat -- user=root --ask-pass --create-table --database mytest --interval=1 --interval=1 -- update --replace --daemonize
root 76643 76367 0 23:53 pts/2 00:00:00 grep --color=auto pt-heartbeat

在主库运行监测同步延迟

[root@localhost home]# pt-heartbeat --database mytest --table=heartbeat --monitor -- user=root --password=root --master-server-id=1
0.02s [ 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的值

5. 主从延迟处理

对于从库的延时问题最为重要的就是主库与从库之间连接的网络环境，从库的写入和读这两个点 - 其次就是对于主从的架构的优化;

注意:一旦使用了主从必然是会有一定的延时问题，因此我们就需要考虑程序对于延迟的容忍度。如果是0 容忍的话建议还是不用主从了

MySQL从库产生配置

网络环境跳过，，，从库的写入主要是指insert，update，delete的语句的执行速度这些语句的执行速度我们就需要考虑MySQL的执行SQL语句的一个特点 -》对于每一个写的sql会默认开启事务并提交事务 ; 而事务是会影响到io的消耗的这和innodb_flush_log_at_trx_commit参数有关系。默认为1 我们可以尝试设置为0 或2可以提高效率，另一个就是sync_binlog

sync_binlog 配置说明:
sync_binlog”:这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下: sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者 cache满了之后才同步到磁盘。 sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。
从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。
innodb_flush_log_at_trx_commit 配置说明: 默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入(flush)硬盘，这是很费时的。特别是使用电池供电缓存(Battery backed up cache)时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使 MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统挂了时才可能丢数据。

硬件:服务器的配置要好点，

架构:

可以考虑对于一些库进行单独分离。
服务的基础架构在业务和MySQL之间加入memcache或者redis的cache层。
从库的配置要好。