Mysql进阶之主从复制、基于gtid的主从复制、半同步复制、组复制

小鱼儿 2021-11-16 12:44 429阅读 0赞

**前言：**

Mysql是现在普遍使用的数据库，但是如果宕机了必然会造成数据丢失。为了保证mysql数据库的可靠性。就要会一些提高可靠性的技术。因此，一般来说都是通过 主从复制（Master-Slave）的方式来同步数据提高可靠性，再通过读写分离（MySQL-Proxy）来提升数据库的并发负载能力。

本文我们将会详细给大家介绍在Redhat7环境下Mysql的主从复制以及基于gtid的主从复制、半同步复制、组复制，实现数据同步！！！

### 一、主从复制 ###

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

**1.主要用途：  
1.1、读写分离**

在开发工作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。  
**1.2、数据实时备份**，当系统中某个节点发生故障时，可以方便的故障切换  
**1.3、高可用HA**  
**1.4、架构扩展**

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

**2.主从复制的原理**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70][]

**2.1****主节点 binary log dump 线程**

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

**2.2 从节点I/O线程**

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

SQL线程负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。  
PS：

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

要实施复制，首先必须打开Master 端的binary log（bin-log）功能，否则无法实现。  
**2.4 主从复制的主要步骤：**

整个复制过程实际上就是Slave 从Master 端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。如下图所示：

**复制的基本过程如下：**

从节点上的I/O 进程连接主节点，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；  
    主节点接收到来自从节点的I/O请求后，通过负责复制的I/O进程根据请求信息读取指定日志指定位置之后的日志信息，返回给从节点。返回信息中除了日志所包含的信息之外，还包括本次返回的信息的bin-log file 的以及bin-log position；从节点的I/O进程接收到内容后，将接收到的日志内容更新到本机的relay log中，并将读取到的binary log文件名和位置保存到master-info 文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log 的哪个位置开始往后的日志内容，请发给我”；  
    Slave 的 SQL线程检测到relay-log 中新增加了内容后，会将relay-log的内容解析成在祝节点上实际执行过的操作，并在本数据库中执行。

三、异步复制（主从复制、以及基于gtid的主从复制）

MySQL 主从复制默认是异步的模式。MySQL增删改操作会全部记录在binary log中，当slave节点连接master时，会主动从master处获取最新的bin log文件。并把bin log中的sql relay。

异步模式如下图所示，这种模式下，主节点不会主动push bin log到从节点，这样有可能导致failover的情况下，也许从节点没有即时地将最新的bin log同步到本地。

## 实验环境： ##

<table> 
 <thead> 
  <tr> 
   <th>server1</th> 
   <th>172.25.60.1</th> 
   <th>master</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>server2</td> 
   <td>172.25.60.2</td> 
   <td>slaver</td> 
  </tr> 
 </tbody> 
</table>

异步：速度快，对主无影响（但当网络卡顿，从可能接受不到某些数据）

**3.1mysql的主从复制**

**3.1.1**获得资源，解压（安装rpm包，不用编译）基础设置

tar xf mysql-5.7.24-1.el7.x86\_64.rpm-bundle.tar

yum install -y mysql-community-client-5.7.24-1.el7.x86\_64.rpm mysql-community-common-5.7.24-1.el7.x86\_64.rpm mysql-community-libs-5.7.24-1.el7.x86\_64.rpm mysql-community-libs-compat-5.7.24-1.el7.x86\_64.rpm mysql-community-server-5.7.24-1.el7.x86\_64.rpm

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 1][]

**3.1.2开启log-bin日志**

vim /etc/my.cnf
    29 log-bin=mysql-bin
    30 server-id=1

**3.1.3启动mysql，并安全初始化**

systemctl start mysqld

cat /var/log/mysqld.log | grep password \#\#查看mysql生成的随机密码

mysql\_secure\_installation \#\#初始化修改高级密码

mysql -uroot -p

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 2][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 3][]

**3.1.4 server2上安装mysql  步骤同上**

my.cnf不同：添加server-id=2即可

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 4][]

**3.1.5 server1  mysql中写入**

