618 当晚 00:05，我在香港葵涌机房值班。监控屏上 Master 的 CPU 一路飙红到 92%，磁盘队列深度一度冲到 12。客服群开始闪烁：“用户下单卡在‘提交订单’”。这不是我们第一次遭遇跨境高峰的抖动，但这次更猛：主库的写入队列被一堆读查询“挤”得喘不过气。
我做了一个决定：把读压力全部从主库剥离，在香港机房落地 MySQL 读写分离，并把现场踩坑、参数、流程都记录下来。下面就是我那一夜+后续一周加固的完整实操手记。

架构目标 & 约束

目标：

• 下单链路（写）稳定优先，P95 < 120ms（跨境网络除外）。
• 读查询（商品浏览、订单查询、库存看板等）不压主。
• 复制延迟可控（绝大多数场景 < 300ms；对强一致读有兜底策略）。
• 可平滑扩容、支持滚动维护。

约束：

• 机房： 香港（HK），10GbE 内网，BGP 多线对外，跨境主线路为 CN2/GIA。
• 系统： CentOS 7（生产里我们仍大量使用它，内核 3.10.x）。
• MySQL： 8.0 系列（Percona Server 8.0 也可）。
• 中间件： ProxySQL（主备 2 节点 + Keepalived 漂移 VIP）。
• 复制： GTID + 半同步（Semi-sync）+ 异步只读从库做扩展。
• Failover： Orchestrator 负责拓扑探测与主从切换（可选但强烈建议）。

现场硬件与网络拓扑

服务器与磁盘参数（落地配置）

• 磁盘要点： NVMe RAID1 + XFS，/etc/fstab 加 noatime,nodiratime；关 THP；设置 deadline/none 调度器（根据内核/驱动）。
• 网络要点： MySQL/ProxySQL/Keepalived 走 10GbE，跨机房复制仅在香港内部完成；对外 CN2/GIA 仅承接业务流量。

预估容量与读写拆分的收益模型

• 峰值 UV：120k/min
• 下单写 QPS（含库存扣减、订单落库）：2.8k ~ 3.2k
• 读 QPS（商品、订单查询、活动等）：18k ~ 25k（高峰）
• 以往瓶颈：主库读写混合，Buffer Pool 命中率不稳，redo 竞争+IO 抖动，max_connections 与线程调度抖动导致排队变长。

读写分离预期：

• 主库仅承载写事务 + 必要的强一致读：写延迟下降 30%+。
• 读全部落到从库或只读组，横向扩容读节点：吞吐线性拉高。
• 冷热分离后，主库缓冲/redo 更稳，p95 抖动降低。

系统与内核调优（CentOS 7）

1) 关闭透明大页 & NUMA 策略

# 立即关闭
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

# 开机参数（/etc/default/grub）
# GRUB_CMDLINE_LINUX="... transparent_hugepage=never numa=off"
grub2-mkconfig -o /boot/grub2/grub.cfg

2) 文件句柄与内核参数

/etc/security/limits.conf

mysql soft nofile 1048576
mysql hard nofile 1048576

/etc/sysctl.d/99-mysql-tuning.conf

vm.swappiness = 1
vm.dirty_ratio = 10
vm.dirty_background_ratio = 3

net.core.somaxconn = 65535
net.core.netdev_max_backlog = 250000
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.tcp_tw_reuse = 1
net.ipv4.ip_local_port_range = 10000 65000

提示： 老内核上别碰 tcp_tw_recycle。跨境 NAT 链路上会出事。

MySQL 8.0：主从搭建（GTID + 半同步）

3) 基础安装

yum install -y mysql-community-server # 或 Percona Server 8.0
systemctl enable mysqld && systemctl start mysqld

4) 主库 my.cnf（关键参数）

/etc/my.cnf

[mysqld]
server_id = 1001
log_bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON

# 半同步（插件）
plugin_load_add = semisync_master.so
rpl_semi_sync_master_enabled = ON
rpl_semi_sync_master_timeout = 1000   # 1s
rpl_semi_sync_master_wait_for_slave_count = 1

