MySQL中的主备、主从和读写分离的原理

共计 12657 个字符，预计需要花费 32 分钟才能阅读完成。

本篇内容介绍了“MySQL 中的主备、主从和读写分离的原理”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

一、MySQL 主备的基本原理

在状态 1 中，客户端的读写都直接访问节点 A，而节点 B 是 A 的备库，只是将 A 的更新都同步过来，到本地执行。这样可以保持节点 B 和 A 的数据是相同的。当需要切换的时候，就切成状态 2。这时候客户端读写访问的都是节点 B，而节点 A 是 B 的备库。【相关推荐：mysql 视频教程】

在状态 1 中，虽然节点 B 没有被直接访问，但是建议把备库节点 B，设置成只读模式。有以下几个原因：

1. 有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作

2. 防止切换逻辑有 bug

3. 可以用 readonly 状态，来判断节点的角色

把备库设置成只读，还怎么跟主库保持同步更新？

readonly 设置对超级权限用户是无效的，而用于同步更新的线程，就拥有超级权限

下图是一个 update 语句在节点 A 执行，然后同步到节点 B 的完整流程图：

备库 B 和主库 A 之间维持了一个长连接。主库 A 内部有一个线程，专门用于服务备库 B 的这个长连接。一个事务日志同步的完整过程如下：

1. 在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量

2. 在备库 B 上执行 start slave 命令，这时备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接

3. 主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B

4. 备库 B 拿到 binlog 后，写到本地文件，称为中转日志

5.sql_thread 读取中转日志，解析出日志里的命令，并执行

由于多线程复制方案的引入，sql_thread 演化成了多个线程

二、循环复制问题

双 M 结构：

节点 A 和节点 B 互为主备关系。这样在切换的时候就不用再修改主备关系

双 M 结构有一个问题要解决，业务逻辑在节点 A 上更新了一条语句，然后再把生成的 binlog 发给节点 B，节点 B 执行完这条更新语句后也会生成 binlog。那么，如果节点 A 同时是节点 B 的备库，相当于又把节点 B 新生成的 binlog 拿过来执行了一次，然后节点 A 和 B 间，会不断地循环执行这个更新语句，也就是循环复制

MySQL 在 binlog 中记录了这个命令第一次执行时所在实例的 server id。因此，可以用下面的逻辑，来解决两个节点间的循环复制问题：

1. 规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系

2. 一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog

3. 每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志

双 M 结构日志的执行流如下：

1. 从节点 A 更新的事务，binlog 里面记的都是 A 的 server id

2. 传到节点 B 执行一次以后，节点 B 生成的 binlog 的 server id 也是 A 的 server id

3. 再传回给节点 A，A 判断这个 server id 与自己的相同，就不会再处理这个日志。所以，死循环在这里就断掉了

三、主备延迟

1、什么是主备延迟？

与数据同步有关的时间点主要包括以下三个：

1. 主库 A 执行完成一个事务，写入 binlog，这个时刻记为 T1

2. 之后传给备库 B，备库 B 接收完这个 binlog 的时刻记为 T2

3. 备库 B 执行完这个事务，把这个时刻记为 T3

所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就是 T3-T1

可以在备库上执行 show slave status 命令，它的返回结果里面会显示 seconds_behind_master，用于表示当前备库延迟了多少秒

seconds_behind_master 的计算方法是这样的：

1. 每个事务的 binlog 里面都有一个时间字段，用于记录主库上写入的时间

2. 备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时间的差值，得到 seconds_behind_master

如果主备库机器的系统时间设置不一致，不会导致主备延迟的值不准。备库连接到主库的时候，会通过 SELECTUNIX_TIMESTAMP()函数来获得当前主库的系统时间。如果这时候发现主库的系统时间与自己不一致，备库在执行 seconds_behind_master 计算的时候会自动扣掉这个差值

网络正常情况下，主备延迟的主要来源是备库接收完 binlog 和执行完这个事务之间的时间差

主备延迟最直接的表现是，备库消费中转日志的速度，比主库生产 binlog 的速度要慢

2、主备延迟的原来

1. 有些部署条件下，备库所在机器的性能要比主库所在的机器性能差

