notebook
yubin
2024-03-22
目录

MySQL 一主多从

参考：

大多数互联网应用都是读多写少，而解决读性能问题的方案就是：一主多从架构。

下图展示了 MySQL 的一主多从结构：

image-20240322165247456

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

下面首先介绍一主多从架构下，主库故障后的主备切换问题，之后介绍读写分离的相关知识。

# 1. 主库故障后的主备切换

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

下面看看如何完成一主多从的主备切换。

# 1.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
1
2
3
4
5
6
7

这条命令有这么 6 个参数：

  • MASTER_HOST、MASTER_PORT、MASTER_USER 和 MASTER_PASSWORD 四个参数，分别代表了主库 A’的 IP、端口、用户名和密码。
  • 最后两个参数 MASTER_LOG_FILE 和 MASTER_LOG_POS 表示，要从主库的 master_log_name 文件的 master_log_pos 这个位置的日志继续同步。而这个位置就是我们所说的同步位点，也就是主库对应的文件名和日志偏移量。

所以，如果想要执行 change master，我们必须要传入同步位点参数，但我们之前记录的是旧主库的同步位点，并不知道新主库的同步位点，所以，从库 B 的切换需要先经过“找同步位点”这个逻辑。

而人工去找这个同步位点存在很多困难，我们往往只能找到一个“稍微靠前”的不那么精准的位点来开始同步，然后再通过判断跳过那些已经执行过的事务。

但实际上，在一些边界情况中，B 的主库从 A 切到 A' 可能会导致重复执行一些事务，而这些事务的重复执行可能会导致“主键重复”、“主键未找到”这类错误，所以在切换任务时，我们需要显式忽略这些错误。具体做法可以参考原文章。

# 1.2 GTID

前面说的通过忽略事务错误来建立主备关系的方法虽然可以成功，但操作很复杂且容易出错，因此 MySQL 5.6 版本引入了 GTID 来彻底解决这个问题。

# 1.2.1 GTID 是什么

GTID 的全称是 Global Transaction Identifier，也就是全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。格式为：

GTID = server_uuid:gno
1

其中：

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

这里的 gno 不同于事务 ID，事务 ID 是每次创建一个事务都会分配，而这个 gno 则是在事务提交时创建的。

# 1.2.2 启动 GTID 模式

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

# 1.2.3 事务的 GTID 的生成方式

在 GTID 模式下，每个事务关联一个 GTID，这个 GTID 有两种生成方式，具体使用哪一种取决于 session 的 gtid_next 值：

  • 如果 gtid_next = automatic，代表使用默认值。这时 MySQL 会把一个创建一个新的 GTID 分配给这个事务，具体流程是：
    1. 记录 binlog 时，先记录一行：SET @@SESSION.GTID_NEXT='server_uuid:gno'
    2. 然后把这个 GTID 加入到本 MySQL 实例的 GTID 集合中
  • 如果 gtid_next 是一个指定的 GTID 值 A，那么有两种可能：
    1. 如果值 A 已经存在于该实例的 GTID 集合中，那接下来要执行的这个事务会直接被忽略
    2. 如果值 A 没有存在于实例的 GTID 集合中，那就将这个值 A 分配给接下来要执行的事务

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

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

# 1.3 基于 GTID 的主备切换

下面看一下基于 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
1
2
3
4
5
6

其中，master_auto_position=1 就表示这个主备关系使用的是 GTID 协议。可以看到，前面让我们头疼不已的 MASTER_LOG_FILE 和 MASTER_LOG_POS 同步位点参数，已经不需要指定了。

如果我们把此时实例 A’ 的 GTID 集合记为 ，实例 B 的 GTID 集合记为 ，那看一下主备切换的逻辑。

我们在实例 B 上执行 start slave 命令，取 binlog 的逻辑如下：

  1. 实例 B 指定主库 A'，基于主备协议建立连接
  2. 实例 B 把 发送给主库 A'
  3. 主库 A' 计算出 与 的差集，对于差集中的所有元素：
    • 如果 A' 中已经把 B 需要的 binlog 删除掉了，那直接返回错误
    • 如果 A' 确认还存在所有 B 需要的 binlog，就找出第一个差集中的事务并发送给 B
  4. 之后就从这个事务开始，顺序往后取出 binlog 发送给 B

可以看出，基于 GTID 的主备切换不再需要找同步位点了，只需要从库执行 change master 命令来执行主库即可。

# 1.4 小结

从上面讨论可以看到，一主多从模式下，如果你的 MySQL 版本支持 GTID 的话，都建议尽量使用 GTID 模式来做一主多从的主备切换。

# 2. 读写分离有哪些坑？

这一大节主要讨论一下一主多从架构的应用场景：读写分离。读写分离的主要目标就是分摊主库的压力。

读写分离的架构有两种，区别在于负载均衡由谁来做：

  1. 一种是 client 主动做负载均衡。由 client 来选择所要连接的数据库实例：
