主从复制与读写分离

举报
yd_94907100 发表于 2026/11/03 14:30:10 2026/11/03
【摘要】 讲清 binlog 三种格式与复制线程模型，附搭建步骤与延迟治理方案

主从复制是 MySQL 高可用的地基：读写分离、备份、故障切换、数据分析都建立在它之上。但它也是最容易被"配通了就不管"的组件——直到某天从库延迟几小时，或者主从数据悄悄不一致。这篇文章按"原理 → 搭建 → 读写分离 → 延迟治理 → 故障切换"展开，命令可直接执行。基准 MySQL 8.0，5.7 的差异单独标注。

一、复制的基本模型

主从复制是异步的，三个线程协作：

  • 主库 Binlog Dump 线程：每个从库连接对应一个，负责把 binlog 发给从库。
  • 从库 IO 线程：接收 binlog 事件，写入本地 relay log。
  • 从库 SQL 线程：读 relay log，重放为实际的数据变更。

数据流是：主库执行事务 → 写 binlog → 提交 → dump 线程推送 → 从库 IO 线程写 relay log → SQL 线程重放。

注意：MySQL 8.0 中 SQL 线程默认是多线程的（replica_parallel_workers，8.0.27 前叫 slave_parallel_workers），5.7 默认是 0（即单线程）。这是 8.0 复制性能提升的关键，升级后必须显式配置并行度。

二、binlog 三种格式

| 格式 | 内容 | 优点 | 缺点 |

| --- | --- | --- | --- |

| STATEMENT | 原始 SQL | 日志小 | 函数、UUID()、LIMIT 不确定时主从不一致 |

| ROW | 每行的前后镜像 | 绝对一致，可做闪回 | 日志大，批量更新膨胀 |

| MIXED | 按语句自动切换 | 折中 | 行为不易预期 |

8.0 默认是 ROW，5.7 默认也是 ROW（5.7.7 起）。生产环境必须用 ROW，理由是确定性与可恢复性——STATEMENT 下 NOW()、RAND()、自增锁行为都可能让从库产生不同结果。

SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'binlog_row_image';   -- 默认 FULL，可改 MINIMAL 减小日志
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';  -- 8.0 默认 2592000（30 天）

5.7 用 expire_logs_days（默认 0，即不过期），8.0 改为秒级变量 binlog_expire_logs_seconds，旧变量在 8.0.11 后废弃。

三、GTID 与位点复制

传统复制靠 MASTER_LOG_FILE + MASTER_LOG_POS 定位，主库切换后要重新计算位点，容易出错。GTID（Global Transaction Identifier）给每个事务一个全局唯一编号，格式为 server_uuid:transaction_id，从库只需告诉主库"我已执行到哪"，自动定位。

# 主库与从库都要配置
[mysqld]
server_id = 1                 # 每个实例唯一
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON

# 从库额外配置
relay_log = /var/log/mysql/relay-bin
read_only = ON
super_read_only = ON
replica_parallel_workers = 8
replica_parallel_type = LOGICAL_CLOCK
replica_preserve_commit_order = ON

8.0 推荐 replica_parallel_type = LOGICAL_CLOCK（基于组提交的并行），5.7 中该参数叫 slave_parallel_type，且 LOGICAL_CLOCK 从 5.7.2 起可用。replica_preserve_commit_order 保证并行重放后提交顺序与主库一致，避免从库出现"后提交的事务先可见"。

四、搭建主从（GTID 方式）

1. 主库准备

-- 创建复制账号
CREATE USER 'repl'@'%' IDENTIFIED BY 'Repl@2026';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

-- 确认 GTID 已执行集合
SHOW MASTER STATUS\G

输出示例：

*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 157
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 3e11fa47-71ca-11ee-9c2f-0242ac110002:1-42

2. 主库做一致性备份

mysqldump -uroot -p \
  --single-transaction \
  --master-data=2 \
  --set-gtid-purged=ON \
  --routines --triggers --events \
  --databases shop > /tmp/shop.sql

