MySQL：主备延迟、可靠性优先与可用性优先策略

课程B站大学记录学习极客时间团队MySQL45讲进阶数据分析和数据处理MySQL普通索引和唯一索引MySQL是怎么保证高可用的一、问题背景二、主备延迟seconds_behind_master 的计算三、主备延迟的来源来源一备库机器性能差来源二备库压力大来源三大事务来源四备库的并行复制能力四、可靠性优先策略五、可用性优先策略示例binlog_format mixed若 binlog_format row 呢结论例外场景六、异常切换的效果七、核心总结MySQL是怎么保证高可用的一、问题背景上一讲介绍了 binlog 的基本内容在一个主备关系中每个备库接收主库的 binlog 并执行。正常情况下主库执行更新生成的所有 binlog 都能传到备库并正确执行备库最终能达到与主库一致的状态——这就是最终一致性。但 MySQL 要提供高可用能力只有最终一致性是不够的。高可用的核心在于当主库故障时能否快速、正确地把流量切到备库且尽可能缩短不可用时间、避免数据不一致。双 M 结构的主备切换流程沿用上一讲二、主备延迟主备切换可能是主动运维软件升级、机器按计划下线也可能是被动操作主库机器掉电。无论哪种都需要先理解同步延迟。与数据同步有关的三个时间点主库 A 执行完一个事务、写入 binlog记为T1binlog 传给备库 B备库接收完成的时刻记为T2备库 B 执行完成这个事务记为T3。主备延迟 T3 - T1即同一事务在备库执行完成时间与主库执行完成时间的差值。在备库执行show slave status返回结果中的seconds_behind_master就表示当前备库延迟了多少秒。seconds_behind_master 的计算每个事务的 binlog 里都有一个时间字段记录主库上的写入时间备库取出当前正在执行的事务的时间字段值计算它与当前系统时间的差值得到seconds_behind_master。即该参数计算的就是T3 - T1精度为秒。疑问主备机器系统时间不一致会不会导致延迟值不准不会。备库连接主库时会执行SELECT UNIX_TIMESTAMP()获取主库系统时间若发现不一致计算时会自动扣掉这个差值。说明网络正常情况下日志从主库传到备库的时间T2-T1很短主备延迟主要来自备库接收完 binlog 后执行事务的时间。即备库消费 relay log 的速度慢于主库生产 binlog 的速度。三、主备延迟的来源来源一备库机器性能差常见错误部署认为备库没有请求可以用差一点的机器或把 20 个主库的备库集中在一台机器上。但更新请求对 IOPS 的压力主库和备库是无差别的更新还会触发大量读操作。当备库主机上多个备库争抢资源时就容易出现主备延迟。现状主备可能发生切换备库随时会变成主库因此**主备对称部署同规格机器**已是更常见的做法。来源二备库压力大主库提供写能力备库常被用来分担读压力或跑运营后台的分析语句。由于主库直接影响业务、使用克制反而容易忽视备库的压力控制——备库上大量查询耗尽 CPU拖慢同步速度造成主备延迟。处理方式一主多从多接几个从库分担读压力从库也适合做定期全量备份binlog 输出到外部系统如 Hadoop让外部系统提供统计类查询能力。本文约定HA 过程中可能被选为新主库的称为备库其余称为从库。来源三大事务主库必须等事务执行完成才写 binlog再传给备库。若主库上一个语句执行 10 分钟从库就可能延迟 10 分钟。典型场景一次性 delete 太多数据归档类数据平时不删空间快满时半夜批量删除 → DBA 半夜收到延迟报警。改进控制单事务删除量分多次删除。大表 DDL计划内 DDL 建议使用gh-ost方案见第13讲。来源四备库的并行复制能力这是主备延迟的另一个大方向原因留到第26讲详述。四、可靠性优先策略在双 M 结构下从状态1到状态2主库 A → 备库 B的切换流程判断备库 B 的seconds_behind_master若小于某个阈值如5秒继续否则重试把主库 A 设为只读readonly true等待备库 B 的seconds_behind_master变为 0把备库 B 设为可读写readonly false把业务请求切到备库 B。该流程通常由专门的 HA 系统完成。缺点存在不可用时间。步骤2之后主库、备库都处于 readonly系统不可写直到步骤5完成。其中最耗时的是步骤3可能好几秒所以步骤1要先确保延迟足够小。若一开始延迟就达 30 分钟不做判断直接切换则不可用时间长达 30 分钟业务通常无法接受。五、可用性优先策略若把步骤4、5提前到最开始——不等主备同步、直接把连接切到备库 B 并允许读写系统几乎无不可用时间代价是可能出现数据不一致。示例binlog_format mixed表t自增主键 id初始 3 行数据。业务依次执行insert (4)、insert (5)。假设主备延迟 5 秒插入 c4 后发起主备切换。流程分析主库 A 执行完 insert插入(4,4)开始主备切换因 5 秒延迟备库 B 还没应用插入 c4的中转日志就开始接收客户端插入 c5备库 B 插入(4,5)并把 binlog 发给主库 A备库 B 应用插入 c4的中转日志插入(5,4)而直接在备库执行的插入 c5传到主库 A插入(5,5)。结果主库 A 与备库 B 上出现两行不一致的数据——数据不一致由可用性优先流程导致。若 binlog_format row 呢row 格式记录 binlog 时会记录新插入行的所有字段值最后只会有一行不一致且双方的主备同步应用线程会报duplicate key error并停止——备库 B 的(5,4)和主库 A 的(5,5)都不会被对方执行。数据不一致问题更容易被发现。结论row 格式更易暴露数据不一致mixed / statement 格式可能悄悄不一致过久才发现时往往已不可查或连带引发更多逻辑不一致。可用性优先策略会导致数据不一致因此大多数情况建议使用可靠性优先策略——对数据服务而言数据可靠性一般优于可用性。例外场景存在可用性优先级更高的场景例如某库仅记录操作日志不一致可通过 binlog 修补短暂不一致不引发业务问题业务系统依赖该日志写入逻辑库不可写会导致线上操作无法执行。此时可选择先强行切换、事后补数据策略。事后改进让业务逻辑不依赖这类日志写入日志写入模块可降级如写本地文件或临时库从而仍可使用可靠性优先策略。六、异常切换的效果若主库 A 掉电、备库 B 延迟已达 30 分钟HA 系统要切换 B 为主库。此时别无选择采用可靠性优先策略必须等备库 B 的seconds_behind_master 0才能切换。在此期间系统处于完全不可用状态主库已掉电、连接未切到备库。若直接切换但保持 B 只读也不行中转日志尚未应用完客户端查询会看不到已执行完成的事务误认为数据丢失——虽然后续会随日志应用恢复但部分业务无法接受这种暂时丢失状态。核心结论在满足数据可靠性的前提下MySQL 高可用系统的可用性依赖于主备延迟——延迟越小主库故障时服务恢复越快可用性越高。七、核心总结主题关键点建议主备延迟T3 - T1即 seconds_behind_master精度秒自动校正主备时钟差延迟来源1备库机器性能差 / 多备库争抢资源主备对称部署、同规格机器延迟来源2备库压力大读/分析抢占CPU一主多从、binlog 输出到外部系统延迟来源3大事务批量 delete、大表 DDL分批删除、gh-ost 做 DDL延迟来源4备库并行复制能力见第26讲可靠性优先等延迟趋近0再切换推荐数据可靠为底线可用性优先不等同步直接切换可能数据不一致仅限特殊场景