image-20240322165247456
  1. 一种是由中间代理层 proxy 来实现负载均衡。MySQL 与 client 的中间代理层 proxy 来实现请求的分发：
image-20240322204439836

两种架构的优缺点也很明显：

  • 客户端直连的方案性能更好一点，但由 client 维护各个 MySQL 实例的连接信息会产生信息的冗余
  • 带 proxy 的架构对于 client 更加友好

目前来看，趋势是往带 proxy 的架构方向发展的。

但由于主从延迟，在从库上的读取可能会读取到过期状态，这种现象称为过期读。

主从延迟是不能 100% 避免的，但我们还是尽力处理好这个问题。加下来就来讨论如何处理过期读问题。

本文介绍的处理过期读的方案汇总如下：

  • 强制走主库方案；
  • sleep 方案；
  • 判断主备无延迟方案；
  • 配合 semi-sync 方案；
  • 等主库位点方案；
  • 等 GTID 方案。

# 2.1 强制走主库的方案

强制走主库的方案其实就是根据查询请求：

  • 对于必须要拿到最新结果的查询请求，强制将其发到主库上
  • 对于可以读到旧数据的请求，才将其发送到从库上

这个方案是使用最多的。但问题在于可能会遇到“所有查询都不能过期读”的需求，比如金融类的业务，这种情况就需要其他方案了。

# 2.2 Sleep 方案

Sleep 方案是指：读从库之前先执行一条类似起到 select sleep(1) 效果的命令。这个方案的假设是：大多数情况下主备延迟在 1s 之内。

一个使用这个方案的案例：电商中卖家发布商品时，在卖家点击发布后，前端在发出 AJAX 请求后，接着把新商品添加到前端页面上，而不是真正去等数据库做查询。这样等卖家之后再刷新页面

时，其实已经过了一段时间了，这也达到了 sleep 的效果。

所以 sleep 方案并不一定真的执行 sleep，其核心思想在于查备库前先等一等。

这个方案的问题在于不严谨，因为我们并没有去判断主备延迟的具体时间，接下来看一个更准确的方案。

# 2.3 判断主备无延迟的方案

要确保主备无延迟，通常有三种做法。

从前面的文章我们直到，show slave status 结果里的 seconds_behind_master 参数的值，可以用来衡量主备延迟时间的长短。

# 2.3.1 方案 1：借助 seconds_behind_master

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

seconds_behind_master 的单位是秒，如果觉得精度还不够的话，就需要使用下面的方案。

# 2.3.2 方案 2：对比位点确保主备无延迟

下图是 show slave status 的结果截图：

image-20240406100741277
  • Master_Log_File 和 Read_Master_Log_Pos，表示的是读到的主库的最新位点；
  • Relay_Master_Log_File 和 Exec_Master_Log_Pos，表示的是备库执行的最新位点。

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

# 2.3.3 方案 3：对比 GTID 集合确保主备无延迟

也是看上图：

  • Auto_Position=1 ，表示这对主备关系使用了 GTID 协议。
  • Retrieved_Gtid_Set，是备库收到的所有日志的 GTID 集合；
  • Executed_Gtid_Set，是备库所有已经执行完成的 GTID 集合。

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

可见，对比位点和对比 GTID 这两种方法，都要比判断 seconds_behind_master 是否为 0 更准确。

# 2.3.4 三种方案的小总结

这三种在查询前先判断从库是否同步完成的方法，相比于 sleep 方案确实准确度提升了不少，但还没有达到“精确”的程度，因为我们上面判断主备无延迟的逻辑，是在判断“备库收到的日志是否都执行了”，但存在 client 已经收到事务的提交确认，但备库还没有到主库发来的日志的情况。所以仍有可能出现过期读的现象。

# 2.4 配合 semi-sync

要解决这个问题，就要引入半同步复制，也就是 semi-sync replication。

semi-sync 做了这样的设计：

  1. 事务提交的时候，主库把 binlog 发给从库；
  2. 从库收到 binlog 以后，发回给主库一个 ack，表示收到了；
  3. 主库收到这个 ack 以后，才能给客户端返回“事务完成”的确认。

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

这样，semi-sync 配合前面关于位点的判断，就能够确定在从库上执行的查询请求，可以避免过期读。

但是 semi-sync 只在一主一从的情况下成立，当有多个从节点时，主节点只等一个节点的 ack 就提交确认，仍可能存在过期读现象。所以，semi-sync 判断主备无延迟的方案存在两个问题：

  1. 一主多从的时候，在某些从库执行查询请求会存在过期读的现象；
  2. 在持续延迟的情况下，可能出现过度等待的问题。

夏敏的等主库位点的方案，就可以解决这两个问题。

# 2.5 等主库位点的方案

// TODO

# 3. 如何判断一个数据库实例是否出问题了

在主从架构中，我们需要判断数据库实例是否出现问题从而决定是否进行主备切换。

