MySQL是怎么保证高可用的

MySQL高可用系统的基础即主备切换, 而MySQL的几乎所有的高可用架构，都直接依赖于binlog。

binlog的三种格式

statement

当binlog_format=statement时，binlog里面记录的就是SQL语句的原文。由于只记录语句，缺乏执行的上下文，可能出现主备选择不同的索引而执行结果不同的情况。一般线上不推荐用此格式。

row

row格式记录的非SQL语句原文. 记录的是被操作的数据行的主键id以及执行的server id，从而保证了主备的一致性。
缺点：很占空间。

mix

折中statement和row。

主备同步流程

在状态 1 中，虽然节点 B 没有被直接访问，但是依然建议把节点 B（也就是备库）设置成只读（readonly）模式。这样做，有以下几个考虑：

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

2. 防止切换逻辑有 bug，比如切换过程中出现双写，造成主备不一致；

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

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

1. 在备库B上通过change master命令，设置主库A的IP、端口、用户名、密码，以及要从哪个位置开始请求binlog, 这个位置包含文件名和日志偏移量。
2. 在备库不上执行start_slave命令，这时候备库会启动两个线程，就是图中io_thread和sql_thread。其中io_thread负责与主库建立连接。
3. 主库A校验完用户名、密码后，开始按照备库B传来的位置，从本地读取binlog,发给B。
4. 备库B拿到binlog后，写到本地文件，称为中转日志 relay log.
5. sql_thread读取中转日志，解析出日志里的命令，并执行。

主备延迟

诚然，MySQL高可用的基础是主备切换。然而主备切换可能会遇到由于主备延迟导致的问题问题。

主备切换可能是一个主动运维动作，比如软件升级，主库所在机器按计划下线，也可能是被动操作，比如主库所在机器掉电。

与数据同步有关的时间点主要包括以下三个：
1.主库 A 执行完成一个事务，写入 binlog，我们把这个时刻记为 T1;
2.之后传给备库 B，我们把备库 B 接收完这个 binlog 的时刻记为 T2;
3.备库 B 执行完成这个事务，我们把这个时刻记为 T3。
所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就是 T3-T1。如果在备库执行show slave status命令，它的返回结果会显示second_behind_master, 用于表示当前备库延迟了多少秒。

主备延迟可能发生在以下情形（分钟级延迟）：

• 在有些部署条件下，备库所在机器的性能要比主库所在机器性能差；–解决方案：机器对称部署
• 备库压力大；– 解决方案：一主多从，分担读的压力。通过binlog输出到外部系统，例如Hadoop。让外部系统提供统计类查询能力。
• 大事务。
  • 例如一次性事务做大量的CUD
  • 大表DDL

由于有主备延迟的存在，所以在主备切换的过程中，就相应有不同的策略：可靠性优先策略(停服切)和可用性优先策略（在线切）。

主备切换策略

可靠性优先策略

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

• 优点：不存在主备数据不一致。
• 缺点：切换流程存在不可用时间。步骤2后，主库A和备库B都处于readonly状态，也就是这时系统处于不可写状态，直到步骤5完成后才能恢复。

实际应用中，建议使用可靠性优先的策略。

可用性优先策略

先4，5调整到最开始执行。

• 优点：几乎没有不可用时间。
• 缺点：可能出现数据主备不一致。

备库并行复制能力

如果备库执行日志的速度（消费能力）持续低于主库生成日志的速度（生产能力），那这个延迟就可能成了小时级别。而且对于一个压力持续比较高的主库来说，备库很可能永远追不上主库的节奏。MySQL5.6之前，MySQL只支持单线程复制，由此主库并发高，TPS高时就会出现严重主备延迟问题。

关于主备的并行复制能力，需要关注的是图中黑色的两个箭头。一个箭头代表客户端写入主库（由于行锁的存在，写入的并发度支持的很高）。另一个箭头代表的是备库上sql_thread执行中转日志relay log。 要提高备库并行复制能力，需要M有SQL sql_thread多线程复制。线程模型：