2. 备库的压力大。主库提供写能力，备库提供一些读能力。忽略了备库的压力控制，导致备库上的查询耗费了大量的 CPU 资源，影响了同步速度，造成主备延迟

可以做以下处理：

一主多从。除了备库外，可以多接几个从库，让这些从库来分担读的压力

通过 binlog 输出到外部系统，比如 Hadoop 这类系统，让外部系统提供统计类查询的能力

3. 大事务。因为主库上必须等事务执行完才会写入 binlog，再传给备库。所以，如果一个主库上的语句执行 10 分钟，那这个事务很可能会导致从库延迟 10 分钟

典型的大事务场景：一次性地用 delete 语句删除太多数据和大表的 DDL

四、主备切换策略 1、可靠性优先策略

双 M 结构下，从状态 1 到状态 2 切换的详细过程如下：

1. 判断备库 B 现在的 seconds_behind_master，如果小于某个值继续下一步，否则持续重试这一步

2. 把主库 A 改成只读状态，即把 readonly 设置为 true

3. 判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止

4. 把备库 B 改成可读写状态，也就是把 readonly 设置为 false

5. 把业务请求切到备库 B

这个切换流程中是有不可用的时间的。在步骤 2 之后，主库 A 和备库 B 都处于 readonly 状态，也就是说这时系统处于不可写状态，直到步骤 5 完成后才能恢复。在这个不可用状态中，比较耗时的是步骤 3，可能需要耗费好几秒的时间。也是为什么需要在步骤 1 先做判断，确保 seconds_behind_master 的值足够小

系统的不可用时间是由这个数据可靠性优先的策略决定的

2、可用性优先策略

可用性优先策略：如果强行把可靠性优先策略的步骤 4、5 调整到最开始执行，也就是说不等主备数据同步，直接把连接切到备库 B，并且让备库 B 可以读写，那么系统几乎没有不可用时间。这个切换流程的代价，就是可能出现数据不一致的情况

mysql  CREATE TABLE `t` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `c` int(11) unsigned DEFAULT NULL,
 PRIMARY KEY (`id`)) ENGINE=InnoDB;insert into t(c) values(1),(2),(3);

表 t 定义了一个自增主键 id，初始化数据后，主库和备库上都是 3 行数据。继续在表 t 上执行两条插入语句的命令，依次是：

insert into t(c) values(4);insert into t(c) values(5);

假设，现在主库上其他的数据表有大量的更新，导致主备延迟达到 5 秒。在插入一条 c = 4 的语句后，发起了主备切换

下图是可用性优先策略，且 binlog_format=mixed 时的切换流程和数据结果

1. 步骤 2 中，主库 A 执行完 insert 语句，插入了一行数据(4,4)，之后开始进行主备切换

2. 步骤 3 中，由于主备之间有 5 秒的延迟，所以备库 B 还没来得及应用插入 c = 4 这个中转日志，就开始接收客户端插入 c = 5 的命令

3. 步骤 4 中，备库 B 插入了一行数据(4,5)，并且把这个 binlog 发给主库 A

4. 步骤 5 中，备库 B 执行插入 c = 4 这个中转日志，插入了一行数据(5,4)。而直接在备库 B 执行的插入 c = 5 这个语句，传到主库 A，就插入了一行新数据(5,5)

最后的结果就是，主库 A 和备库 B 上出现了两行不一致的数据

可用性优先策略，设置 binlog_format=row

因此 row 格式在记录 binlog 的时候，会记录新插入的行的所有字段值，所以最后只会有一行不一致。而且，两边的主备同步的应用线程会报错 duplicate key error 并停止。也就是说，这种情况下，备库 B 的 (5,4) 和主库 A 的 (5,5) 这两行数据都不会被对方执行

3、小结

1. 使用 row 格式的 binlog 时，数据不一致问题更容易被发现。而使用 mixed 或者 statement 格式的 binlog 时，可能过了很久才发现数据不一致的问题

2. 主备切换的可用性优先策略会导致数据不一致。因此，大多数情况下，建议采用可靠性优先策略

五、MySQL 的并行复制策略

主备的并行复制能力，要关注的就是上图中黑色的两个箭头。一个代表客户端写入主库，另一个代表备库上 sql_thread 执行中转日志

