MySQL 主从复制原理与实战

MySQL 从 3.25.15 版本开始提供数据库复制 (replication) 功能。MySQL 复制指的是将数据从一个 MySQL 主服务器 (Master) 复制到另一台或多台 MySQL 从服务器 (Slaves) 的过程,通常称为“主从复制”。该过程通过二进制日志 (BinLog) 将主数据库的 DDL 和 DML 操作传输到从服务器,然后在从服务器上重新执行这些日志,以保持主从服务器的数据同步。

在 MySQL 中,复制操作是异步执行的,Slaves 服务器无需持续保持与 Master 服务器的连接以接收数据。

MySQL 支持一台主服务器同时向多台从服务器进行复制操作,同时从服务器也可以充当其他从服务器的主服务器。如果 MySQL 主服务器的访问量较大,可以通过复制数据并在从服务器上执行查询操作来减轻主服务器的访问压力。同时,将从服务器作为主服务器的备份可以避免主服务器故障导致数据丢失的问题。

1 实现原理

MySQL 数据库复制操作大致可以分成以下几个步骤:

  1. 主服务器将数据的改变记录到二进制日志 (BinLog) 中,然后发送给从服务器;
  2. 从服务器将主服务器的 BinLog events 复制到它的中继日志 (RelayLog) 中;
  3. 从服务器重做 RelayLog 中的事件,将数据的改变与主服务器保持同步。

详细过程如下图所示:

mysql 主从示意图
mysql 主从示意图
  • 首先 Slave 上的 I/O Thread 连接 Master,并发出日志请求,Master 接收到来自 Slave 的 I/O Thread 的请求后,通过负责复制的 I/O Thread 根据请求信息读取与定制日志指定位置之后的日志信息,返回给 Slave 的 I/O Thread。返回的信息中除了包含日志内容外,还有本次返回的信息已经到达的 Master 端的 BinLog 文件名称以及文件内的偏移量。
  • Slave 的 I/O Thread 接收到信息后,将接收到的日志内容依次添加到 Slave 端的 RelayLog 文件的末尾(微观上的顺序磁盘 I/O,速度远大于 4K 纯随机写),并将读取到 Master 端的 BinLog 文件名和文件内的位置记录到 master-info 文件中。
  • 当 Slave 的 SQL Thread 检测到 RelayLog 中有新内容添加时,它会立即解析这些内容并将其转化为主服务器实际执行的可执行内容,最终在自身执行。
问题引入

详细分析以上过程,我们会发现异步复制中可能存在以下两个问题:

  1. 同步延迟:由于主从复制是异步执行的,从服务器需要等待主服务器将数据操作记录到二进制日志(BinLog)并传输到从服务器后才能执行。这可能导致从服务器的数据与主服务器存在一定的时间差,从而引起同步延迟。而且从服务器上只有一个 SQL Thread,当主服务器有大量写操作时,复制延时也可能会增大。
  2. 数据一致性:在主从复制过程中,如果主服务器发生故障或网络中断,从服务器可能会丢失部分尚未复制的数据。

为了降低数据复制过程中由于主服务器宕机带来的数据丢失,MySQL 引入了“半同步复制”机制;

为了提高从服务器复制的性能,MySQL 引入了“并行复制”机制。

2 半同步复制

为了降低数据丢失的风险,MySQL 5.5 版本引入了半同步复制机制,该模式下 MySQL 主节点在发送完 BinLog 后不会立刻提交数据,而是至少等待一个从服务器写完 RelayLog 并返回 ACK 消息后,才提交事务。

通过 rpl_semi_sync_master_wait_point 参数,我们可以控制半同步模式下主服务器在返回给会话事务成功之前提交事务的方式。该参数有两个值:

  • AFTER_COMMIT:在事务提交到存储引擎之后等待 ACK,收到 ACK 后再返回确认信息给客户端。是 5.6 版本的默认值;
  • AFTER_SYNC:发送 BinLog 后立刻等待 ACK,收到 ACK 后再将事务提交给存储引擎,并返回给客户端。是 5.7 版本的默认值,但 5.6 中无此模式。