coordinator在分发的时候，需要满足以下这两个基本要求：
1.不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个worker中。
2.同一个事务不能被拆开，必须放到同一个worker中。

不同部署结构的同步策略

双主结构的同步流程

节点A和节点B互为主备。

这样会存在“循环复制”问题：业务逻辑在节点 A 上更新了一条语句，然后再把生成的 binlog 发给节点 B，节点 B 执行完这条更新语句后也会生成 binlog。

解决方案：

1. 规定两个库的server id必须不同，如果相同，则它们之间不能设定为主备关系；
2. 一个备库接到binlog并重放过程中，生成与原binlog的server id相同的新的binlog;
3. 每个库在收到从自己的主库发过来的日志后，先判断server id, 如果跟自己相同，表示这个日志是自己生成的，直接丢弃这个日志。

一主多从部署

生产环境中，MySQL部署结构往往是一主多从。

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

如果主库发生故障，主备切换问题。

相比于一主一备的切换流程，一主多从结构在切换完成后，A’会成为新的主库，从库 B、C、D 也要改接到 A’。正是由于多了从库 B、C、D 重新指向的这个过程，所以主备切换的复杂性也相应增加了。

一主多从的主备切换有以下两个策略：基于位点的主备切换和基于GTID的主备切换。

基于位点的主备切换

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

1
2
3
4
5
6
7

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_LOG_FILE和MASTER_LOG_POS表示，要从主库的master_log_name文件的master_log_pos这个位置的日志继续同步。而这个位置即“同步位点”， 也就是主库对应的文件名和日志偏移量。

同步位点很难精确取到，只是一个大概位置，找位点的时候，需要找一个“稍微往前”的，然后再通过判断跳过那些在从库B上已经执行过的事务。

以上操作缺点是复杂且容易出错，MySQL5.6 引入GTID彻底解决了这个困难。

基于GTID的主备切换

Global Transaction Identifier, 全局事务ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。它由两部分组成：

1

GTID=server_uuid:gno

其中：

• server_uuid 是一个实例第一次启动时，自动生成的，是一个全局唯一的值；
• gno是一个整数，初始值是1，每次提交事务的时候分配给这个事务，并加1。

在GTID模式下，每个事务都会跟一个GTID一一对应。只需要启动一个MySQL实例的时候，加上参数:

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

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

1
2
3
4
5
6

CHANGE MASTER TO 
MASTER_HOST=$host_name 
MASTER_PORT=$port 
MASTER_USER=$user_name 
MASTER_PASSWORD=$password 
master_auto_position=1 // 表示这个主备关系使用的是GTID协议，取消MASTER_LOG_FILE和MASTER_LOG_POS

我们把实例A’的GTID集合记为set_a, 实例B的GTID集合记为set_b。将A’提升为新的主，B指向A‘的主备切换逻辑：

1. 实例B指定主库A’，基于主备协议建立连接；
2. 实例B把set_b发给主库A’；
3. 实例A’算出set_a与set_b的差集，判断A’本地是否包含了这个差集需要的所有binlog事务；
   a. 如果不包含，表示A’已经把实例B需要的binlog给删除掉了，直接返回错误；
   b. 如果确认全部包含，A’从自己的binlog文件里面，找出第一个不在set_b的事务，发给B；
4. 之后就从这个事务开始，往后读文件，按顺序取binlog发给B去执行。

这个逻辑里面包含了一个设计思想：在基于GTID的主备关系里，系统认为只要建立主备关系，就必须保证主库发给备库的日志是完整的。因此，如果实例B需要的日志已经不存在，A’就拒绝把日志发给B。

这样主备切换实现：只需要B,C,D分别执行change master指向A’即可。严谨的说，主备切换部署不需要找位点了，而是找位点这个工作，在实例A’内部就已经自动完成了。但是由于这个工作是自动的，所以对于维护人员来说非常友好。

