14.5.1 读写分离原理与适用场景

读写分离原理：将写请求路由到主库（writer hostgroup），读请求路由到从库（reader hostgroup）。ProxySQL 在连接层解析 SQL 并匹配路由规则，依据 hostgroup 权重与健康检查选择目标后端。读写分离提升读吞吐并降低主库压力，但受主从复制延迟影响，需要在一致性场景启用“强制主库”策略。

原理草图（应用仅连接 ProxySQL）：

适用场景与边界：
- 适用：读多写少、可容忍短暂延迟、报表与分析类读负载、热点数据读多写少。
- 不适用/风险：强一致性要求高、写入密集、跨库事务复杂、复制延迟明显。
- 优势：读负载分担、水平扩展读能力、故障隔离。
- 边界：可能读到旧数据、复杂 SQL 误判、写热点仍集中主库。

最小可运行示例（含安装、配置与效果验证）：

# 1) 安装 ProxySQL（以 CentOS/RHEL 为例）
sudo yum -y install https://repo.proxysql.com/ProxySQL/proxysql-2.5.x-el7.x86_64.rpm
sudo systemctl enable --now proxysql

# 2) 进入管理端口（默认 6032）
mysql -u admin -padmin -h 127.0.0.1 -P6032

-- 3) 配置后端 MySQL 节点与 hostgroup
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES
(10,'10.0.0.11',3306,100), -- writer
(20,'10.0.0.21',3306,100), -- reader
(20,'10.0.0.22',3306,100); -- reader

-- 4) 设置用户与默认读写分离目标
INSERT INTO mysql_users(username,password,default_hostgroup,transaction_persistent)
VALUES ('app','app_pwd',10,1);

-- 5) 读写分离路由规则：SELECT -> reader，其他 -> writer
INSERT INTO mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply)
VALUES
(1,1,'^SELECT .*',20,1);

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;

验证读写分离效果：

# 应用侧仅连接 ProxySQL 6033
mysql -u app -papp_pwd -h 127.0.0.1 -P6033 -e "SELECT @@hostname;"
mysql -u app -papp_pwd -h 127.0.0.1 -P6033 -e "INSERT INTO test.t1 VALUES(1);"

# 查看路由命中统计（管理端 6032）
mysql -u admin -padmin -h 127.0.0.1 -P6032 -e \
"SELECT hostgroup, srv_host, status, ConnUsed, Queries FROM stats_mysql_connection_pool;"

预期效果：SELECT @@hostname 返回从库主机名；INSERT 进入主库。stats_mysql_connection_pool 中 reader hostgroup 的 Queries 计数增加。

关键命令解释：
- LOAD ... TO RUNTIME：加载到内存立即生效。
- SAVE ... TO DISK：持久化到磁盘，重启不丢失。
- stats_mysql_connection_pool：查看各 hostgroup 连接与请求统计，排查路由是否生效。

排错要点（含命令）：

# 1) 读请求仍走主库：检查规则是否生效
mysql -u admin -padmin -h 127.0.0.1 -P6032 -e \
"SELECT rule_id,active,match_pattern,destination_hostgroup FROM mysql_query_rules;"

# 2) reader 不可用：检查健康状态
mysql -u admin -padmin -h 127.0.0.1 -P6032 -e \
"SELECT hostgroup_id,hostname,port,status FROM mysql_servers;"

# 3) 验证实际路由命中
mysql -u admin -padmin -h 127.0.0.1 -P6032 -e \
"SELECT * FROM stats_mysql_query_rules ORDER BY rule_id;"

常见原因与修复：
- 正则匹配不严谨（如大小写）：可用 (?i)^select 或启用 mysql_query_rules 的 case_sensitive=0。
- 复制延迟导致读到旧数据：对“写后读”启用强制主库策略（会话级或规则级）。

练习（可在测试环境完成）：
1. 新增规则：对 SELECT ... FOR UPDATE 强制走主库。
2. 将 reader hostgroup 的权重改为 200/50，观察 stats_mysql_connection_pool 的 Queries 分布变化。
3. 制造从库宕机（停掉一个从库），观察 ProxySQL 是否自动剔除并将读流量切换到可用从库。

命令示例（练习答案参考）：

-- 1) 强制 SELECT ... FOR UPDATE 走主库
INSERT INTO mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply)
VALUES (2,1,'^SELECT .* FOR UPDATE',10,1);

-- 2) 调整权重
UPDATE mysql_servers SET weight=200 WHERE hostgroup_id=20 AND hostname='10.0.0.21';
UPDATE mysql_servers SET weight=50  WHERE hostgroup_id=20 AND hostname='10.0.0.22';

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