--single-transaction 利用 RR 快照保证一致性，不锁表，只对 InnoDB 有效。--master-data=2 会把位点以注释形式写入备份文件（8.0.26 起建议用 --source-data=2，旧参数仍兼容）。

3. 从库导入并启动复制

mysql -uroot -p < /tmp/shop.sql

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '10.0.1.10',
  SOURCE_PORT = 3306,
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'Repl@2026',
  SOURCE_AUTO_POSITION = 1,
  SOURCE_CONNECT_RETRY = 10,
  SOURCE_RETRY_COUNT = 3;

START REPLICA;
SHOW REPLICA STATUS\G

注意：8.0.23 起 CHANGE MASTER TO 改名为 CHANGE REPLICATION SOURCE TO，START SLAVE 改为 START REPLICA，SHOW SLAVE STATUS 改为 SHOW REPLICA STATUS。旧语法仍可用但会告警；5.7 只有旧语法。运维脚本跨版本时必须兼容两套。

关键状态字段：

Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Seconds_Behind_Source: 0
Retrieved_Gtid_Set: 3e11fa47-...:1-42
Executed_Gtid_Set: 3e11fa47-...:1-42
Last_IO_Error:
Last_SQL_Error:

两个 Running 都是 Yes、Seconds_Behind_Source 为 0、两个 GTID 集合相等，才算正常。5.7 中对应字段名是 Slave_IO_Running、Seconds_Behind_Master。

五、读写分离

方案一：应用层路由

用 Spring 的 AbstractRoutingDataSource 配合 AOP，按方法注解切换数据源。

public class RoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContext.isReadOnly() ? "slave" : "master";
    }
}

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ReadOnly {
}

这种方式简单，但要自己处理"写后立即读"的一致性问题。

方案二：中间件

ShardingSphere-Proxy、MyCat、ProxySQL 在代理层解析 SQL，自动把 SELECT 路由到从库。优点是业务无侵入、支持强制走主库的 hint；缺点是增加一跳网络、代理本身成为需要高可用的组件。

ShardingSphere-JDBC 的读写分离配置：

spring:
  shardingsphere:
    rules:
      readwrite-splitting:
        data-sources:
          rw-ds:
            write-data-source-name: master
            read-data-source-names: slave0,slave1
            load-balancer-name: round-robin
        load-balancers:
          round-robin:
            type: ROUND_ROBIN

写后读一致性

主从异步复制下，写完立刻读从库可能读不到。三种处理：

  1. 强制走主库：写操作后的 N 秒内，或同一事务/同一请求内，读走主库。
  2. 按 GTID 等待：记录写入返回的 GTID，读从库前等待 WAIT_FOR_EXECUTED_GTID_SET 超时。
  3. 会话粘滞：同一会话固定走主库一段时间。
-- 在从库上等待指定 GTID 集合执行完成，超时 1 秒
SELECT WAIT_FOR_EXECUTED_GTID_SET('3e11fa47-71ca-11ee-9c2f-0242ac110002:43', 1);

返回值 0 表示已追上，1 表示超时。这是比"睡 100 ms"精确得多的方案。

六、主从延迟的成因与治理

Seconds_Behind_Source 的计算方式是"从库当前时间 - 正在重放的事件的原始时间戳"，只在 SQL 线程运行时有效，SQL 线程停了会显示 NULL。

常见成因：

| 成因 | 表现 | 治理 |

| --- | --- | --- |

| 大事务 | 单事务重放几分钟 | 拆分批量更新，控制单事务行数 |

| 从库单线程重放 | 主库并发高时堆积 | 开并行复制，LOGICAL_CLOCK |

| 从库硬件弱于主库 | 持续落后 | 从库配置不低于主库 |

| 从库承担大量查询 | SQL 线程抢不到 CPU/IO | 读写分离的从库与备份从库分开 |

