14.6.7 典型配置示例与验证方法
本节提供 ProxySQL 在“负载均衡与故障切换”场景下的典型配置与验证流程，包含架构草图、完整可执行的配置、验证命令、排错步骤与练习。
1) 典型配置示例（读写分离+健康检查+故障切换）#
以下示例在 ProxySQL Admin 端执行，默认 Admin 地址 127.0.0.1:6032。
说明：写组只允许主库可写；读组按权重分流；设置健康检查与自动摘除。
# 连接管理端
mysql -uadmin -padmin -h127.0.0.1 -P6032
-- 1. 定义后端 MySQL 节点（写组 10，读组 20）
-- hostgroup: 10=写组，20=读组
-- weight: 权重；max_connections: 连接上限；status: ONLINE/OFFLINE
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, max_connections, status)
VALUES
(10, '10.0.0.11', 3306, 1, 200, 'ONLINE'), -- 主库
(20, '10.0.0.12', 3306, 5, 200, 'ONLINE'), -- 从库1
(20, '10.0.0.13', 3306, 3, 200, 'ONLINE'); -- 从库2
-- 2. 用户与默认路由（默认写组）
INSERT INTO mysql_users(username, password, default_hostgroup, transaction_persistent)
VALUES ('appuser', 'app_pass', 10, 1);
-- 3. 读写分离规则：SELECT -> 读组20，写请求 -> 写组10
-- 规则优先级：rule_id 越小越优先
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES
(10, 1, '^SELECT', 20, 1),
(20, 1, '^(INSERT|UPDATE|DELETE|REPLACE|CREATE|ALTER|DROP)', 10, 1);
-- 4. 健康检查与故障摘除阈值
UPDATE global_variables SET variable_value='2000' WHERE variable_name='mysql-monitor_connect_timeout'; -- 连接超时(ms)
UPDATE global_variables SET variable_value='3' WHERE variable_name='mysql-monitor_connect_timeout_server_max'; -- 失败阈值
UPDATE global_variables SET variable_value='2000' WHERE variable_name='mysql-monitor_ping_interval'; -- 探测间隔(ms)
UPDATE global_variables SET variable_value='2' WHERE variable_name='mysql-monitor_ping_max_failures'; -- 最大失败次数
-- 5. 应用配置
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;
LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;
关键参数解释：
- hostgroup_id：后端分组编号，读写分离依赖它；
- weight：读组内流量权重；
- transaction_persistent=1：事务内固定路由；
- mysql-monitor_*：健康检查与自动摘除相关阈值。
2) 验证方法（功能、故障切换、稳定性）#
2.1 功能验证：读写是否命中预期组#
# 使用应用用户连接 ProxySQL 数据端口
mysql -uappuser -papp_pass -h127.0.0.1 -P6033
-- 读请求应进入 hostgroup=20
SELECT * FROM test.t1 LIMIT 1;
-- 写请求应进入 hostgroup=10
INSERT INTO test.t1(value) VALUES('rw-test');
-- 在管理端观察路由统计
SELECT hostgroup, srv_host, srv_port, ConnUsed, Queries
FROM stats_mysql_connection_pool;
SELECT hostgroup, digest, count_star
FROM stats_mysql_query_digest
ORDER BY count_star DESC LIMIT 5;
预期效果：
- SELECT 主要命中 hostgroup=20；
- INSERT/UPDATE/DELETE 命中 hostgroup=10；
- stats_mysql_connection_pool 中读组连接数增加。
2.2 故障切换验证：模拟主库宕机#
# 方式1：在主库服务器临时阻断 3306 端口
sudo iptables -A INPUT -p tcp --dport 3306 -j REJECT
# 方式2：停止 MySQL
sudo systemctl stop mysqld
-- 管理端查看后端状态变化
SELECT hostgroup_id, hostname, port, status, last_error
FROM mysql_servers;
预期效果：
- 主库状态变为 OFFLINE_SOFT 或 OFFLINE_HARD；
- 写请求切换到新主库（需外部主从切换或MHA/Orchestrator联动）；
- 无可写节点时应触发应用层降级策略（可配置规则返回错误）。
恢复验证：
sudo systemctl start mysqld
sudo iptables -D INPUT -p tcp --dport 3306 -j REJECT
-- 观察节点重新 ONLINE
SELECT hostgroup_id, hostname, port, status
FROM mysql_servers;
2.3 稳定性验证：压测下切换窗口观测#
# 使用 sysbench 模拟负载
sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-host=127.0.0.1 --mysql-port=6033 \
--mysql-user=appuser --mysql-password=app_pass \
--mysql-db=test --tables=4 --table-size=10000 \
--threads=16 --time=60 run
观察指标：
- stats_mysql_connection_pool 中 ConnFree/ConnUsed 变化；
- 错误率与超时峰值是否在可接受范围；
- 日志 /var/lib/proxysql/proxysql.log 是否出现大量连接失败。
3) 排错清单（常见问题与定位命令）#
-- 1. 查询规则是否生效
SELECT rule_id, match_pattern, destination_hostgroup, active
FROM mysql_query_rules;
-- 2. 检查监控线程是否工作
SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start DESC LIMIT 5;
-- 3. 查看连接池是否有后端可用
SELECT hostgroup, srv_host, status, ConnUsed, ConnFree
FROM stats_mysql_connection_pool;
-- 4. 检查用户权限与路由默认组
SELECT username, default_hostgroup, transaction_persistent
FROM mysql_users;
排错建议：
- 规则未生效：确认 LOAD MYSQL QUERY RULES TO RUNTIME 已执行；
- 读写不分离：检查 match_pattern 正则与 rule_id 优先级；
- 节点不上线：检查 mysql-monitor_* 阈值与后端账号权限；
- 连接耗尽：增大 max_connections 或减小应用并发。
4) 练习#
- 将读组权重调整为 10:1，观察
stats_mysql_query_digest的命中变化。 - 将
mysql-monitor_ping_interval设置为 1000ms，模拟故障观察切换速度差异。 - 为
SELECT ... FOR UPDATE添加规则固定写组，验证事务一致性。