Mysql集群篇：主从复制

type

status

date

slug

summary

1、简介

1.1 主从复制的过程

数据库的主从复制，是指通过配置把数据（指新写入的数据，旧的数据不会复制）从主数据库复制到从数据库，使主从两个数据库的数据保持一致性。

Mysql 的主从复制过程：从一个 Mysql 服务器（Master）将数据通过日志的方式经过网络传送到另一台或多台 Mysql 服务器（Slave），然后在Slave 上重放（replay 或 redo）传送过来的日志，以达到和 Master 数据同步的目的。

整个复制流程基本如下：

Master 开启二进制日志（binlog）。

Slave 上执行 sart slave 命令开始进行主从复制。此时 Slave 上的 I/O 进程连接主节点，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容。

Master 接收到来自 Slave 的 I/O 请求后，会为每一个 Slave 创建一个 Dump 线程。Dump 线程根据请求信息读取指定 Binlog 文件指定位置之后的日志信息，返回给 Slave。返回信息中除了日志所包含的信息之外，还包括本次返回的信息的 Binlog file 以及 Binlog position（Binlog 下一个数据读取位置）。

Slave 的 I/O 进程接收到主节点发送过来的日志内容、日志文件及位置点后，将接收到的日志内容更新到本机的 relay log 文件（ mysql-relay-bin.xxx）的最末端，并将读取到的 Binlog 文件名和位置保存到master-info 文件中，以便在下一次读取的时候能够清楚的告诉 Master：我需要从哪个 Binlog 的哪个位置开始往后的日志内容，请发给我。

Slave 的 SQL 线程检测到 relay log 中新增加了内容后，会将 relay log 的内容解析成在能够执行 SQL 语句，然后在本数据库中按照解析出来的顺序执行，并在 relay log.info 中记录当前应用中继日志的文件名和位置点。

由此可见在 Slave 上有两种线程，分别是 IO 线程和 SQL 线程：
IO 线程：用于连接 Master，监控和接受 Master 的 Binlog。当 IO 线程接收到 Master 的 Dump 线程发出 Binlog 更新的通知后，IO 线程就将其复制过来并写入到自己的中继日志（ relay log）文件中。
SQL线程：用于监控、读取并重放 relay log 中的日志，将数据写入到自己的数据库中。

1.2 主从同步的策略

从数据同步方式的角度考虑，MySQL 支持 4 种不同的同步方式：

同步（synchronous）

半同步（semisynchronous）

异步（asynchronous）

延迟（delayed）

1.2.1 同步复制

客户端发送DDL/DML语句给 Master，Master 执行完毕后还需要等待所有的 Slave 都写完了 relay log 才认为此次DDL/DML成功，然后才会返回成功信息给客户端。同步复制的问题是 Master 必须等待，所以延迟较大，在 MySQL 中不推荐使用这种复制方式。

1.2.2 半同步复制

客户端发送 DDL/DML 语句给 Master，Master 执行完毕后等待至少一个 Slave 写完 relay log 并返回确认信息给 Master，Master 才认为此次 DDL/DML 语句是成功的，然后才会发送成功信息给客户端。半同步复制只需等待一个 Slave 的回应，且等待的超时时间可以设置，超时后会自动降级为异步复制，所以在局域网内(网络延迟很小)使用半同步复制是可行的。

1.2.3 异步复制

客户端发送 DDL/DM L语句给 Master，Master 执行完毕立即返回成功信息给客户端，而不管 Slave 是否已经开始复制。这样的复制方式有可能造成数据丢失。当 Master 写完了 Binlog，而 Slave 还没有开始复制或者复制还没完成时，Slave 和 Master 上的数据暂时不一致。此时 Master 突然宕机，如果把 Slave 提升为新的 Master，那么整个数据库就永久丢失这部分数据。

1.2.4 延迟复制

Slave 落后于指定的时间从 Master 同步数据，生产几乎不会使用，了解即可。

2、传统复制和基于GTID复制

Mysql 主从数据同步方式有传统复制和基于 GTID 复制两种方式：

