ProxySQL实现MySQL读写分离的配置与优化-育师

1. ProxySQL与MySQL读写分离的核心价值

在数据库架构设计中，读写分离是提升系统性能的经典方案。ProxySQL作为高性能数据库代理，通过智能路由机制将写操作定向到主库（Master），读操作分散到多个从库（Slave）。这种架构能有效解决单机MySQL在高并发场景下的性能瓶颈问题。

实际生产环境中，我们观察到一个典型现象：当读请求占比超过70%时，主库的CPU和I/O压力会显著上升。通过ProxySQL实现的读写分离，可以使主库专注于处理写操作和关键事务，而读请求则由从库集群分担。某电商平台的数据显示，在引入ProxySQL后，其峰值时段的数据库吞吐量提升了3倍，主库负载下降60%。

2. 环境准备与ProxySQL安装

2.1 系统要求与前置条件

在CentOS 7系统上部署ProxySQL 2.5.3，需要确保：

可用内存 ≥ 1GB（建议2GB以上）
磁盘空间 ≥ 500MB
已配置好MySQL主从复制集群（至少1主2从）
防火墙开放6032（管理端口）、6033（代理端口）

重要提示：所有MySQL从库必须设置read_only=ON，否则可能导致数据写入从库造成主从不一致。

2.2 分步安装指南

# 安装依赖项 yum install -y perl-DBD-MySQL ncurses-compat-libs # 下载RPM包（需替换为最新版本） wget https://github.com/sysown/proxysql/releases/download/v2.5.3/proxysql-2.5.3-1-centos7.x86_64.rpm # 安装ProxySQL rpm -ivh proxysql-2.5.3-1-centos7.x86_64.rpm # 启动服务 systemctl start proxysql systemctl enable proxysql

安装验证命令：

# 检查服务状态 systemctl status proxysql # 连接管理接口 mysql -u admin -p admin -h 127.0.0.1 -P 6032 --prompt='ProxySQL> '

3. MySQL主从集群配置

3.1 创建专用账户

在所有MySQL节点执行：

-- 管理账户（用于ProxySQL配置） CREATE USER 'proxyAdmin'@'%' IDENTIFIED BY 'StrongPassword!123'; GRANT ALL PRIVILEGES ON *.* TO 'proxyAdmin'@'%' WITH GRANT OPTION; -- 监控账户 CREATE USER 'proxyMonitor'@'%' IDENTIFIED BY 'MonitorPass!456'; GRANT REPLICATION CLIENT ON *.* TO 'proxyMonitor'@'%'; -- 从库设置只读模式 SET GLOBAL read_only = ON; SET GLOBAL super_read_only = ON;

3.2 主从复制验证

在主库执行：

SHOW MASTER STATUS\G

在从库执行：

SHOW SLAVE STATUS\G

确保Slave_IO_Running和Slave_SQL_Running均为Yes，且Seconds_Behind_Master为0。

4. ProxySQL核心配置详解

4.1 服务器组配置

-- 添加MySQL服务器到ProxySQL INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'192.168.75.129',3306), -- 写组 (20,'192.168.75.128',3306), -- 读组 (20,'192.168.75.130',3306); -- 读组 -- 定义读写组关系 INSERT INTO mysql_replication_hostgroups VALUES (10,20,'read_only'); -- 加载配置到运行时 LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;

4.2 用户认证配置

-- 添加应用连接账户 INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('app_user','AppPassword!789',10); -- 配置监控账户 SET mysql-monitor_username = 'proxyMonitor'; SET mysql-monitor_password = 'MonitorPass!456'; -- 持久化配置 LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;

5. 读写分离规则优化

5.1 智能路由规则设计

-- 清除默认规则 DELETE FROM mysql_query_rules; -- 读操作规则（路由到读组20） 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,'^WITH.*SELECT',20,1), -- CTE查询 (4,1,'^SHOW',20,1); -- SHOW命令 -- 写操作规则（路由到写组10） INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) VALUES (10,1,'^INSERT',10,1), (11,1,'^UPDATE',10,1), (12,1,'^DELETE',10,1), (13,1,'^REPLACE',10,1), (14,1,'^CREATE',10,1), (15,1,'^ALTER',10,1), (16,1,'^DROP',10,1), (17,1,'^TRUNCATE',10,1); -- 加载规则 LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