2.1 AFTER_COMMIT

AFTER_COMMIT 即普通的半同步复制模式,主服务器在执行 Engine Commit 后开始等待 ACK。下面是该模式的时序图,主服务器等待从服务器将数据写入 RelayLog 并返回 ACK 后才进行提交确认:

after commit
after commit
  • Master 将事务的写事件传递到 Slave 后落盘到自己的 BinLog,然后执行 Engine Commit,并通知 Slave 返回 ACK,只有收到 ACK 后 Master 才将 Commit OK 结果反馈给客户端。

如此一来万一主机崩溃,所有提交的事务至少已被复制到一个从属服务器。

2.1.1 AFTER_COMMIT 的幻读问题

在 MySQL 5.6 及之前的版本开启半同步模式时,要等到 InnoDB Commit 后,才开始等待从服务器的 ACK 应答,在收到客户端 ACK 应答或者超时后,再将事务的提交状态返回给客户端的会话。但实际上,只要 InnoDB 在 RedoLog 中提交了 Commit 事件,事务就会将数据持久化并且释放相关的锁,因此其他的事务此时是可以访问到这些数据的,即使当前的事务会话仍在等待 ACK 应答。这将导致一旦主服务器崩溃,从服务器升级为新的主服务器后,原本在旧的主服务器上可以查询到的数据在从服务器上可能就查不到了(可能还没来得及复制到从服务器上),从而产生幻读。

after commit phantom read
after commit phantom read
注意
对于这种崩溃了的主服务器,不应该被重新用作数据源服务器,最好直接丢弃,因为它内部存在未被任何其他副本确认的未提交事务,恢复起来十分复杂。

2.2 AFTER_SYNC

AFTER_SYNC 是一种增强半同步机制,在该模式下事务的 Engine Commit 发生在从服务器 ACK 应答之后,从而解决了 AFTER_COMMIT 的幻读问题。下面是该模式的时序图:

after sync
after sync
  • Master 将事务的写事件传递到 Slave 后落盘到自己的 BinLog,然后通知 Slave 返回 ACK,只有收到 ACK 后 Master 才提交事务到存储引擎,最后将 Commit OK 结果反馈给客户端。

AFTER_SYNC 能够保证在主服务器崩溃的情况下,所有提交的事务至少已被复制到一个从属服务器。而且由于其收到 ACK 后才执行 Engine Commit 的特性,避免了幻读发生的可能。此外即便主服务器崩溃了,也更容易恢复。

2.3 AFTER_COMMIT 与 AFTER_SYNC 的对比

  • ACK 时间点不同:
    • AFTER_COMMIT 在 InnoDB 层 Commit Log 之后等待 ACK。
    • AFTER_SYNC 在 MySQL Server 层 Write BinLog 后等待 ACK。
  • 数据一致性区别:
    • AFTER_COMMIT 意味着在 Master 节点上,这个刚刚提交的事务对数据库的修改,对其他事物是可见的。因此如果在等待 ACK 的时候崩溃了,就会导致其他事务出现幻读,相应的数据也会丢失。
    • AFTER_SYNC 在收到 ACK 之前都不会执行 Engine Commit,因此对其他事务是不可见的,不会出现幻读,不会丢失数据,一致性更强。

2.4 半同步复制降级

当半同步复制发生超时时(由 rpl_semi_sync_master_timeout 参数控制,单位是毫秒,默认为 10000,即 10s),会暂时关闭半同步复制,转而使用常规异步复制。

master dump 线程发送完一个事务的所有事件之后,如果在 rpl_semi_sync_master_timeout 内,收到了从服务器的响应,则主从又重新恢复为半同步复制。

3 并行复制

MySQL 的主从复制延迟一直倍受开发者关注,MySQL 从 5.6 版本开始追加了并行复制功能,目的就是为了改善复制延迟问题,并行复制称为 Enhanced multi-threaded slave(简称 MTS)。