在 MySQL5.6 版本之前，MySQL 只支持单线程复制，由此在主库并发高、TPS 高时就会出现严重的主备延迟问题

多线程复制机制都是把只有一个线程的 sql_thread 拆成多个线程，都符合下面这个模型：

coordinator 就是原来的 sql_thread，不过现在它不再直接更新数据了，只负责读取中转日志和分发事务。真正更新日志的，变成了 worker 线程。而 worker 线程的个数就是由参数 slave_parallel_workers 决定的

coordinator 在分发的时候，需要满足以下两个基本要求：

不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个 worker 中

同一个事务不能被拆开，必须放到同一个 worker 中

1、MySQL5.6 版本的并行复制策略

MySQL5.6 版本支持了并行复制，只是支持的粒度是按库并行。用于决定分发策略的 hash 表里，key 是数据库名

这个策略的并行效果取决于压力模型。如果在主库上有多个 DB，并且各个 DB 的压力均衡，使用这个策略的效果会很好

这个策略的两个优势：

构造 hash 值的时候很快，只需要库名

不要求 binlog 的格式，因为 statement 格式的 binlog 也可以很容易拿到库名

可以创建不同的 DB，把相同热度的表均匀分到这些不同的 DB 中，强行使用这个策略

2、MariaDB 的并行复制策略

redo log 组提交优化，而 MariaDB 的并行复制策略利用的就是这个特性：

能够在同一个组里提交的事务，一定不会修改同一行

主库上可以并行执行的事务，备库上也一定是可以并行执行的

在实现上，MariaDB 是这么做的：

1. 在一组里面一起提交的事务，有一个相同的 commit_id，下一组就是 commit_id+1

2.commit_id 直接写到 binlog 里面

3. 传到备库应用的时候，相同 commit_id 的事务分发到多个 worker 执行

4. 这一组全部执行完成后，coordinator 再去取下一批

下图中假设三组事务在主库的执行情况，trx1、trx2 和 trx3 提交的时候，trx4、trx5 和 trx6 是在执行的。这样，在第一组事务提交完成的时候，下一组事务很快就会进入 commit 状态

按照 MariaDB 的并行复制策略，备库上的执行效果如下图：

在备库上执行的时候，要等第一组事务完全执行完成后，第二组事务才能开始执行，这样系统的吞吐量就不够

另外，这个方案容易被大事务拖后腿。假设 trx2 是一个超大事务，那么在备库应用的时候，trx1 和 trx3 执行完成后，下一组才能开始执行。只有一个 worker 线程在工作，是对资源的浪费

3、MySQL5.7 版本的并行复制策略

MySQL5.7 版本由参数 slave-parallel-type 来控制并行复制策略：

配置为 DATABASE，表示使用 MySQL5.6 版本的按库并行策略

配置为 LOGICAL_CLOCK，表示的就是类似 MariaDB 的策略。MySQL 在此基础上做了优化

同时处于执行状态的所有事务，是不是可以并行？

不可以，因为这里面可能有由于锁冲突而处于锁等待状态的事务。如果这些事务在备库上被分配到不同的 worker，就会出现备库跟主库不一致的情况

而 MariaDB 这个策略的核心是所有处于 commit 状态的事务可以并行。事务处于 commit 状态表示已经通过了锁冲突的检验了

其实只要能够达到 redo log prepare 阶段就表示事务已经通过锁冲突的检验了

因此，MySQL5.7 并行复制策略的思想是：

1. 同时处于 prepare 状态的事务，在备库执行时是可以并行的

2. 处于 prepare 状态的事务，与处于 commit 状态的事务之间，在备库执行时也是可以并行的

binlog 组提交的时候有两个参数：

binlog_group_commit_sync_delay 参数表示延迟多少微妙后才调用 fsync

binlog_group_commit_sync_no_delay_count 参数表示基类多少次以后才调用 fsync

这两个参数是用于故意拉长 binlog 从 write 到 fsync 的时间，以此减少 binlog 的写盘次数。在 MySQL5.7 的并行复制策略里，它们可以用来制造更多的同时处于 prepare 阶段的事务。这样就增加了备库复制的并行度。也就是说，这两个参数既可以故意让主库提交得慢些，又可以让备库执行得快些

