本文共 3746 字，大约阅读时间需要 12 分钟。

MySQL 是怎么保证高可用的

学习检测

1. 主备延时原因说几个？

学习总结

1. 主库DML语句并发大,从库qps高
2. 从库服务器配置差或者一台服务器上几台从库(资源竞争激烈,特别是io)
3. 主库和从库的参数配置不一样
4. 大事务(DDL,我觉得DDL也相当于一个大事务)
5. 从库上在进行备份操作
6. 表上无主键的情况(主库利用索引更改数据,备库回放只能用全表扫描,这种情况可以调整slave_rows_search_algorithms参数适当优化下)
7. 设置的是延迟备库
8. 备库空间不足的情况下

双M结构

主备延时原因分析

同步时间

1. 主库A执行完成一个事务，写入binlog，我们把和这个时刻记为T1；
2. 之后传给备库B，我们把备库B接收完这个binlog的时刻记为T2；
3. 备库B执行完这个事务，我们把这个时刻记为T3

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

show slave status 会显示当前主备延迟了多少秒

seconds_behind_master（主备延时秒）：

1. 每个事务的binlog里面都有一个时间字段，用于记录主库上写入的时间2. 备库取出当前正在执行的事务时间字段的值，计算它和当前系统时间的差值，我们可以用seconds_behind_master来作为主备延迟的值，这个值的时间是秒。

如果主备系统时间设置不一致，会不会导致主备延迟的值不准？

其实不会的。因为，备库连接到主库的时候，会通过执行 SELECT UNIX_TIMESTAMP() 函数来获得当前主库的系统时间。如果这时候发现主库的系统时间与自己不一致，备库在执行 seconds_behind_master 计算的时候会自动扣掉这个差值。

需要说明的是，在网络正常的时候，日志从主库传给备库所需的时间是很短的，即 T2-T1 的值是非常小的。也就是说，网络正常情况下，主备延迟的主要来源是备库接收完 binlog 和执行完这个事务之间的时间差。

所以说，主备延迟最直接的表现是，备库消费中转日志（relay log）的速度，比主库生产 binlog 的速度要慢。接下来，我就和你一起分析下，这可能是由哪些原因导致的。

备库压力大

1. 备库的机器没有主库的机器配置高
2. 主库为写，备库为读的同时还要写入binlog传过来的relay log，压力也不小

一主多从 延迟分析

这时候 备库的压力不会超过主库的压力

大事务

​ 删除大量数据

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

不知道你所在公司的 DBA 有没有跟你这么说过：不要一次性地用 delete 语句删除太多数据。其实，这就是一个典型的大事务场景。

​ 大表DDL

造成主备延迟还有一个大方向的原因，就是备库的并行复制能力。这个话题，我会留在下一篇文章再和你详细介绍。

主备切换–可靠性优先策略

在图中双M结构下，从状态1到状态2切换的过程时这样的：

1. 判断备库B现在的secneds_behind_master，如果小于某个值（例如-5秒）继续下一步，否则持续重试这一步
2. 把主库 A 改成只读状态，即把 readonly 设置为 true；
3. 判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；
4. 把备库 B 改成可读写状态，也就是把 readonly 设置为 false；
5. 把业务请求切到备库 B。
   
   SBM 是seconeds_behind_master参数的简写

可以看到，这个切换流程中是有不可用时间的。因为在步骤 2 之后，主库 A 和备库 B 都处于 readonly 状态，也就是说这时系统处于不可写状态，直到步骤 5 完成后才能恢复。

在这个不可用状态中，比较耗费时间的是步骤 3，可能需要耗费好几秒的时间。这也是为什么需要在步骤 1 先做判断，确保 seconds_behind_master 的值足够小。

试想如果一开始主备延迟就长达 30 分钟，而不先做判断直接切换的话，系统的不可用时间就会长达 30 分钟，这种情况一般业务都是不可接受的。

当然，系统的不可用时间，是由这个数据可靠性优先的策略决定的。你也可以选择可用性优先的策略，来把这个不可用时间几乎降为 0。可用性优先策略

主备切换–可用性优先策略

如果我强行把步骤 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);

这个表定义了一个自增主键 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) 这两行数据，都不会被对方执行。

1. 使用 row 格式的 binlog 时，数据不一致的问题更容易被发现。而使用 mixed 或者 statement 格式的 binlog 时，数据很可能悄悄地就不一致了。如果你过了很久才发现数据不一致的问题，很可能这时的数据不一致已经不可查，或者连带造成了更多的数据逻辑不一致。
2. 主备切换的可用性优先策略会导致数据不一致。因此，大多数情况下，我都建议你使用可靠性优先策略。毕竟对数据服务来说的话，数据的可靠性一般还是要优于可用性的。

循环复制问题

场景

1. 在一个主库更新事务后，用命令 set global server_id=x 修改了 server_id。等日志再传回来的时候，发现 server_id 跟自己的 server_id 不同，就只能执行了。
2. 有三个节点的时候，如图 7 所示，trx1 是在节点 B 执行的，因此 binlog 上的 server_id 就是 B，binlog 传给节点 A，然后 A 和 A’搭建了双 M 结构，就会出现循环复制。
   
   如果出现循环复制，可以再A或者A’上，执行下面命令

stop slave；CHANGE MASTER TO IGNORE_SERVER_IDS=(server_id_of_B);start slave;

这样这个节点收到日志后就不会再执行。过一段时间后，再执行下面的命令把这个值改回来。

stop slave；CHANGE MASTER TO IGNORE_SERVER_IDS=();start slave;

主备延时情况

1. 主库DML语句并发大,从库qps高
2. 从库服务器配置差或者一台服务器上几台从库(资源竞争激烈,特别是io)
3. 主库和从库的参数配置不一样
4. 大事务(DDL,我觉得DDL也相当于一个大事务)
5. 从库上在进行备份操作
6. 表上无主键的情况(主库利用索引更改数据,备库回放只能用全表扫描,这种情况可以调整slave_rows_search_algorithms参数适当优化下)
7. 设置的是延迟备库
8. 备库空间不足的情况下

转载地址：http://rfesz.baihongyu.com/