深色模式
读写分离与代理路由
摘要：读写分离把 SELECT 打到从库、写操作打到主库，可显著提升读扩展能力。本文用 ProxySQL 配置后端主从分组与路由规则，并说明主从延迟带来的读一致性问题与规避办法。
适用环境
bash
# 已有主从复制且延迟正常
mysql -uroot -p -e "SHOW REPLICA STATUS\G" | grep -E 'Running|Behind'
# 安装 ProxySQL
sudo yum install -y proxysql # 或从官方仓库获取
proxysql --version1
2
3
4
5
2
3
4
5
操作步骤
1. 准备 ProxySQL 使用的监控账号（主库执行）
sql
CREATE USER 'monitor'@'%' IDENTIFIED BY 'MonPass!2026';
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';
CREATE USER 'app'@'%' IDENTIFIED BY 'AppPass!2026';
GRANT SELECT, INSERT, UPDATE, DELETE ON shopdb.* TO 'app'@'%';1
2
3
4
2
3
4
2. 启动并登录 ProxySQL 管理端口
bash
sudo systemctl enable --now proxysql
mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt='ProxySQL> '1
2
2
3. 注册后端节点（10 为写组，20 为读组）
sql
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, max_connections)
VALUES (10, '10.0.1.10', 3306, 1, 1000), -- 主库：写组
(20, '10.0.1.11', 3306, 1, 1000), -- 从库1：读组
(20, '10.0.1.12', 3306, 1, 1000); -- 从库2：读组
INSERT INTO mysql_users(username, password, default_hostgroup)
VALUES ('app', 'AppPass!2026', 10);
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='MonPass!2026' WHERE variable_name='mysql-monitor_password';
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;
LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
4. 配置读写分离规则
sql
-- 写类语句走写组
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (10, 1, '^SELECT.*FOR UPDATE$', 10, 1),
(11, 1, '^SELECT', 20, 1);
-- 其余（INSERT/UPDATE/DELETE/DDL）默认走 default_hostgroup=10
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;1
2
3
4
5
6
7
2
3
4
5
6
7
危险
SELECT ... FOR UPDATE、以及在事务中且已发生过写入的查询，必须路由到主库，否则会读到旧数据或产生错误结果。默认把「事务里的所有查询都走主库」是更安全的做法。
5. 处理主从延迟导致的读旧数据
sql
-- 延迟过大的从库自动摘除（超过 30 秒则暂停接收读流量）
UPDATE mysql_servers SET max_replication_lag = 30 WHERE hostgroup_id = 20;
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;1
2
3
2
3
sql
-- 业务侧：强一致读直连主库，或在写后短时间内强制走主
-- 常见做法：在 SQL 前加注释 hint（如 /*FORCE_MASTER*/），代理按 hint 路由
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (5, 1, '/\*FORCE_MASTER\*/', 10, 1);1
2
3
4
2
3
4
6. 观察统计
sql
SELECT hostgroup, srv_host, status, ConnUsed, Queries, Bytes_data_sent
FROM stats_mysql_connection_pool;
SELECT digest_text, count_star, sum_time FROM stats_mysql_query_digest
ORDER BY sum_time DESC LIMIT 10;1
2
3
4
2
3
4
验证
bash
# 通过代理端口 6033 连接，写后立刻读
mysql -uapp -p'AppPass!2026' -h127.0.0.1 -P6033 shopdb1
2
2
sql
INSERT INTO t_probe VALUES (3, NOW());
SELECT * FROM t_probe ORDER BY id DESC LIMIT 1; -- 应能读到刚写入的行
SELECT @@hostname; -- 观察实际落到哪台1
2
3
2
3
常见坑
WARNING
ProxySQL 增加了单点风险，需部署多个实例并配合 VIP/负载均衡；同时它自身配置要纳入备份。
WARNING
只用正则匹配 ^SELECT 会误把 SELECT ... INTO、SELECT @@ 等也发到从库，导致取到从库状态；应按实际 SQL 补充规则。
DANGER
从库延迟时业务读到旧数据（如刚下单查不到订单）是读写分离最典型的投诉来源，必须在应用层区分「强一致读」与「可容忍延迟读」。