ynglan 技术博客

MySQL 主从复制原理与故障处理，从原理到实战一篇讲透

ynglan2026/03/02mysqlMySQL主从复制高可用数据库架构

MySQL 主从复制原理与故障处理，从原理到实战一篇讲透

凌晨三点，被电话吵醒：数据库报警，从库延迟超过 30 分钟，业务开始出现读写分离访问异常。

爬起来一看：

  • 主库正常，QPS 20000
  • 从库延迟 35 分钟
  • 从库 CPU 打满，IO 等待高

最后查到原因：有一个全量表扫描的查询跑到从库了，导致从库 relay log 应用速度跟不上。

这是主从复制架构里很典型的问题。要解决这类问题，得先理解主从复制的工作原理。

MySQL 主从复制原理

三种复制方式

方式原理优点缺点
异步复制主库执行后立即返回，不等从库性能最好可能丢数据
半同步复制主库等至少一个从库确认收到日志数据安全性提高有一定延迟
全同步复制主库等所有从库执行完才返回完全一致延迟大，性能差

MySQL 默认是异步复制。

复制原理核心组件

┌─────────────┐         ┌─────────────┐
│   主库      │         │   从库      │
│             │         │             │
│  ┌───────┐  │  ────>  │  ┌───────┐  │
│  │Binlog │  │         │  │Relay  │  │
│  │Dump   │  │         │  │Log    │  │
│  │Thread │  │         │  │IO     │  │
│  └───────┘  │         │  │Thread │  │
│             │         │  └───────┘  │
│             │         │  ┌───────┐  │
│             │         │  │SQL    │  │
│             │         │  │Thread │  │
│             │         │  └───────┘  │
└─────────────┘         └─────────────┘
  1. Binlog Dump Thread：主库上运行，发送 binlog 给从库
  2. IO Thread：从库上运行，接收 binlog 并写入 relay log
  3. SQL Thread：从库上运行，读取 relay log 并执行

复制格式

binlog 有三种格式：

格式记录内容优点缺点
STATEMENT记录 SQL 语句binlog 小函数、存储过程结果可能不一致
ROW记录行变化精确binlog 大
MIXED混合，优先 STATEMENT平衡可能不一致
-- 查看当前格式
SHOW VARIABLES LIKE 'binlog_format';

-- 设置（需要重启或重新开会话）
SET GLOBAL binlog_format = 'ROW';

建议用 ROW 格式，数据一致性更有保障。

主从复制配置

主库配置

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
expire-logs-days = 7
sync-binlog = 1  # 每次事务提交同步 binlog 到磁盘

从库配置

[mysqld]
server-id = 2
log-replica-updates = 1  # 5.7+ 用这个
replicate-do-db = app_db  # 只复制特定库
replicate-ignore-db = mysql  # 忽略特定库
relay-log = relay-bin
read-only = 1  # 从库只读（但 super 用户不受限制）

建立复制关系

-- 主库：创建复制账号
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- 主库：获取 binlog 位置
SHOW MASTER STATUS;
-- File: mysql-bin.000123
-- Position: 456

从库执行：

CHANGE MASTER TO
    MASTER_HOST = '主库IP',
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'repl_password',
    MASTER_LOG_FILE = 'mysql-bin.000123',
    MASTER_LOG_POS = 456;

START SLAVE;
SHOW SLAVE STATUS\G

常见主从问题

问题一：主从延迟

-- 从库查看延迟
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master: 300  -- 延迟 5 分钟

延迟原因和解决方案：

原因 1：从库 IO 线程网络延迟

-- 查看 IO 线程状态
SHOW SLAVE STATUS\G
-- Slave_IO_State: Waiting for master to send event
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes

-- 查看网络延迟
SHOW SLAVE STATUS\G
-- Read_Master_Log_Pos: 12345678  -- 主库已写到位置
-- Exec_Master_Log_Pos: 12345678  -- 从库已执行位置

原因 2：从库 SQL 线程执行慢

-- 查看 SQL 线程状态
SHOW SLAVE STATUS\G
-- Relay_Log_Pos: 12345678
-- Relay_Log_Space: 87654321  -- relay log 大小异常

-- 查看从库慢查询
SHOW GLOBAL STATUS LIKE 'Slow_queries';

原因 3：大事务导致延迟

主库执行一个大事务只需要 1 秒，但 binlog 传送到从库、从库重放需要 10 分钟。

-- 主库查看当前事务
SHOW PROCESSLIST;
-- Command: Binlog Dump
-- Info: BEGIN; DELETE FROM large_table ...; COMMIT

-- 优化：拆大事务
DELETE FROM large_table WHERE id BETWEEN 1 AND 10000;
DELETE FROM large_table WHERE id BETWEEN 10001 AND 20000;
-- 分批删除

原因 4：缺少索引导致从库执行慢

-- 主库执行：0.5秒（结果集在内存）
SELECT * FROM orders WHERE user_id = 10086;

