前言

我们公司数据库这段时间由于数据量过大，导致备份的时间过长，一次全量备份的备份时间从凌晨12点到了早上八九点，这个时间段备份还没有结束就会占用服务器大量的网络和磁盘io，导致出现很多慢查询，影响到用户体验。这时我们就上mysql延迟复制技术，来解决mysql备份时间过长的问题。

那么什么是延迟复制技术呢？从MySQL5.6开始支持了主从延迟复制，简单的说延迟复制就是设置一个固定的延迟时间，比如1个小时，让从库落后主库一个小时。用来在数据库误操作后，快速的恢复数据。例如，可以设定某一个从库和主库的更新延迟1小时，这样主库数据出问题以后，1个小时以内发现，可以对这个从库进行无害恢复处理，使之依然是正确的完整的数据，省去了数据恢复占用的时间，用户体验有所增加。

环境搭建

环境说明

本次测试一共用到两台服务器，一台主库一台从库。

Mysql 版本为mysql 5.7

操作系统版本为centos 7.2

mysql5.7 安装

无特殊需求yum安装即可

wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
rpm -ivh mysql57-community-release-el7-11.noarch.rpm
yum install mysql-community-server
systemctl start mysqld

mysql 初始化

mysql安装完成之后，在/var/log/mysqld.log文件中给root生成了一个默认密码。通过下面的方式找到root默认密码，然后登录mysql进行修改：

(1) 查询默认密码

# grep 'temporary password' /var/log/mysqld.log
2018-03-27T07:23:21.010696Z 1 [Note] A temporary password is generated for root@localhost: eIgm0HUjr#Mj

(2) 修改本地密码

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!'; 

注意：mysql5.7默认安装了密码安全检查插件（validate_password），默认密码检查策略要求密码必须包含：大小写字母、数字和特殊符号，并且长度不能少于8位。否则会报错。

mysql 主从复制配置

在配置mysql 延迟复制之前，需要先配置mysql主从复制，下面我们就配置一个基于gtid的mysql主从复制。

详细的mysql 主从复制原理，可以查询我在51cto博客写的文章，有详细的图文说明：Mariadb 主从复制

(1) 主库配置文件修改

[root@c7-node1 ~]# vim /etc/my.cnf
# binlog 和 server id配置
server-id=1
log-bin=mysql-bin
binlog-do-db=test
binlog-ignore-db=mysql

# gtid 配置
gtid_mode=ON
enforce-gtid-consistency=true

(2) 从库配置文件修改

[root@c7-node2 ~]# vim /etc/my.cnf
server-id=2

# gtid 配置
gtid_mode=ON
enforce-gtid-consistency=true

# 并行复制配置
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE

# 中继日志配置
relay_log_info_repository=TABLE
relay_log_recovery=ON

（3）配置完成之后重启服务，使配置文件生效

# systemctl restart mysqld

（4）主库配置复制账号

mysql> grant replication slave on *.* to repl@'%' identified by '1234+asDF';  
mysql> flush privileges;

（5）查看master 状态

mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000001 |      437 | test         | mysql            | ba46e4ba-318f-11e8-9124-000c290342b7:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

（6）开启主从复制

mysql> change master to master_host='192.168.28.71', master_user='repl', master_password='1234+asDF', master_log_file='mysql-bin.000001', master_log_pos=437;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.28.71
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 589
               Relay_Log_File: c7-node2-relay-bin.000002
                Relay_Log_Pos: 472
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

（7）验证

master操作，创建一个test数据库，和usertb表，并插入一行数据

mysql> create database test;
mysql> use test;
mysql> create table usertb ( username varchar(20) not null, password varchar(20) not null);
mysql> insert into usertb values ('user1','111');
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
+----------+----------+

slave操作，查询数据正常同步过来

mysql> select * from test.usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
+----------+----------+
1 row in set (0.00 sec)

因为我在master上执行了三个写入动作：创建database，创建table，往table里插入数据，所以可以看到gtid_executed这里有三条记录

mysql> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| ba46e4ba-318f-11e8-9124-000c290342b7 |              2 |            2 |
| ba46e4ba-318f-11e8-9124-000c290342b7 |              3 |            3 |
| ba46e4ba-318f-11e8-9124-000c290342b7 |              4 |            4 |
+--------------------------------------+----------------+--------------+
3 rows in set (0.00 sec)

延迟复制配置

延迟复制配置，通过设置slave上的 MASTER TO MASTER_DELAY参数实现。

CHANGE MASTER TO MASTER_DELAY = N；

N为多少秒，该语句设置从数据库延时N秒后，再与主数据库进行数据同步复制。

由于是测试，这里配置延迟主库一分钟，至于生产配置多长时间，就需要看你们公司的实际情况了。

mysql> stop slave;
mysql> change master to MASTER_DELAY = 60;
mysql> start slave;

测试，在主库插入如下一条数据

mysql> insert into usertb values ('user2','222');

数据插入后，在主节点可以很快查询到，但是从节点需要60秒后才会查询到，如果你配置完成后的效果是这样的，那就说明mysql5.7延迟复制配置成功了。

mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
| user2    | 222      |
+----------+----------+
2 rows in set (0.01 sec)

延迟复制原理解析

mysql的延迟复制实际上影响的只是SQL线程将数据应用到从数据库，而I/O线程早已经把主库更新到数据写入到了从库的中继日志中，因此，在延迟复制期间即使主库down掉了，从库到了延迟复制的时间，依然会把数据更新到和主库down机时一致。

转载请注明：西门飞冰的博客 » mysql 5.7 延迟复制