直接说结论MySQL主从复制搭好之后读写分离就是下一个绕不开的话题。主库的写压力不想让读流量来抢从库的资源长期闲着也是浪费。市面上的方案我基本都试过——应用层双数据源、ShardingSphere、Mycat、MySQL Router最后留在生产环境里跑得最稳的还是ProxySQL。这个系列写到第十篇这篇就把ProxySQL做读写分离的核心配置一次讲清楚主机组怎么设计、路由规则怎么写、有哪些必须避开的坑。我会以一套一主一从的真实环境为基线从方案选型讲到具体落地再从故障排查讲到连接池调优。文中所有SQL和配置片段都基于ProxySQL 2.x如果你还在用1.4.x个别表结构和字段需要对照官方文档微调但整体思路完全一致。1. 读写分离方案选型为什么在众多中间件里选ProxySQL1.1 主从复制搭好了读写分离还要解决哪些问题很多人以为主从复制做完读写分离就是SELECT走从库、其他走主库一句话的事。真到落地的时候你会发现还有一堆隐藏问题等着处理。第一是连接管理问题。客户端并不知道哪台是主库、哪台是从库如果应用层自己维护两套连接池主从切换时就要改配置、发版本运维成本直接拉满。第二是流量路由的粒度问题。不是所有SELECT都能走从库SELECT ... FOR UPDATE需要走主库事务内的普通SELECT为了保证隔离性和一致性也不能简单甩给从库。第三是高可用感知问题。某个从库挂了ProxySQL得自动把它从转发列表里摘掉而不是让业务连接一直撞墙。如果采用应用层双数据源方案这些逻辑全部要写在业务代码里每个团队都要重复造一遍轮子。用中间件业务侧只需要连一个逻辑地址路由、故障转移、连接池全部由中间件接管。这就是我选型时的核心判断标准业务代码的侵入程度必须降到最低。1.2 中间件方案横向对比市面上常见的读写分离中间件我把它们放在一张表里对比过优劣非常明显。方案透明性路由能力连接池管理运维成本适合场景应用层双数据源差侵入业务代码靠代码硬编码每套连接池独立维护高每次拓扑变更都要发版小型项目、团队能hold住MySQL Router较好规则简单只做基础分流弱中简单读写分离无复杂规则需求Mycat较好分片强读写分离偏重一般较高需要分库分表与读写分离一体ShardingSphere较好功能丰富规则复杂强较高大团队、复杂分片场景ProxySQL好正则digest匹配灵活强低生产环境读写分离首选我最终留下ProxySQL主要原因有三个。一是它用SQL语句做配置所有变更都走LOAD ... TO RUNTIME热加载不用重启进程这对我这种凌晨三点改配置的运维场景太关键了。二是它的监控统计非常完善stats_mysql_connection_pool、stats_mysql_query_digest这些表能直接把每个后端的连接池状态和SQL去向看得明明白白。三是它对事务的处理有专门设计transaction_persistent字段能避免事务内读写在主从节点间漂移这是很多轻量中间件做不到的。2. 弄清ProxySQL的核心配置模型再动手2.1 三层配置结构与一切皆表的设计ProxySQL的配置体系是学习过程中最容易绕晕的地方。它把配置分成三层runtime层是真正生效的配置memory层是当前会话里修改的草稿配置disk层是持久化到SQLite文件里的配置。每个配置操作的正确姿势是先修改memory层然后执行LOAD ... TO RUNTIME让配置生效确认没问题后再执行SAVE ... TO DISK落盘。如果你只改了memory层而没有SAVE重启之后所有变更全部丢失。我在一次生产变更中就吃过这个亏改了规则忘了落盘晚上系统自动重启第二天早上所有流量又回到了主库从库白白空转。ProxySQL的配置入口是admin接口6032端口用MySQL客户端登录mysql -h127.0.0.1 -P6032 -uadmin -padmin进去之后就是一堆表操作方式跟操作MySQL表完全一样。这种一切皆表的设计我第一次用的时候觉得很奇怪但习惯之后会发现特别顺手能SELECT查配置、能UPDATE改配置、能INSERT新增配置排查问题的时候一套SQL全搞定。2.2 路由规则看懂match_pattern和优先级读写分离的核心在mysql_query_rules表。这张表决定了每条SQL进到ProxySQL之后该往哪个主机组转发。关键字段就这么几个rule_id是规则编号规则按rule_id从小到大顺序匹配active1表示规则生效match_pattern填正则表达式匹配SQL语句destination_hostgroup指定匹配后转发到哪个主机组apply1表示命中这条规则后就不再往后匹配了。规则匹配有几个细节必须注意。第一匹配的是去掉注释、去掉前后空白后的SQL语句正则默认对大小写不敏感。第二规则顺序极其重要因为命中即停所以精确规则一定要放在宽泛规则前面。典型的例子是处理SELECT ... FOR UPDATE的规则必须排在普通SELECT规则之前否则FOR UPDATE会被当成普通SELECT发给从库然后从库以只读模式直接报错。除match_pattern之外还有match_digest字段匹配的是SQL的指纹digest。指纹是把SQL中的具体值参数化后得到的模板比如SELECT * FROM users WHERE id ?。用match_digest的好处是正则匹配只对标准化后的模板执行性能更好也更稳定不会因为某个where条件的值变化导致正则失效。生产环境的规则我建议优先用match_digest。3. 一步步配置读写分离基于ProxySQL 2.x3.1 环境准备与前置检查先说环境。我这次演示用的是一主一从架构IP规划如下主库192.168.1.10:3306从库192.168.1.11:3306ProxySQL192.168.1.20admin端口6032业务端口6033动手配置ProxySQL之前先把主从复制状态检查一遍。在从库上执行SHOW SLAVE STATUS\G重点关注Slave_IO_Running和Slave_SQL_Running必须都是Yes同时Seconds_Behind_Master为0。如果从库本身就没跟上主库后面配置得再漂亮读到的数据也是旧的。接下来准备两个MySQL账号。一个是ProxySQL用来做后端健康检查的监控账号权限不需要太大REPLICATION CLIENT就够用这个权限能读取主从状态信息便于ProxySQL判断复制是否正常。CREATE USER monitor% IDENTIFIED BY Monitor123; GRANT REPLICATION CLIENT ON *.* TO monitor%;另一个是业务账号这个账号必须同时在主库和从库上创建密码保持一致因为ProxySQL会拿这套账号去连接后端两个节点。CREATE USER appuser% IDENTIFIED BY App123; GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO appuser%;3.2 初始化ProxySQL与后端注册ProxySQL安装完成之后admin接口默认账号是admin/admin。登录进去之后我习惯先把管理员密码和监控账号信息改了这些都是基础的动作。配置监控账号不是写在某张表里而是通过全局变量设置的SET mysql-monitor_usernamemonitor; SET mysql-monitor_passwordMonitor123; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;重点来了ProxySQL 2.x支持直接声明主从复制主机组。我建议用mysql_replication_hostgroups表来定义写组和读组让ProxySQL自己去感知主从角色变化而不是手动把每个节点绑死在某个主机组里。INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, comment) VALUES (10, 20, 主库写组/从库读组);这里的设计思路是10号主机组放主库负责所有写流量20号主机组放从库负责读流量。ProxySQL会根据后端节点的复制状态自动维护这两个组里的成员。接下来注册后端节点INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES (10, 192.168.1.10, 3306, 1); INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES (20, 192.168.1.11, 3306, 1); LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;注意主库节点虽然只在10号组里被注册但从库节点也只需要注册在20号组里mysql_replication_hostgroups会自动处理角色映射。这种设计让我少了很多手工维护的工作主从切换时ProxySQL的表现比我想象中智能。3.3 业务账号、监控账号与读写分离规则配置注册完节点接着配置业务账号。我对default_hostgroup的选择有一个明确的偏好设置为10也就是主库组。这样做的原因是一旦某条SQL没有被任何规则命中它会默认走主库。对于写操作这是安全的兜底对于读操作顶多是主库多扛一点压力但绝不会因为误路由到从库而产生数据不一致。INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent) VALUES (appuser, App123, 10, 1); LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;transaction_persistent1这个参数一定要理解透。它表示同一个事务内的所有查询都绑定到同一台后端MySQL上。我后面会在第4章专门展开讲这个字段带来的行为变化现在先记住结论默认开启对绝大多数业务都是正确的选择。最后是读写分离规则。我的规则表设计如下-- 规则1: SELECT ... FOR UPDATE 走主库 INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (1, 1, ^SELECT.*FOR UPDATE, 10, 1); -- 规则2: 普通SELECT走从库 INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (2, 1, ^SELECT, 20, 1); -- 规则3: 写操作走主库 INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (3, 1, ^(INSERT|UPDATE|DELETE|REPLACE), 10, 1); LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;规则1必须排在规则2前面这是我在1.1节反复强调过的顺序问题。但规则3其实在大多数时候可以省略因为default_hostgroup已经兜底了写操作。保留规则3的好处是语义清晰排查问题时一眼能看懂设计意图。3.4 验证路由是否按预期工作配置完成之后最需要验证的是流量是否真的按规则走。我的验证方法很简单用业务账号直连ProxySQL的6033端口然后反复执行SELECT hostname。mysql -h192.168.1.20 -P6033 -uappuser -pApp123 -e SELECT hostname;多执行几次观察返回的主机名。如果一会儿是主库IP一会儿是从库IP说明普通SELECT确实在读写组之间做了分发。如果全部返回同一个节点就要回头检查mysql_servers里20号组是否注册成功、规则是否LOAD到runtime了。写操作的验证我习惯开启MySQL的general log或者在ProxySQL里查stats_mysql_query_digest表能看到每条SQL去了哪个hostgroup。强烈建议配置完成后跑一段时间的真实业务然后频繁查看这张统计表确认读写分布比例符合预期。4. 生产环境里的坑与排查实录4.1 事务粘性带来的写入放大transaction_persistent1虽然保证了同一事务内的所有语句都落在同一台后端但它也带来了一个容易忽略的现象事务内第一句话如果是写操作那么后面所有的SELECT都会跟着跑主库。我在一个订单系统里碰到的场景是这样的应用层代码先执行BEGIN接着第一条是UPDATE后面跟着一串SELECT查询全部被绑到主库上。从库虽然配置了读写分离但这类事务读流量完全打不过去从库负载一直很低主库却吃满了。这不是ProxySQL的Bug目的是保证事务的隔离性。想想就明白事务内如果先写了主库马上又从从库读从库的复制延迟可能会导致读到旧数据破坏事务一致性。解法要看业务场景。如果事务内的SELECT都是读当前事务修改的数据那它本来就该走主库保持默认即可。如果事务内混入了大量只读查询建议把这类查询拆出去放到autocommit1的只读接口里这样它们才能走从库。实在拆不掉的可以在开发规范里明确规定事务第一条语句不要是写操作除非必须如此。4.2 从库延迟导致读脏数据读写分离最大的数据风险就是刚写完就读场景。主库写完从库还没来得及同步业务端马上查这条数据查不到或者查到旧值用户感知就是保存成功了但列表里看不到。ProxySQL 2.x在mysql_replication_hostgroups表里提供了max_replication_lag字段。这个字段的作用是当某台从库的复制延迟超过设定阈值时ProxySQL会把这个后端临时标记为SHUNNED状态所有新流量不再往它身上分配直到延迟追平恢复。UPDATE mysql_replication_hostgroups SET max_replication_lag 5 WHERE writer_hostgroup 10 AND reader_hostgroup 20; LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;这个5秒的含义是一旦从库延迟超过5秒立即从读组中摘除。对于大部分读多写少的系统5秒已经能给数据同步留出足够余量。但如果你的业务对实时性要求极高比如库存查询、余额查询这个策略还不够。我在这种场景下的做法是规则层加一条让关键SQL通过SQL注释强制走主库。应用端在SQL前面加/*master*/标记ProxySQL侧配置对应规则INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (10, 1, ^\\/\\*master\\*\\/, 10, 1);需要提醒的是这句规则能否生效取决于ProxySQL是否解析并保留SQL注释相关由mysql-parse_comment变量控制。如果发现注释标记没有命中规则先检查这个变量的设置。4.3 后端状态异常排查ProxySQL的mysql_servers表里有个status字段它直接反映了后端MySQL的健康状态。我遇到过的状态有下面几种整理成一张表供排查时对照。状态含义常见处置ONLINE后端正常正常接收流量无需处理OFFLINE_SOFT软摘除已有连接继续用新连接不再分配多用于计划内维护OFFLINE_HARD硬摘除马上断开所有连接用于紧急故障隔离SHUNNED后端被临时拉黑等状态恢复后自动解除排查时第一步是看stats_mysql_connection_poolSELECT hostgroup, srv_host, srv_port, status, ConnUsed, ConnFree, ConnERR FROM stats_mysql_connection_pool;如果某个后端的ConnERR数字一直往上涨大概率是账号密码错误、网络不通或者后端max_connect_errors达到了上限。这里有个很经典的坑监控账号密码配错ProxySQL会一直报connect error同时后端的status会被打到SHUNNED。我排查这类问题时习惯先用SELECT * FROM mysql_servers\G确认后端配置再用命令行手动连一次后端MySQL验证账号密码是否真的能用。中间件说连不上先用MySQL客户端自己试能快速排除后端自身的问题。4.4 FOR UPDATE、存储过程等几个经典误判我第一次配读写分离就把SELECT ... FOR UPDATE漏掉了结果线上从库报了一堆read-only transaction错误。原因就是FOR UPDATE规则排在普通SELECT规则后面永远轮不到它。这个问题在2.2节已经强调过但依然值得在这里用真实案例再提示一次。另一个容易踩坑的点是存储过程和预处理语句。ProxySQL只能解析并路由客户端发过来的SQL文本。存储过程内部的动态SQL是在MySQL端执行的ProxySQL根本看不到所以你在规则里写什么都管不到存储过程里的SELECT。遇到我明明配了SELECT走从库为什么存储过程里的查询全在主库跑这类问题先别怀疑规则直接把存储过程里的SQL挪到应用端用普通SQL执行才能被中间件分流。还有一类是预处理语句比如PREPARE stmt FROM SELECT ...。ProxySQL能匹配到的是PREPARE这个SQL本身后面EXECUTE时它没法按预处理里的真实语句内容做路由。这类场景当前版本下没有太优雅的绕法要么改业务写法要么干脆让这类请求统一走主库。5. 连接池与性能调优的落地经验5.1 ProxySQL侧连接池配置读写分离看起来只是路由真正影响稳定性的往往是连接池。ProxySQL作为客户端与MySQL之间的中间层会把前端连接池化再维护一组到后端的连接池。如果配置不合理可能会出现前端连接被大量占用、后端连接反复新建的尴尬局面。我常用的几个调优点位如下mysql-threads控制的是ProxySQL自身处理客户端请求的线程数默认4。在CPU核数较多的机器上可以逐步调大到8或16处理能力会明显提升。mysql-connection_max_age_ms控制后端连接的最大存活时间。连接长时间不释放MySQL里会堆积大量空闲会话时间一长旧的事务隔离状态、临时表资源都有可能出问题。我给生产环境设定的值一般是10分钟。mysql-max_connections是ProxySQL允许的前端最大连接数。这里的值建议跟后端口径一起看注意不要随手设成几万否则后端口径没接住MySQL直接被打爆。配置示例SET mysql-threads8; SET mysql-connection_max_age_ms600000; SET mysql-max_connections6000; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;每次调整完都要观察stats_mysql_connection_pool里的ConnUsed和ConnFree确认连接池有空闲连接可用且不会频繁创建新连接再固定参数。5.2 上线前必须过一遍的检查清单我在生产环境推读写分离之前会严格执行一份自检清单。不要嫌啰嗦这些都是在事故中学到的教训。第一规则优先级确认。FOR UPDATE规则必须在普通SELECT之前这是零容忍项。第二default_hostgroup设置为写组。保证漏网SQL不会打到只读从库。第三监控账号权限确认。能正常执行SHOW SLAVE STATUS否则复制延迟检测失效。第四mysql_replication_hostgroups配置确认max_replication_lag要按业务容忍度设定不要用默认值不管。第五所有配置执行过SAVE ... TO DISK。没有落盘机器重启后一切归零。第六压测验证。用sysbench或公司压测工具跑一轮观察stats_mysql_query_digest中SELECT语句是否均匀分布到从库组写语句是否只进主库组。第七准备主从切换演练。临时把主库节点停掉确认ProxySQL能按预期将写组流量切到新主库并观察业务日志是否有异常报错。这套配置我在生产环境跑了两三年最大的感受是读写分离的瓶颈往往不在ProxySQL本身而在你对业务SQL的理解。先把规则顺序设计清楚再把事务行为和延迟阈值想明白剩下的只是加载配置的事。如果你在配置过程中有哪一步跟这篇文章里的情况不一样先看版本再看日志最后检查权限。版本差异能解释掉一半的灵异现象日志能定位到另外四成的问题剩下那一成才是权限和账号配置这些基础项没做干净。