14.5.2 hostgroup设计与读写规则配置

hostgroup设计与读写规则配置#

hostgroup用于将后端实例按角色与用途分组，是读写分离的基础。常见设计为主库写组（如10）与从库读组（如20），也可按地域/业务拆分多个读组。设计目标：写请求只入写组；读请求优先入读组；读组允许负载均衡与故障转移。

原理草图（请求路由到不同hostgroup）：

一、环境准备与安装（示例）#

# 以Ubuntu为例安装（若已安装可跳过）
sudo apt update
sudo apt install -y proxysql mariadb-client

# 登录管理端口（默认6032）
mysql -u admin -padmin -h 127.0.0.1 -P6032

预期：能进入ProxySQL管理控制台。

二、hostgroup与后端映射配置#

1）配置后端实例与hostgroup

-- 写组（主库）
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, max_connections)
VALUES (10, '192.168.56.10', 3306, 100, 200);

-- 读组（从库）
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, max_connections)
VALUES (20, '192.168.56.11', 3306, 80, 200),
       (20, '192.168.56.12', 3306, 80, 200);

命令解释：
- hostgroup_id：逻辑分组，10写组、20读组
- weight：负载权重，越大分配越多
- max_connections：连接池上限

2）配置用户与默认组

INSERT INTO mysql_users(username, password, default_hostgroup, transaction_persistent)
VALUES ('app', 'app_pass', 10, 1);

命令解释：
- default_hostgroup=10：默认走写组
- transaction_persistent=1：事务内强制同一hostgroup，避免读写不一致

3）加载生效

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

预期：SELECT * FROM runtime_mysql_servers;中可看到已生效实例。

三、读写规则配置（mysql_query_rules）#

-- 规则1：事务内强制写组（优先级最高）
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (100, 1, '^BEGIN|^START TRANSACTION', 10, 1);

-- 规则2：读请求走读组
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (200, 1, '^SELECT', 20, 1);

-- 规则3：写请求与DDL走写组
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (300, 1, '^(INSERT|UPDATE|DELETE|REPLACE|ALTER|CREATE|DROP|TRUNCATE)', 10, 1);

-- 规则4：关键表强制主库（示例：订单表）
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (150, 1, 'orders', 10, 1);

规则顺序说明：
- 按 rule_id 从小到大匹配
- 强制主库的规则放在SELECT规则之前（如150 < 200）

加载与持久化

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

四、连通性与路由验证（完整示例）#

# 连接ProxySQL数据端口（默认6033）
mysql -u app -papp_pass -h 127.0.0.1 -P6033 -e "SELECT 1;"

# 执行读写混合测试
mysql -u app -papp_pass -h 127.0.0.1 -P6033 <<'SQL'
SELECT NOW();
INSERT INTO test.t1(id, v) VALUES(1, 'a');
SELECT * FROM test.t1;
SQL

查看规则命中与连接分布：

-- 规则命中统计
SELECT rule_id, hits, match_pattern FROM stats_mysql_query_rules ORDER BY rule_id;

-- 连接池使用情况
SELECT hostgroup, srv_host, ConnUsed, ConnFree
FROM stats_mysql_connection_pool;

预期：SELECT命中rule_id=200并进入HG20；写语句命中rule_id=300进入HG10。

五、常见排错#

读请求未走读组
- 检查规则顺序：SELECT * FROM mysql_query_rules ORDER BY rule_id;
- 检查规则是否生效：LOAD MYSQL QUERY RULES TO RUNTIME;
规则命中为0
- 检查match_pattern是否匹配SQL（建议加^SELECT）
- 确认SQL未被应用层改写（如注释、前置hint）
从库连接不可用
- 查看stats_mysql_connection_pool中ConnFree=0
- 检查mysql_servers中从库状态与网络连通

六、练习#

设计3个读组（20、21、22），按权重 50/30/20 分流，验证连接分配比例。
增加规则：SELECT ... FOR UPDATE 强制走主库，并验证命中。
使用transaction_persistent=0对比事务内读写一致性差异，记录观察结果。