传统复制：即基于二进制文件（Binlog）和位置（Position）的复制。Slave 在同步 Master 的数据前，必须要从 Master上找到指定的 Binlog 文件和 Position 位置，并保证在该 Position 前的数据和 Master 完全一致。特别是在主从切换或者数据库重启的时候，需要手动准确找到 Binlog 和 Position 的位置，如果不满足这样的一致性条件，主从复制很容易出错。

基于 GTID 复制：GTID(Global Transaction ID)是 MySQL5.6 引入的功能，用来标志全局事务 ID。在主库提交的事务会被分配 GTID，事务在从库被应用时 GTID 不变，因此从库可以跟踪和识别主库的GTID。在使用 GTID 复制时或者故障转移切换时，不需要手动指定 Binlog 文件和 Position 位置，从库会自动根据 GTID 来定位到对应的 Binlog 文件和 Position 位置，并且会过滤掉重复和异常的 GTID。

传统复制和基于 GTID 的复制对比：

特性	基于 Binlog 位置（File/Position）的复制	基于 GTID 的复制
标识方法	使用二进制日志文件名和偏移量（`MASTER_LOG_FILE`, `MASTER_LOG_POS`）	使用全局事务ID（GTID）
故障切换	复杂且易错：需要在新源库上精确找到正确的日志文件和位置。	简单可靠：副本自动根据已有的 GTID 集合从新源库获取缺失的事务。
一致性	容易因指定错误的位置而导致数据不一致或重复。	强一致性：GTID 的幂等性保证了数据不会重复执行。
自动化	手动管理，需要人工干预。	高度自动化：复制拓扑自动管理事务的传输和应用。
依赖项	依赖于具体的文件路径和位置。	依赖于事务本身的全局唯一标识。

2.1 GTID是什么

GTID（Global Transaction Identifier） 是 MySQL 5.6 版本引入的一个强大特性。它为每一个在源库上提交的事务分配一个全局唯一的标识符。一个 GTID 的格式是：

uuid：服务器 ID，每个 MySQL 服务器唯一，在 MySQL 第一次启动的时候自动生成并记录在$datadir/auto.cnf中。可以使用show variables like "%uuid%"进行查看。

transaction_id：事务 ID，唯一标记某 MySQL 服务器上执行的某个事务。事务号从 1 开始，每提交一个事务，事务号加 1。

例如：

5ad9cb8e-2092-11e7-ac95-000c29bf823d:1-6，表示 uuid 为 5ad9cb8e-2092-11e7-ac95-000c29bf823d 的 Mysql 服务器上执行了事务 ID 为 1 到 6 的事务。

基于 GTID 复制的优势：

全局唯一标识：GTID 由服务器的 UUID 和一个递增的事务 ID 组成（server_uuid:transaction_id），确保了每个事务在复制拓扑中的唯一性。

保证主从同步的准确性：基于 GTID 的复制不用像传统复制那样保证 Binlog 的坐标准确，GTID 具备自动去重的功能，可以保证在一个 MySQL 服务器上，同一个 GTID 对应的事务只会被应用一次，没有执行过的 GTID 事务总是会被执行，这有效防止了数据重复或混乱。原理是 MySQL 服务器会维护一个已执行过的 GTID 集合（gtid_executed）。在从库应用事务之前，会检查该事务的 GTID 是否已经存在于 gtid_executed 集合中。如果发现当前要应用的事务的 GTID 已经存在于 gtid_executed 集合中，从库的 SQL 线程会自动跳过这个事务，不再执行它。这保证了同一个事务不会被重复应用。

简化故障转移：在发生主从切换或者数据库重启的时候不需要在从库上手动更新主库的 Binlog 信息。当复制重新连接时，从库会通过 GTID 自动定位（MASTER_AUTO_POSITION=1）告诉主库自己已经执行了哪些GTID。主库随后只会发送从库缺失的那些事务，从而避免了重复传输和应用。

2.2 GTID的相关变量

我们可以执行下面命令查看 GTID 的相关变量：

这里有几个重要的变量：

gtid_executed：当前 MySQL 实例上执行过的 GTID 集合，实际上包含了所有记录到 Binlog 中的事务。系统表 mysql.gtid_executed 用于存储当前服务器上应用的所有 GTID。mysql.gtid_executed表可能有很多行。MySQL 会定期压缩此表，将多行合并为一行，从而减少空间使用并提高性能。

