ProxySQL实现MySQL MGR读写分离配置实战

1. 项目概述在数据库架构设计中读写分离是提升系统性能的经典方案。今天要分享的是基于ProxySQL中间件实现MySQL MGR集群的读写分离代理配置。这套方案在我们电商平台的订单系统中稳定运行了两年多成功将数据库查询性能提升了3倍以上。ProxySQL作为高性能MySQL代理能够智能路由SQL请求到不同的数据库节点。而MySQL Group ReplicationMGR则提供了高可用的数据同步机制。两者的结合既保证了数据一致性又实现了负载均衡。下面我就从实际配置角度详细解析这个方案的实现过程。2. 环境准备与架构设计2.1 基础环境要求在开始配置前需要准备好以下环境至少3个节点的MySQL MGR集群推荐5.7.17以上版本安装ProxySQL的服务器建议2核4G配置所有节点间网络延迟低于5ms操作系统建议使用CentOS 7.6重要提示MGR集群必须已经完成初始化并正常运行可以通过SELECT * FROM performance_schema.replication_group_members;命令验证集群状态。2.2 架构拓扑设计我们采用的典型部署架构如下应用层 → ProxySQL → [MGR主节点] → [MGR从节点1] ↘ [MGR从节点2]这种架构下所有写操作自动路由到MGR主节点读操作均匀分配到各个从节点ProxySQL会实时监测节点状态自动剔除故障节点3. ProxySQL核心配置详解3.1 安装与初始化首先在代理服务器安装ProxySQL# CentOS系统 yum install -y proxysql mysql-client systemctl start proxysql systemctl enable proxysql初始化管理界面连接mysql -u admin -padmin -h 127.0.0.1 -P 6032 --promptProxySQL 3.2 后端服务器配置在ProxySQL中添加MGR集群节点INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,mgr-node1,3306), (20,mgr-node2,3306), (20,mgr-node3,3306);这里将hostgroup_id设为10写组主节点20读组从节点3.3 监控账户配置创建监控账号并设置检查参数UPDATE global_variables SET variable_valuemonitor WHERE variable_namemysql-monitor_username; UPDATE global_variables SET variable_valuemonitor_password WHERE variable_namemysql-monitor_password; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;3.4 读写分离规则配置定义路由规则实现读写分离INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) VALUES (1,1,^SELECT.*FOR UPDATE,10,1), (2,1,^SELECT,20,1), (3,1,^INSERT,10,1), (4,1,^UPDATE,10,1), (5,1,^DELETE,10,1); LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;4. MGR集群适配配置4.1 节点权重设置根据服务器配置设置不同的读权重UPDATE mysql_servers SET weight100 WHERE hostnamemgr-node2; UPDATE mysql_servers SET weight80 WHERE hostnamemgr-node3;4.2 故障转移配置设置自动故障检测参数UPDATE global_variables SET variable_value2000 WHERE variable_namemysql-monitor_connect_timeout; UPDATE global_variables SET variable_value3000 WHERE variable_namemysql-monitor_read_only_timeout; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;5. 性能优化实战技巧5.1 连接池配置建议根据业务特点调整连接池UPDATE mysql_servers SET max_connections300 WHERE hostgroup_id10; UPDATE mysql_servers SET max_connections500 WHERE hostgroup_id20;5.2 查询缓存优化启用查询缓存并设置合理大小UPDATE global_variables SET variable_valuetrue WHERE variable_namemysql-query_cache_enabled; UPDATE global_variables SET variable_value256M WHERE variable_namemysql-query_cache_size;6. 常见问题排查指南6.1 连接失败问题检查项网络连通性telnet mgr-node1 3306账号权限确保proxy用户有足够权限防火墙设置开放3306和6032端口6.2 读写分离失效诊断步骤SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC LIMIT 10;查看SQL实际路由情况检查规则是否生效。6.3 性能瓶颈分析关键监控指标SELECT * FROM stats_mysql_connection_pool; SELECT * FROM stats_mysql_global;重点关注连接复用率和查询延迟。7. 生产环境维护建议定期检查ProxySQL日志tail -f /var/lib/proxysql/proxysql.log配置监控告警重点关注节点健康状态连接池使用率查询响应时间每季度进行一次配置审计SAVE MYSQL CONFIG TO FILE /tmp/proxysql_config_audit.sql;这套配置在我们生产环境中日均处理2000万查询请求平均延迟控制在15ms以内。最关键的是要定期根据业务增长调整连接池和缓存参数。