凌晨两点，我在香港荃湾机房紧盯着监控大屏：订单明细的读查询 p99 突然从 38ms 爬到 420ms，ProxySQL 的 Seconds_Behind_Source 曲线像一根刺，猛地抬头到 12 秒。那一瞬间，我明白这不是网络抖动那么简单——香港本地的主从延迟被一次批量写入“打爆”了。
那晚我做了两件事：第一，把读流量在 30 秒内“拉回”强一致（强制走主库）；第二，连夜把整套 MySQL + Redis 读写分离的链路，按“延迟治理 + 回滚预案”重搭了一遍。下面是一份完整的、我自己踩过坑的实操手册。

拓扑与硬件：别让瓶颈埋在铁皮里

机房：香港荃湾 T 级机房（双路供电 + N+1 制冷）

网络：同机房同架（ToR 交换机 10GbE，跨机柜延迟 < 0.1ms）

主库（Writer）：

• 1U，AMD EPYC 7313（16C/32T）
• 256GB DDR4 ECC
• 两块 3.84TB 企业级 NVMe（RAID1，硬件阵列卡带电容）
• 双口 10GbE
• 只读库（Reader）×2：同配置（NVMe 容量 1.92TB）
• Redis 主从 + Sentinel（3 个 Sentinel，2 台 Redis 实例）：
• 32GB 内存，NVMe 960GB，AOF 开启
• 负载均衡：ProxySQL 2.5（独立虚机，4C/8G，双网卡）

说明：业务历史原因运行在 CentOS 7（是的，已经 EOL）。文中按 CentOS 7 操作，但我实际同时准备了 Rocky/Alma 8/9 的替代方案。如果你正新建环境，建议上 RHEL/Alma/Rocky 8+。

部署概览（一步到位总览图）

MySQL 主从：MySQL 8.0，GTID、ROW 模式、半同步（降低丢数据风险），只读库并行复制。

ProxySQL：写入指向主库，读请求优先只读库，自动依据复制延迟/健康探测调整路由，支持“强制走主”的开关和“会话粘滞”。

Redis：读缓存（写穿/删缓存策略），Key-Tag 级联失效，Sentinel 保高可用。

延迟治理：pt-heartbeat + ProxySQL Lag 策略 + 业务“读你所写”粘滞（可选 GTID 等待）。

回滚预案：

• 延迟只读库（延迟 10 分钟）
• XtraBackup 全量/增量 + Binlog 持续归档（PITR）
• 快速“闪回”流程（基于 binlog 反向 SQL 或延迟库导出回放）

压测与验收：Sysbench + 自定义读写混合压测，记录优化前后指标。

1）MySQL 安装与基础配置（CentOS 7）

1.1 安装 MySQL 8.0 社区版（RPM 仓库）

# 官方仓库 RPM
rpm -Uvh https://repo.mysql.com/mysql80-community-release-el7-7.noarch.rpm
yum-config-manager --enable mysql80-community
yum install -y mysql-community-server

systemctl enable mysqld
systemctl start mysqld

# 首次登录重置 root 密码
grep 'temporary password' /var/log/mysqld.log
mysql_secure_installation

1.2 主库 my.cnf（关键项）

/etc/my.cnf（仅展示关键片段）

[mysqld]
server_id=1
log_bin=binlog
binlog_format=ROW
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
binlog_expire_logs_seconds=604800
binlog_row_image=FULL

# 刷新策略：牺牲一点吞吐换数据安全
innodb_flush_log_at_trx_commit=1
sync_binlog=1
innodb_flush_method=O_DIRECT

# 写集合追踪，利于并行复制
transaction_write_set_extraction=XXHASH64
binlog_transaction_dependency_tracking=WRITESET

# InnoDB 缓冲池等
innodb_buffer_pool_size=64G
innodb_io_capacity=4000
innodb_flush_neighbors=0
thread_cache_size=64

# 半同步复制插件
plugin_load_add='semisync_master=semisync_master.so;semisync_slave=semisync_slave.so'
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=250  # ms

# 元数据表
master_info_repository=TABLE
relay_log_info_repository=TABLE

# 连接与字符集（按需）
max_connections=4000
character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci

1.3 只读库 my.cnf（并行复制 + 只读）

[mysqld]
server_id=2        # 每台只读库不同
read_only=ON
super_read_only=ON
relay_log_recovery=ON