4、MySQL5.7.22 的并行复制策略

MySQL5.7.22 增加了一个新的并行复制策略，基于 WRITESET 的并行复制，新增了一个参数 binlog-transaction-dependency-tracking 用来控制是否启用这个新策略。这个参数的可选值有以下三种：

COMMIT_ORDER，根据同时进入 prepare 和 commit 来判断是否可以并行的策略

WRITESET，表示的是对于事务涉及更新的每一行，计算出这一行的 hash 值，组成集合 writeset。如果两个事务没有操作相同的行，也就是说它们的 writeset 没有交集，就可以并行

WRITESET_SESSION，是在 WRITESET 的基础上多了一个约束，即在主库上同一个线程先后执行的两个事务，在备库执行的时候，要保证相同的先后顺序

为了唯一标识，hash 值是通过库名 + 表名 + 索引名 + 值计算出来的。如果一个表上除了有主键索引外，还有其他唯一索引，那么对于每个唯一索引，insert 语句对应的 writeset 就要多增加一个 hash 值

1.writeset 是在主库生成后直接写入到 binlog 里面的，这样在备库执行的时候不需要解析 binlog 内容

2. 不需要把整个事务的 binlog 都扫一遍才能决定分发到哪个 worker，更省内存

3. 由于备库的分发策略不依赖于 binlog 内容，索引 binlog 是 statement 格式也是可以的

对于表上没主键和外键约束的场景，WRITESET 策略也是没法并行的，会暂时退化为单线程模型

六、主库出问题了，从库怎么办？

下图是一个基本的一主多从结构

图中，虚线箭头表示的是主备关系，也就是 A 和 A’互为主备，从库 B、C、D 指向的是主库 A。一主多从的设置，一般用于读写分离，主库负责所有的写入和一部分读，其他的读请求则由从库分担

一主多从结构在切换完成后，A’会成为新的主库，从库 B、C、D 也要改接到 A’

1、基于位点的主备切换

当我们把节点 B 设置成节点 A’的从库的时候，需要执行一条 change master 命令：

CHANGE MASTER TO 
MASTER_HOST=$host_name 
MASTER_PORT=$port 
MASTER_USER=$user_name 
MASTER_PASSWORD=$password 
MASTER_LOG_FILE=$master_log_name 
MASTER_LOG_POS=$master_log_pos

MASTER_HOST、MASTER_PORT、MASTER_USER 和 MASTER_PASSWORD 四个参数，分别代表了主库 A’的 IP、端口、用户名和密码

最后两个参数 MASTER_LOG_FILE 和 MASTER_LOG_POS 表示，要从主库的 master_log_name 文件的 master_log_pos 这个位置的日志继续同步。而这个位置就是所说的同步位点，也就是主库对应的文件名和日志偏移量

找同步位点很难精确取到，只能取一个大概位置。一种去同步位点的方法是这样的：

1. 等待新主库 A’把中转日志全部同步完成

2. 在 A’上执行 show master status 命令，得到当前 A’上最新的 File 和 Position

3. 取原主库 A 故障的时刻 T

4. 用 mysqlbinlog 工具解析 A’的 File，得到 T 时刻的位点，这个值就可以作为 $master_log_pos

这个值并不精确，有这么一种情况，假设在 T 这个时刻，主库 A 已经执行完成了一个 insert 语句插入了一行数据 R，并且已经将 binlog 传给了 A’和 B，然后在传完的瞬间主库 A 的主机就掉电了。那么，这时候系统的状态是这样的：

1. 在从库 B 上，由于同步了 binlog，R 这一行已经存在

2. 在新主库 A’上，R 这一行也已经存在，日志是写在 master_log_pos 这个位置之后的

3. 在从库 B 上执行 change master 命令，指向 A’的 File 文件的 master_log_pos 位置，就会把插入 R 这一行数据的 binlog 又同步到从库 B 去执行，造成主键冲突，然后停止 tongue

通常情况下，切换任务的时候，要先主动跳过这些错误，有两种常用的方法

一种是，主动跳过一个事务

set global sql_slave_skip_counter=1;start slave;