5.2 高级流量控制

-- 设置读组负载均衡策略 UPDATE mysql_servers SET weight=100 WHERE hostgroup_id=20; -- 设置连接池参数 SET mysql-connection_max_age_ms = 600000; -- 连接最大存活时间(10分钟) SET mysql-connect_retries_on_failure = 3; -- 失败重试次数 -- 启用查询缓存 SET mysql-query_cache_size_mb = 256; SET mysql-query_cache_stores_empty_result = true;

6. 生产环境调优建议

6.1 性能监控指标

关键监控项包括：

连接池利用率（Connections_connected）
查询响应时间（Query_Response_time）
各主机组流量分布（Hostgroup_Stats）
缓存命中率（Query_Cache_Memory_bytes）

通过Admin接口查看：

SELECT * FROM stats.stats_mysql_connection_pool; SELECT * FROM stats.stats_mysql_query_rules; SELECT * FROM stats.stats_mysql_global;

6.2 高可用配置

-- 设置自动故障转移 UPDATE global_variables SET variable_value='true' WHERE variable_name='mysql-hostgroup_manager_healthy'; -- 配置从库延迟阈值（秒） UPDATE mysql_servers SET max_replication_lag=30 WHERE hostgroup_id=20; -- 心跳检测间隔（毫秒） SET mysql-monitor_connect_interval=2000; SET mysql-monitor_read_only_interval=2000;

7. 应用层集成实践

7.1 Spring Boot配置示例

# application.yml spring: datasource: url: jdbc:mysql://proxy-host:6033/app_db?useSSL=false username: app_user password: AppPassword!789 hikari: maximum-pool-size: 20 connection-timeout: 30000

7.2 连接池注意事项

建议使用HikariCP而非DBCP
最大连接数应小于ProxySQL的mysql-max_connections

启用连接测试查询：

spring: datasource: hikari: connection-test-query: SELECT 1

8. 常见问题排查指南

8.1 连接失败排查步骤

检查ProxySQL日志：
```
journalctl -u proxysql -f
```
验证端口连通性：
```
telnet proxy-host 6033
```
检查用户权限：
```
SELECT * FROM runtime_mysql_users;
```

8.2 查询路由异常处理

当发现SELECT查询被路由到主库时：

检查查询规则匹配：

SELECT * FROM runtime_mysql_query_rules;

分析查询模式：

SELECT hostgroup, count_star FROM stats_mysql_query_digest;

临时解决方案（强制路由）：
```
/* HG=20 */ SELECT * FROM large_table;
```

9. 进阶配置技巧

9.1 分片集群支持

-- 添加分片集群配置 INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (30,'shard1-host',3306), (40,'shard2-host',3306); -- 分片路由规则 INSERT INTO mysql_query_rules (rule_id,active,schemaname,destination_hostgroup,apply) VALUES (100,1,'shard1_db',30,1), (101,1,'shard2_db',40,1);

9.2 查询重写示例

-- 将COUNT(*)优化为COUNT(1) INSERT INTO mysql_query_rules (rule_id,active,match_pattern,replace_pattern,apply) VALUES (200,1,'COUNT\(\*\)','COUNT(1)',1); -- 移除不必要的ORDER BY INSERT INTO mysql_query_rules (rule_id,active,match_pattern,replace_pattern,apply) VALUES (201,1,'ORDER BY RAND\(\)','',1);

10. 版本升级与迁移

10.1 平滑升级步骤

备份当前配置：

proxysql-admin --config-file=/etc/proxysql.cnf --export

安装新版本：

rpm -Uvh proxysql-2.5.4-1-centos7.x86_64.rpm

恢复配置：

proxysql-admin --config-file=/etc/proxysql.cnf --import

10.2 配置迁移工具

使用ProxySQL自带的配置导出工具：

mysql -uadmin -padmin -h127.0.0.1 -P6032 --skip-column-names \ -e "SELECT CONCAT('SAVE ', variable_name, ' TO DISK;') \ FROM global_variables WHERE variable_name LIKE 'mysql-%'" \ | mysql -uadmin -padmin -h127.0.0.1 -P6032

ProxySQL实现MySQL读写分离的配置与优化