在 MySQL 5.6 之前的版本中,从服务器有两个线程:I/O Thread 和 SQL Thread,从服务器首先通过 I/O Thread 拉取主服务器的 BinLog 并落盘到 RelayLog,而后 SQL Thread 再重演 RelayLog 完成数据同步。其中拉取 BinLog 这一步就是网络 I/O + 顺序磁盘 I/O,不会有性能瓶颈。但是单线程的 SQL Thread Relay 操作在 Master 的 TPS 比较高的情况下,会出现严重的性能瓶颈,进而导致主从同步延迟。

MySQL 为了解决这个问题,将 SQL Thread 演化成了多个 Worker 的形式,在 Slave 端并行应用 Relay Log 中的事务,从而提升 Relay Log 的应用速度,降低复制延迟,如下图所示:

relay worker
relay worker

并行复制的条件是同时执行的 SQL 不能存在锁争用,因此并行复制演进的过程就是在不产生锁冲突的情况下尽可能的细化并发粒度的过程。

3.1 MySQL 5.6 基于 Schema 的并行复制

在 MySQL 5.6 版本中,并行重放 Relay 的粒度是数据库级别的,由于不同数据库的 SQL 肯定不会修改到同一个表中的同一行内容,因此也就不会产生锁争用。

这种模式的优点就是实现起来特别简单,但缺点也很明显,并行的粒度太粗。在一些数据库均匀分布,每个数据库使用频率都差不多的情况下,这种并行复制的方法还算堪用。一旦业务的数据都集中在了一个热点库,这种并行复制就会退化为单线程复制。

3.2 MySQL 5.7 基于 Group Commit 的并行复制

MySQL 5.7 的并行复制是基于分组的,粒度相较于 5.6 版本更高。MySQL 5.7 为了实现基于 Group Commit 的并行复制,在 BinLog 中引入了两个值:last_committedsequence_number

  • sequence_number 表示当前提交事务的编号;
  • last_committed 表示事务 prepare 的时候,当前已提交的事务的最大 sequence_number

Group Commit 并行复制共有两个模式,一个是旧模式 Commit-Parent-Based SchemeWL,另一个是官方后来引入的新模式 Lock-Based SchemeWL,接下来我们详细介绍这两种模式。

3.2.1 Commit-Parent-Based SchemeWL

上边说过 BinLog 引入了两个字段 last_committedsequence_number。在 Master 中,事务在进入 “prepare” 阶段之前,会把当前已提交事务的最大 sequence_number 存入 last_committed 字段,这个字段称为事务的 commit-parent。

在事务并发的情况下,假如两个事务几乎同时进入 "prepare" 阶段,并且都顺利执行,那么说明这两个事务之间是不存在锁竞争的,而且由于进入 "prepare" 的时间几乎相同,所以这两个事务很可能得到相同的 commit-parent。因此可知:如果两个事务的 commit-parent 相同,那么这两个事务之间必然不存在锁竞争所以在 Slave 上,如果事务拥有相同的 commit-parent,那这些事务将被划分到同一组,以便进行并行 Relay。

其详细流程如下:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
P:进入 prepare 阶段之前读到 commit-parent 的时间点,可以简单理解为当前事务加锁的时间点;
C:全局计数器增加的时间点,可以简单理解为当前事务释放锁的时间点。

Trx1 ---------P---------C--------------------------------->
                        |
Trx2 ------------P------+---C----------------------------->
                        |   |
Trx3 ---------------P---+---+-----C----------------------->
                        |   |     |
Trx4 -------------------+-P-+-----+---C------------------->
                        |   |     |   |
Trx5 -------------------+---+-P---+---+---C--------------->
                        |   |     |   |   |
Trx6 -------------------+---+---P-+---+---+---C----------->
                        |   |     |   |   |   |
Trx7 -------------------+---+-----+---+---+-P-+--C-------->
                        |   |     |   |   |   |  |

上图中,Trx5 与 Trx6 的 commit-parent 均为 Trx2 的 sequence_number,因此可以并发执行,此外其他事务就不可以并发了。