# MySQL 8 推荐使用 replica_* 同义词（也兼容 slave_*）
replica_parallel_workers=8
replica_preserve_commit_order=ON
# 兼容变量（某些版本仍用 slave_*）
slave_parallel_workers=8
slave_preserve_commit_order=ON

# 半同步从端
plugin_load_add='semisync_master=semisync_master.so;semisync_slave=semisync_slave.so'
rpl_semi_sync_slave_enabled=ON

1.4 建立主从（基于 GTID）

在主库创建复制账号：

CREATE USER 'repl'@'10.%' IDENTIFIED BY 'StrongPass#123';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'10.%';
FLUSH PRIVILEGES;

在只读库执行（以主库 10.0.0.10 为例）：

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='10.0.0.10',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='StrongPass#123',
  SOURCE_AUTO_POSITION=1;

START REPLICA;
SHOW REPLICA STATUS\G

并行复制小贴士：replica_parallel_workers 大于 0 才会启用，WRITESET 追踪能显著提升应用并行度，业务多表写入时效果尤佳。

2）ProxySQL：智能读写分离与延迟兜底

2.1 安装 & 管理入口

yum install -y proxysql
systemctl enable proxysql && systemctl start proxysql
mysql -u admin -p -h 127.0.0.1 -P 6032   # 默认 admin/admin

2.2 定义 hostgroups（10 写，20 读）

-- 后端 MySQL 实例
INSERT INTO mysql_servers(hostgroup_id,hostname,port,max_connections) VALUES
(10,'10.0.0.10',3306,2000),   -- writer
(20,'10.0.0.11',3306,2000),   -- reader1
(20,'10.0.0.12',3306,2000);   -- reader2

-- 监控账号（到各 MySQL）
INSERT INTO mysql_users(username,password,default_hostgroup,active) VALUES
('app','AppPass#123',10,1);

-- 将 ProxySQL 与复制关系绑定（自动识别只读）
INSERT INTO mysql_replication_hostgroups
(writer_hostgroup,reader_hostgroup,check_type,comment)
VALUES (10,20,'read_only','rw split');

LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL USERS TO RUNTIME;   SAVE MYSQL USERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

2.3 读写规则 + 会话粘滞（读你所写）

-- 1) 写语句 → 写组
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)
VALUES
(100,1,'^\\s*(INSERT|UPDATE|DELETE|REPLACE|ALTER|CREATE|DROP)\\b',10,1);

-- 2) 显式强一致（SQL 注释触发） → 写组
INSERT INTO mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply,flagOUT)
VALUES
(110,1,'/\\*force_master\\*/',10,1,1);

-- 3) 事务内读 → 写组（防止事务读到旧数据）
INSERT INTO mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES
(120,1,'^\\s*SELECT.*FOR\\s+UPDATE',10,1);

-- 4) 其他 SELECT → 读组
INSERT INTO mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES
(130,1,'^\\s*SELECT\\b',20,1);

LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

2.4 延迟感知：自动“回拉到主”

ProxySQL 会定期从只读库读取 Seconds_Behind_Source。我们设定阈值 mysql-monitor_slave_lag_when_null_ms 与脚本联动，把滞后的只读节点临时下线或把读路由回主。

简易策略（思路）：

• Seconds_Behind_Source > 1s：该只读库权重降为 0（不接新查询）。
• 所有只读库都 > 1s：全量 SELECT 回写组（强一致保护）。
• 恢复 < 500ms 持续 60s：逐步放量恢复。

示例：用调度器更新权重（伪 SQL，很多团队用外部脚本+REST/6032 API 实现）：

-- 假设我们把 reader 的 weight 存在 mysql_servers 的 'weight' 字段，脚本周期性调整
UPDATE mysql_servers SET weight=0 WHERE hostgroup_id=20 AND status='SHUNNED'; -- 滞后标记
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;

生产建议：配合 pt-heartbeat 写入心跳表，比 Seconds_Behind_Source 更敏感、稳定。

3）Redis：缓存不是魔法，失效才是核心

3.1 安装与 Sentinel

yum install -y redis
# redis.conf 关键项
bind 0.0.0.0
protected-mode no
requirepass StrongRedis#123
appendonly yes
appendfsync everysec
maxmemory 24gb
maxmemory-policy allkeys-lru
notify-keyspace-events Ex

Sentinel（3 个）：

port 26379
sentinel monitor myredis 10.0.0.13 6379 2
sentinel auth-pass myredis StrongRedis#123
sentinel down-after-milliseconds myredis 5000
sentinel failover-timeout myredis 60000