那这一节的问题就是：如何判断一个主库出问题了呢？

# 3.1 SELECT 1 判断

很多人用 SELECT 1 来判断主库是否可用。SELECT 1 能成功返回只能说明这个实例的进程还在，并不能说明主库没问题。

::: info 并发连接与并发查询 这里先区分两个概念：并发连接和并发查询：

  • 并发连接是指：连接到 MySQL 实例的并发数量
  • 并发查询是指：同时在 MySQL 实例中执行查询的并发数量

MySQL 参数 innodb_thread_concurrency 控制了 InnoDB 并发查询线程的上限，通常设置为 64~128，但这可能支持上千的并发连接，因为连接多了只是占用内存多一点而已，而并发查询太高才是 CPU 杀手。

MySQL 会计算并发查询的线程，当连接的线程进入锁等待后，并发线程的计数会减一，并不算在并发查询的计数里面。 :::

假如说当前 MySQL 实例的并发查询数量太高，这时候新的查询无法得到响应了，因为同时在执行的语句超过了设置的 innodb_thread_concurrency 的值，这时候系统其实已经不行了，但是通过 SELECT 1 来检测系统，会认为系统还是正常的。

# 3.2 查表判断

为了能够检测 InnoDB 并发线程数过多导致的系统不可用情况，我们需要找一个访问 InnoDB 的场景。

一般做法是：在系统库（mysql 库）中创建一个表，比如命名为 health_check，里面只放一行数据，然后定期执行 SELECT * FROM mysql.health_check。

使用这个方法，我们可以检测出由于并发线程过多导致的数据库不可用的情况。但这种方法的问题是：如果空间满了之后，这种做法又变得不好用了。

# 3.3 更新判断

如果要通过更新来判断，常见做法就是放一个 timestamp 字段，用来表示最后一次执行检测的时间，健康检测定期执行的语句就是：

update mysql.health_check set t_modified=now();
1

节点可用性的检测都应该包含主库和备库。如果用更新来检测主库的话，那么备库也要进行更新检测。由于我们往往把两个数据库实例的主备关系设计为双 M 结构，所以备库 B 上执行的检测命令写下的 binlog 也会发回给主库 A，但是如果主备使用相同的更新命令，就有可能会出现行冲突，所以 mysql.health_check 表就应该存入多行数据，并用不同库的 server id 作为主键，每个库的健康检测就是更新不同的行。

更新判断是一个常见的方案，但仍然存在问题。这种方案毕竟还是外部检测 MySQL，是存在随机性的，可能 MySQL 实例已经压力很大了，偶然执行成功了健康检测导致管理员认为实例还是正常，但业务系统上的正常 SQL 已经执行很慢了。这也是为什么“判定慢”是一个大问题。接下来介绍从 MySQL 内部来发现数据库问题的方法。

# 3.4 内部统计

MySQL 5.6 版本后提供的 performance_schema 库记录了 MySQL 的运行统计数据。

这个库有很多统计指标，如果打开所有指标会产生性能损耗（大约会性能下降 10%）。我们建议只打开自己需要的统计项。比如如果要打开 redo log 的时间监控，你可以执行这个语句：

mysql> update setup_instruments set ENABLED='YES', Timed='YES' where name like '%wait/io/file/innodb/innodb_log_file%';
1

假设，现在你已经开启了 redo log 和 binlog 这两个统计信息，那要怎么把这个信息用在实例状态诊断上呢？

很简单，你可以通过 MAX_TIMER 的值来判断数据库是否出问题了。比如，你可以设定阈值，单次 IO 请求时间超过 200 毫秒属于异常，然后使用类似下面这条语句作为检测逻辑。

mysql> select event_name,MAX_TIMER_WAIT  FROM performance_schema.file_summary_by_event_name where event_name in ('wait/io/file/innodb/innodb_log_file','wait/io/file/sql/binlog') and MAX_TIMER_WAIT>200*1000000000;
1

发现异常后，取到你需要的信息，再通过下面这条语句：

mysql> truncate table performance_schema.file_summary_by_event_name;
1

把之前的统计信息清空。这样如果后面的监控中，再次出现这个异常，就可以加入监控累积值了。

# 3.5 小结

这一节介绍了检测一个 MySQL 实例健康状态的几种方法，以及这些方法存在的问题。

SELECT 1 的方法存在很多问题，但实际上使用很广泛。

之后的改进方法都会增加额外损耗，我们需要根据实际情况来做权衡，个人比较倾向的方案是：优先考虑 update 系统表，然后再配合增加检测 performance_schema 的信息。

编辑 (opens new window)
上次更新: 2024/04/06, 04:00:44
MySQL 的主备架构
误删数据后怎么办

← 误删数据后怎么办→

最近更新
01
Deep Reinforcement Learning
10-03
02
误删数据后怎么办
04-06
03
代理模式
03-03
更多文章>
Theme by Vdoing | Copyright © 2021-2024 yubincloud | MIT License
×