罗罗编程学习记录
本页内容 展开查看

05.2.2.5 复制、高可用与运维监控

所属:全栈开发面试题库 / 05 数据库与存储 / 05.2 MySQL / 05.2.2 SQL 优化与高可用运维

覆盖范围

  • 主从复制流程、binlog/relay log/GTID
  • 主从延迟、半同步、读写分离一致性
  • 高可用切换、MGR 组复制、分库分表
  • 备份恢复、Online DDL 与迁移双写
  • 核心监控指标、故障定位与容量规划

高频题速览

编号 题目 难度 频率
05.2.2.5-001 主从复制工作流程 进阶 ★★★★★
05.2.2.5-003 主从延迟原因与解决 进阶 ★★★★★
05.2.2.5-006 主从切换与高可用方案 高级 ★★★★☆
05.2.2.5-011 备份恢复方案 进阶 ★★★★☆
05.2.2.5-013 核心监控指标 进阶 ★★★★☆

复制与高可用主线

主库写 binlog → IO 线程拉取写 relay log → SQL 线程重放
  延迟来源:网络 / 单线程回放 / 大事务 / 慢查询
  高可用:故障检测 → 选主仲裁 → 切换 VIP/配置 → 应用路由

05.2.2.5-001 MySQL 主从复制流程是什么?

频率 ★★★★★ | 难度 进阶

一句话结论

主库把变更写 binlog,从库 IO 线程拉取 binlog 写入 relay log,SQL 线程按 relay log 重放变更;复制基于 binlog 位点/GTID,异步把主库数据同步到从库。

概念解释

  • binlog:主库记录逻辑变更日志。
  • relay log:从库缓存拉取到的主库日志。
  • IO/SQL 线程:IO 拉日志,SQL 重放。

原理流程

主库:事务提交 → 写 binlog
从库:IO 线程拉取 binlog → 写 relay log
      SQL 线程读取 relay log → 重放到从库数据

代码 / 命令

SHOW MASTER STATUS;
SHOW REPLICA STATUS\G   -- 旧版为 SHOW SLAVE STATUS

常见误区

  • 误区:复制是实时同步。正解:默认异步,存在延迟窗口,从库读到可能不是最新数据。
  • 误区:从库重放的是数据页。正解:重放 binlog 记录的行/语句变更,依赖复制格式。

追问

  • 问:复制格式有哪几种?答:statement、row、mixed,记录粒度与回放行为不同。
  • 问:GTID 解决什么?答:用事务全局标识替代位点定位,便于切换与自愈。

05.2.2.5-002 binlog、relay log 与 GTID 各自作用?

频率 ★★★★☆ | 难度 进阶

一句话结论

binlog 记录主库变更供复制与恢复;relay log 是从库中转的主库日志;GTID 是事务全局唯一标识,定位已执行事务、支持故障切换自动找位点。

概念解释

  • binlog:逻辑日志,复制与 PITR 基础。
  • relay log:从库临时存放待重放日志。
  • GTID:server_uuid:transaction_id,标识全局事务。

原理流程

  1. 主库事务提交生成 binlog 事件并分配 GTID。
  2. 从库拉取事件写入 relay log。
  3. SQL 线程按 GTID/位点重放,记录已执行 GTID 集合。

代码 / 命令

SHOW MASTER STATUS;
SELECT @@gtid_executed;

常见误区

  • 误区:relay log 是从库自己的 binlog。正解:它是中转日志,从库自身 binlog 独立配置。
  • 误区:GTID 自动解决所有复制冲突。正解:它简化位点管理,数据冲突仍需应用/一致性方案处理。

追问

  • 问:GTID 跨库迁移怎么用?答:按已执行 GTID 集合确定复制起点。
  • 问:binlog 三种格式对 GTID 兼容性?答:具体限制以版本手册为准,混合/行格式常见。

05.2.2.5-003 主从延迟由哪些原因造成?怎么解决?

频率 ★★★★★ | 难度 进阶

一句话结论

延迟来自网络传输、从库回放单线程瓶颈、大事务/慢查询在从库重放、从库负载高、锁竞争;解决包括并行回放、拆分大事务、降低从库压力、监控延迟并按一致性要求路由读写。