另一种方式是，通过设置 slave_skip_errors 参数，直接设置跳过指定的错误。这个背景是，我们很清楚在主备切换过程中，直接跳过这些错误是无损的，所以才可以设置 slave_skip_errors 参数。等到主备间的同步关系建立完成，并稳定执行一段时间之后，还需要把这个参数设置为空，以免之后真的出现了主从数据不一致，也跳过了

2、GTID

MySQL5.6 引入了 GTID，是一个全局事务 ID，是一个事务提交的时候生成的，是这个事务的唯一标识。它的格式是：

GTID=source_id:transaction_id

source_id 是一个实例第一次启动时自动生成的，是一个全局唯一的值

transaction_id 是一个整数，初始值是 1，每次提交事务的时候分配给这个事务，并加 1

GTID 模式的启动只需要在启动一个 MySQL 实例的时候，加上参数 gtid_mode=on 和 enforce_gtid_consistency=on 就可以了

在 GTID 模式下，每个事务都会跟一个 GTID 一一对应。这个 GTID 有两种生成方式，而使用哪种方式取决于 session 变量 gtid_next 的值

1. 如果 gtid_next=automatic，代表使用默认值。这时，MySQL 就把 GTID 分配给这个事务。记录 binlog 的时候，先记录一行 SET@@SESSION.GTID_NEXT=‘GTID’。把这个 GTID 加入本实例的 GTID 集合

2. 如果 gtid_next 是一个指定的 GTID 的值，比如通过 set gtid_next=‘current_gtid’，那么就有两种可能：

如果 current_gtid 已经存在于实例的 GTID 集合中，接下里执行的这个事务会直接被系统忽略

如果 current_gtid 没有存在于实例的 GTID 集合中，就将这个 current_gtid 分配给接下来要执行的事务，也就是说系统不需要给这个事务生成新的 GTID，因此 transaction_id 也不需要加 1

一个 current_gtid 只能给一个事务使用。这个事务提交后，如果要执行下一个事务，就要执行 set 命令，把 gtid_next 设置成另外一个 gtid 或者 automatic

这样每个 MySQL 实例都维护了一个 GTID 集合，用来对应这个实例执行过的所有事务

3、基于 GTID 的主备切换

在 GTID 模式下，备库 B 要设置为新主库 A’的从库的语法如下：

CHANGE MASTER TO MASTER_HOST=$host_name 
MASTER_PORT=$port 
MASTER_USER=$user_name 
MASTER_PASSWORD=$password 
master_auto_position=1

其中 master_auto_position= 1 就表示这个主备关系使用的是 GTID 协议

实例 A’的 GTID 集合记为 set_a，实例 B 的 GTID 集合记为 set_b。我们在实例 B 上执行 start slave 命令，取 binlog 的逻辑是这样的：

1. 实例 B 指定主库 A’，基于主备协议建立连接

2. 实例 B 把 set_b 发给主库 A’

3. 实例 A’算出 set_a 与 set_b 的差集，也就是所有存在于 set_a，但是不存在于 set_b 的 GTID 的集合，判断 A’本地是否包含了这个差集需要的所有 binlog 事务

如果不包含，表示 A’已经把实例 B 需要的 binlog 给删掉了，直接返回错误

如果确认全部包含，A’从自己的 binlog 文件里面，找出第一个不在 set_b 的事务，发给 B

4. 之后从这个事务开始，往后读文件，按顺序取 binlog 发给 B 去执行

4、GTID 和在线 DDL

如果是由于索引缺失引起的性能问题，可以在线加索引来解决。但是，考虑到要避免新增索引对主库性能造成的影响，可以先在备库加索引，然后再切换，在双 M 结构下，备库执行的 DDL 语句也会传给主库，为了避免传回后对主库造成影响，要通过 set sql_log_bin=off 关掉 binlog，但是操作可能会导致数据和日志不一致

两个互为主备关系的库实例 X 和实例 Y，且当前主库是 X，并且都打开了 GTID 模式。这时的主备切换流程可以变成下面这样：

在实例 X 上执行 stop slave

在实例 Y 上执行 DDL 语句。这里不需要关闭 binlog

执行完成后，查出这个 DDL 语句对应的 GTID，记为 source_id_of_Y:transaction_id

到实例 X 上执行一下语句序列：

set GTID_NEXT= source_id_of_Y:transaction_id begin;commit;set gtid_next=automatic;start slave;

