由于blog各种垃圾评论太多,而且本人审核评论周期较长,所以懒得管理评论了,就把评论功能关闭,有问题可以直接qq骚扰我

mysql 5.7 延迟复制

数据库 西门飞冰 6389℃

前言

我们公司数据库这段时间由于数据量过大,导致备份的时间过长,一次全量备份的备份时间从凌晨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 延迟复制

喜欢 (0)or分享 (0)