但是根据时间轴,并非只有 Trx5、Trx6 之间可以并发,例如 Trx4 也持有自己的锁,且没有与 Trx5 和 Trx6 发生冲突,因此,实际上 Trx4 也可以加入并发执行的行列。这意味着按照 commit-parent 分组的模式并没有充分地提高并发性能。

3.2.2 Lock-Based SchemeWL

该模式定义了一个 Lock Interval 的概念,描述一个事务持有锁的事件间隔:当一个事务获取锁,Lock Interval 开始,当存储引擎提交释放了锁,Lock Interval 结束。

假设有两个事务 Trx1 和 Trx2,Trx1 先于 Trx 2。那么当且仅当 Trx1 与 Trx2 的 Lock Interval 有重叠时,这两个事务才可以并行 Relay,如图:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
L:Lock Interval 开始时间点
C:Lock Interval 结束时间点

# 可以并行:
Trx1 ---------L=========C--------------------------------->
Trx2 ----------------L======C----------------------------->

# 不可并行:
Trx1 ---------L=========C--------------------------------->
Trx2 ----------------------L======C----------------------->

MySQL 会给每个事务分配一个逻辑时间戳来表示 Lock Interval 释放的时间点,即 sequence_number,同时 MySQL 会将所有已经结束的 Lock Interval 的事务的最大 sequence_number 作为全局变量 max_committed_transaction。当 Lock Interval 开始时,MySQL 会把 max_committed_transaction 分配给当前事务的 last_committed 变量。如果两个事务的 Lock Interval 存在重合,那么说明这两个事务间没有锁竞争。所以对于 Slave,如果一个事务的 Lock Interval 区间与其他事务的重合,那么这些事务就可以并行 Relay。

再举一个例子:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
Trx1 ---------L=========C--------------------------------->
                        |
Trx2 ------------L==========C----------------------------->
                        |   |
Trx3 ---------------L=============C----------------------->
                        |   |     |
Trx4 -------------------+---+L===========C------------------->
                        |   |     |   |
Trx5 -------------------+---+-L===========C--------------->
                        |   |     |   |   |
Trx6 -------------------+---+---L=============C----------->
                        |   |     |   |   |   |
Trx7 -------------------+---+-----+---+---+-L====C-------->
                        |   |     |   |   |   |  |

在这种模式下:

  1. Trx1、Trx2、Trx3 可以并发执行;
  2. Trx1、Trx2 执行完毕后,Trx3、Trx4、Trx5、Trx6 又可以并发执行;
  3. 当 Trx3、Trx4、Trx5 执行完毕后,Trx6、Trx7 又可以并发执行了。

如此一来就大大提升了 Relay 的并发度。

3.3 MySQL 8.0 基于 Write-Set 的并行复制

MySQL 8.0 是基于 Write-Set 的并行复制。MySQL 会有一个集合变量来存储事务修改的记录信息(主键哈希值),所有已经提交的事务所修改的主键值经过 hash 后都会与那个变量的集合进行对比,来判断改行是否与其冲突,并以此来确定依赖关系,没有冲突即可并行,如此便实现了行级别的并发。

4 主从复制实战

4.1 准备工作

角色准备工作操作系统MySQL 版本
Masterxxx.xxx.xxx.162:33333Debian GNU/Linux 11 (bullseye) x86_645.7.37
Slavexxx.xxx.xxx.223:33333Debian GNU/Linux 10 (buster) x86_645.7.37

首先我们准备两台 VPS,分别是 Debian 11 的 162 作为 Master,和 Debian 10 的 223 作为 Slave(如果没有可以用 VMware 虚拟机代替),然后在两台 VPS 上都安装好 MySQL 5.7.37 服务端软件。

然后在主服务器跟从服务器上创建测试用的相同的数据库、数据表:

1
2
3
4
5
6
7
create database testdb char set utf8mb4;

create table testdb.test
(
    id int not null
        primary key
);