gtid_purged：用于记录已经被清除了的 Binlog 事务集合，它是 gtid_executed 的子集。Binlog 不可能永远驻留在服务上，需要定期进行清理(通过 expire_logs_days 可以控制定期清理间隔)。只有 gtid_executed 为空时才能手动设置该变量，此时会同时更新 gtid_executed 为和 gtid_purged 相同的值。 gtid_executed 为空意味着要么之前没有启动过基于 GTID 的复制，要么执行过 RESET MASTER。执行 RESET MASTER 时同样也会把 gtid_purged 置空，即始终保持 gtid_purged 是 gtid_executed 的子集。

gtid_next：会话级变量，指示如何产生下一个 GTID。可能的取值如下:

AUTOMATIC：自动生成下一个 GTID，实现上是分配一个当前实例上尚未执行过的序号最小的 GTID。
ANONYMOUS：设置后执行事务不会产生 GTID。
显式指定的GTID：可以指定任意形式合法的 GTID 值，但不能是当前 gtid_executed 中的已经包含的 GTID，否则，下次执行事务时会报错。

2.3 基于GTID复制的过程

事务分配与记录（源库侧）

当一个事务在源库上提交时，源库会在其提交前为其分配一个 GTID。

这个 GTID 和事务本身的内容会被一起写入源库的二进制日志（Binlog） 中。

源库还会将当前执行过的所有 GTID 集合（gtid_executed）和当前最新的 GTID（gtid_purged）信息记录下来。

事务传输（IO线程）

副本上的 I/O 线程 负责与源库建立连接。

副本会告诉源库：“我已经执行过的 GTID 集合是 gtid_executed”。

基于 GTID 的复制协议，源库会从自己的 Binlog 中找出所有 GTID 不在副本 gtid_executed 集合中的事务，然后将这些新事务发送给副本的 I/O 线程。

I/O 线程将这些事务数据（包括 GTID 信息）写入到副本本地的中继日志（Relay Log） 中。

事务应用（SQL线程 & 幂等性）

副本上的 SQL 线程 负责从中继日志中读取事务。

在应用每个事务之前，SQL 线程会先检查该事务的 GTID，并设置系统变量 gtid_next 的值为该 GTID，表示下一个要操作的事务是该 GTID。

关键检查（幂等性核心）：SQL 线程会查询副本系统表 mysql.gtid_executed 或内存中的 GTID 集合，判断gtid_next 是否已经存在。

如果存在：说明这个事务已经被副本执行过（可能是重复传输或故障恢复后的情况），SQL 线程会自动跳过这个事务，不会再次执行。
如果不存在：SQL 线程会执行这个事务，并在事务提交后，将这个新的 GTID 记录到副本的 gtid_executed 集合中，以标记该事务已被应用。

注意：

gtid_next 是基于会话的，不同会话的 gtid_next 不同。

Slave 在重放relay log 中的事务时，不会自己生成 GTID。所有的 Slave（无论是一主一从或一主多从复制架构）通过重放 relay log 中事务获取的 GTID 都来源于 Master，并永久保存在 Slave上。

3、开启主从复制

以下基于MySQL8.0.27版本就两种不同的方法分别说明（注意：高版本与低版本的配置项会有所不同，详细可查看官网文档）。

3.1 基于传统复制的开启方法

主库的所有变更操作（写入更新删除）都会视为事件，被写入二进制日志文件中。从库通过读取主库的二进制日志文件，并在从库中执行这些事件，达到主从同步。

配置主数据库的 my.cnf 配置文件（只贴核心部分）：

启动主数据库，创建一个同步复制用户（非必需，也可以用root用户，但不建议）：

说明：

MySQL创建用户命令：CREATE USER 'username'@'host' IDENTIFIED BY 'password';

username：指定创建的用户名

host：指定用户登录的主机ip，% 表示任意主机都可远程登录，localhost 表示本地才能登录

password：指定该用户的登录密码