双活部署

生产环境引入DBProxy做代理，Client访问数据库，首先通过LVS提供的VIP。主节点在主机房，因此无论是主机房还是从机房流量的写操作，都会落入主机房的DB。从机房是主机房的从库，读是分离的，读一半会走本机房，但是部分读会落到主库，因此会有少部分流量落到主机房。如果事务中的读，都会落到主机房。

可用性目标：

1. 主机房MySQL实例不可用，能够通过Proxy到可用的MySQL实例。
2. 办公网到主机房网络中断，可以同时通过访问备用机房读数据。当主机房挂掉后，Proxy实现自动切换，无需人工接入。

可以看出生产环境的DB可用性是利用一个中间层代理来提升的。简单介绍下代理：
代理主要做负载均衡、读写分离、安全认证、失败重连、连接池、表路由、表hash，还有一些其他的功能，如强制读主库、压测使用shadow表。dbproxy本身是分布式的，自带高可用，其中一台挂掉不会影响业务访问数据库。dbproxy主要是为了高可用和后面的扩展性服务。

另外，双活部署也会存在不一致的问题：
1、因为数据库的主从复制本身是异步的，有一定的延迟。因此写入之后马上读取的时候，可能路由到从库，而读取到错误的数据，这时就需要走强一致的逻辑，直接读主库（/{“router”:”m”}/ select order_id from order_info limit 1）。但是这个操作不能配置到所有的查询上，不然的话从库作为读分流的作用就失效了，并且主库不一定能抗住所有的读操作
2、乱码问题，业务读取数据库数据，发现出现乱码问题。是因为在写入数据和读取数据的时候使用了不同的编码字符集，此时在链接数据库的时候需要显式地指定字符集，使得写入和读取数据的时候使用相同的字符集（显式指定是为了防止后端dbproxy到db的长连接已经被设置过字符集）。正常的一般都选择UTF-8即可，带有表情emoji的使用UTF8mb4

读写分离

读写分离的目标：分担主库压力。

读写分离可以A：客户端端主动做负载均衡买这种模式一般会把DB连接信息放在客户端的连接层，客户端选择DB进行查询。
更成熟的方案是B：在MySQL和客户端之间有一个中间代理层proxy, 客户端只连接proxy, 由proxy根据请求类型和上下文决定请求的分发路由。
带 proxy 的架构，对客户端比较友好。客户端不需要关注后端细节，连接维护、后端信息维护等工作，都是由 proxy 完成的。但这样的话，对后端维护团队的要求会更高。而且，proxy 也需要有高可用架构。因此，带 proxy 架构的整体就相对比较复杂。

读写分离可能遇到的坑

由于主从可能存在延迟，客户端执行完一个更新事务后马上发起查询，如果查询选择的是从库的话，就可能读到刚刚事务更新之前的状态 – “过期读”

强制组主库方案

查询请求可以分为以下两类：
1、对于必须要拿到最新结果的请求，强制将其发到主库上。比如，在一个交易平台上，卖家发布商品以后，马上要返回主页面，看商品是否发布成功。那么，这个请求需要拿到最新的结果，就必须走主库。
2、对于可以读到旧数据的请求，才将其发到从库上。在这个交易平台上，买家来逛商铺页面，就算晚几秒看到最新发布的商品，也是可以接受的。那么，这类请求就可以走从库。

此方案是用的最多的，且我厂线上使用的此方案。

sleep方案

从库sleep 1s，解决1s内的主备延迟。超过1s就无用了。

判断主备无延迟方案

可以通过second_behind_master/比对主备位点/比对主备GTID集合判断。

其他方案还有，配合semi-sync方案，等主库位点方案，等GTID方案。

参考

1. https://time.geekbang.org/column/article/76446
2. https://time.geekbang.org/column/article/77083