CREATE USER 'repl'@'172.25.60.%' IDENTIFIED BY 'Van+128ff';

GRANT REPLICATION SLAVE ON \*.\* TO 'repl'@'172.25.60.%';

flush privileges;

show master status;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 5][]

server2 mysql中写入

CHANGE MASTER TO
        -> MASTER_HOST='172.25.60.1',
        -> MASTER_USER='repl',
        -> MASTER_PASSWORD='Van+128ff',
        -> MASTER_LOG_FILE='mysql-bin.000002',
        -> MASTER_LOG_POS=1323;
    
    start slave;
    show slave status\G;
    
    	Slave_IO_Running: Yes   ##表示同步成功
    	Slave_SQL_Running: Yes

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 6][]

**3.1.6 测试**

在master(server1)上创建表：

create database westos;
    
    use westos;
    
    create table usertb(
        -> username varchar(10) not null,
        -> password varchar(20) not null);
    
    desc usertb;
    
    insert into usertb values ('user1','123');
    
    select * from usertb;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 7][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 8][]

server2 查看数据库，发现更新同步  
show databases;

select \* from westos.usertb;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 9][]

**3.2 mysql基于gtid的主从复制**

3.2.1、将master和slave的mysql关闭，修改配置文件

**master：**

systemctl stop mysqld
    vim /etc/my.cnf	#更改主的配置文件，启动mysql
    29 log-bin=mysql-bin
    30 server-id=1
    31 gtid_mode=ON
    32 enforce-gtid-consistency=true
    
    systemctl start mysqld

**slave****:**

systemctl stop mysqld
    vim /etc/my.cnf
    29 server-id=2
    30 gtid_mode=ON
    31 enforce-gtid-consistency=true
    
    systemctl start mysqld

3.2.2在从库端进入mysql，关闭slave，进行gtid授权

mysql> stop slave;
    Query OK, 0 rows affected (0.32 sec)
    
    mysql> CHANGE MASTER TO
        -> MASTER_HOST = '172.25.60.1',
        -> MASTER_USER = 'repl',
        -> MASTER_PASSWORD = 'Van+128ff',
        -> MASTER_AUTO_POSITION = 1;
    Query OK, 0 rows affected, 2 warnings (0.16 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.40 sec)
    
    mysql> show slave status\G
    
    #IO和SQL打开即授权成功
                Slave_IO_Running: Yes
                Slave_SQL_Running: Yes

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 10][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 11][]

3.2.3 测试

在主库重新插入信息：

insert into usertb values ('user2','123');

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 12][]

在从端测试 结果同步

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 13][]

server2中同步的源uuid  与 server1 的uuid 相同 证明是及与gtid的同步复制

server1 的uuid

![20190730165456181.png][]

server2 同步mysql的源uuid

![20190730165518100.png][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70]: /images/20211116/c6e2245c52e14ec8aab8d60d3b0f766d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 1]: /images/20211116/5a2a30f80720437bb6f6fe029e87c39e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 2]: /images/20211116/9006437ffe04471b94ae95c55bc8ec3b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 3]: /images/20211116/c26a41d1bfc448bda00f5a4e264b022c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 4]: /images/20211116/2b028a6ab6134697982663830f432002.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 5]: /images/20211116/a0746a38b79f48d281dc524e5993ed61.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 6]: /images/20211116/2cfc08d233134689b5d769772b5f0b8b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 7]: /images/20211116/db30ffea46654f54ae5a7ef4df98e597.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 8]: /images/20211116/be5390caa91843b08d886afb508d92fb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 9]: /images/20211116/65d4b74a26d44693b9565c9c4f84ed39.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 10]: /images/20211116/dea6e7c39e2d4d7a8100ee761608d6fd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 11]: /images/20211116/0a27ca1471f1463bb855a5de5b6c452a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 12]: /images/20211116/64bfbe18c8064510b31e360c6d086962.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 13]: /images/20211116/314d3efbd387460796f750ce6ff9c967.png
[20190730165456181.png]: /images/20211116/15bfb193104c4595b371d9da7e1f8383.png
[20190730165518100.png]: /images/20211116/1f1e36ca550a48eaa38127aea0e109b0.png