3.2 Key 设计与失效策略

• 写穿（Write-through）：写 MySQL 成功后，同步更新/删除对应 Redis Key。
• 删缓存优先：更新路径：先 DELETE cache → 再 UPDATE DB → 成功后不立即回填，读时懒加载（避免并发下缓存脏读）。
• Key-Tag：集合/列表页使用 Tag（集合 Set 记录成员 Key），批量变更时按 Tag 清理。

示例（Python 伪代码）：

def update_order(order_id, payload):
    # 1) 先删缓存
    redis.delete(f"order:{order_id}")
    # 2) 写DB（事务）
    with mysql.begin() as tx:
        tx.execute("UPDATE orders SET ... WHERE id=%s", (order_id,))
    # 3) 可选：发一个Pub/Sub让边缘节点做旁路预热
    redis.publish("cache_invalidate", f"order:{order_id}")

3.3 “读你所写”在缓存层的两种做法

会话粘滞：用户在一次写后 N 秒内的读强制走主库（通过 ProxySQL 注释或路由），并且跳过缓存。简单可靠。

时间水位：把“上次写入时间戳”放到 Redis（user:{uid}:last_write_ts），读时若缓存数据 data_ts < last_write_ts + epsilon，则绕过缓存或强制读主库。适合评论/订单这种强一致需求不高但又要尽量走缓存的场景。

4）延迟治理：从“看得见”到“控得住”

4.1 心跳与监控

心跳表（每秒主库写一次当前时间），只读库读：

