ProxySQL实现MySQL主备读写分离配置指南

ProxySQL是一个高性能的MySQL中间件，可以实现读写分离、查询路由、负载均衡等功能。下面介绍如何使用ProxySQL配置一主一备的MySQL读写分离环境。

基本架构

应用层 → ProxySQL → {主库(写), 备库(读)}

配置步骤

1. 安装ProxySQL

# Ubuntu/Debian
sudo apt-get install proxysql

# CentOS/RHEL
sudo yum install proxysql

2. 启动ProxySQL服务

sudo systemctl start proxysql
sudo systemctl enable proxysql

3. 登录ProxySQL管理界面

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

4. 添加MySQL服务器节点

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES 
(10,'master_ip',3306),   -- hostgroup_id=10为主库组
(20,'slave_ip',3306);    -- hostgroup_id=20为从库组

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

5. 配置监控用户

UPDATE global_variables SET variable_value='monitor' 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主备服务器上创建了监控用户：

CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor_password';
GRANT REPLICATION CLIENT ON *.* TO 'monitor'@'%';

6. 配置读写分离规则

-- 创建用户并指定默认主机组（写操作）
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('app_user','app_password',10);

-- 设置读写分离规则（所有SELECT发送到从库）
INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) VALUES 
(1,1,'^SELECT.*FOR UPDATE',10,1),   -- SELECT FOR UPDATE发送到主库
(2,1,'^SELECT',20,1),               -- SELECT发送到从库
(3,1,'^INSERT',10,1),               -- INSERT发送到主库
(4,1,'^UPDATE',10,1),               -- UPDATE发送到主库 
(5,1,'^DELETE',10,1);               -- DELETE发送到主库 

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

7. MySQL复制配置检查

确保MySQL主备已经配置好复制关系：

在主库上：

SHOW MASTER STATUS;

在备库上：

SHOW SLAVE STATUS\G;   -- Slave_IO_Running和Slave_SQL_Running都应为Yes

ProxySQL常用管理命令

查看服务器状态:

SELECT * FROM mysql_servers;
SELECT hostgroup_id,srv_host,status FROM runtime_mysql_servers;

查看查询规则:

SELECT * FROM mysql_query_rules\G;

查看流量统计:

SELECT hostgroup hg,
       sum_time/count AS avg_time,
       count,
       sum_time/1000000 AS sum_time_sec 
FROM stats_mysql_query_digest_reset 
GROUP BY hostgroup ORDER BY sum_time DESC;