4.2 主服务器配置

  1. 首先,修改主数据库配置文件 my.cnf,开启 BinLog,并设置 server-id 为 1:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    
    [mysqld]
    ...
    server-id=1
    log-bin=/var/log/mysql/mysql-bin.log
    expire_logs_days=10
    max_binlog_size=100M
    
    # 忽略同步的表,这里设置系统表不要同步
    binlog-ignore-db=mysql
    
    # 需要同步的表,不配置默认同步所有数据库
    binlog-do-db=testdb
  2. 然后重启 MySQL Server:

    1
    
    systemctl restart mysqld.service
  3. 这里我们对 Master 设置读锁有效,确保没有数据库写操作:

    1
    2
    
    use testdb;
    flush tables with read lock;

    等将来 Slave 同步完成,再执行以下 SQL 释放锁:

    1
    
    unlock tables;
  4. 最后查询 Master 上当前 BinLog 日志名和偏移量,目的是为了在 Slave 启动后,从这个点进行数据恢复:

    1
    2
    3
    4
    5
    6
    7
    
    show master status;
    
    +----------------+--------+------------+----------------+-----------------+
    |File            |Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
    +----------------+--------+------------+----------------+-----------------+
    |mysql-bin.000012|154     |testdb      |mysql           |                 |
    +----------------+--------+------------+----------------+-----------------+

4.3 从服务器配置

  1. 首先修改从服务器的 my.cnf,增加 server-id 的值,不要与主服务器相同:

    1
    2
    3
    4
    5
    6
    
    [mysqld]
    ...
    server-id=2
    # 需要同步的表,不配置默认同步所有数据库
    binlog-do-db=testdb
    skip-slave-start

    从服务器的 my.cnf 中指定了一个 skip-slave-start 参数,这样 Slave 启动时不会立刻启动复制流程,方便我们在会话中再配置。

  2. 然后重启 MySQL Server:

    1
    
    systemctl restart mysqld.service

4.4 开始复制过程

复制过程的操作都在从服务器端进行。

  1. 首先我们利用客户端连接从服务器,打开一个会话,输入以下 SQL:

    1
    2
    3
    4
    5
    6
    7
    
    change master to
    master_host = 'xxx.xxx.xxx.162',
    master_port = 33333,
    master_user = 'mysql_test_user',
    master_password = 'ASFH89f73h24r23hs_ksdf',
    master_log_file = 'mysql-bin.000012',
    master_log_pos = 154;
    • change master to 表示设置 Master 命令;
    • master_hostmaster_portmaster_usermaster_password:设置主服务器的登录信息;
    • master_log_file:设置当前 Master 的 BinLog,就是上文查询得到的结果;
    • master_log_pos:设置当前 Master 的 BinLog 偏移量,也是上文查询得到的结果。
  2. 然后执行以下 SQL 启动复制:

    1
    
    start slave;
  3. 最后通过以下 SQL 查询复制情况:

    1
    
    show processlist;

    得到如下内容,表示 Slave 已经连上 Master,并且开始接收并执行 BinLog:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    
    [
        {
            "Id": 6,
            "User": "system user",
            "Host": "",
            "db": null,
            "Command": "Connect",
            "Time": 32,
            "State": "Waiting for master to send event",
            "Info": null
        },
        {
            "Id": 7,
            "User": "system user",
            "Host": "",
            "db": null,
            "Command": "Connect",
            "Time": 31,
            "State": "Slave has read all RelayLog; waiting for more updates",
            "Info": null
        }
    ]

    这里我们执行 show slave status 语句,如果 Slave_IO_RunningSlave_SQL_Running 状态均为 YesLast_IO_ErrnoLast_SQL_Errno 错误标志均为 0 ,则表明主从复制成功:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    
    [
        {
            "Slave_IO_State": "Waiting for master to send event",
            //...
            "Connect_Retry": 60,
            "Master_Log_File": "mysql-bin.000012",
            "Read_Master_Log_Pos": 154,
            "Relay_Log_File": "racknerd-dd6380-relay-bin.000002",
            "Relay_Log_Pos": 320,
            "Relay_Master_Log_File": "mysql-bin.000012",
            "Slave_IO_Running": "Yes",
            "Slave_SQL_Running": "Yes",
            //...
            "Last_IO_Errno": 0,
            "Last_IO_Error": "",
            "Last_SQL_Errno": 0,
            "Last_SQL_Error": "",
        }
    ]