概念解释

  • 回放瓶颈:传统单 SQL 线程串行重放,主库并发写被串行化。
  • 大事务:一个长事务在从库回放耗时久。
  • 从库负载:读查询挤占回放资源。

原理流程

  1. 对比主库位点与从库回放位点计算延迟。
  2. 定位是传输、回放还是大事务/负载导致。
  3. 并行复制、拆分事务、隔离负载后复测。

代码 / 命令

SHOW REPLICA STATUS\G   -- Seconds_Behind_Source

常见误区

  • 误区:延迟只看网络带宽。正解:回放能力与大事务往往是主因。
  • 误区:开启并行复制就没有延迟。正解:并行度受事务依赖与组提交约束,仍需控制大事务。

追问

  • 问:并行复制按什么维度并行?答:基于组提交/库粒度等策略,具体模式随版本以手册为准。
  • 问:延迟监控看什么指标?答:位点差、Seconds_Behind、未回放队列长度。

05.2.2.5-004 半同步复制是什么?代价如何?

频率 ★★★★☆ | 难度 高级

一句话结论

半同步要求主库事务提交后至少等一个从库确认收到日志才返回成功,降低数据丢失窗口;代价是提交等待网络往返,吞吐下降,需在一致性与延迟间权衡。

概念解释

  • 异步复制:主库提交即返回,不等从库。
  • 半同步:至少一从库确认收到 binlog 后提交返回。
  • 超时降级:确认超时后退化为异步,行为按配置。

原理流程

  1. 主库事务提交,等待从库 ACK。
  2. 收到确认后返回客户端成功。
  3. 超时未收到则按策略降级异步。

代码 / 命令

SHOW VARIABLES LIKE '%semi_sync%';

常见误区

  • 误区:半同步等于强一致同步复制。正解:它只确认日志收到,不等从库重放完成,仍有窗口。
  • 误区:半同步零性能代价。正解:提交路径多一次网络等待,高并发下吞吐受影响。

追问

  • 问:半同步与全同步区别?答:全同步等待从库应用完成,延迟更高;半同步只确认接收。
  • 问:从库宕机半同步会怎样?答:按超时配置降级异步,避免主库长期阻塞。

05.2.2.5-005 读写分离怎么保证读一致性?

频率 ★★★★☆ | 难度 高级

一句话结论

写主读从会读到延迟旧数据;一致性要求高的场景读主库,或按 GTID/位点等待从库追上再读,写后短时间内的读路由到主,其他容忍延迟的读走从库。

概念解释

  • 延迟窗口:主从异步复制,从库非最新。
  • 读主策略:强一致读走主库。
  • 追平等待:按位点/GTID 确认从库已回放再路由。

原理流程

  1. 写请求落主库并记录位点。
  2. 后续读按一致性要求选择路由。
  3. 读从前库前确认位点追平,否则读主或等待。

代码 / 命令

-- 对比主库位点与从库回放位置判断是否追平
SHOW MASTER STATUS;
SHOW REPLICA STATUS\G

常见误区

  • 误区:读写分离后所有读都走从库。正解:写后即时读与强一致读需走主或等追平。
  • 误区:从库延迟可以忽略。正解:业务读到旧数据会引发库存/余额类错误感知。

追问

  • 问:如何实现"读自己写"?答:写后短时间窗口内读主,或会话级粘主路由。
  • 问:从库一致性有哪些校验手段?答:定期行数/内容一致性比对,结合延迟监控。

05.2.2.5-006 主从切换与高可用方案怎么设计?

频率 ★★★★☆ | 难度 高级

一句话结论

高可用由故障检测、仲裁选主、自动切换、应用路由更新组成;常见方案基于 VIP/配置中心/代理层切换,切换前确认数据追平,避免脑裂与旧主回写。

概念解释

  • 故障检测:心跳与健康检查判断主库不可用。
  • 仲裁选主:多数派投票选新主,防脑裂。
  • 路由切换:更新 VIP/代理/配置使应用连新主。

原理流程

  1. 检测主库故障并进入仲裁。
  2. 选数据最新的合格从库提升为主。
  3. 应用路由切到新主,旧主隔离防回写。