| 无主键表 | ROW 格式下全表扫描定位行 | 所有表必须有主键 |

| DDL | 大表 ALTER 阻塞重放 | 用 gh-ost / pt-osc 在线变更 |

无主键表是隐藏杀手：ROW 格式下，从库重放 UPDATE/DELETE 时如果没有主键，只能全表扫描匹配行，一条语句可能扫几百万行。检查方法：

SELECT t.TABLE_SCHEMA, t.TABLE_NAME
FROM information_schema.TABLES t
LEFT JOIN information_schema.TABLE_CONSTRAINTS c
  ON c.TABLE_SCHEMA = t.TABLE_SCHEMA
 AND c.TABLE_NAME = t.TABLE_NAME
 AND c.CONSTRAINT_TYPE = 'PRIMARY KEY'
WHERE t.TABLE_TYPE = 'BASE TABLE'
  AND t.TABLE_SCHEMA NOT IN ('mysql','sys','information_schema','performance_schema')
  AND c.CONSTRAINT_NAME IS NULL;

监控延迟建议用 pt-heartbeat，它不依赖 Seconds_Behind_Source，通过在主库定时写心跳表、在从库读时间差，能测出真实延迟：

# 主库持续写心跳
pt-heartbeat --daemonize --update --database heartbeat \
  --host 10.0.1.10 --user repl --password 'Repl@2026'

# 从库查看延迟
pt-heartbeat --monitor --database heartbeat \
  --host 10.0.1.11 --user repl --password 'Repl@2026'

七、故障切换

主库宕机后，需要选一个延迟最小的从库提升为主：

-- 1. 确认从库已追平
SHOW REPLICA STATUS\G   -- Seconds_Behind_Source = 0

-- 2. 停止复制并提升
STOP REPLICA;
RESET REPLICA ALL;

-- 3. 关闭只读
SET GLOBAL super_read_only = OFF;
SET GLOBAL read_only = OFF;

其余从库指向新主：

STOP REPLICA;
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '10.0.1.11',
  SOURCE_AUTO_POSITION = 1;
START REPLICA;

GTID 模式下不需要计算位点，这是它最大的价值。生产环境应使用 MHA、Orchestrator 或云厂商的托管切换，并配合 VIP / DNS 切换，避免应用改配置。

八、常见坑 / 最佳实践

  1. server_id 必须全局唯一。重复会导致复制中断且报错隐晦，云环境克隆实例时尤其容易踩。
  2. 所有表必须有主键。这是 ROW 格式复制的前提，也是延迟和一致性的保障。上线前用上面的 SQL 做一次全库扫描。
  3. 控制单事务大小。批量 UPDATE 或 DELETE 一次几十万行，会造成秒级到分钟级的延迟尖刺。按 500~1000 行分批提交。
  4. 从库开启 super_read_only。只开 read_only 时，有 SUPER 权限的账号仍能写入，是主从数据不一致的常见来源。8.0 默认账号权限模型下更要注意。
  5. 读写分离要有强制走主库的开关。写后读、强一致查询、报表校验必须能指定主库，否则会出现"刚保存就查不到"的用户投诉。

九、小结

  • 复制由 dump 线程、IO 线程、SQL 线程协作完成，异步复制天然存在延迟窗口。
  • binlog 用 ROW 格式，保证确定性与可恢复性；GTID 让主库切换无需计算位点。
  • 8.0 的 CHANGE REPLICATION SOURCE TO、SHOW REPLICA STATUS 与 5.7 的 MASTER/SLAVE 语法不通用，脚本要兼容。
  • 读写分离的核心难点是写后读一致性，用强制主库或 WAIT_FOR_EXECUTED_GTID_SET 解决。
  • 延迟治理的关键是并行复制、拆大事务、消除无主键表，监控用 pt-heartbeat 而不是 Seconds_Behind_Source。
【声明】本内容来自华为云开发者社区博主，不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息，否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MySQL SQL 数据库
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入