mysql_native_password：MySQL 8.0 之前的默认身份验证插件，负责密码验证。在 MySQL 8.0 以后默认使用 caching_sha2_password 作为身份验证插件，该插件提供了更好的安全性。

接着给创建的用户授权同步复制权限：

说明：

MySQL创建授权命令：GRANT REPLICATION SLAVE ON database.table TO 'username'@'host'；

privilege ：指定授权的权限，比如create、drop等权限，具体有哪些权限，可查看官网文档

database：指定哪些数据库生效，表示全部数据库生效

table：指定所在数据库的哪些数据表生效，表示所在数据库的全部数据表生效

username：指定被授予权限的用户名

host：指定用户登录的主机ip，%表示任意主机都可远程登录

最后刷新权限生效：

接着执行命令查看并记下binary log二进制日志文件名 File 以及位置 Position的值，需要在从数据库用到：

配置从数据库的my.cnf配置文件（只贴核心部分）：

启动从数据库，执行以下命令设置与主数据库的联系（不同版本语法有所区别）：

低于8.0.23版本的语法：

自8.0.23版本后的语法：

说明：

MASTER_HOST/SOURCE_HOST：主数据库的主机ip

MASTER_PORT/SOURCE_PORT：主数据库的端口，不设置则默认是3306

MASTER_USER/SOURCE_USER：主数据库被授予同步复制权限的用户名

MASTER_PASSWORD/SOURCE_PASSWORD：对应的用户密码

MASTER_LOG_FILE/SOURCE_LOG_FILE：在主数据库执行命令show master status 查询到的二进制日志文件名称

MASTER_LOG_POS/SOURCE_LOG_POS：在主数据库执行命令show master status 查询到的位置 Position的值

最后开启主从复制工作：

低于8.0.22版本的语法：

自8.0.22版本后的语法：

可执行命令查看详细信息以及状态：

低于8.0.22版本的语法：

自8.0.22版本后的语法：

假如显示 Slave_IO_Running/Replica_IO_Running和 Slave_SQL_Running/Replica_SQL_Running 为 Yes ，以及Slave_IO_State/Replica_IO_State 为 Waiting for master to send event/Waiting for source to send event，则证明主从复制成功！

假如需要停止主从复制工作，则执行以下命令：

低于8.0.22版本的语法：

自8.0.22版本后的语法：

假如需要重启主从复制工作，则执行以下命令：

低于8.0.22版本的语法：

自8.0.22版本后的语法：

测试：

在主数据库创建一个test数据库，并新建user表，然后在主数据库执行插入语句：

接着在从数据库发现已同步test库，查看数据发现已同步复制数据成功！

3.2 基于GTID复制的开启方法

配置主数据库的 my.cnf 配置文件（只贴核心部分）：

启动主数据库，创建一个同步复制用户（非必需，也可以用root用户，但不建议）：

接着给创建的用户授权同步复制权限：

最后刷新权限生效：

接着执行命令查看状态，对比发现与基于二进制日志文件的方法不同的是Executed_Gtid_Set会有一个具体的全局事务id值：

配置从数据库的my.cnf配置文件（只贴核心部分）：

启动从数据库，执行以下命令设置与主数据库的联系（不同版本语法有所不同）：

低于8.0.23版本的语法：

自8.0.23版本后的语法：

说明：

MASTER_HOST/SOURCE_HOST：主数据库的主机ip

MASTER_PORT/SOURCE_PORT：主数据库的端口，不设置则默认是3306

MASTER_USER/SOURCE_USER：主数据库被授予同步复制权限的用户名

MASTER_PASSWORD/SOURCE_PASSWORD：对应的用户密码

MASTER_AUTO_POSITION/SOURCE_AUTO_POSITION：

最后开启主从复制工作：

低于8.0.22版本的语法：

自8.0.22版本后的语法：

可执行命令查看详细信息以及状态：

低于8.0.22版本的语法：

自8.0.22版本后的语法：

假如显示 Slave_IO_Running/Replica_IO_Running 和 Slave_SQL_Running/Replica_SQL_Running 为 Yes ，以及Slave_IO_State/Replica_IO_State 为 Waiting for master to send event/Waiting for source to send event，则证明主从复制成功！