# InnoDB
innodb_buffer_pool_size = 160G          # 物理内存 ~60-65%
innodb_buffer_pool_instances = 8
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
# 8.0.30+:
# innodb_redo_log_capacity = 8G
# 老版本：
innodb_log_file_size = 4096M
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_neighbors = 0
innodb_flush_method = O_DIRECT

# 连接与线程
max_connections = 4000
thread_cache_size = 256
table_open_cache = 8000
table_definition_cache = 4000

# 其他
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci
skip_name_resolve = ON

5) 从库 my.cnf（只读 + 延迟控制）

[mysqld]
server_id = 2001   # 不同于主
read_only = ON
super_read_only = ON
relay_log_recovery = ON
relay_log_info_repository = TABLE
master_info_repository = TABLE

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

# 其他同主，适当降低 innodb_io_capacity

6) 账号与复制

在主库：

CREATE USER repl@'10.%' IDENTIFIED BY 'StrongPassw0rd!';
GRANT REPLICATION SLAVE ON *.* TO repl@'10.%';
FLUSH PRIVILEGES;

-- 初始化数据（推荐 xtrabackup 物理热备恢复到从库）

在从库：

CHANGE MASTER TO
  MASTER_HOST='10.0.0.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='StrongPassw0rd!',
  MASTER_AUTO_POSITION=1;

START SLAVE;
SHOW SLAVE STATUS\G

半同步说明： 写事务在主库提交时至少等待一个从库确认收到 binlog（写落磁即可），能显著降低主从差异。若从库挂了会回退为异步（避免主库卡死）。

代理层：ProxySQL + Keepalived 实战

7) 安装

yum install -y proxysql keepalived
systemctl enable proxysql && systemctl start proxysql

8) ProxySQL 基本对象

• Hostgroup 10：Writer（主库）
• Hostgroup 20：Reader（从库组）
• Hostgroup 30：Monitor/故障转移备用（可选）

登录 ProxySQL Admin（默认 6032）：

mysql -u admin -p -h 127.0.0.1 -P6032

注册后端：

-- Writer
INSERT INTO mysql_servers(hostgroup_id, hostname, port, max_connections)
VALUES (10, '10.0.0.10', 3306, 2000);

-- Readers
INSERT INTO mysql_servers(hostgroup_id, hostname, port, max_connections)
VALUES (20, '10.0.0.21', 3306, 1500),
       (20, '10.0.0.22', 3306, 1500);

-- 心跳与复制延迟检查
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;

接入账户（业务侧使用 ProxySQL 的账号）：

INSERT INTO mysql_users(username, password, default_hostgroup, transaction_persistent)
VALUES ('appuser', 'AppStrongP@ss', 20, 1); -- 默认读组，事务内粘滞

-- 允许写入：将 INSERT/UPDATE/DELETE/DDL 等重定向到 10

读写分离规则（核心）：

-- 规则优先级从小到大，先匹配写/强一致读
-- 1) 事务、锁/强一致读走主
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, destination_hostgroup, apply, flagIN, comment)
VALUES
(100, 1, '^BEGIN', 10, 1, 0, '事务起始走主'),
(110, 1, 'FOR UPDATE', 10, 1, 0, '加锁读走主'),
(120, 1, '/\\*\\s*read_from_master\\s*\\*/', 10, 1, 0, '显式强一致读'),
(130, 1, '^SET\\s', 10, 1, 0, '会话级设置走主');

-- 2) 写操作走主
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, destination_hostgroup, apply, flagIN, comment)
VALUES
(200, 1, '^(INSERT|UPDATE|DELETE|REPLACE|CREATE|ALTER|DROP|TRUNCATE)\\s', 10, 1, 0, '写走主');

-- 3) 普通 SELECT 走读组（延迟过滤）
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, destination_hostgroup, apply, flagIN, cache_ttl, comment)
VALUES
(300, 1, '^SELECT', 20, 1, 0, 100, '读走从，缓存100ms');

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

复制延迟与健康检查：

-- 启用监控模块的 replication_lag 过滤（8.0 需 sys 库/事件）
SET mysql-monitor_enabled=1;
SET mysql-monitor_connect_interval=2000;
SET mysql-monitor_ping_interval=1000;
SET mysql-monitor_read_only=1;

-- 动态把 Seconds_Behind_Master > 2s 的从库临时下线或降低权重
-- （可配合 mysql_replication_hostgroups 机制）