这样做的目的在于，既可以让实例 Y 的更新有 binlog 记录，同时也可以确保不会在实例 X 上执行这条更新

七、MySQL 读写分离

读写分离的基本结构如下图：

读写分离的主要目的就是分摊主库的压力。上图中的结构是客户端主动做负载均衡，这种模式下一般会把数据库的连接信息放在客户端的连接层。由客户端来选择后端数据库进行查询

还有一种架构就是在 MySQL 和客户端之间有一个中间代理层 proxy，客户端只连接 proxy，由 proxy 根据请求类型和上下文决定请求的分发路由

1. 客户端直连方案，因此少了一层 proxy 转发，所以查询性能稍微好一点，并且整体架构简单，排查问题更方便。但是这种方案，由于要了解后端部署细节，所以在出现主备切换、库迁移等操作的时候，客户端都会感知到，并且需要调整数据库连接信息。一般采用这样的架构，一定会伴随一个负责管理后端的组件，比如 Zookeeper，尽量让业务端只专注于业务逻辑开发

2. 带 proxy 的架构，对客户端比较友好。客户端不需要关注后端细节，连接维护、后端信息维护等工作，都是由 proxy 完成的。但这样的话，对后端维护团队的要求会更高，而且 proxy 也需要有高可用架构

在从库上会读到系统的一个过期状态的现象称为过期读

1、强制走主库方案

强制走主库方案其实就是将查询请求做分类。通常情况下，可以分为这么两类：

1. 对于必须要拿到最新结果的请求，强制将其发到主库上

2. 对于可以读到旧数据的请求，才将其发到从库上

这个方案最大的问题在于，有时候可能会遇到所有查询都不能是过期读的需求，比如一些金融类的业务。这样的话，就需要放弃读写分离，所有读写压力都在主库，等同于放弃了扩展性

2、Sleep 方案

主库更新后，读从库之前先 sleep 一下。具体的方案就是，类似于执行一条 select sleep(1)命令。这个方案的假设是，大多数情况下主备延迟在 1 秒之内，做一个 sleep 可以很大概率拿到最新的数据

以买家发布商品为例，商品发布后，用 Ajax 直接把客户端输入的内容作为最新商品显示在页面上，而不是真正地去数据库做查询。这样，卖家就可以通过这个显示，来确认产品已经发布成功了。等到卖家再刷新页面，去查看商品的时候，其实已经过了一段时间，也就达到了 sleep 的目的，进而也就解决了过期读的问题

但这个方案并不精确：

1. 如果这个查询请求本来 0.5 秒就可以在从库上拿到正确结果，也会等 1 秒

2. 如果延迟超过 1 秒，还是会出现过期读

3、判断主备无延迟方案

show slave status 结果里的 seconds_behind_master 参数的值，可以用来衡量主备延迟时间的长短

1. 第一种确保主备无延迟的方法是，每次从库执行查询请求前，先判断 seconds_behind_master 是否已经等于 0。如果还不等于 0，那就必须等到这个参数变为 0 才能执行查询请求

show slave status 结果的部分截图如下：

2. 第二种方法，对比位点确保主备无延迟：

Master_Log_File 和 Read_Master_Log_Pos 表示的是读到的主库的最新位点

Relay_Master_Log_File 和 Exec_Master_Log_Pos 表示的是备库执行的最新位点

如果 Master_Log_File 和 Read_Master_Log_Pos 和 Relay_Master_Log_File 和 Exec_Master_Log_Pos 这两组值完全相同，就表示接收到的日志已经同步完成

3. 第三种方法，对比 GTID 集合确保主备无延迟：

Auto_Position= 1 表示这堆主备关系使用了 GTID 协议

Retrieved_Gitid_Set 是备库收到的所有日志的 GTID 集合

Executed_Gitid_Set 是备库所有已经执行完成的 GTID 集合

如果这两个集合相同，也表示备库接收到的日志都已经同步完成

4. 一个事务的 binlog 在主备库之间的状态：

1）主库执行完成，写入 binlog，并反馈给客户端

2）binlog 被从主库发送给备库，备库收到

3）在备库执行 binlog 完成

