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。

2.1 GTID是什么

GTID 的全称是 global transaction id，表示的是全局事务 ID。GTID 的分配方式为uuid:trans_id

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

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

例如： 5ad9cb8e-2092-11e7-ac95-000c29bf823d:1-6，表示 uuid 为 5ad9cb8e-2092-11e7-ac95-000c29bf823d 的 Mysql

上执行了从 1 到 6 的事务。

2.2 GTID中的几个重要变量

我们可以执行下面命令来观察 GTID：

这里有几个重要的变量：

gtid_executed：当前实例上执行过的 GTID 集合，实际上包含了所有记录到 Binlog 中的事务。

gtid_purged：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复制的过程

客户端发送 DDL/DML 给 Master，Master 首先对此事务生成一个唯一的 GTID。（如果未将事务写入 Binlog，不会分配GTID ，例如主库过滤某个事务操作)。

Master 执行并提交事务后，将 GTID 更新到系统变量 gtid_executed。

Slave 从主库拉取 Binlog 并写入 relay log ，SQL线程首先读取主库的 GTID，并设置系统变量 gtid_next 的值为该 GTID，表示下一个要操作的事务是该 GTID。

Slave 检查 gtid_next 中的 GTID 在自己的 Binlog 中是否存在。如果存在，则放弃此 GTID 事务；如果不存在，则将此 GTID 写入到自己的 Binlog 中，然后立刻执行该事务，最后把 GTID 更新到自己的系统变量 gtid_executed。

注意：

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

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

2.4 基于GTID复制的优势

保证主从同步的准确性：同一个 GTID 事务在某 Slave 上绝对只执行一次，没有执行过的 GTID 事务总是会被执行。而且很容易判断 Master 和Slave 的数据是否一，只要 Master 上提交的事务在 Slave 上也提交了，那么一定是一致的。

简化主从复制的配置：不用像传统复制那样保证 Binlog 的坐标准确，因为根本不需要 Binlog 以及坐标。

简化故障转移：在发生主从切换或者数据库重启的时候不需要在从库上手动更新主库的 Binlog 信息。

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，则证明主从复制成功！

测试：

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

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

3.3 常见问题

1236: Could not find first log file name in binary log index file 解决方法

这个错误通常发生在 MySQL 主从复制环境中，表示从服务器无法在主服务器的二进制日志索引文件中找到指定的起始日志文件。

错误原因：

主服务器上的二进制日志已被轮换或清除

从服务器的 relay log 信息已损坏或不一致

主服务器和从服务器之间的二进制日志位置信息不同步

解决方案：

使用 reset slave 命令在从库中重置主从复制状态，执行下面命令：

slave reset执行候做了这样几件事：

删除 slave_master_info ，slave_relay_log_info 两个表中数据；

删除所有 relay log 文件，并重新创建新的 relay log 文件；

不会改变 gtid_executed 或者 gtid_purged 的值

Slave failed to initialize relay log info structure from the repository 解决方法

这个错误通常发生在 MySQL 主从复制环境中，当从库(slave)尝试初始化中继日志信息结构时失败。

可能的原因：

中继日志信息文件损坏 (relay-log.info)

复制元数据存储方式不一致 (文件 vs 表)

权限问题导致无法读取或写入相关文件

磁盘空间不足导致无法创建日志文件

MySQL 版本升级后兼容性问题

解决方案：

使用 reset slave 命令在从库中重置主从复制状态，执行下面命令：

World-writable config file '/etc/mysql/my.cnf' is ignored 解决方法

MySQL 出于安全考虑，会忽略任何全局可写（world-writable）的配置文件。当 /etc/mysql/my.cnf 文件的权限设置为 777 或 666 时，就会出现这个问题。

解决方案：修改配置文件权限

参考

‣