粘滞性（stickiness）： transaction_persistent=1 能保证事务内后续查询都在同一后端（避免“读到旧数据”）。非事务场景若有“写后读”，推荐在关键读语句前加注释 /* read_from_master */ 或走“短事务”。

9) Keepalived 漂移 VIP（两台 ProxySQL）

/etc/keepalived/keepalived.conf（节点 A，优先级高）

vrrp_instance VI_1 {
  state MASTER
  interface eth0
  virtual_router_id 51
  priority 120
  advert_int 1
  authentication {
    auth_type PASS
    auth_pass 6c9fxxxx
  }
  virtual_ipaddress {
    10.0.0.100/24 dev eth0
  }
  track_script {
    chk_proxysql
  }
}
vrrp_script chk_proxysql {
  script "/usr/local/bin/check_proxysql.sh"
  interval 2
  fall 2
  rise 2
}

业务只连 10.0.0.100:6033，ProxySQL A/B 自动漂移，避免单点。

业务侧改造要点

连接串改为 ProxySQL VIP：mysql://appuser:***@10.0.0.100:6033/db。

强一致读：

• 写后紧跟的关键查询在 SQL 前加 /* read_from_master */；或将写与读包在 BEGIN; ... COMMIT; 内（短事务）。
• 使用 SELECT ... FOR UPDATE 的场景自然走主。
• 慢 SQL 治理：读多但耗时的查询要加索引或做只读副本的二级索引优化（不影响主库写性能）。

索引与表结构的“快修”示例（真实案例）

订单表 orders（亿级数据）在订单列表页出现 P95 > 700ms（读压在从库上展示更明显）。快速体检发现 where/order by 的“组合索引”缺失。

-- 典型查询
SELECT order_id, user_id, status, created_at
FROM orders
WHERE user_id = ? AND created_at >= ? AND created_at < ?
ORDER BY created_at DESC
LIMIT 50;

-- 快修索引（覆盖）
CREATE INDEX idx_orders_userid_createdat ON orders(user_id, created_at DESC)
INVISIBLE;  -- 先 INVISIBLE 做黑盒验证

-- 观察执行计划/回放流量无回退，再切 VISIBLE
ALTER TABLE orders ALTER INDEX idx_orders_userid_createdat VISIBLE;

经验： 只读从库可以承载更多“为读而建”的索引，避免主库写入膨胀太多（仍需评估 DDL 开销）。用 INVISIBLE 索引先做灰度很香。

压测与观测

10) Sysbench 读写压测（抽样）

压测命令（示意）：

sysbench oltp_read_write.lua \
  --mysql-host=10.0.0.100 --mysql-port=6033 \
  --mysql-user=appuser --mysql-password=*** \
  --tables=32 --table-size=1000000 --threads=256 \
  --time=300 run

11) 需要看的核心指标（PMM/Prometheus）

• 主库：trx_commit, Innodb_log_waits, redo write/s, fsync/s, Rows_written/s
• 从库：Seconds_Behind_Master, SQL_Thread, IO_Thread, relay log 应用速率
• ProxySQL：Query Processor Time, Queries Routed, Conn Pool 命中
• 系统：磁盘队列深度、NVMe 延迟、NIC 丢包、CPU iowait

常见坑与现场解法

复制延迟突增

症状：Seconds_Behind_Master 突然 5s+。

解法：定位某从库 redo 写入饱和（发现 Prometheus 上 fsync/s 抖动），排查为某报表大查询触发 massive tmp 磁盘 IO。临时将该从库权重降为 0（ProxySQL 动态下掉），夜间重建二级索引并对报表改写成分批分页聚合。

读到旧数据（写后读一致性）

症状：用户下单后立即查询订单，偶发“查不到”。

解法：在订单提交成功后的 200ms 内，订单查询请求自动带注释 /* read_from_master */；且补充短事务包裹模式。问题消失。

ProxySQL 规则误伤

症状：某些 SET/CALL 被错误路由到读库。

解法：规则表中优先匹配 ^SET, ^CALL 到 Writer；并开启规则命中日志短期观察。

DDL 抖动

症状：从库做在线 DDL（gh-ost/pt-osc），读延迟轻微上升。

