MySQL 读写分离完全指南：从一主一从到双主双从
当单台 MySQL 的读请求扛不住、CPU 天天报警时，读写分离是性价比最高的扩容手段。本文从概念到落地，完整讲透两种主流读写分离架构：一主一从（入门必备）和双主双从（高可用进阶），每一步配置都可直接复用。
目录
- 一、一主一从架构基础准备
- 二、一主一从读写分离部署与配置
- 三、双主双从架构核心介绍
- 四、双主双从架构搭建流程
- 五、双主双从读写分离实现
- 六、读写分离技术总结
一、一主一从架构基础准备
1.1 核心逻辑：主库写、从库读的分工原理
一句话：一台库专门接单（写），一台库专门出餐（读），两库之间靠"菜单"（binlog）实时同步。
具体分工：
- 主库（Master）：处理所有写操作（INSERT、UPDATE、DELETE），每执行一笔写操作，就把"操作记录"写入自己的二进制日志（binlog）。
- 从库（Slave）：通过 IO 线程拉取主库的 binlog，存到自己的中继日志（relay log），再由 SQL 线程把 relay log 里的操作在自己身上重放一遍，从而和主库保持数据一致。
生活化类比：
主库是餐厅唯一的大厨，所有菜必须经他手做（写操作）。 从库是大厨的学徒，站在旁边拿个小本子（relay log），把大厨的每一步操作抄下来，然后在旁边的灶台上照着重做一遍（SQL 线程重放）。 客人点单（写）必须找大厨；客人只是看看菜品图片（读），找学徒就行——学徒做的和大厨一模一样。
为什么这样能扩容？因为大多数业务读多写少（电商读写比通常 10:1 甚至更高），把 90% 的读流量从主库分走，主库就能专心处理写请求。
1.2 前置软硬件要求
服务器最低配置（生产环境建议）：
| 项目 | 最低要求 | 生产建议 |
|---|---|---|
| 服务器数量 | 2 台（1主1从） | 2 台物理机或独立云主机 |
| CPU | 2 核 | 4 核+ |
| 内存 | 4 GB | 16 GB+ |
| 磁盘 | 50 GB（SSD 优先） | 500 GB NVMe SSD |
| 操作系统 | CentOS 7+ / Ubuntu 20.04+ | 主从版本保持一致 |
MySQL 版本兼容要求：
- 推荐 MySQL 5.7（≥5.7.20）或 MySQL 8.0，主从版本必须一致或从库版本 ≥ 主库版本（低版本从库可能无法解析高版本主库的 binlog）
- 禁止使用 5.6 及以下版本（已停止维护，且不支持 GTID 等现代复制特性）
网络互通条件：
防火墙开放 MySQL 端口（默认 3306）：
bash# CentOS 防火墙放行 3306 firewall-cmd --permanent --add-port=3306/tcp firewall-cmd --reload两台服务器能互相 ping 通，且
telnet 对方IP 3306能连通（验证端口未被拦截）。服务器时间必须同步：主从时间差会导致 binlog 时间戳混乱、监控数据失真。配置 NTP 校时：
bash# 两台服务器都执行，同步阿里云时间服务器 ntpdate ntp.aliyun.com # 写入定时任务，每小时校时一次 echo "0 * * * * /usr/sbin/ntpdate ntp.aliyun.com" >> /etc/crontab
初始环境要求：
- 两台服务器都全新安装 MySQL，清理冗余测试数据（
mysql、sys系统库保留，业务测试库删除） - 主库提前规划好 binlog 配置（第二部分详述），确保磁盘剩余空间充足——binlog 会持续累积，生产环境至少预留数据量 2 倍的空间
1.3 搭建前准备工作清单
第 1 步：主库创建专属复制账号
从库需要一个账号去主库"领"binlog，这个账号只需授予 REPLICATION SLAVE 权限，严禁直接用 root：
sql
-- 在主库执行
-- 创建复制账号 repl，只允许从库IP（192.168.1.11）连接，密码要求强复杂度
CREATE USER 'repl'@'192.168.1.11' IDENTIFIED BY 'Repl@123456';
-- 授予复制从库权限（这是复制专用的最小权限）
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.11';
-- 刷新权限使生效
FLUSH PRIVILEGES;第 2 步：主库全量备份
从库要先有一份和主库"起点一致"的数据，才能开始增量同步。用 mysqldump 做全量备份：
bash
# 在主库执行
mysqldump -uroot -p \
--all-databases \ # 备份所有数据库
--single-transaction \ # InnoDB专用：开启一致性快照，备份过程不锁表（关键！）
--master-data=2 \ # 在备份文件中记录当前的binlog文件名和位置点（从库同步起点）
--flush-logs \ # 备份前切换一个新的binlog文件，让位置点更清晰
--triggers \ # 包含触发器
--routines \ # 包含存储过程和函数
--events \ # 包含事件调度器
> /data/backup/full_backup_$(date +%Y%m%d).sql参数说明：
--single-transaction：通过开启一个可重复读事务获得一致性快照，备份期间业务正常写入不受影响（仅适用于 InnoDB；如果有 MyISAM 表，必须改用--lock-all-tables，会锁全库）--master-data=2：在备份文件头部写入一行注释，记录备份时刻的 binlog 文件名 + 位置点，如CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154;。这个值是从库开始同步的"起跑线"，后面要用=2表示以注释形式写入（不直接执行）；=1会写成可执行语句，导入时自动生效（有覆盖风险，建议用 2）
第 3 步：从库导入备份
bash
# 把备份文件传到从库
scp /data/backup/full_backup_20260905.sql root@192.168.1.11:/data/backup/
# 在从库执行导入（从库MySQL需为干净状态）
mysql -uroot -p < /data/backup/full_backup_20260905.sql第 4 步：查看并记录主库同步起点
bash
# 从备份文件中提取 binlog 位置点（--master-data=2 写入的那行）
grep "CHANGE MASTER TO" /data/backup/full_backup_20260905.sql | head -1
# 输出示例：
# -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154;记录好这两个值：mysql-bin.000003 和 154，配置从库时需要填入。
准备清单核对表：
- [ ] 两台服务器 MySQL 已安装且版本符合要求
- [ ] 防火墙 3306 端口互通，时间已同步
- [ ] 主库已创建 repl 复制账号并验证可远程登录
- [ ] 主库已全量备份，备份文件含 binlog 位置点
- [ ] 从库已导入备份，数据量与主库一致
- [ ] 已记录 binlog 文件名和位置点
二、一主一从读写分离部署与配置
2.1 主库核心配置（my.cnf）
编辑主库的 /etc/my.cnf（Linux）或 my.ini（Windows），在 [mysqld] 段添加：
ini
[mysqld]
# ===== 复制基础配置 =====
server-id = 1 # 服务器唯一标识，整个复制集群内不能重复，主库习惯设为1
log-bin = mysql-bin # 开启二进制日志，日志文件前缀为 mysql-bin（复制的基础，必开）
binlog-format = ROW # binlog格式：ROW(记录每行数据变化，最安全) / STATEMENT(记录SQL原文) / MIXED
# 生产推荐ROW：数据一致性最好，代价是日志体积略大
# ===== 同步范围控制（二选一，不配则同步所有库）=====
binlog-do-db = shop_db # 只记录 shop_db 的binlog（白名单），多行可配多个库
# binlog-ignore-db = test_db # 忽略 test_db（黑名单），与白名单互斥，别同时用
# 配置错误的影响：
# - binlog-do-db 的坑：它按"当前use的库"过滤而不是SQL实际操作的库！
# 执行 use other_db; UPDATE shop_db.t1... 这条SQL不会被记录 → 从库数据丢失
# 建议：明确知道影响才用白名单，否则同步全库更稳妥
# ===== 性能与安全 =====
expire_logs_days = 7 # binlog保留7天，超期自动删除，防止磁盘撑爆
max_binlog_size = 500M # 单个binlog文件最大500M，超过自动切新文件
sync_binlog = 1 # 每次事务提交都刷盘，最安全(默认1)；设0性能高但宕机可能丢binlog重启生效并验证：
bash
systemctl restart mysqldsql
-- 验证binlog已开启
SHOW VARIABLES LIKE 'log_bin'; -- 应为 ON
SHOW MASTER STATUS; -- 记录 File 和 Position 的值2.2 从库核心配置
第 1 步：从库 my.cnf 配置
ini
[mysqld]
server-id = 2 # 从库ID，必须与主库不同（整个集群唯一），习惯设为2
relay-log = mysql-relay-bin # 中继日志前缀：从库把主库binlog先存这里，再重放
read_only = 1 # 从库只读（防止误写入导致主从不一致），普通用户无法写
# 注意：拥有SUPER权限的账号（如root）仍可写，要管好账号
super_read_only = 1 # MySQL5.7.8+推荐开启：连SUPER权限也只读，彻底杜绝误写第 2 步：配置主库连接信息
重启从库 MySQL 后，执行 CHANGE MASTER TO 绑定主库：
sql
-- 在从库执行
CHANGE MASTER TO
MASTER_HOST = '192.168.1.10', -- 主库IP
MASTER_PORT = 3306, -- 主库端口
MASTER_USER = 'repl', -- 第一部分创建的复制账号
MASTER_PASSWORD = 'Repl@123456', -- 复制账号密码
MASTER_LOG_FILE = 'mysql-bin.000003',-- 备份文件中记录的binlog文件名
MASTER_LOG_POS = 154, -- 备份文件中记录的位置点
MASTER_CONNECT_RETRY = 30; -- 断线后每30秒自动重连
-- 启动复制
START SLAVE;参数解释：
MASTER_LOG_FILE/MASTER_LOG_POS：告诉从库"从主库 binlog 的哪个位置开始同步"，填备份时记录的值。填错了要么丢数据（位置点太靠后），要么重复执行报错（位置点太靠前）MASTER_CONNECT_RETRY：网络闪断后自动重连的间隔秒数，避免人工介入
2.3 复制状态验证与排错
sql
-- 在从库执行
SHOW SLAVE STATUS\G核心状态参数解读：
| 参数 | 正常值 | 含义与排错 |
|---|---|---|
Slave_IO_Running | Yes | IO 线程（拉取主库 binlog）是否正常。No/Connecting：检查网络、复制账号、主库 binlog 是否被误删 |
Slave_SQL_Running | Yes | SQL 线程（重放 relay log）是否正常。No：通常是 SQL 执行报错，看 Last_Error 字段定位 |
Seconds_Behind_Master | 0 或小数值 | 从库落后主库的秒数。0=实时同步；持续增大=从库性能不足或有大事务；NULL=复制中断 |
Read_Master_Log_Pos | 与主库接近 | IO 线程读到的主库 binlog 位置 |
Exec_Master_Log_Pos | 追平前者 | SQL 线程执行到的位置。两者差距大 = 从库重放慢 |
Last_Error | 空 | SQL 线程最近一次报错信息，排错第一入口 |
常见异常排查思路：
text
Slave_IO_Running = Connecting
→ 检查：telnet 主库 3306 通不通 → repl 账号密码对不对 → 主库binlog文件是否已被purge
Slave_SQL_Running = No，Last_Error 提示主键冲突（Duplicate entry）
→ 原因：从库被误写入数据 / 导入的备份起点不一致
→ 临时跳过一个错误事务：STOP SLAVE; SET GLOBAL sql_slave_skip_counter=1; START SLAVE;
（治标不治本，治本要核对数据差异后修复）
Seconds_Behind_Master 持续 > 60
→ 检查：主库是否有大事务/批量DDL → 从库机器性能是否弱于主库 →
MySQL5.7+可开启并行复制：slave_parallel_type=LOGICAL_CLOCK, slave_parallel_workers=8数据一致性快速验证：
sql
-- 主库写入测试数据
CREATE TABLE test.repl_check(id INT PRIMARY KEY, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
INSERT INTO test.repl_check(id) VALUES (1);
-- 从库查询，能查到且时间差很小即同步正常
SELECT * FROM test.repl_check WHERE id = 1;2.4 基于 Sharding-JDBC 实现读写分离（SpringBoot 实战）
复制搭好了，应用层怎么"写走主库、读走从库"？手动在代码里切换数据源太容易漏，业界主流做法是用 ShardingSphere-JDBC 自动路由。
第 1 步：引入依赖
xml
<!-- pom.xml -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.4.1</version>
</dependency>第 2 步：application.yml 完整配置
yaml
spring:
shardingsphere:
# ===== 数据源定义 =====
datasource:
names: master,slave0 # 声明两个数据源：主库 + 从库
master: # 主库数据源（承接所有写操作）
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.1.10:3306/shop_db?serverTimezone=Asia/Shanghai
username: root
password: mysql123
slave0: # 从库数据源（承接读操作）
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.1.11:3306/shop_db?serverTimezone=Asia/Shanghai
username: root
password: mysql123
# ===== 读写分离规则 =====
rules:
readwrite-splitting:
data-sources:
readwrite_ds: # 读写分离逻辑数据源名称（业务代码无感知）
write-data-source-name: master # 写库：所有 INSERT/UPDATE/DELETE 路由到这里
read-data-source-names: slave0 # 读库：SELECT 路由到这里，多个从库用逗号分隔
load-balancer-name: round_robin # 读库负载均衡策略
# 读库负载均衡算法定义
load-balancers:
round_robin: # 轮询：多个从库轮流承接读请求
type: ROUND_ROBIN
# type: RANDOM # 随机：随机选一个从库
# type: WEIGHT # 权重：按权重分配，配合 props 给不同规格从库配不同权重
# ===== 调试配置 =====
props:
sql-show: true # 控制台打印逻辑SQL和实际路由的物理节点，联调期开启第 3 步：业务代码零改动
java
// 业务代码完全不用改，普通 MyBatis/JPA 写法即可
@Mapper
public interface OrderMapper {
// SELECT → 自动路由到 slave0
@Select("SELECT * FROM t_order WHERE order_id = #{id}")
Order selectById(Long id);
// INSERT → 自动路由到 master
@Insert("INSERT INTO t_order(order_id, amount) VALUES(#{orderId}, #{amount})")
int insert(Order order);
}路由逻辑（ShardingSphere 内部规则）：
text
INSERT / UPDATE / DELETE / DDL → 一律走写库 master
SELECT 在普通场景 → 走读库 slave0
SELECT 在显式事务中 → 走写库 master（防止事务内"写完立刻读"读到从库旧数据）
SELECT FOR UPDATE（加锁读） → 走写库 master（从库不允许加写锁）
SELECT 通过 HintManager 强制指定 → 走写库（应对"写完马上查"的延迟敏感场景）主从延迟的应对方案（写完立刻读的场景）：
java
public Order createAndQuery(Order order) {
orderMapper.insert(order); // 写入主库
// 从库同步有几毫秒~几秒延迟，立刻查从库可能查不到
// 用 HintManager 强制这条 SELECT 也走主库
try (HintManager hintManager = HintManager.getInstance()) {
hintManager.setWriteRouteOnly(); // 强制后续SQL走写库
return orderMapper.selectById(order.getOrderId());
}
}三、双主双从架构核心介绍
3.1 核心架构：两台主库互备 + 各自带一个从库
架构拓扑：
text
┌─────────────────────────┐
│ 应用/中间件层 │
└───────┬─────────┬───────┘
│写 │写
┌───────▼───┐ ┌───▼───────┐
│ 主节点M1 │◄═►│ 主节点M2 │ M1、M2 互为主从，都可写
│(192.168.1.10)│ │(192.168.1.11)│
└───────┬───┘ └───┬───────┘
│复制 │复制
┌───────▼───┐ ┌───▼───────┐
│ 从节点S1 │ │ 从节点S2 │ S1 同步 M1，S2 同步 M2，承接读请求
│(192.168.1.12)│ │(192.168.1.13)│
└───────────┘ └───────────┘通俗解释：
一主一从是"一个大厨 + 一个学徒"。双主双从是"两个大厨互为学徒"：M1 做菜时 M2 抄作业，M2 做菜时 M1 抄作业，两边菜谱实时保持一致；同时每个大厨还带一个专职学徒（S1、S2）负责给客人展示菜品（读请求）。
如何避免两个核心问题？
- 循环复制：M1 写的数据同步给 M2，M2 会不会再同步回 M1，无限循环？不会——binlog 里每条日志都带着产生它的服务器 server-id，节点看到日志的 server-id 是自己，就直接丢弃。这就是循环复制的拦截机制。
- 数据冲突：两个主库同时写同一行数据怎么办？靠两个手段规避：
- 自增主键错峰（详下）：M1 用奇数 ID，M2 用偶数 ID，主键永不撞车
- 业务层分片：中间件按规则把写请求分流，比如用户 ID 为奇数的写 M1、偶数的写 M2，让同一行数据永远只被一个主库修改
3.2 对比一主一从的核心优势
| 维度 | 一主一从 | 双主双从 |
|---|---|---|
| 高可用 | 主库挂了 = 写中断，需人工切换 | M1 挂了 M2 秒级接管写流量，业务几乎无感知 |
| 写吞吐 | 单主写，天花板明显 | 双主同时写，理论写吞吐翻倍 |
| 读承载 | 1 个从库分担读 | 2 个从库分担读，读能力翻倍 |
| 数据备份 | 1 份热备（从库） | 每个主库有 2 个副本来源，数据冗余更高 |
3.3 适用与不适用场景
适用场景：
- 日活百万级以上的核心业务系统：订单中心、用户中心、商品中心
- 对可用性要求 99.99% 以上的场景：全年停机时间不能超过 52 分钟，主库故障必须自动切换
- 写请求持续增长、单主节点 CPU/IO 已接近瓶颈的业务
- 多机房部署：M1 和 M2 分放两个机房，机房级故障也能扛
不适用场景：
- 数据一致性要求极强的金融核心结算业务：双主异步复制存在毫秒级延迟，极端情况下（M1 写完还没同步到 M2，M1 宕机且切换）可能丢最后几条数据——金融核心库应该用半同步复制、MGR 或分布式数据库（如 TiDB）
- 小体量业务：日活几万的系统搭四台服务器属于杀鸡用牛刀，一主一从足够
- 团队运维能力不足：双主双从的故障域、运维复杂度远超一主一从，没有专职 DBA 慎入
四、双主双从架构搭建流程
4.1 四台服务器角色分配
| 节点 | 角色 | IP | 职责说明 |
|---|---|---|---|
| 节点1 | 主节点 M1 | 192.168.1.10 | 处理写请求，与 M2 互为主从，同步给 S1 |
| 节点2 | 主节点 M2 | 192.168.1.11 | 处理写请求，与 M1 互为主从，同步给 S2 |
| 节点3 | 从节点 S1 | 192.168.1.12 | 同步 M1 数据，承接读请求 |
| 节点4 | 从节点 S2 | 192.168.1.13 | 同步 M2 数据，承接读请求 |
前置准备（每台服务器）：MySQL 安装、防火墙 3306 互通、NTP 时间同步——与第一部分要求一致，四台都要做。M1 和 M2 上都要创建复制账号 repl（互相同步要用）。
4.2 M1、M2 互为主从配置
M1（192.168.1.10）的 my.cnf：
ini
[mysqld]
server-id = 1 # 集群内唯一
log-bin = mysql-bin # 开binlog（它要当别人的主库）
binlog-format = ROW
relay-log = mysql-relay-bin # 开中继日志（它也要当M2的从库）
# ===== 自增主键错峰配置（双主防冲突的核心！）=====
auto_increment_increment = 2 # 自增步长=2：每次自增加2而不是加1
auto_increment_offset = 1 # 自增起始偏移=1
# 效果：M1 产生的自增ID永远是 1,3,5,7,9...（奇数）M2（192.168.1.11）的 my.cnf：
ini
[mysqld]
server-id = 2
log-bin = mysql-bin
binlog-format = ROW
relay-log = mysql-relay-bin
auto_increment_increment = 2 # 步长同样为2
auto_increment_offset = 2 # 偏移设为2
# 效果：M2 产生的自增ID永远是 2,4,6,8,10...（偶数）
# M1奇数 + M2偶数 → 两个主库同时插入，主键永远不会冲突自增错峰原理：
自增序列 = offset + n × increment。M1 是 1+2n（奇数），M2 是 2+2n（偶数）。如果未来扩到 3 个主库，就设increment=3，三个节点 offset 分别为 1、2、3。
建立双向复制链路：
sql
-- 第1步：在 M1 上查看binlog位置
SHOW MASTER STATUS;
-- 记录：File=mysql-bin.000001, Position=154
-- 第2步：在 M2 上执行，把 M1 配为自己的主库（M2 → 同步 M1）
CHANGE MASTER TO
MASTER_HOST='192.168.1.10', MASTER_PORT=3306,
MASTER_USER='repl', MASTER_PASSWORD='Repl@123456',
MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
START SLAVE;
-- 第3步：在 M2 上查看binlog位置
SHOW MASTER STATUS;
-- 记录：File=mysql-bin.000002, Position=154
-- 第4步：在 M1 上执行，把 M2 配为自己的主库（M1 → 同步 M2），完成互为主从
CHANGE MASTER TO
MASTER_HOST='192.168.1.11', MASTER_PORT=3306,
MASTER_USER='repl', MASTER_PASSWORD='Repl@123456',
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
START SLAVE;验证互为主从是否生效：
sql
-- 在 M1 写入测试数据
INSERT INTO test.repl_check(id, note) VALUES (NULL, 'from M1'); -- 自增ID=1(奇数)
-- 在 M2 查询，应能看到这条 M1 写入的记录（M1→M2 方向复制正常）
SELECT * FROM test.repl_check;
-- 在 M2 写入测试数据
INSERT INTO test.repl_check(id, note) VALUES (NULL, 'from M2'); -- 自增ID=2(偶数)
-- 在 M1 查询，应能看到 M2 写入的记录（M2→M1 方向复制正常）
-- 且两条记录 ID 一奇一偶，自增错峰生效
SELECT * FROM test.repl_check;循环复制拦截机制验证：M1 写入的数据同步到 M2 后，M2 的 binlog 也会记录这次重放，但日志中的 server-id=1。M1 拉取到这条日志发现 server-id 是自己，直接丢弃，不会再次执行——循环被自动打破。
4.3 S1、S2 从节点配置
S1 和 S2 就是两台标准从库，配置方法与第二部分完全一致，只是绑定的主库不同：
S1（192.168.1.12）：
ini
[mysqld]
server-id = 3 # 集群内唯一
relay-log = mysql-relay-bin
read_only = 1
super_read_only = 1sql
-- S1 绑定 M1
CHANGE MASTER TO
MASTER_HOST='192.168.1.10', MASTER_PORT=3306,
MASTER_USER='repl', MASTER_PASSWORD='Repl@123456',
MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
START SLAVE;
SHOW SLAVE STATUS\G -- 确认两个 Running 都是 YesS2（192.168.1.13）：
ini
[mysqld]
server-id = 4 # 集群内唯一
relay-log = mysql-relay-bin
read_only = 1
super_read_only = 1sql
-- S2 绑定 M2
CHANGE MASTER TO
MASTER_HOST='192.168.1.11', MASTER_PORT=3306,
MASTER_USER='repl', MASTER_PASSWORD='Repl@123456',
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
START SLAVE;
SHOW SLAVE STATUS\G4.4 健康检测与虚拟 IP 漂移（Keepalived）
双主架构的最后一块拼图：M1 宕机时，应用怎么自动找到 M2？ 答案是 Keepalived 提供一个"虚拟 IP（VIP）"，应用只连 VIP，Keepalived 负责把 VIP 绑到活着的主库上。
text
应用 → 连接 VIP(192.168.1.100) → Keepalived 转发到当前健康的主库
M1 宕机 → Keepalived 检测到 → VIP 漂移到 M2 → 应用无感知，重连后自动落到 M2M1 上的 Keepalived 配置（/etc/keepalived/keepalived.conf）：
nginx
vrrp_script check_mysql {
script "/etc/keepalived/check_mysql.sh" # MySQL健康检测脚本
interval 2 # 每2秒检测一次
weight -20 # 检测失败优先级降20
}
vrrp_instance VI_1 {
state BACKUP # 两台都配BACKUP，谁先抢谁当（避免主备脑裂切换抖动）
interface eth0 # 网卡名，用 ip addr 查看实际网卡
virtual_router_id 51 # 虚拟路由ID，M1 M2 必须一致
priority 100 # M1优先级100（高于M2），正常时VIP在M1
nopreempt # 非抢占模式：M1恢复后不抢回VIP，避免来回漂移
advert_int 1
virtual_ipaddress {
192.168.1.100/24 # 虚拟IP，应用连接这个地址
}
track_script {
check_mysql # 关联健康检测
}
}M2 上的配置：与 M1 几乎一致，只有两处不同：
nginx
priority 90 # M2优先级90，低于M1，正常情况下VIP漂在M1健康检测脚本（/etc/keepalived/check_mysql.sh，两台都放）：
bash
#!/bin/bash
# 检测本地MySQL是否存活，不存活则杀掉keepalived让VIP漂移
mysql -uroot -p'密码' -e "SELECT 1" > /dev/null 2>&1
if [ $? -ne 0 ]; then
systemctl stop keepalived
exit 1
fi
exit 0bash
chmod +x /etc/keepalived/check_mysql.sh
systemctl enable keepalived && systemctl start keepalived验证 VIP 漂移：
bash
# 正常情况下 VIP 在 M1 上
ip addr | grep 192.168.1.100 # M1 上能看到，M2 上看不到
# 模拟 M1 宕机
systemctl stop mysqld # 在 M1 上执行
# 2~5秒后，M2 上能看到 VIP，应用重连 192.168.1.100 自动落到 M2
ip addr | grep 192.168.1.100 # 在 M2 上执行，能看到即漂移成功五、双主双从读写分离实现
5.1 读写请求路由规则
双主双从下的路由比分一主一从多了一层"写请求分流"：
text
写请求（INSERT/UPDATE/DELETE）→ 写负载均衡器 → M1 或 M2（轮流/按权重）
读请求（SELECT） → 读负载均衡器 → S1 或 S2（轮流/按权重）写请求分流的关键原则：避免同一行数据被两个主库同时修改。 常见分流策略：
- 按业务字段哈希：
user_id % 2 = 0的写 M1，= 1的写 M2（同一用户的数据永远在一个主库，天然无冲突） - 轮询/随机：简单但同一行数据可能被不同主库先后写（依赖自增错峰 + 最后写入生效，有冲突风险）
- 单写模式（推荐新手）：VIP 只绑一个主库承接写，另一个主库纯热备，故障才切换——牺牲写扩展性，换取零冲突
5.2 双主写冲突的处理机制
冲突来源：两个主库几乎同时 UPDATE 同一行数据，binlog 同步到对方时产生版本覆盖。
中间件层面的规避方案（ShardingSphere）：
text
方案1（推荐）：分库分表 + 读写分离组合
按 user_id 取模分片，奇数用户的表路由到 M1 集群、偶数到 M2 集群
同一用户的数据物理上只存在于一个主库 → 冲突从源头消失
方案2：应用层幂等 + 乐观锁
表增加 version 字段，UPDATE ... SET version=version+1 WHERE version=#{oldVersion}
冲突时后到的更新失败，应用层捕获后重试（重读最新数据再更新）
方案3：冲突后重试与回滚
中间件捕获到主键冲突/死锁错误码（1062/1213）时，
自动在另一个主库重试；重试仍失败则整个事务回滚，记录告警日志人工介入5.3 Sharding-JDBC 双主双从完整配置
yaml
spring:
shardingsphere:
datasource:
names: m1,m2,s1,s2 # 四个数据源：双主 + 双从
m1: # 主节点1
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.1.100:3306/shop_db?serverTimezone=Asia/Shanghai
# 注意：生产环境这里填 VIP(192.168.1.100)，故障切换由Keepalived保证
username: root
password: mysql123
m2: # 主节点2（VIP漂移目标，通常作为热备/第二写节点）
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.1.11:3306/shop_db?serverTimezone=Asia/Shanghai
username: root
password: mysql123
s1: # 从节点1（同步M1）
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.1.12:3306/shop_db?serverTimezone=Asia/Shanghai
username: root
password: mysql123
s2: # 从节点2（同步M2）
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.1.13:3306/shop_db?serverTimezone=Asia/Shanghai
username: root
password: mysql123
rules:
readwrite-splitting:
data-sources:
# 读写分离组A：M1写，S1读
group_a:
write-data-source-name: m1 # 写流量入口1
read-data-source-names: s1 # 读流量出口1
load-balancer-name: read_balance # 读负载均衡策略
# 读写分离组B：M2写，S2读
group_b:
write-data-source-name: m2 # 写流量入口2
read-data-source-names: s2 # 读流量出口2
load-balancer-name: read_balance
load-balancers:
read_balance:
type: ROUND_ROBIN # 读请求在组内从库间轮询
# type: WEIGHT # 从库规格不同时用权重
# props:
# s1: 2 # 高配从库承接2份流量
# s2: 1 # 低配从库承接1份流量
props:
sql-show: true # 联调期打印路由日志
# ===== 双主写分流（配合分库分表规则，按user_id分流到两个写组）=====
# 在 rules 中追加 sharding 规则：
# - !SHARDING
# tables:
# t_order:
# actualDataNodes: group_a.t_order,group_b.t_order # 两个写组作为数据源
# databaseStrategy:
# standard:
# shardingColumn: user_id
# shardingAlgorithmName: user-db-mod # user_id % 2 决定写组
# shardingAlgorithms:
# user-db-mod:
# type: MOD
# props:
# sharding-count: 2路由效果总览：
| SQL 类型 | 路由目标 | 说明 |
|---|---|---|
| INSERT（user_id 为奇数） | group_a → M1 | 分片规则选定写组，组内写库 |
| INSERT（user_id 为偶数） | group_b → M2 | 同上 |
| SELECT（user_id 为奇数） | group_a → S1 | 同一组分片，组内读库 |
| SELECT FOR UPDATE（user_id 为偶数） | group_b → M2 | 加锁读强制走写库 |
| 事务内的 SELECT | 该事务的写组主库 | 保证事务一致性 |
5.4 性能测试方法与观测指标
压测工具选择：
| 工具 | 特点 | 适用场景 |
|---|---|---|
| sysbench | MySQL 官方御用压测工具，内置读写混合场景 | 数据库层极限压测 |
| JMeter | 模拟真实 HTTP 业务流量，经应用层再到数据库 | 端到端全链路压测 |
sysbench 压测示例：
bash
# 准备测试数据（10张表，每张100万行）
sysbench oltp_read_write \
--mysql-host=192.168.1.100 --mysql-port=3306 \
--mysql-user=root --mysql-password=mysql123 --mysql-db=sbtest \
--tables=10 --table-size=1000000 prepare
# 读写混合压测：64线程，跑300秒（oltp_read_write 默认读写比约 14:4:4:3）
sysbench oltp_read_write \
--mysql-host=192.168.1.100 --mysql-port=3306 \
--mysql-user=root --mysql-password=mysql123 --mysql-db=sbtest \
--tables=10 --table-size=1000000 \
--threads=64 --time=300 --report-interval=10 run
# 纯读压测（验证读扩展能力）
sysbench oltp_read_only --threads=64 --time=300 ... run核心指标观测方法：
QPS/TPS（吞吐量）：sysbench 报告直接输出
queries: xxx (yyy per sec.)。对比基线：单机 MySQL → 一主一从 → 双主双从，纯读 QPS 应接近线性增长。响应时间：关注报告的
latency (ms) 95th percentile（95 分位延迟），而不是平均值——平均值会被长尾掩盖。生产警戒线：95 分位 < 100ms。主从延迟：压测期间在 S1/S2 上持续观测：
sqlSHOW SLAVE STATUS\G -- 盯 Seconds_Behind_Master高并发写入时该值冲到 10~30 秒是常见现象；如果持续增长不回落，说明从库重放速度跟不上（开启并行复制，见 2.3 节）。
服务器资源水位：压测期间用
top、iostat -x 1、vmstat 1观测四台服务器的 CPU、磁盘 IO、网络带宽，确认瓶颈在哪一层——读写分离扩容的前提是瓶颈在数据库连接/读 IO，而不是应用层。
验收标准参考：
text
- 读 QPS：双从库的总读 QPS ≥ 单库的 1.8 倍（有归并损耗，达不到理论 2 倍）
- 故障切换：kill 掉 M1 的 mysqld，应用 5 秒内恢复写入能力
- 数据一致性：压测结束后，四台库的行数校验和（checksum table）完全一致六、读写分离技术总结
6.1 一主一从 vs 双主双从对比表
| 对比维度 | 一主一从 | 双主双从 |
|---|---|---|
| 服务器数量 | 2 台 | 4 台 |
| 高可用能力 | 弱：主库故障需人工切换（分钟~小时） | 强：VIP 自动漂移，秒级切换 |
| 写吞吐 | 单主写，无法扩展 | 双主写，理论翻倍 |
| 读吞吐 | 1 从库分担 | 2 从库分担 |
| 数据一致性 | 较好（单点写入，冲突源少） | 需精心设计（自增错峰 + 分片隔离） |
| 运维复杂度 | 低，半天可搭完 | 高，涉及四节点 + Keepalived + 冲突预案 |
| 成本 | 低 | 服务器成本翻倍 |
| 适用业务规模 | 日活 10 万以下、读写比高 | 日活百万级、可用性 99.99%+、写量持续增长 |
6.2 读写分离的核心收益
- 性能提升：读请求被从库分流，主库专注写操作，整体 QPS 随从库数量近线性增长。
- 可用性增强：主库故障时从库可顶上（一主一从需人工、双主双从自动切换）；从库还是天然的热备数据源。
- 业务可扩展性（Scalability）提升：读压力大就加从库（一主多从），写压力大再演进分库分表——读写分离是数据库水平扩展的第一步，为后续架构升级铺路。
6.3 落地常见痛点与规避方案
| 痛点 | 表现 | 规避方案 |
|---|---|---|
| 主从延迟 | 写完立刻查从库，查不到刚写的数据 | 延迟敏感场景用 HintManager.setWriteRouteOnly() 强制读主库；或按延迟路由（中间件探测 Seconds_Behind_Master，超阈值摘除从库） |
| 数据一致性 | 从库被误写入 / 复制中断导致主从不一致 | 从库开 super_read_only；定期用 pt-table-checksum 校验主从数据差异 |
| 双主写冲突 | 同一行被两个主库同时更新，数据互相覆盖 | 自增主键错峰 + 按业务字段分片，让同一行只在一个主库可写 |
| 运维成本增加 | 节点从 1 变 4，监控、备份、故障演练量翻倍 | 自动化先行：复制状态监控告警、备份脚本、Keepalived 切换演练纳入日常 SOP |
| 中间件复杂度 | 引入 ShardingSphere 后 SQL 排查链路变长 | 联调期开 sql-show，生产期用 DistSQL 查路由；团队培训中间件原理 |
6.4 选型建议
text
业务规模小（日活 < 10万）、读多写少、预算有限
→ 单机 MySQL + 优化索引 SQL，别急着上读写分离
业务增长中（日活 10万~100万）、读压力明显、可容忍主库故障人工切换
→ 一主一从（或一主多从）+ ShardingSphere 读写分离
核心业务（日活 > 100万）、可用性要求 99.99%+、写量接近单库瓶颈、有专职运维/DBA
→ 双主双从 + Keepalived + 中间件分片分流
数据一致性要求极强的金融结算核心
→ 不要用异步复制的双主！考虑半同步复制、MGR 组复制或分布式数据库（TiDB/OceanBase）一句话总结：读写分离解决的是"读"的扩展问题，高可用解决的是"主库挂"的问题，分库分表解决的是"写和存储"的扩展问题——三者层层递进，按业务阶段逐步引入，永远别超前建设。