代码 / 命令

方案要点:健康检查 / 仲裁多数派 / 追平确认 / 路由切换 / 旧主隔离

常见误区

  • 误区:高可用切换不会丢数据。正解:异步复制下故障瞬间可能有未同步事务,需按 RPO 设计。
  • 误区:双主同时对外写。正解:双写/双主需防脑裂,常规方案一主多从。

追问

  • 问:脑裂怎么防止?答:仲裁多数派 + 旧主 fencing(隔离/拒绝写入)。
  • 问:切换时应用怎么感知?答:代理层或配置中心更新连接,应用重连新主。

05.2.2.5-007 MGR 组复制是什么?

频率 ★★☆☆☆ | 难度 高级

一句话结论

MGR(Group Replication)基于组通信与多数派提交,提供多节点复制与故障自动选主;可单主/多主模式,成员需满足网络与一致性前提,选型时评估成熟度与运维复杂度(具体版本能力以官方文档为准,待确认)。

概念解释

  • 组通信:成员间消息传递与成员感知。
  • 多数派提交:事务需多数节点认可。
  • 单主/多主:单主对外写,多主模式写冲突控制要求高。

原理流程

  1. 成员组成复制组,交换事务与状态。
  2. 事务在组内冲突检测后多数派提交。
  3. 成员故障自动剔除并重新选主。

代码 / 命令

-- 待确认:MGR 插件状态与配置语法以目标版本官方手册为准

常见误区

  • 误区:MGR 多主可以随意并发写。正解:多主冲突检测严格,热点冲突易失败。
  • 误区:MGR 替代所有主从方案。正解:它解决组内高可用,跨机房/大规模仍需评估架构。

追问

  • 问:MGR 与传统主从区别?答:组内自动成员管理与多数派协调,非单一主从复制拓扑。
  • 问:选型 MGR 前要验证什么?答:网络延迟、成员规模、冲突场景与版本成熟度。

05.2.2.5-008 分库分表解决什么?带来什么问题?

频率 ★★★★☆ | 难度 高级

一句话结论

单表数据量/写入压力超阈值时按维度分库分表分散存储与负载;代价是跨库事务、跨片聚合、全局 ID、扩容迁移与路由复杂度上升,应在单库优化触顶后再拆。

概念解释

  • 水平拆分:按分片键把数据分散到多库多表。
  • 分片键:决定数据落点,需结合查询模式。
  • 分布式代价:跨片 JOIN/聚合/事务、扩容再平衡。

原理流程

  1. 评估单表容量与写入瓶颈,确定分片维度。
  2. 设计路由规则与全局 ID。
  3. 迁移双写校验,上线后处理跨片查询与扩容。

代码 / 命令

拆分决策:容量/写入触顶 → 选分片键 → 路由层 → 迁移双写校验 → 跨片查询方案

常见误区

  • 误区:分库分表是数据库优化第一步。正解:应先做索引/SQL/缓存优化,触顶再拆。
  • 误区:分片键随意选。正解:热点倾斜与跨片查询多由分片键不当导致。

追问

  • 问:全局 ID 方案有哪些?答:号段、雪花算法等,需保证唯一与趋势有序,按生态选型。
  • 问:跨片聚合怎么做?答:路由层合并各片结果,或汇总到分析库,避免大跨度跨片查询。

05.2.2.5-009 分区表与分库分表有什么区别?

频率 ★★★☆☆ | 难度 进阶

一句话结论

分区表是单库内按规则把一张表物理拆成多个分区,对应用透明,由优化器做分区裁剪;分库分表是跨库跨表水平拆分,需路由层处理,解决容量与实例级压力。

概念解释

  • 分区表:逻辑一张表,物理多分区,裁剪消除无关分区。
  • 分库分表:多实例多表,应用/中间件路由。
  • 适用边界:分区缓解单表体积,分库分表分散实例负载。

原理流程

  1. 分区表按范围/列表/哈希划分,查询时裁剪。
  2. 分库分表按分片键路由到目标库表。
  3. 两者可组合,但复杂度递增。

代码 / 命令

-- 分区裁剪:WHERE 命中分区规则只扫描相关分区
EXPLAIN SELECT * FROM orders WHERE created_at >= '2026-01-01';