-- 从库执行：30秒（需要回表扫描大量数据）
-- 从库执行计划可能和主库不同

解决方案：

-- 在主从库都建相同索引
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

问题二：从库报错跳过

-- 错误代码 1032：记录不存在
-- 错误代码 1062：主键冲突

-- 方案一：跳过错误
SET GLOBAL sql_slave_skip_counter = 1;
STOP SLAVE;
START SLAVE;

-- 方案二：在配置里忽略错误
[mysqld]
slave-skip-errors = 1032,1062

-- 方案三：手动修复
-- 找到错误的 SQL，手动在从库执行

问题三：binlog position 丢失

GTID（Global Transaction Identifier）可以解决这个问题：

[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON

GTID 复制不依赖文件位置：

CHANGE MASTER TO
    MASTER_AUTO_POSITION = 1;  -- 自动定位

问题四：从库 read_only 无效

-- 检查 read_only 配置
SHOW VARIABLES LIKE 'read_only';
SHOW VARIABLES LIKE 'super_read_only';

-- super_read_only 限制 super 权限用户
-- 如果需要完全只读，要开启 super_read_only
SET GLOBAL super_read_only = ON;

主从复制监控

关键监控指标

-- 延迟秒数
SHOW SLAVE STATUS\G
Seconds_Behind_Master

-- IO 线程状态
Slave_IO_Running: Yes/No
Slave_SQL_Running: Yes/No

-- 错误信息
Last_IO_Error
Last_SQL_Error

-- binlog 位置
Read_Master_Log_Pos
Exec_Master_Log_Pos

监控脚本

#!/bin/bash
# 检查主从复制状态
STATUS=$(mysql -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master")

if echo "$STATUS" | grep -q "Slave_IO_Running: No"; then
    echo "IO thread not running"
    exit 1
fi

if echo "$STATUS" | grep -q "Slave_SQL_Running: No"; then
    echo "SQL thread not running"
    exit 1
fi

LAG=$(mysql -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master" | awk '{print $2}')
if [ "$LAG" -gt 60 ]; then
    echo "Replication lag: $LAG seconds"
    exit 1
fi

echo "Replication healthy"

主从切换流程

主库故障时，需要把一个从库提升为新的主库：

步骤一：确认从库数据一致

-- 在所有从库执行，选择最领先的
SHOW SLAVE STATUS\G
-- 检查 Relay_Master_Log_File 和 Exec_Master_Log_Pos

步骤二：停止所有从库复制

STOP SLAVE;

步骤三：提升候选从库

-- 在候选从库上执行
RESET SLAVE ALL;  -- 清除复制关系

-- 开启 log-bin
SET GLOBAL read_only = OFF;

步骤四：通知应用切换

更新连接配置，指向新的主库。

步骤五：重建其他从库

-- 在其他从库执行，指向新的主库
CHANGE MASTER TO
    MASTER_HOST = '新主库IP',
    MASTER_AUTO_POSITION = 1;
START SLAVE;

读写分离最佳实践

读写分离的坑

坑一：读到旧数据

主从延迟导致写完立即读可能读到从库未同步的旧数据。

解决方案：

  • 强一致读走主库
  • 允许延迟读走从库
  • 用 GTID + 配置延迟阈值

坑二：从库无写权限但应用配置错误

应用有时会意外写到从库。

解决方案：

  • 从库配置 read_only = 1 和 super_read_only = 1
  • 应用层做好权限分离

坑三：自增主键冲突

-- 主库
INSERT INTO orders (...) VALUES (...);
-- 从库 AUTO_INCREMENT 可能不一致

-- 解决：在所有库配置相同的自增步长
[mysqld]
auto_increment_increment = 2
auto_increment_offset = 1  -- 主库
-- 从库 offset = 2

读写分离框架

推荐使用：

  • ShardingSphere：支持读写分离、数据库分片
  • Vitess：YouTube 开源的数据库中间件
  • MySQL Router：MySQL 官方代理

总结

主从复制是 MySQL 高可用架构的基础：

  1. 原理：Binlog + IO Thread + SQL Thread，异步或半同步
  2. 配置：server-id、binlog-format、GTID
  3. 延迟：监控 Seconds_Behind_Master，优化 SQL、加索引、拆大事务
  4. 故障处理：跳过错误、手动修复、切换主库
  5. 读写分离：区分强一致读和可延迟读，合理路由

遇到主从问题不要慌，按这个顺序排查：

  1. SHOW SLAVE STATUS 看状态
  2. Seconds_Behind_Master 看延迟
  3. Last_IO_Error / Last_SQL_Error 看错误
  4. 根据错误类型选择跳过或修复

主从复制的本质是让多个数据库节点保持数据一致，理解了这个本质，大部分问题都能自己推导出来。

最后更新 4/20/2026, 6:02:32 AM
ON THIS PAGE