4.5 停止主从复制

解除主从关系,只需要执行以下 SQL 即可:

1
stop slave;

4.6 扩展配置

4.6.1 增强半同步配置

  1. 首先在主服务器上安装 semisync_master 插件:

    1
    
    install plugin rpl_semi_sync_master SONAME 'semisync_master.so';
  2. 然后在从服务器上安装 semisync_slave 插件:

    1
    
    install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';
  3. 接着在主服务器上配置增强半同步复制:

    1
    2
    3
    4
    5
    6
    7
    8
    
    [mysqld]
    ...
    #半同步配置(主服务器)
    rpl_semi_sync_master_enabled=on
    #主服务器上的事务等待从服务器响应时间
    rpl_semi_sync_master_timeout=1000
    #半同步类型设置为增强型
    rpl_semi_sync_master_wait_point=AFTER_SYNC
  4. 从服务器打开增强半同步复制:

    1
    2
    3
    4
    
    [mysqld]
    ...
    #打开半同步(从服务器)
    rpl_semi_sync_slave_enabled=ON
  5. 重启主服务器和从服务器

4.6.2 并行复制配置与调优

开启并行复制只需要在从服务器端设置以下内容:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
[mysqld]
...
#多线程复制(从服务器)
#复制依赖主服务器 commit 时刻的时间戳
slave_parallel_type=LOGICAL_CLOCK
#在复制过程中,保证和主服务器事务提交顺序的一致性
slave_preserve_commit_order=ON
#执行线程数量
slave_parallel_workers=4
relay_log_recovery=ON
master_info_repository=table
relay_log_info_repository=table  

最后,我们再介绍一些属性调优内容:

  • binlog_transaction_dependency_history_size:用于控制 BinLog 文件中事务之间的依赖关系,即 last_committed 值:

    • COMMIT_ORDERE: 基于组提交机制;
    • WRITESET: 基于写集合机制;
    • WRITESET_SESSION: 基于写集合,比 WRITESET 多了一个约束,即同一个 session 中的事务 last_committed 按先后顺序递增。
  • transaction_write_set_extraction:用于控制事务的检测算法,参数值为:OFFXXHASH64MURMUR32

  • master_info_repository:开启并行复制功能后,务必将参数 master_info_repostitory 设置为 TABLE,这样性能可以有 50%~80% 的提升。这是因为并行复制开启后对于元 master.info 这个文件的更新将会大幅提升,资源的竞争也会变大。

  • slave_parallel_workers

    • 若将 slave_parallel_workers 设置为 0,MySQL 5.7 将退化为原始的单线程复制模式。
    • 若将 slave_parallel_workers 设置为 1,则 SQL 线程的功能将转变为协调器 (coordinator) 线程,但只有一个工作线程 (worker) 用于执行回放操作,依旧是单线程复制。然而,由于引入了一次额外的协调器线程,导致 slave_parallel_workers=1 的性能比设置为 0 时更差。

    因此,建议将该值设置为大于 1 的数值,以获得更好的性能。

  • slave_preserve_commit_order:MySQL 5.7 后的 MTS 可以实现更小粒度的并行复制,但需要将 slave_parallel_type 设置为 LOGICAL_CLOCK。然而,仅仅设置为 LOGICAL_CLOCK 会出问题,因为此时在从服务器 (slave) 上应用事务的顺序是无序的,与 RelayLog 中记录的事务顺序不一致。这样就无法确保数据一致性。为了保证事务按照 RelayLog 中记录的顺序进行回放,需要启用 slave_preserve_commit_order 参数。


欢迎关注我的公众号,第一时间获取文章更新:

微信公众号

相关内容