解法：扩一个临时只读节点承接 DDL；ProxySQL 中将 DDL 专用从库权重设高，其他从库避让。

半同步“退化”

症状：从库掉线，主库写延迟突然变“正常”（其实退回异步）。

解法：接入 Orchestrator，确保至少一台半同步 ACK 节点常驻；同时在主库报警 rpl_semi_sync_master_status=OFF 即告警。

灰度与回滚策略

灰度比例：先将 10% 的读流量（某业务线、或某地区）指向 ProxySQL VIP，其余走直连主库；观测 24 小时后扩大到 100%。

回滚：保持业务侧保留“直连主库”的连接串配置项（feature flag 切换），ProxySQL/Keepalived 任一组件异常可紧急回切。

数据一致性：每日比对主从校验（pt-table-checksum），对差异表夜间 pt-table-sync 校准。

安全与权限

• 业务账号最小化授权（只对业务库授权，读写分离下读库也要严格 SELECT）。
• 从库 read_only+super_read_only 防误写。
• 管控台操作走 bastion，MySQL 开启 audit_log（Percona/企业版可选）。
• ProxySQL Admin 口令旋转、仅限内网访问。

运维清单（上线前 1 小时核对）

•  主从延迟 < 300ms，半同步状态 ON。
•  ProxySQL 规则命中正确，写入全走 Hostgroup 10。
•  “写后读”路径已加 /* read_from_master */ 或事务包裹。
•  慢 SQL 白名单/黑名单审计完毕。
•  Keepalived 漂移测试通过，VIP 漂移 < 2s。
•  监控告警已接入（含 Seconds_Behind_Master、rpl semi-sync、ProxySQL 5xx）。
•  回滚预案与直连配置可用。

我的一些“参数口袋本”（可按需微调）

替代方案与何时选它

MySQL Router + InnoDB Cluster (MGR)：原生、强一致、自动故障转移，但对网络与仲裁要求更高，写扩展有限。

MariaDB MaxScale：读写分离强，商用许可需注意。

云托管（RDS + 代理）：如果你在云上且对自建成本敏感，优先选云代理（读写分离开关+白屏配置），但跨境链路可控性略差。

复盘：那一夜与后一周

那一夜，我们先把 ProxySQL + 一主两从跑起来，业务只切换了“订单查询”和“商品列表”两个路径（约 35% 的读流量）。半个小时后，主库 p95 从 410ms 掉到 240ms，卡单告警明显下降。
后三天，我们补齐了“写后读”的注释与短事务，慢 SQL 做了两处覆盖索引；新增一台只读节点专供报表，Seconds_Behind_Master 的尖刺再没出现。
一周后，我们把读写分离扩到所有读请求，峰值 25k QPS 平稳度过，客服群安静了很多。

第二周的周五夜班，我又坐在同一个机柜前，空调的风还是那么冷。但这次屏幕上绿油油一片，ProxySQL 上的路由曲线像心电图一样稳定。

我给同事留了张纸条：

“读写分离不是银弹，但在跨境订单高峰，它是最划算的一笔工程。
下一步，我们把 Orchestrator 的自动化切换再练熟一点，把只读层做成弹性池，给 11.11 提前打底。”
我关上机柜门，听到那声熟悉的“咔哒”。那是这套系统，继续稳定运行的声音。

附：关键命令/配置一览（可直接套用）

关闭 THP / 设置 limits：见上文章节

主库/从库 my.cnf：复制粘贴 + server_id 调整

复制搭建：CHANGE MASTER TO ... MASTER_AUTO_POSITION=1; START SLAVE;

ProxySQL：

• mysql_servers：Writer=10、Reader=20
• mysql_users：default_hostgroup=20, transaction_persistent=1
• mysql_query_rules：100~130（强一致/事务/SET/CALL 走主），200（写入走主），300（SELECT 走读组）
• LOAD ... TO RUNTIME; SAVE ... TO DISK;

Keepalived：VRRP 实例 + chk_proxysql 脚本

压测：sysbench oltp_read_write.lua ...

强一致读：/* read_from_master */ 或短事务包裹

如果你也在香港机房扛跨境流量，希望这份“带着油污的笔记”能帮你少踩几个坑。祝你在线上，永远只有绿灯。