MySQL 主从**搭建与故障切换

dbaai

MySQL 主从**搭建与故障切换

一、要解决的具体问题

很多团队把 MySQL 当作单点库撑着业务，直到某天主库磁盘坏了、或者一次误删表，才意识到没有从库就意味着没有退路。本文聚焦一个最常被问到的实操问题：怎么在一台主库和一台从库之间把**跑通，并且当主库宕机时，能把从库顶上去继续对外服务。目标很明确——用最小代价拿到一份可随时接管的热备，并且切换过程可控、可回退。

真正动手时一般会卡在三个地方：主从之间的账号和权限怎么配最省事、**到底用的是文件位点还是 GTID、主库挂了之后从库怎么判断“数据到底追平没有”。这三件事处理不好，**要么起不来，要么切换时丢数据。下面把这三点一次说清。

二、核心原理

1. **靠什么跑起来

MySQL 主从**的本质是“主库把 binlog 发给从库，从库重放”。主库把所有改动（写、改、删）按顺序写进二进制日志 binlog；从库上有两个线程在干活：I/O 线程负责连主库、把 binlog 拉回来写进本地的 relay log（中继日志），SQL 线程负责把 relay log 里的事件一条条重放到自己的数据上。只要这两个线程都在跑，从库的数据就会逐步追上主库。

2. 两种定位方式：文件位点 vs GTID

老式做法用“文件位点”（binlog 文件名 + 位置偏移），搭从库时必须先在主库记一个位置点，从库从这个点之后开始拉。它的麻烦在于：一旦主库做过恢复、位点在多个 binlog 之间跳，手动对位点很容易对错。

新做法用 GTID（全局事务标识），每条事务都有唯一编号。从库只需说“我执行到哪个 GTID **了”，主库自动补发缺失的事务，不用人工对齐位点。新项目一律建议开 GTID，故障切换时尤其省心。

3. **延迟与数据一致

从库追主库天然有延迟，高并发写入时延迟会被放大。判断“从库是否追平”要看 Seconds_Behind_Source（旧版叫 Seconds_Behind_Master）这个指标，它近似等于从库 SQL 线程落后主库的秒数。切换前必须确认这个值接近 0，否则顶上去会丢最近几秒的数据。

三、实例参考（动手步骤）

下面以“一主一从、启用 GTID”为例，给出可照做的步骤。假设主库 IP 为 192.168.1.10，从库为 192.168.1.11。

1) 主库开启 binlog 与 GTID（my.cnf 关键配置）：

2. server-id = 1
3. log_bin = mysql-bin
4. gtid_mode = ON
5. enforce_gtid_consistency = ON
6. binlog_format = ROW

复制代码

改完重启主库，确认生效：

2. SHOW VARIABLES LIKE 'gtid_mode';
3. SHOW MASTER STATUS;

复制代码

2) 在主库建一个只用于**的账号，并授权拉取 binlog：

2. CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'ReplPwd#2026';
3. GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
4. FLUSH PRIVILEGES;

复制代码

3) 从库开启 GTID 并指向主库（MySQL 8.0.23 之后用新语法）：

2. server-id = 2
3. gtid_mode = ON
4. enforce_gtid_consistency = ON
5. read_only = ON

复制代码

2. CHANGE REPLICATION SOURCE TO
3.   SOURCE_HOST='192.168.1.10',
4.   SOURCE_USER='repl',
5.   SOURCE_PASSWORD='ReplPwd#2026',
6.   SOURCE_AUTO_POSITION=1;
7. START REPLICA;

复制代码

4) 验证**状态，重点看两个线程是否都是 Yes、延迟是否接近 0：

2. SHOW REPLICA STATUS\G

复制代码

应看到 Replica_IO_Running: Yes、Replica_SQL_Running: Yes、Seconds_Behind_Source: 0。

5) 故障切换（主库宕机时）的手动流程：

• 在从库先确认已追平：SHOW REPLICA STATUS\G 的 Retrieved_Gtid_Set 与 Executed_Gtid_Set 是否相等；
  
• 停止**线程：STOP REPLICA;
  
• 提升从库为可写：SET GLOBAL read_only = OFF;
  
• 把应用连接串指向从库新地址，原主库恢复后降级为从库重新接入。
  

前后对比：搭建前主库单点、宕机即停写、无恢复点；搭建后从库实时热备、切换可在分钟级完成、RPO 从“整段业务中断”降到秒级以内（前提是切换前延迟已归零）。

四、实操检查清单

• 主从 server-id 必须不同，否则**无法启动。
  
• 新环境一律开启 gtid_mode + enforce_gtid_consistency，切换时不用手动对位点。
  
• **账号只授 REPLICATION SLAVE，不要给多余权限，密码单独、不与业务账号混用。
  
• 从库设 read_only = ON，避免被误写入造成主从数据分歧。
  
• 搭建后必须 SHOW REPLICA STATUS\G 确认两个线程都是 Yes，有一个 No 就是断了。
  
• 日常监控 Seconds_Behind_Source，超过阈值（如 30 秒）要告警，别等切换时才发现落后几小时。
  
• 切换前务必核对 Retrieved_Gtid_Set 与 Executed_Gtid_Set 相等，否则会丢未重放的事务。
  
• 原主库恢复后不能直接上线，应先降级为从库、校验 GTID 无冲突再重新接入集群。
  
• 主库 binlog 保留时长（binlog_expire_logs_seconds）要足够长，短了从库断开久了会拉不到日志。
  
• 应用连接建议走 VIP 或中间件（如 ProxySQL），切换时只改后端指向，避免逐个改连接串。