ProxySQL实现MySQL读写分离：从规则配置到生产踩坑实战

1. 为什么用ProxySQL扛读写分离聊聊我的选型思路最早接触读写分离是在一次压测后端的半夜。应用层的QPS涨到一定水位主库CPU先扛不住了一看慢查询全是连表聚合查询那会儿其实还没上中间件纯粹靠业务代码里手写数据源路由——DataSource切来切去时间久了维护成本很高到处是这个接口走读库、那个接口走写库的约定。后来才下定决心引入ProxySQL把读写分离从业务代码里剥出去。先说清楚ProxySQL是干什么的。它是一个轻量级的MySQL协议代理部署在应用和MySQL之间对外暴露一个MySQL端口应用正常连它它再根据规则把语句转发给后端的某个实例。读写分离是它最常用的能力之一把SELECT一类读语句路由到从库把INSERT/UPDATE/DELETE这类写语句路由到主库。核心价值在于应用无感知不改一行业务代码切换数据源从代码逻辑变成配置文件规则热加载在线改路由规则不用重启应用LOAD ... TO RUNTIME一下就生效后端点变透明主从切换、加库加实例应用连接串不用改我对比过几个主流方案简单说一下为什么最后留在了ProxySQL而不是别的。应用层数据源路由如Spring的AbstractRoutingDataSource无需额外组件但每个项目都要实现一遍而且切换逻辑和业务代码耦合。万一忘了某个接口没走对库事故就来了。MyCat / ShardingSphere功能很全分库分表、读写分离都支持但部署和调优都比较重。如果你只是要读写分离不需要分片上这一套有点压秤。而且MyCat后期的社区活跃度、文档完善度都让我不太敢上生产。MySQL RouterOracle官方出品轻量但它更偏向透明路由场景。读写分离的规则配置能力很弱复杂查询、事务绑定、用户级路由这些基本做不到。它更像一个端口转发器。ProxySQL体积小C实现性能和并发能力都很能打。它有一个核心杀手锏——查询规则query_rules可以做非常细粒度的路由判断比如按用户、按正则匹配语句、按事务状态决定走哪个库。这套机制做读写分离是非常顺手的。我自己用它大致是这样的拓扑一套MySQL主从主库负责写从库负责读ProxySQL部署在两台服务器上做负载但单机先跑也没问题。下面所有配置我拿一个具体版本说事ProxySQL 2.4.xMySQL 8.0.x系统是CentOS 7.9。版本不同差异不会太大但我会在关键的坑上指出来。2. 安装与基础初始化把ProxySQL先跑起来安装不是难点但有些细节会卡人先说安装再说启动。2.1 编译安装还是包管理器安装如果环境能访问外网直接用官方源装省事。# 以CentOS 7/RHEL 7为例 cat /etc/yum.repos.d/proxysql.repo EOF [proxysql_release] nameProxySQL YUM Repository baseurlhttps://repo.proxysql.com/ProxySQL/proxysql-2.4.x/centos/\$releasever gpgcheck1 gpgkeyhttps://repo.proxysql.com/ProxySQL/repo_pub_key EOF yum install -y proxysql安装后二进制默认放在/usr/bin/proxysql卸载脚本和配置文件情况随版本略有差异。如果你的环境是内网官网上会有对应的rpm包下载下来离线安装即可。源码编译我不太推荐因为它的编译依赖比较杂涉及OpenSSL、libevent等线上机器很可能缺各种头文件。能直接说我装完了的都是包管理路线的。注意ProxySQL编译安装和包安装默认的数据目录都叫/var/lib/proxysql如果你要自定义记得先启动一次并确认写入权限否则后面SAVE ... TO DISK会失败。2.2 第一次启动admin接口和连接MySQL安装完启动服务systemctl start proxysql systemctl enable proxysql启动之后会有两个端口6032管理端口Admin接口连接这个端口操作ProxySQL自身的配置6033服务端口应用连这个端口来发SQL用MySQL客户端连管理端口mysql -uadmin -padmin -h127.0.0.1 -P6032默认用户名和密码都是admin登录后可以查看当前配置SELECT * FROM global_variables WHERE variable_name LIKE mysql-%;注意ProxySQL本身用SQLite持久化配置但它对外提供的是一整套MySQL兼容接口所以用MySQL客户端操作管理端口是很自然的。这也意味着它的配置全部是改表-LOAD到RUNTIME-SAVE到DISK三步走而不是改配置文件再重启。这三步是ProxySQL配置的灵魂# 改表/插入数据后先加载到内存运行时 LOAD MYSQL SERVERS TO RUNTIME; LOAD MYSQL USERS TO RUNTIME; LOAD MYSQL QUERY RULES TO RUNTIME; # 确认无误后持久化到磁盘 SAVE MYSQL SERVERS TO DISK; SAVE MYSQL USERS TO DISK; SAVE MYSQL QUERY RULES TO DISK;我习惯把对MySQL实例的操作放一起执行规则单独弄账号单独弄。每次改动前先SELECT一下当前配置防止自己之前改到一半忘了。启动后第一件事不是配读写分离而是验证ProxySQL能不能正常连上后端MySQL。因为配置后端实例之前ProxySQL自己也不知道该往哪里转发。验证方式很简单在管理端口执行SHOW DATABASES;正常会看到main、stats、monitor、disk这几个库。main是配置库stats是统计库monitor是健康检查监控库。看到这些基础环境就OK了。3. 核心配置让ProxySQL认识你的主从库这是读写分离的第一板斧把主从实例注册进ProxySQL并告诉它哪些实例属于写组、哪些属于读组。3.1 注册后端实例mysql_servers与主机组先看主从的拓扑假设实例角色主机IP端口备注主库写192.168.1.1013306可读可写从库1读192.168.1.1023306只读从库2读192.168.1.1033306只读登录管理端口向mysql_servers表插入实例INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, max_connections) VALUES (10, 192.168.1.101, 3306, 1, 300), (20, 192.168.1.102, 3306, 1, 300), (20, 192.168.1.103, 3306, 1, 300);这里我用了两个主机组10是写组20是读组。但更标准的做法是用mysql_replication_hostgroups表来声明这样ProxySQL会自己去识别后端复制拓扑里的角色INSERT INTO mysql_replication_hostgroups(writer_hostgroup, reader_hostgroup) VALUES (10, 20);然后删掉前面对实例的hostgroup_id指定比如都先设为0再执行LOADProxySQL会去后端MySQL上执行SHOW SLAVE STATUS和检查read_only状态自动把主实例放进写组、从实例放进读组。这个自动归组的机制在生产上非常有用。因为主从切换的时候老主变成新从老从变成新主手动改hostgroup_id很容易出错。用了mysql_replication_hostgroups角色一换路由跟着变。但注意read_only这个参数在后端MySQL上必须配置好。从库要设为read_only1ProxySQL检测到这个值就知道这台只适合进读组。主库则要保持read_only0。我的习惯是新集群用 mysql_replication_hostgroups 自动归组老集群保持手写 hostgroup_id。前者省心后者可控看你对运维自动化的信任程度。注册完执行LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;查看实例状态SELECT hostgroup_id, hostname, port, status, weight FROM mysql_servers;status字段初始是ONLINE如果你看到OFFLINE_HARD说明ProxySQL无法连接该后端需要回头检查网络、账号、端口。3.2 定义前端账户mysql_users应用是通过ProxySQL连后端的所以ProxySQL需要知道前端谁可以连、用哪个账号去连后端MySQL。在mysql_users表里插入INSERT INTO mysql_users(username, password, default_hostgroup, max_connections) VALUES (app_user, AppPassw0rd!, 10, 100);username/password应用连接ProxySQL时使用的账号密码default_hostgroup这个账号默认落在哪个主机组我设为10写组这样即使没有命中任何读规则写组也能兜底max_connections该账号在ProxySQL层的最大连接数防止某个业务把连接池打爆需要注意的是ProxySQL的账号和后端MySQL账号可以同名也可以不同名。最省事的做法是同名同密码这样ProxySQL可以直接透传认证。但如果后端账号密码很敏感不想暴露给业务可以在mysql_users里用一对不同的映射比如前端账号叫app_ro后端账号用app_user那就在mysql_users中指定usernameapp_ro, passwordxx, backend_usernameapp_user, backend_passwordyy。2.4版本支持这个字段。插入后LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;验证连接mysql -uapp_user -pAppPassw0rd! -h127.0.0.1 -P6033 -e SELECT 1这条能过说明前端账号OK。如果报密码错误先确认你是不是漏了LOAD那条命令。3.3 健康监控参数monitor配置ProxySQL内部有一套monitor模块周期性向后端MySQL发起心跳检查包括ping、连接、复制延迟等。检查结果直接影响你看到的status字段。SET mysql-monitor_usernamemonitor_user; SET mysql-monitor_passwordMonitorPass; SET mysql-monitor_interval_ms2000; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;这里要求后端MySQL上存在monitor_user%账号并授权GRANT REPLICATION CLIENT ON *.* TO monitor_user% IDENTIFIED BY MonitorPass;注意REPLICATION CLIENT权限就够不需要给SUPER或ALL。给大了反而有安全风险。我在生产环境见过有人图省事直接给ALL结果ProxySQL monitor账号被拖库后攻击者可以从管理端口操作后端任意库。配置完成后等几秒查看监控状态SELECT hostgroup_id, hostname, port, status FROM monitor.mysql_server_connect_log ORDER BY time_start DESC LIMIT 5;这里能看到最近的心跳结果。如果一直失败十有八九是监控账号认证问题或网络不通。这个点后面踩坑章节我会重点展开。4. 读写分离规则落地query_rules的匹配逻辑后端实例注册好了账号也通了接下来就是关键的规则配置——告诉ProxySQL哪些SQL走读、哪些走写。4.1 规则表结构和字段优先级mysql_query_rules是这个功能的核心。常用字段有rule_id规则ID数字越小优先级越高active是否启用1启用match_pattern正则表达式匹配SQL文本destination_hostgroup匹配后转发到哪个主机组apply是否停止继续匹配后面的规则1是最终应用cache_ttl查询结果缓存时间单位毫秒ProxySQL按rule_id递增顺序逐条匹配apply1后立即终止。所以规则顺序不能乱。我的规则优先级安排大致是明确写语句的规则INSERT/UPDATE/DELETE/REPLACE直接打到写组SQL注释中带了路由标注的语句比如/* WRITE */打到写组SELECT语句打到读组其他没匹配上的走default_hostgroup具体建规则示例INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (1, 1, ^INSERT, 10, 1), (2, 1, ^UPDATE, 10, 1), (3, 1, ^DELETE, 10, 1), (4, 1, ^REPLACE, 10, 1), (5, 1, ^SELECT, 20, 1);注意ProxySQL默认是大小写敏感的SELECT和select在正则匹配下是不同的。如果应用里SQL风格不统一会出现有的查询走了写库有的走了读库看似能用但没达到分流效果。解决办法有两个一是设置规则的case_sensitive0注意这是ProxySQL 2.0之后支持的在规则表里的列老版本需要正则里自己处理大小写UPDATE mysql_query_rules SET case_sensitive0 WHERE rule_id5;二是用正则兼容大小写UPDATE mysql_query_rules SET match_pattern^[Ss][Ee][Ll][Ee][Cc][Tt] WHERE rule_id5;我推荐第一个简单直接。4.2 规则设计与参数调优线上环境光有基础规则是不够的。你至少还要处理这几种情况带FOR UPDATE的SELECT。SELECT ... FOR UPDATE是要加锁的如果走了从库从库只读没有锁语义数据一致性直接出问题。所以必须把这条语句打到写库。规则如下INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (6, 1, SELECT.*FOR UPDATE, 10, 1);注意这条规则的rule_id要大于基础READ规则比如rule_id5并且匹配后直接 apply1 跳过后面的SELECT读库规则也就是它抢在普通SELECT规则之前命中写库。我把顺序调整为rule_id 5 是FOR UPDATErule_id 6 才是普通^SELECT这样在正则匹配里FOR UPDATE先命中不会落入读库。事务绑定问题如果应用在一个事务里先BEGIN然后执行SELECT再UPDATE默认情况下SELECT会走读库而事务的其他语句走写库这在MySQL的从库上会造成严重的数据不一致——因为在从库上执行SELECT时事务还没把该提交的提交看不到写库应该看到的数据。要处理这个可以给事务的BEGIN加规则让它强制走写库INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (4, 1, ^BEGIN, 10, 1);如果不希望事务里所有读都走写库就需要应用配合把事务内的语句显式打标。这个复杂度看业务我一般先要求应用事务内不要跨库读。限定表名的SELECT如果你的从库是专门用来扛大查询的某些表比如订单流水表查询频率特别高可以针对性地把它的SELECT全部打到某个从库组甚至打到专用的分析实例INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (7, 1, ^SELECT.*FROM order_flow, 30, 1);后面这个30是指定读组你得确保mysql_servers里有hostgroup_id30的实例否则这条规则会把SQL发到空组然后报错。匹配短路优先级注意规则顺序从上到下。一旦命中如果apply1就停止。所以高优先级规则要放前面低优先级放后面。4.3 从库延迟的容错设计读写分离还有一个容易忽视的问题主从延迟。虽然MySQL 8.0的半同步复制让延迟窗口小了很多但总归不是零。你的应用在写完主库后马上去读从库很有会读到旧数据。解决方案有三种常见思路关键读走主库在规则层面让包含某些关键表名的SELECT走写组。比如^SELECT.*FROM order直接走写组10。牺牲一点性能保一致性。写后强制刷新应用层在写完之后强制使用一个带提示的SQL注释打标ProxySQL规则匹配注释把它送到主库。比如参数mysql-query_processor_comment_protocol1开启后ProxySQL会解析SQL前的注释你还可以用replica这类HINT做更精细的路由。读从库加写组轮询用权重控制读流量比例但这不能彻底解决延迟。我个人建议核心业务先保证一致性跑批类的非实时查询才放心大胆走从库。别把读写分离的设计初衷用反了。规则配置完执行LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;每次改规则都走这三步出问题可以马上回滚上次的配置文件。5. 验证读写分离效果从日志到状态统计配置完别着急上线先用实弹测试。验证读写分离做没做对有两层路由层验证和统计层验证。5.1 用sysbench模拟混合负载我习惯先用sysbench压一压让流量混合起来然后在ProxySQL的统计库看路由分布。假设你已经在从库上端好了sysbench压测数据读写都跑在从库上主库不做压测目标。命令行大致是sysbench /usr/share/sysbench/oltp_read_write.lua \ --mysql-host127.0.0.1 --mysql-port6033 \ --mysql-userapp_user --mysql-passwordAppPassw0rd! \ --mysql-dbtestdb --tables10 --table_size100000 \ --threads16 --time60 --report-interval5 run压完看统计。5.2 检查查询路由分布在管理端口执行SELECT hostgroup, digest_text, count_star FROM stats.stats_mysql_query_digest ORDER BY count_star DESC LIMIT 20;如果读写分离生效你会看到大量SELECT ...语句的hostgroup20INSERT/UPDATE/DELETE语句的hostgroup10如果全在10说明规则没生效或者match_pattern匹配不上。再检查连接池状态SELECT * FROM stats.stats_mysql_connection_pool;看hostgroup20的连接池是否有连接建立、后端连接数是否正常。5.3 直接验证路由到哪台后端如果你想快速确认某一条SQL实际走了哪台机器办法很土但有效在后端MySQL上开general log或者直接看performance_schema.events_statements_summary_by_digest。另外可以用ProxySQL自带的 debug 模式管理端口执行SET mysql-query_processor_debug1;之后在main.mysql_query_rules的hit列上能看到被命中的规则的次数。定位问题很好用排查完记得把这个变量关掉否则影响性能。实际上最快的验证方式# 在应用侧执行 mysql -uapp_user -pAppPassw0rd! -h127.0.0.1 -P6033 -e SELECT server_id;连执行几次如果看到不同的server_id说明SELECT被轮流或按权重路由到了多个实例上。再执行mysql -uapp_user -pAppPassw0rd! -h127.0.0.1 -P6033 -e UPDATE testdb.sysbench_test SET aa WHERE id1;如果主从server_id不同这条UPDATE返回后你再看后端主库的SHOW MASTER STATUS能对得上。5.4 理解监控数据避免自欺欺人很多人喜欢直接看stats.stats_mysql_query_digest里的count_star但你得注意digest_text是参数归一化之后的SQL模板。比如两条不同的SELECT * FROM user WHERE id1和SELECT * FROM user WHERE id2会归并为同一条。这样你会看到某个模板的命中数特别高。这反而是正常的说明规则匹配是按模板级别统计而不是按原始SQL级别。另外stats_mysql_query_digest是一段时间的累积值重启后清零。你可以通过RESET STATS命令清零后重新测试。6. 踩坑实录一段完整的排错链路这部分分享我实际遇到过的坑每一个都是真实生产中排查过的按排查过程写出来希望能帮你少走弯路。6.1 坑之一monitor账号权限不足导致的假离线现象配置完所有实例SELECT * FROM mysql_servers看到两台从库的status是OFFLINE_HARD。排查过程检查后端MySQL上从库的read_only和网络正常。在管理端口查询 connect logSELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start DESC LIMIT 10;能看到connect_error列显示了具体的报错比如Access denied; ... for user monitor_user%。手动在从库上用监控账号执行mysql -umonitor_user -pMonitorPass -h192.168.1.102 -e SHOW SLAVE STATUS;结果直接报错权限不足。原因是当时图省事只授权了GRANT USAGE ON *.* TO monitor_user%没给REPLICATION CLIENT。ProxySQL的monitor模块去检查read_only/read_only_connected其实需要这个权限。解决GRANT REPLICATION CLIENT ON *.* TO monitor_user%; FLUSH PRIVILEGES;然后重看状态从OFFLINE_HARD变成ONLINE。这个坑的隐形成本在于你以为是主从问题实际上只是监控账号权限问题而ProxySQL会因此拒绝把SQL路由过去。注意monitor账号里还有一项容易漏的mysql-monitor_username/mysql-monitor_password默认是monitor/monitor这两个值。生产环境如果没改后端MySQL上又没建这个账号就会一直报认证失败。别问我怎么知道的。6.2 坑之二规则大小写敏感导致ALL SELECT走写库现象规则建好了也LOAD了但stats_mysql_query_digest里所有SELECT都落在hostgroup10读库完全没流量。排查过程手动执行SELECT server_id一直是主库的ID说明SELECT确实走了写库。检查mysql_query_rulesmatch_pattern写的是^SELECT看着没问题。后来发现应用用的是小写SQLselect * from user。正则匹配大小写敏感^SELECT匹配不到^select于是这条SQL没命中任何规则走了default_hostgroup10写组。解决给规则统一设置case_sensitive0。或者把所有规则里的SELECT用[Ss][Ee][Ll][Ee][Cc][Tt]替代。我推荐前者。补充一个还有一种情况是应用发来的是带注释的SQL比如/* app_name */ SELECT * FROM user因为注释在最前^SELECT就匹配不上了。处理办法是规则里改成匹配注释后的语句或者让应用去掉SQL前缀注释。更稳妥的做法是启用mysql-query_processor_comment_protocol1这样ProxySQL会把注释内容提取出来单独处理不会影响实际的正文匹配。6.3 坑之三改配置不SAVE重启全丢这个说起来低级但特别常见。在测试环境我们可以只LOAD不SAVE改错了一重启配置就回退。但生产环境如果有人LOCAL了规则调试完忘了SAVE重启后配置丢失读写分离直接失效。排查方法就是登录管理端口看配置是否仍在SELECT rule_id, active, match_pattern, destination_hostgroup FROM mysql_query_rules;此时如果mysql_query_rules当前加载的配置和当初SAVE到磁盘的不一致那说明有人只LOAD没SAVE。我自己的习惯是把这三条SAVE命令写进一个shell脚本每次调整配置都先回放一遍LOAD MYSQL SERVERS TO RUNTIME; LOAD MYSQL USERS TO RUNTIME; LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL SERVERS TO DISK; SAVE MYSQL USERS TO DISK; SAVE MYSQL QUERY RULES TO DISK;用一个proxysql_apply.sh管理避免步骤遗漏。6.4 坑之四连接池连接数暴涨有次上线后后端MySQL的max_connections被打满了。查ProxySQL的连接池状态发现mysql_users里设置的max_connections100是每用户每主机组的最大连接数而不是全局的。如果读写规则会转发到多个主机组同一个账号在写组和读组各有100连接加起来就是200。而且如果后端MySQL的max_connections本身只有200ProxySQL这边在连接还活着的情况下不会主动断会一直占着MySQL连接。所以你在mysql_users里设置的连接数要考虑转发到每个主机组要小于等于后端MySQL的max_connections / 主机组数。我现在的经验值是单个用户单主机组连接数设为后端max_connections的30%~40%空闲连接尽快被杀掉。这可以在全局变量mysql-free_connections_pct或者每个用户的max_connections里控制。如果后端MySQL连接数紧张还可以把mysql-connection_max_age_ms调小强制定期回收连接。6.5 坑之五配置_SELECT_ FOR UPDATE_漏网后的数据事故这个坑是在一个真实的电商业务里被我撞上的。需求是订单列表走读库订单详情走读库但订单查询前有个SELECT ... FOR UPDATE锁订单防止并发改单。规则只写了^SELECT走到读库没写FOR UPDATE例外。结果就是两个并发请求同时查到同一条订单然后一起更新。因为从库的锁和写库不共享两个人都在自己的事务里以为锁住了最后产生脏数据。排查链路反正很痛苦最后查ProxySQL的查询日志发现带FOR UPDATE的SQL也落到了读库。给规则加了SELECT.*FOR UPDATE到写库后再没出现过一起同样的问题。这里我现在的原则是凡是和写、锁、事务沾边的SQL一律在高优先级规则中指到写组。宁可损失一点读性能不要冒数据一致性的险。7. 给生产环境的几点运维建议最后说几个我在生产环境总结下来的经验不一定全面但都能直接用。第一所有配置文件的修改都要走版本管理。ProxySQL的SQLite文件就是配置本身我每次调整完会顺手把main库导出成SQL文本存进Git仓库便于回溯和对比。一旦生产出问题能快速知道配置从什么时候开始变了。第二重启ProxySQL前先备份SQLite文件cp /var/lib/proxysql/proxysql.db /var/lib/proxysql/proxysql.db.bak.$(date %F)SAVE ... TO DISK的配置文件在重启后会自动加载如果升级或回滚二进制版本备份能保命。第三用多实例ProxySQL做高可用。单点ProxySQL在故障时影响所有应用。推荐部署两台做VIP漂移或者负载均衡配置共享同一套SQLite通过文件同步或定期导出导入后端点就是两套一模一样的前端代理IP。但注意两个ProxySQL实例同时管理同一套后端MySQL规则要保证一致不然会出现同样的账号在一台代理上允许的规则和另一台代理上完全不一致的情况。第四监控不能只盯着后端MySQL。ProxySQL自身的monitor_mysql_replication_lag表、连接池相关的stats_mysql_connection_pool都要接到监控告警。主从延迟大时读组会自动下线这需要在全局变量里配mysql-monitor_replication_lag_threshold但你不能依赖它自动恢复要有告警去触发人工决策。第五规则上线前先在测试环境跑一轮路由验证。如果条件不允许应用连接串指向6033端口前可以先指向6033的测试实例用脚本对几十条有代表性的SQL做断言看是否路由到了期望的hostgroup。这套脚本我目前放在Git仓库里每次业务变更SQL风格都会跑一遍。读写分离的方案形态很多ProxySQL不是唯一的解但把规则、主机组、监控三者想清楚之后它确实是我用下来最顺手、最可控的一个工具。希望这篇内容能帮你把ProxySQL从听说过变成能落地至少踩坑的时候有个参考方向。