常见误区

  • 误区:分区表能提升并发写入能力。正解:分区仍在单实例,主要改善大表扫描与维护,不分散实例压力。
  • 误区:分区裁剪自动生效。正解:查询条件必须命中分区规则,否则扫全部分区。

追问

  • 问:分区类型有哪些?答:范围、列表、哈希等,按数据分布与查询选择。
  • 问:分区表有什么维护坑?答:分区键约束、跨分区查询与全局索引限制,具体以手册为准。

05.2.2.5-010 数据迁移与双写怎么做?

频率 ★★★☆☆ | 难度 高级

一句话结论

大版本/跨库迁移采用:存量全量导出导入 + 增量同步 + 双写/校验比对 + 灰度切流;双写期新旧库并行,校验一致后切读切写,保留回滚窗口。

概念解释

  • 全量迁移:历史数据批量搬到目标库。
  • 增量同步:迁移期间持续同步新变更。
  • 双写校验:新旧库并行写入,比对一致性。

原理流程

  1. 全量迁移存量数据。
  2. 增量同步追平迁移期间变更。
  3. 双写校验一致后灰度切流,保留回滚。

代码 / 命令

迁移流程:全量 → 增量追平 → 双写比对 → 灰度切读 → 切写 → 观察回滚窗口

常见误区

  • 误区:直接停机拷贝最安全。正解:停机窗口业务不可用,在线迁移需增量与校验。
  • 误区:双写成功就代表数据一致。正解:需比对内容与约束,双写失败需补偿。

追问

  • 问:迁移期间索引怎么建?答:目标库先按设计建索引,避免迁移后批量建索引影响写入。
  • 问:如何回滚?答:切流前保留旧库写入与同步链路,异常时切回。

05.2.2.5-011 备份与恢复方案怎么设计?

频率 ★★★★☆ | 难度 进阶

一句话结论

备份分全量与增量/日志备份,恢复依赖全量备份 + binlog 时间点回放;定期演练恢复验证可用性,明确 RPO/RTO,备份异地存放并校验可恢复。

概念解释

  • 全量备份:某时点完整数据快照。
  • 日志备份:binlog 记录增量变更,支撑 PITR。
  • 恢复演练:定期实际恢复验证备份可用。

原理流程

  1. 周期全量备份 + 持续 binlog 归档。
  2. 故障时恢复全量快照,回放 binlog 到目标时间点。
  3. 校验数据一致性后对外。

代码 / 命令

-- 备份工具与恢复流程按团队选型;binlog 归档是 PITR 基础
SHOW BINARY LOGS;

常见误区

  • 误区:备份文件存在就等于可恢复。正解:必须定期演练恢复,备份损坏/缺日志才会暴露。
  • 误区:只做全量备份。正解:无 binlog 增量只能恢复到备份时点,丢失之后数据。

追问

  • 问:RPO/RTO 怎么定?答:RPO 允许丢失数据窗口,RTO 恢复时长目标,按业务等级设计。
  • 问:备份对线上有影响吗?答:备份占用 IO/CPU,需低峰执行并评估资源。

05.2.2.5-012 Online DDL 与 gh-ost 类工具怎么选?

频率 ★★★☆☆ | 难度 高级

一句话结论

在线 DDL 避免长锁表阻塞业务;原生 Online DDL 按操作类型支持原地/重建,第三方工具(如 gh-ost 类)通过影子表+binlog 同步变更;大表变更评估锁、复制延迟与回滚方案。

概念解释

  • 原地(in-place):不整表拷贝,锁阶段短。
  • 重建(copy):新建表拷贝数据,代价高。
  • 影子表工具:旁路建表、追 binlog、rename 切换。

原理流程

  1. 评估变更类型与表大小,选原生或第三方工具。
  2. 变更过程监控 MDL、锁等待与复制延迟。
  3. 切换前确认无阻塞,异常可回滚。

代码 / 命令

-- 待确认:具体 DDL 算法/锁参数以目标版本手册为准
ALTER TABLE goods ADD COLUMN ext VARCHAR(32), ALGORITHM=INPLACE, LOCK=NONE;