CREATE DATABASE if not exists meta;
CREATE TABLE meta.heartbeat(
  id TINYINT PRIMARY KEY,
  ts TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB;

INSERT INTO meta.heartbeat(id, ts) VALUES (1, NOW(6))
  ON DUPLICATE KEY UPDATE ts=NOW(6);

只读库延迟 = NOW(6) - meta.heartbeat.ts。结合 Prometheus 导出（或 pt-heartbeat）打点，ProxySQL 依据此指标控权重。

4.2 复制参数微调（我在香港机房的有效组合）

• replica_parallel_workers=8~16：多表写入业务收益大。
• replica_preserve_commit_order=ON：读一致性更好。
• rpl_semi_sync_master_enabled=ON + rpl_semi_sync_slave_enabled=ON：至少保证 1 个只读库收到并刷盘 再 ack，降低主机宕机丢事务风险。
• sync_binlog=1 + innodb_flush_log_at_trx_commit=1：牺牲写吞吐换可靠；配合 NVMe 性能可接受。

4.3 阶段化流量保护

• 绿色：延迟 < 100ms，全部 SELECT 走只读库。
• 黄色：100ms ~ 1s，热点表/请求转写，或强制注释 /*force_master*/。
• 红色：> 1s，读全回写组，触发限流/降级（关闭重报表、延后风控离线计算）。

5）回滚预案：真出事时的“后悔药”

5.1 延迟只读库（Delayed Replica）

我固定保留一台只读库 延迟 10 分钟 应急：

STOP REPLICA SQL_THREAD;
CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = ('meta.heartbeat'); -- 可选
CHANGE REPLICATION SOURCE TO SOURCE_DELAY=600;
START REPLICA SQL_THREAD;

出事故（误删/脏数据）时，可以在延迟窗口内从这台库导出“正确版本”的数据，定点还原。

5.2 PITR（Point-in-Time Recovery）

• XtraBackup：周全量 + 日增量。
• binlog 归档：落到对象存储（本地 + 异地）。
• 恢复：还原到最新全量 + 增量后，用 mysqlbinlog --start-datetime/--stop-datetime 回放到事故前一刻。

示例：

# 导出
xtrabackup --backup --target-dir=/backup/full-$(date +%F)
# 恢复
xtrabackup --prepare --target-dir=/backup/full-2025-09-26
xtrabackup --copy-back --target-dir=/backup/full-2025-09-26
# 回放 binlog 到 02:13:40 前
mysqlbinlog --start-datetime="2025-09-27 00:00:00" \
            --stop-datetime="2025-09-27 02:13:40" \
            /var/lib/mysql/binlog.* | mysql -u root -p

5.3 反向 SQL（快速“闪回”）

对于误删/误更新单表，结合 binlog 生成反向 SQL（如开源 binlog2sql 一类工具），在 非业务高峰 回放。
注意：务必在演练库先 dry-run，并与缓存失效/ProxySQL 路由配合，防止再次脏读。

6）应用层的三档一致性（可选方案组合）

强一致（订单/支付）

• 写后 2~3 秒内读：强制走主（会话粘滞）。
• 所有 SELECT ... FOR UPDATE、事务内读：走主。
• 有界陈旧（资料页/列表）
• 只读库延迟阈值 200ms；超阈则该请求走主。
• 缓存 ttl=30~60s，但遇到命中 last_write_ts 则绕过缓存。

读你所写（进阶 GTID 等待）

• 写事务结束后获取本事务 GTID（可通过审计插件或业务侧记录 binlog pos），读时在只读库执行 SELECT WAIT_FOR_EXECUTED_GTID_SET(gtid, 1)，失败则回主。
• 成本略高，适合少量关键路径。

7）压测与优化前后指标（摘自我现场记录）

关键收益来自：并行复制 + WRITESET、半同步稳住主库、ProxySQL 延迟感知、缓存正确失效。

8）常见坑与我的解决过程

坑 1：只改 slave_parallel_workers 不生效
解决：重启复制通道或实例，并确认 binlog_transaction_dependency_tracking=WRITESET 在主上已启用。

坑 2：Seconds_Behind_Source 偶发 0
解决：并行复制下该指标不稳定，上心跳表，以真实业务维度衡量延迟。

坑 3：删缓存顺序
解决：先删缓存再写库，失败回滚后要补偿重建缓存或写回。并发高时用分布式锁/队列避免“缓存穿透雪崩”。

坑 4：半同步导致写抖动
解决：rpl_semi_sync_master_timeout 调到 250ms，并保证至少一台只读库 NVMe 性能可靠；网络层 10GbE、MTU 9000 有助平滑。

坑 5：延迟只读库忘记排除心跳表
解决：延迟库可以过滤心跳表或独立 schema，避免可观察指标“乱跳”。

坑 6：CentOS 7 包源 EOL
解决：锁定 tested 版本 RPM，置灰自动升级；同时准备容器化或 Rocky/Alma 迁移剧本。

9）运维手册：上线 checklist（可直接套用）

•  主从均启用 GTID、ROW、WRITESET；只读库并行复制 8+。
•  半同步开关已验证，主库超时合理，至少一只读库稳定 ACK。
•  ProxySQL 规则到位：DML→写组；事务内读→写组；SELECT→读组；/*force_master*/ 生效。
•  延迟监控：心跳表/pt-heartbeat + Prometheus 告警，阈值 200ms/1s 两级。
•  Redis：AOF everysec、Key-Tag、删缓存优先；Sentinel 故障演练通过。
•  回滚：延迟只读库 10min 在位；XtraBackup 最近一次全量 + binlog 归档可验证恢复。
•  压测：峰值 1.5× 业务流量通过；延迟保护策略自动触发且可回退。

那次夜里，我在机柜门缝看见自己满脸油光——但图表从 12 秒回落到 200ms 以内的时候，心里松了一口气。
读写分离不是把“读流量丢给从库”那么简单；真正的难点在主从延迟发生时如何“控住局面”，在错误发生时如何“快速回到正确”。
今天的这套 MySQL + Redis 方案，在香港机房的网络与 NVMe 条件下跑得很稳。我也把所有“救火动作”写成了自动化脚本和演练文档。这样下一次凌晨两点，我希望我只需要检查一次告警，然后——去喝一杯热咖啡。

附录：关键配置片段（可直接复制）

ProxySQL 只读库健康阈值（示意）

-- 读库延迟超过 1000ms 标记 SHUNNED（脚本轮询）
-- 实际生产推荐配合外部脚本调用 6032 API 动态调整 'status' / 'weight'

心跳采集（Prometheus Exporter 伪 SQL）

SELECT TIMESTAMPDIFF(MICROSECOND, ts, NOW(6))/1000 AS repl_lag_ms FROM meta.heartbeat WHERE id=1;

Redis Pub/Sub 失效

# 订阅端
redis-cli -a StrongRedis#123 SUBSCRIBE cache_invalidate

XtraBackup 定时任务（示例）

# /etc/cron.d/backup
0 3 * * 0 root xtrabackup --backup --target-dir=/backup/full-$(date +\%F)
0 3 * * 1-6 root xtrabackup --backup --incremental-basedir=/backup/full-$(date -d "last sunday" +\%F) --target-dir=/backup/inc-$(date +\%F)