上面判断主备无延迟的逻辑是备库收到的日志都执行完成了。但是，从 binlog 在主备之间状态的分析中，有一部分日志，处于客户端已经收到提交确认，而备库还没收到日志的状态

这时，主库上执行完成了三个事务 trx1、trx2 和 trx3，其中：

trx1 和 trx2 已经传到从库，并且已经执行完成了

trx3 在主库执行完成，并且已经回复给客户端，但是还没有传到从库中

如果这时候在从库 B 上执行查询请求，按照上面的逻辑，从库认为已经没有同步延迟，但还是查不到 trx3 的

4、配合 semi-sync

要解决上面的问题，就要引入半同步复制。semi-sync 做了这样的设计：

1. 事务提交的时候，主库把 binlog 发送给从库

2. 从库收到 binlog 以后，发回给主库一个 ack，表示收到了

3. 主库收到这个 ack 以后，才能给客户端返回事务完成的确认

如果启用了 semi-sync，就表示所有给客户端发送过确认的事务，都确保了备库已经收到了这个日志

semi-sync+ 位点判断的方案，只对一主一备的场景是成立的。在一主多从场景中，主库只要等到一个从库的 ack，就开始给客户端返回确认。这时，在从库上执行查询请求，就有两种情况：

1. 如果查询是落在这个响应了 ack 的从库上，是能够确保读到最新数据

2. 但如果查询落到其他从库上，它们可能还没有收到最新的日志，就会产生过期读的问题

判断同步位点的方案还有另外一个潜在的问题，即：如果在业务更新的高峰期，主库的位点或者 GTID 集合更新很快，那么上面的两个位点等值判断就会一直不成立，很有可能出现从库上迟迟无法响应查询请求的情况

上图从状态 1 到状态 4，一直处于延迟一个事务的状态。但是，其实客户端是在发完 trx1 更新后发起的 select 语句，我们只需要确保 trx1 已经执行完成就可以执行 select 语句了。也就是说，如果在状态 3 执行查询请求，得到的就是预期结果了

semi-sync 配合主备无延迟的方案，存在两个问题：

1. 一主多从的时候，在某些从库执行查询请求会存在过期读的现象

2. 在持续延迟的情况下，可能出现过度等待的问题

5、等主库位点方案

select master_pos_wait(file, pos[, timeout]);

这条命令的逻辑如下：

1. 它是在从库执行的

2. 参数 file 和 pos 指的是主库上的文件名和位置

3.timeout 可选，设置为正整数 N 表示这个函数最多等待 N 秒

这个命令正常返回的结果是一个正整数 M，表示从命令开始执行，到应用完 file 和 pos 表示的 binlog 位置，执行了多少事务

1. 如果执行期间，备库同步线程发生异常，则返回 NULL

2. 如果等待超过 N 秒，就返回 -1

3. 如果刚开始执行的时候，就发现已经执行过这个位置了，则返回 0

对于上图中先执行 trx1，再执行一个查询请求的逻辑，要保证能够查到正确的数据，可以使用这个逻辑：

1.trx1 事务更新完成后，马上执行 show master status 得到当前主库执行到的 File 和 Position

2. 选定一个从库执行查询语句

3. 在从库上执行 select master_pos_wait(file, pos, 1)

4. 如果返回值是 = 0 的正整数，则在这个从库执行查询语句

5. 否则，到主库执行查询语句

流程如下：

6、GTID 方案

 select wait_for_executed_gtid_set(gtid_set, 1);

这条命令的逻辑如下：

1. 等待，直到这个库执行的事务中包含传入的 gtid_set，返回 0

2. 超时返回 1

等主库位点方案中，执行完事务后，还要主动去主库执行 show master status。而 MySQL5.7.6 版本开始，允许在执行完更新类事务后，把这个事务的 GTID 返回给客户端，这样等 GTID 的方案可以减少一次查询

等 GTID 的流程如下：

1.trx1 事务更新完成后，从返回包直接获取这个事务的 GTID，记为 gtid1

2. 选定一个从库执行查询语句

3. 在从库上执行 select wait_for_executed_gtid_set(gtid1, 1);

4. 如果返回值是 0，则在这个从库执行查询语句

5. 否则，到主库执行查询语句

“MySQL 中的主备、主从和读写分离的原理”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！