ProxySQL 双主设置实现一写一读配置

ProxySQL 可以通过配置实现双主(Master-Master)数据库架构下的一写一读模式，以下是详细配置步骤：

1. 前提条件

已搭建好 MySQL/MariaDB 双主复制环境
ProxySQL 已安装并运行
两个主节点分别标记为 writer (写入节点)和 reader (读取节点)

2. ProxySQL 基本配置

2.1 添加服务器到主机组

-- 登录ProxySQL管理界面
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='ProxySQL Admin>'

-- 添加后端服务器
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES 
(10,'master1_ip',3306),   -- writer组 (hostgroup_id=10)
(20,'master2_ip',3306);   -- reader组 (hostgroup_id=20)

2.2 创建用户并设置权限

-- 添加监控用户
UPDATE global_variables SET variable_value='monitor_user' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor_password' WHERE variable_name='mysql-monitor_password';

-- 添加应用连接用户
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES 
('app_user','app_password',10); -- 默认路由到writer组

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

3. 配置读写分离规则

3.1 Hostgroup配置

-- writer组的定义 (hostgroup_id=10)
-- reader组的定义 (hostgroup_id=20)

-- writer应该也是reader候选者之一
INSERT INTO mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,check_type) 
VALUES (10,20,'innodb_read_only');

3.2 Query Rules配置

-- INSERT/UPDATE/DELETE等写操作路由到writer组(10)
INSERT INTO mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) VALUES 
(1,1,'^SELECT.*FOR UPDATE',10,1),
(2,1,'^INSERT',10,1),
(3,1,'^UPDATE',10,1),
(4,1,'^DELETE',10,1),
(5,1,'^REPLACE',10,1);

-- SELECT读操作路由到reader组(20)
INSERT INTO mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) VALUES 
(6,1,'^SELECT',20,0); -- apply=0表示继续评估后面的规则

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

4. Reader节点的特殊设置

为了确保reader节点(master2)不会接受写入请求：

-- master2上执行:
SET GLOBAL read_only = ON;
SET GLOBAL super_read_only = ON;

-- master1上执行:
SET GLOBAL read_only = OFF;
SET GLOBAL super_read_only = OFF;

5. ProxySQL高级优化选项

-- writer失败时自动故障转移的阈值设置
UPDATE mysql_servers SET max_replication_lag = '300'; -- seconds

-- reader负载均衡策略调整
UPDATE global_variables SET variable_value='true' WHERE variable_name='mysql-multiplexing';

验证与测试

验证方法

查看当前路由：

SELECT hostgroup hg,
       SUM(stats_mysql_query_digest.cnt) total_queries  
FROM stats_mysql_query_digest  
GROUP BY hg;

强制测试读写分离：
分别用以下语句测试是否按预期路由：

# Write test（应指向master）
mysql -u app_user -papp_password -h proxysql_ip -e "INSERT INTO test.tb(val) VALUES('write_test')"  

# Read test（应指向slave）  
mysql -u app_user -papp_password -h proxysql_ip -e "SELECT * FROM test.tb LIMIT 100"

通过以上配置，ProxySQL会将所有写操作定向到master1(writer)，而读操作定向到master2(reader)。如果writer节点故障，可以手动或通过脚本自动将另一个节点提升为writer。

内容由零声教学AI助手提供，问题来源于学员提问