ProxySQL 配置双主读写分离指南

ProxySQL 是一个高性能的 MySQL 中间件，可以实现读写分离、查询路由、负载均衡等功能。以下是配置双主(Master-Master)复制环境下读写分离的步骤：

前提条件

已搭建好 MySQL 双主复制环境
已安装 ProxySQL (推荐版本 >= 2.0)

配置步骤

1. 登录 ProxySQL Admin Interface

mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='ProxySQL> '

2. 添加后端数据库服务器

INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES 
(10, 'master1_ip', 3306),   -- writer组
(10, 'master2_ip', 3306),   -- writer组
(20, 'master1_ip', 3306),   -- reader组
(20, 'master2_ip', 3306);   -- reader组
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

3. 配置监控用户

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';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

确保在MySQL服务器上创建了监控用户并授予相应权限。

4. 配置读写分离规则

-- writer组的流量路由规则 (hostgroup=10)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES 
(1, 1, '^SELECT.*FOR UPDATE', 10,- reader组的流量路由规则 (hostgroup=20)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES 
(5, 1, '^SELECT',           20,   1),
(6,	0,'^SELECT.*INTO OUTFILE',10 ,	1),
(7 ,0,'^SELECT.*INTO DUMPFILE',10 ,	1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

###5 .配置用户访问权限

INSERT INTO mysql_users(username ,password ,default_hostgroup ) VALUES ('app_user','app_password' ,10 );
LOAD MYSQL USERS TO RUNTIME ;
SAVE MYSQL USERS TO DISK ;

##高级配置选项

###负载均衡策略

UPDATE mysql_servers SET weight=100 WHERE hostgroup_id=10 AND hostname='master1_ip'; 
UPDATE mysql_servers SET weight=50 WHERE hostgroup_id=10 AND hostname='master2_ip';
--设置reader组的负载均衡方式为轮询(RANDOM)
UPDATE mysql_replication_hostgroups SET reader_writer_mode=2 WHERE writer_hostgroup=10 AND reader_hostgroup=20;
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQ L SERVERS T O DIS K;

##验证配置

检查服务器状态 :

SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT3 ;  
SELECT * FROM monitor.mysql_server_replication_lag_log ORDER BY time_start DESC LIMIT3 ;

测试读写分离 :

#写操作测试  
mysql -u app_user -papp_password -h proxysql_ip -P6033-e"CREATE DATABASE test_prox ysql;"  
#读操作测试  
mysql-u app_user-papp_password-h prox ysq l_i p-P6033-e"SHOW DATABASES ;"