常见误区

  • 误区:Online DDL 全程不锁表。正解:元数据锁阶段仍需短暂排他,长事务会卡住切换。
  • 误区:第三方工具零风险。正解:需监控同步延迟与磁盘空间,切换仍有短暂 MDL 窗口。

追问

  • 问:大表加索引为什么慢?答:需遍历全表构建索引,占用 IO 与空间,期间写入需同步维护。
  • 问:DDL 被长事务卡住怎么办?答:先结束阻塞长事务,再执行变更,避免 MDL 堆积。

05.2.2.5-013 MySQL 必须监控哪些核心指标?

频率 ★★★★☆ | 难度 进阶

一句话结论

监控覆盖:连接数与线程、QPS/TPS、慢查询、缓冲池命中率、锁等待/死锁、主从延迟、磁盘 IO 与空间、binlog/undo 增长、复制状态;按阈值告警并关联业务负载。

概念解释

  • 容量指标:连接数、磁盘空间、表大小。
  • 性能指标:QPS、慢查询、缓冲池命中、IO 等待。
  • 可用性指标:复制延迟、锁等待、死锁、主从状态。

原理流程

  1. 采集实例状态与性能视图指标。
  2. 按基线设阈值告警。
  3. 异常关联慢日志、锁视图与负载排查。

代码 / 命令

SHOW GLOBAL STATUS LIKE 'Threads_%';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_%';
SHOW REPLICA STATUS\G

常见误区

  • 误区:只监控 CPU/内存。正解:数据库专项指标(锁、延迟、缓冲池、空间)才反映真实瓶颈。
  • 误区:指标越高越好。正解:需结合基线与业务时段,单看绝对值无意义。

追问

  • 问:缓冲池命中率低说明什么?答:热点数据未驻留内存,磁盘读增多,需评估内存配置。
  • 问:连接数告警怎么排查?答:看活跃连接来源与长事务,区分连接泄漏与突发流量。

05.2.2.5-014 常见数据库故障如何定位?

频率 ★★★☆☆ | 难度 进阶

一句话结论

按现象分流:连接堆积查连接数与阻塞;响应慢查慢日志/计划/锁等待;主从异常查复制状态与延迟;空间告警查大表/binlog/undo;崩溃恢复看错误日志与 redo/binlog。

概念解释

  • 连接类:连接耗尽、阻塞排队。
  • 性能类:慢查询、锁等待、IO 饱和。
  • 复制/存储类:延迟、断开、空间不足。

原理流程

  1. 按现象分类定位指标与日志。
  2. 结合会话、锁、复制视图找根因。
  3. 先止损(限流/切流/终止阻塞),再根治。

代码 / 命令

SHOW PROCESSLIST;
SHOW REPLICA STATUS\G
SHOW ENGINE INNODB STATUS\G

常见误区

  • 误区:故障先重启实例。正解:先抓现场(会话/锁/日志),重启会丢失阻塞现场。
  • 误区:磁盘满只删日志。正解:需定位增长源(大表/binlog/undo),避免重复踩满。

追问

  • 问:主从断开怎么恢复?答:查错误日志定位中断原因,按 GTID/位点重连,校验数据一致。
  • 问:连接被打满怎么应急?答:识别并终止阻塞/泄漏会话,限流入口,再根治慢事务。

05.2.2.5-015 关键参数调优有哪些关注点?

频率 ★★★☆☆ | 难度 进阶

一句话结论

核心参数关注缓冲池大小、日志/刷盘策略、连接数、排序/临时表内存、慢查询阈值与并行复制;调参基于负载基线与实测,不照搬通用模板,改动灰度验证。

概念解释

  • 内存类:缓冲池、排序/临时表内存。
  • IO 类:redo/binlog 刷盘策略。
  • 并发类:连接数、复制并行度。

原理流程

  1. 按 workload 识别瓶颈指标。
  2. 对照相关参数调整,小步灰度。
  3. 复测性能与稳定性,记录基线。

代码 / 命令

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';

常见误区

  • 误区:参数照搬网上最优值。正解:参数依赖硬件、数据量与负载,必须本机实测。
  • 误区:调大内存参数一定更快。正解:过度占用挤压系统/其他进程,需留余量。

追问

  • 问:缓冲池大小怎么定?答:按可用内存与数据热点规模估算,常见占物理内存一定比例,实测命中为准。
  • 问:刷盘策略影响什么?答:影响持久化保证与写入吞吐,按一致性要求选择。

05.2.2.5-016 安全与权限管理要点有哪些?

频率 ★★☆☆☆ | 难度 进阶

一句话结论

按最小权限授权,区分应用/管理/只读账号,定期回收多余权限;禁止应用超管账号,敏感数据脱敏与审计,连接加密,口令与访问来源受控。

概念解释

  • 最小权限:账号只拥有业务所需库表与操作。
  • 账号分类:写账号、只读账号、管理账号分离。
  • 审计合规:敏感操作可追溯,访问来源限制。

原理流程

  1. 按业务角色建账号与权限。
  2. 定期审计授权与过期账号。
  3. 敏感访问审计与脱敏。

代码 / 命令

SHOW GRANTS FOR 'app'@'%';

常见误区

  • 误区:应用账号用 root/全库权限省事。正解:泄露后风险极大,应最小授权。
  • 误区:权限一次授予长期有效。正解:业务变更后需回收不再使用的权限。

追问

  • 问:生产只读账号怎么限制?答:只授 SELECT,禁止 DDL/写,绑定来源主机。
  • 问:敏感字段如何保护?答:存储加密/脱敏展示,访问审计,具体方案按合规要求。

05.2.2.5-017 容量规划要考虑哪些因素?

频率 ★★★☆☆ | 难度 进阶

一句话结论

按数据增长趋势估算表/索引/binlog/备份空间,结合并发量、连接数与 IO 吞吐预留余量;关注大表膨胀与热点增长,定期复盘容量水位与扩容节点。

概念解释

  • 空间增长:数据行、索引、binlog、undo 与备份。
  • 负载增长:QPS、写入量、连接与 IO 吞吐。
  • 余量预留:水位预警与扩容提前量。

原理流程

  1. 采集历史增长与负载曲线。
  2. 预测未来增长并预留空间/性能余量。
  3. 定期复盘大表、热点与复制延迟。

代码 / 命令

-- 表与索引大小统计(视图名随版本以手册为准)
SELECT table_name, data_length, index_length
FROM information_schema.tables
WHERE table_schema='your_db';

常见误区

  • 误区:容量只算数据行大小。正解:索引、binlog、undo、碎片与备份占用需一并估算。
  • 误区:容量到水位再扩容。正解:应预留提前量,避免临满应急处理。

追问

  • 问:大表增长怎么预警?答:监控表行数/体积增速与碎片,纳入容量周报。
  • 问:写入增长快于读怎么扩容?答:先评估索引/批量优化,再考虑分库分表,见 05.2.2.5-008。

05.2.2.5-018 多源复制与复制过滤有哪些坑?

频率 ★★☆☆☆ | 难度 高级

一句话结论

多源复制把多个主库变更汇聚到同一从库,需避免库表名冲突;复制过滤按库/表/事件规则过滤,过滤规则在主从两侧行为不同,跨库语句易漏过滤,配置需逐条核对并校验。

概念解释

  • 多源复制:一个从库接入多个主库。
  • 复制过滤:过滤指定库表或事件类型。
  • 冲突风险:同名表、过滤遗漏导致数据不一致。

原理流程

  1. 规划多源库表命名空间避免冲突。
  2. 配置过滤规则并理解主/从侧差异。
  3. 校验各源数据在从库的一致性。

代码 / 命令

SHOW REPLICA STATUS\G
-- 过滤参数名随版本以官方手册为准,待确认

常见误区

  • 误区:复制过滤在主从两侧效果完全相同。正解:基于库名的过滤在语句/行格式下行为有差异,需按格式核对。
  • 误区:多源复制天然隔离数据。正解:同名表会互相覆盖,必须规划命名与路由。

追问

  • 问:过滤掉的表还能恢复吗?答:被过滤事件不回放,历史数据需单独迁移,不能从该从库恢复。
  • 问:多源复制延迟怎么看?答:分源监控各通道位点与延迟,不能只看汇总状态。