先说结论这个标题会让不少人大吃一惊——MySQL内核过去一直不直接在数据库层给你做读写分离所谓“8.2支持读写分离了”实际指的是MySQL 8.2里的MySQL Router组件在InnoDB Cluster架构下原生支持了读写自动路由。也就是说你照常用主从复制或组复制但连接层不再需要人为区分“写库地址”和“读库地址”一个端口进去Router帮你把读和写分别送到合适的节点上。这篇文章我按真实项目的推进顺序来写从背景、选型、搭建、踩坑到最终调优尽量把“为什么这么做”也讲清楚。适合正在做数据库读多写少改造、想用官方方案替代ProxySQL或者在公司里被分配了“把MySQL 8.2读写分离落地”这类任务的DBA和后台开发参考。1. 先搞清楚8.2的“读写分离”到底是个什么能力1.1 MySQL内核一直没有内置读写分离很多人在搜索引擎里打“mysql 读写分离”结果翻到的全是ProxySQL、Mycat、ShardingSphere或者自己写个中间层。原因很简单MySQL传统主从架构里面主库和从库除了复制关系彼此是“平级”的数据库本身并不知道谁该处理读、谁该处理写。想让读走从库通常有三种做法应用层双数据源Java里用DynamicDataSource写操作走主库查询走从库。缺点是业务侵入大一不小心就把事务里的读也甩到从库去了。代理层比如ProxySQL、Mycat在SQL协议层做转发。这是目前最主流的方案灵活好用但多维护一个中间件高可用、配置同步、版本升级都是成本。官方MySQL Router传统用法是掰成两个端口6446读写端口打到主库6447只读端口打到从库。应用该连哪个自己挑本质上还是“人工分离”。这三种方案我前几年都折腾过没有谁是零成本的。尤其应用层双数据源一旦上线后遇到“主从延迟导致读不到刚写的数据”产品那边会立刻来找你喝茶。1.2 8.2的新变化Router自动识别读写MySQL 8.2的发行说明里针对InnoDB Cluster的MySQL Router新增了读写分离支持。这不再是让你自己区分端口而是Router在同一个端口上接收应用连接然后根据SQL语句和事务状态自动决定是发给主库还是发给从库。我自己的理解是这相当于把ProxySQL那套“规则路由”的核心逻辑搬进了官方路由器。对开发同学来说配置里只需要一个JDBC地址就能同时获得读写分离能力不需要在代码里写“读库”“写库”两套数据源对运维同学来说少维护一个第三方组件而且Router对InnoDB Cluster的拓扑感知是原生的主节点切换时能自动跟随不需要额外写脚本。注意路由的自动判断是相对的不是“绝对智能”。Router会根据事务是否只读来做分发具体规则后面专门有一节讲提前有个心理准备就行。别以为一个端口接进来Router就能在你写了一条INSERT之后再帮你把同事务的SELECT全部分发到从库——那是不可能的一个事务内一旦出现写操作这个连接后续的读操作就只能老老实实留在主库。1.3 这项能力适合谁、不适合谁先说适合谁典型的读多写少系统总QPS上来了主库CPU持续偏高但大部分请求是查询并且你能容忍从库有几百毫秒级别的复制延迟。再说不适合谁写密集型应用核心写事务又长又多主库根本没有富余CPU给备库复制这种场景就算开启读写分离从库也会被复制拖死。还有一种情况是业务对一致性极度敏感比如交易支付类读请求要求“我刚插入的必须马上查到”这类系统可以上读写分离但要配合“关键读走主库”的策略否则延迟会咬人。如果你已经上了云数据库比如某些云厂商的MySQL集群控制台里一键开启“读写分离”功能本质上也一样——底层是代理集群加只读实例跟你自己在EC2上搭InnoDB Cluster加Router是一回事只是别人把运维动作封装了。2. 我为什么要把读流量拆出去2.1 一次主库“假死”复盘今年年初我接手了一个社区类后台项目用户量涨得很快库里的核心表已经到几千万行。上线前压测看着没问题结果某天下午活动开始后主库Connections直接冲到上限Load Average飙升一堆慢查询把连接池塞得死死的。页面表现就是接口超时部分写请求直接报“Too many connections”。我拉出Performance Schema的统计一看问题特别典型整体QPS大约11000其中SELECT占了8600剩下的UPDATE、INSERT、DELETE加起来只有2400。写量不大但所有读请求都压在同一个主库身上。主库CPU跑到了90%以上innodb_thread_concurrency到了瓶颈连写请求排队都排不上。复盘结论很扎心这属于“典型读多写少但没有做读写分离”导致的架构问题不是数据库配置能救回来的。加索引能解决一部分慢查询但解决不了“所有查询都打到主库”这个结构性矛盾。2.2 方案选型为什么没继续用ProxySQL选型前我先列了几个不能退让的要求应用改动要小。我们接的是既有Java后端Druid连接池不可能为了这个去把所有Mapper拆成读写两套。要能自动感知主从切换。以前用ProxySQL时主库故障切换到新主库ProxySQL里的hostgroup经常要手动改后来虽然写了脚本检测但总归是隐患。最好用官方组件。小团队没人专职维护中间件第三方代理的升级、补丁、故障排查都是一个坑。当时对比了几个方案自研读写分离中间层靠谱但周期长排除ProxySQL功能强大、规则灵活但我们团队没人能花费精力吃透它的配置体系和监控体系Mycat更适合分库分表场景读写分离只是它的附加能力项目已经进入稳定期不想引入那么重的框架MySQL Router加InnoDB Cluster最大的优势是官方配套并且8.2正好原生支持读写分离这成为最终选型的决定因素。说实话如果项目已经是ProxySQL跑得很好我不会劝你迁到Router。工具没有绝对好坏只有是否匹配当前团队的维护成本。我们选Router图的是“少一个外部依赖多一份官方兜底”。2.3 环境规划既然要用8.2的读写分离集群形态必须是InnoDB Cluster。我规划了三台机器都是16核64G的通用机型node1 192.168.10.11主节点node2 192.168.10.12从节点node3 192.168.10.13从节点操作系统是CentOS 7.9MySQL版本选了当时最新的8.2.0社区版。因为8.2属于Innovation版本更新节奏会快我自己测试环境用的是8.2.0生产建议等小版本稳定后再上不过这篇场景我就按8.2.0写。安装这里提醒一句下载安装包别只认“8.2”这个数字要注意是mysql-8.2.0-linux-glibc2.17-x86_64-minimal.tar.xz这种tar包还是rpm包。tar包适合装到自定义路径rpm包适合系统标准路径/usr/local/mysql。我最后用的tar包解压到/usr/local/mysql做软链方便升级。mysqld --initialize --usermysql这一步是必须的别直接启动mysqld否则后面连数据目录都起不来。初始化的输出末尾会带一个临时密码记下来后面第一次登录要用。3. 搭建InnoDB Cluster并开启读写分离的完整过程3.1 组复制基础配置InnoDB Cluster底层依赖Group Replication三台机器都要配置好这些MySQL参数写在/etc/my.cnf的[mysqld]段里server_id11 gtid_modeON enforce_gtid_consistencyON log_binON log_slave_updatesON binlog_formatROW transaction_write_set_extractionXXHASH64 loose-group_replication_group_nameaaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa loose-group_replication_start_on_bootOFF loose-group_replication_local_address192.168.10.11:33061 loose-group_replication_group_seeds192.168.10.11:33061,192.168.10.12:33061,192.168.10.13:33061 loose-group_replication_single_primary_modeON loose-group_replication_enforce_update_everywhere_checksOFF注意server_id每台机器必须不一样我用11、12、13来对应主机。loose-前缀表示即使MySQL实例没有加载组复制插件也不会因为参数不存在而启动失败。group_replication_group_name必须是合法的UUID格式我用命令uuidgen生成的。改完配置后重启MySQL然后登录执行INSTALL PLUGIN group_replication SONAME group_replication.so;如果插件安装报错先去plugin_dir检查有没有这个so文件。没有的话说明你下载的是Minimal版包里有但不全或者路径不对。这个算是环境准备阶段最容易翻车的地方。3.2 用MySQL Shell初始化集群InnoDB Cluster推荐用MySQL Shell操作8.2的shell版本跟着MySQL一起走。第一台机器上执行mysqlsh --uri root192.168.10.11:3306进入MySQL Shell交互界面后先配置实例dba.configureInstance(root192.168.10.11:3306);这条命令会检查实例是否满足组复制条件并创建mysql_innodb_cluster_metadata相关账号和元数据。它还会提示你创建root账号密码或者沿用已有账号。建议在MySQL里先建一个独立账号比如admin专门给MySQL Shell用不要顺手把root给Shell用。我踩过的坑用root配置后Router的bootstrap也拿着root去连万一密码泄露整个集群就裸奔了。配置完成后创建集群var cluster dba.createCluster(mycluster);创建的时候MySQL Shell会告诉你Cluster刚创建时只有主节点一个成员。接下来把另外两个节点加进来cluster.addInstance(root192.168.10.12:3306); cluster.addInstance(root192.168.10.13:3306);加实例的过程会同步初始数据如果主库数据量大这一步会很慢。我当时库有80G等了差不多40分钟。这期间千万别手动去重启MySQLMySQL Shell会在添加实例前自动设置复制账号中途断了会出现半初始化状态后面得cluster.rescan()重新扫描麻烦得很。完成后看集群状态cluster.status();看到三个实例的status都是ONLINEOK了。3.3 引导MySQL Router并开启读写分离集群Ready之后开始在应用服务器上装MySQL Router。建议Router单独部署不要跟数据库实例挤在一个机器上。我用的是mysql-router-8.2.0解压后放在/opt/mysql-router。引导命令长这样mysqlrouter --bootstrap root192.168.10.11:3306 \ --directory/opt/mysqlrouter \ --usermysqlrouter \ --read-write-splitting--read-write-splitting就是8.2新特性跟老版本最大的区别开启后Router会生成一个新的路由配置不再是简单地把6446指向主、6447指向从而是会在单端口上做读写自动分发。引导过程会问要不要注册为系统服务选n就行测试环境直接用前台进程跑。启动/opt/mysqlrouter/bin/mysqlrouter \ --config/opt/mysqlrouter/mysqlrouter.conf启动成功后Router默认监听6446端口。老版本里6446和6447的功能被合并成了一种新的“读写自动识别端口”具体监听参数看生成的mysqlrouter.conf里的[routing]段。这里我必须提醒一下不同8.2小版本和后续patch版本bootstrap参数名可能会有细微差异尤其是--read-write-splitting这个参数后续版本改过几次写法。执行前先看一眼mysqlrouter --help输出确认参数名是对的。若你的Router版本打印的是别的开关名以官方文档和--help为准原理是一样的。3.4 验证路由逻辑是否真的生效启动Router后第一步验证不是跑业务而是确认“读”“写”真的去了不同节点。在应用服务器上执行mysql -h127.0.0.1 -P6446 -uapp -p -e select hostname;连几次你会看到返回的hostname在node2和node3之间轮询但不会出现node1。因为默认情况下纯SELECT会走只读节点这是读写分离的基本表现。再执行mysql -h127.0.0.1 -P6446 -uapp -p -e select hostname; insert into test.t_log(ct) values(now()); select hostname;注意这里同一个连接里先查了一次hostname再做写操作再查一次hostname。第二次查询返回的hostname一定变成node1而且这个连接后续的所有操作都会固定走node1。这个现象后面解释原理时会细说。同时开两个不同的mysql客户端一个做连续查询一个做写入观察查询是否在从库轮询。如果从库地址始终不出现优先看Router日志多半是元数据里没有把节点标记为SECONDARY那就要回MySQL Shell里再执行一次cluster.rescan()刷新。4. 路由原理与事务边界的踩坑点4.1 Router是怎么识别读写的很多人误以为Router会像防火墙一样去解析SQL关键字看到“SELECT”就送从库看到“INSERT、UPDATE、DELETE”就送主库。真实实现没有这么简单粗暴。MySQL Router的读写分离核心依据是连接的事务状态。Router在协议层跟踪每个连接的事务上下文当autocommit1时单个SELECT被判定为只读请求可以路由到从库当客户端执行START TRANSACTION READ ONLY整个事务被标记为只读事务事务内的SELECT都可以去从库当客户端执行START TRANSACTION且没有READ ONLY则默认按读写事务处理后续全走主库如果连接已经执行过写语句那么这个连接在当前事务结束前全部路由到主库绝不允许再切回从库。这个设计逻辑是合理的如果允许一个事务内的读请求跑到从库而刚才的写还在主库从库数据可能还没同步过来读出来就是脏数据。所以Router宁可把所有后续请求都留在主库也不承担一致性风险。实际体验下来最需要注意的就是如果你在事务里先SELECT后UPDATE那这个SELECT大概率也走不了从库。因为业务代码一旦开启事务不带READ ONLY的话Router就按读写事务处理了从BEGIN开始这个连接就固定在主库上。这也是我反复跟开发团队强调的一点单条查询想要读从库就把查询放在autocommit1的连接上或者显式声明只读事务否则读写分离的效果会大打折扣。4.2 会被“误伤”路由到主库的SQL有一类SQL很容易让人踩坑明明写的“SELECT”但本质上带写语义或者依赖写节点状态Router不会让它走从库。我整理一个实际验证过的清单SQL形态Router判定原因说明普通SELECT从库autocommit1标准只读查询SELECT ... FOR UPDATE主库锁语义必须打到主库SELECT ... LOCK IN SHARE MODE主库同样涉及行锁SELECT hostname, port从库返回的是从库自己的变量验证时常用SELECT LAST_INSERT_ID()主库依赖主库会话状态SELECT GET_LOCK()主库命名锁是会话级状态存储过程体视情况而定若过程中有写操作整个流程会在主库执行临时表操作主库临时表是会话私有的不好复制到从库INSERT后同一事务内的SELECT主库事务已被判定为读写事务这里特别说一下存储过程。我们生产库里有不少历史遗留的存储过程它们内部是先查一批数据循环里再UPDATE。开启读写分离后如果业务代码直接CALL proc_xxx()Router会把整个过程路由到主库因为过程体里有写操作。开始我以为是Router能力不够后来想了想这不是缺陷而是保守且正确的策略——如果对过程内容做深度静态分析既复杂又容易误判不如直接默认“存储过程可能带副作用”统一走主库。4.3 业务侧配合的三种模式要让读写分离真正发挥威力光靠DBA调Router不够业务代码最好按下面的模式来写。第一种简单查询模式全自动。所有查询走连接池默认连接autocommit开着Router自动送到从库。适合列表页、详情页这种纯读接口。第二种强一致读模式读主库。某些查询要求在写入后立刻读到最新数据比如用户下单成功后马上跳转订单详情。这种场景给查询方法上加注解强制走一个专门指向主库的数据源。在Router读写分离模式下业务端最简单的方法是连接后先执行一次任意无害写操作把连接状态置为“已写过”。但别这么干太丑了。更好的是在JDBC URL上配一个参数让连接默认处于读写事务状态比如用connectionAttributes或者直接连Router的第二个端口。没错虽然8.2新增了单端口读写分离但Router生成的配置里通常还是会保留一个仅读端口和一个读写端口。强一致读就走读写端口普通读走自动端口这相当于把“人工指定”和“自动识别”结合使用。架构上稍微复杂但一致性最可控。第三种只读事务模式最推荐。对于那些需要在一个事务里执行多条SELECT的业务请务必用START TRANSACTION READ ONLY或者ORM里明确声明事务只读。这样Router能把整个事务继续留在从库而不至于中途提升到主库。我们在改造中把大约80%的只读查询都收敛到了第二种和第三种模式主库压力肉眼可见地降下来了。5. 上线后遇到的高频故障与处理5.1 SSL连接错误这个坑在测试阶段就炸了。我们Java应用连Router时设置过useSSLtrueverifyServerCertificatetrue结果报错信息是Communications link failure SSL connection error: unknown error number原因是Router在8.2默认启用了SSL但证书是自己本地生成的并没有自动下发到应用端的TrustStore。应用拿着自己的CA去校验Router的证书校验不过就直接断开。我当时让运维同学把Router生成的CA证书烤出来丢到应用的truststore里并在JDBC URL加上trustCertificateKeyStoreUrlfile:/path/truststore。这一套配置在测试环境没问题但到了生产环境又冒出来一个新问题——Router用的是什么编解码格式Java KeyStore默认要JKSMySQL生成的却是PEM格式。最后绕了一圈最简单的方式是应用侧把verifyServerCertificate关掉内网环境用SSL加密即可不做证书强校验?useSSLtrueverifyServerCertificatefalse这个做法在内网完全可以接受两个机房之间如果还是公网链路我建议加专线或隧道而不要靠篡改证书校验来硬扛。安全这块别图省事内网默认信任链路加密已经足够满足大多数合规要求了。5.2 连接池把连接搞“脏”读写分离上线一周后监控里发现从库的查询量并不稳定有时候明明压测流量很大从库却没多少QPS。排查半天问题出在Druid连接池的testOnBorrow和testWhileIdle配置上。Druid在归还连接时会执行一个验证SQL默认是SELECT 1。这个SELECT 1在Router看来就是一条标准只读查询会自动路由到从库。但问题在于一个之前执行过写操作的连接Router已经把它绑定到了主库会话上连接归还后下一个请求从连接池拿到同一个物理连接执行第一条查询时Router一看“这个连接有过写痕迹”后续所有查询都继续走主库等于这个连接“被写脏了”。就这样连接池里一部分连接被永久钉在主库上读写分离的效果被稀释了不少。解决办法有两个方向。一个是连接池层面把连接的autocommit重置、隔离级别重置都做好尽量让归还的连接回到干净的初识状态另一个是Router层面在配置里缩短“写状态记忆”的保持时间让连接在会话结束后立即重置路由状态。实际生产里我两个都做了效果最明显的是把Druid的removeAbandoned打开并确保testOnReturnfalse减少多余探测。HikariCP用户相对省心一些它默认行为是connectionInitSql只在创建连接时执行一次对路由状态干扰小。但也不是完全免疫如果你在connectionInitSql里写了SET autocommit1没问题千万别写START TRANSACTION这种语句否则所有连接一开始就被判定成读写事务全部跑到主库去。5.3 复制延迟、锁等待和升级问题从库几乎没有写入压力但SELECT压力大时也会出现锁等待而且表现很隐蔽从库的information_schema.innodb_trx长期挂着只读事务但这些事务占着旧版本数据导致purge线程清理不掉undo log最终ibdata1膨胀。这个问题在开启长时间事务的报表业务上特别明显。建议所有从库都打开innodb_buffer_pool_size足够大并且把长查询拆分成分页查询缩短事务持续时间。还有一次比较惊险的复制延迟是主库执行了一个大批量UPDATE生成近10G的binlog两个从库的Seconds_Behind_Source直接飙到几百秒。这种延迟不仅会让读写分离读到旧数据还会导致Router的元数据检查超时——因为从库在追赶复制时自身IO资源被占用Router定期健康检查返回变慢于是Router把该从库临时标记为不可用切走了流量。处理完之后我把并行复制参数调低了批量同时上线前跟业务确认了“大批量更新必须分批”这种全表UPDATE以后再也不允许一条SQL直接跑。安装升级方面也要唠叨一句。如果是从5.7往8.2升见过最典型的一个报错是[ERROR] [MY-014060] [SERVER] Invalid MySQL server upgrade:查资料会发现这是数据字典升级冲突的通用提示要么是之前用了非官方补丁包要么是从5.7直接跳版本升级时系统表结构比对失败。正确姿势是先升到8.0再用mysql_upgrade组件做校验最后才到8.2。别心存侥幸也别信网上说的“直接改版本号跳过升级检查”。5.4 关于Docker部署的提醒测试环境里我常直接用Docker起MySQL一条docker run搞定确实方便。但生产环境的InnoDB Cluster千万别用裸容器方式跑数据节点。原因有三个一是组复制的网络端口、IP地址在容器里很容易漂移MySQL Shell在元数据里记录的是创建集群时的地址容器重建后元数据对不上集群直接不认识你二是数据卷权限问题老遇到MySQL启动时对/var/lib/mysql没有写权限logs里就是Permission denied三是状态检查脚本、监控采集器连容器里的MySQL时经常因为网络模式不同而连不上排查成本远超收益。Docker跑Router倒是可以Router本身是无状态连接层跟容器调度挺兼容。但前提是端口映射要固定好别用随机映射不然应用配置的地址隔一次重启就变了。6. 实测数据与调优参数6.1 上线前后的对比整个项目从搭建到灰度上线用了两周灰度一周后看监控最直观的变化是主库各项指标回落了。我简单列一个对比数字都来自我们当时的监控不同业务规模参考趋势即可指标上线前全部走主库上线后读写分离主库CPU85% ~ 95%40% ~ 55%主库活跃连接峰值150峰值60从库CPU5%以下50% ~ 65%主库慢查询数/小时40030以内接口平均查询耗时40ms25ms注意一个反直觉的点业务接口平均查询耗时不一定会因为走从库而大幅下降因为从库同样要执行相同的SQL只是压力分担了。真正明显的改善是高峰期不再出现“雪崩式”的超时和连接拒绝。6.2 几个值得调的参数读写分离不仅取决于Router还取决于从库有没有能力扛住读流量。以下参数是我在三个节点上都调过的innodb_buffer_pool_size32G innodb_buffer_pool_instances8 innodb_flush_log_at_trx_commit1 sync_binlog1 replica_parallel_workers8 replica_parallel_typeLOGICAL_CLOCK binlog_transaction_dependency_trackingWRITESET主库的innodb_flush_log_at_trx_commit1和sync_binlog1不能为了性能改成0或2否则主库一旦宕机数据丢失风险高。从库可以把replica_parallel_workers调高配合binlog_transaction_dependency_trackingWRITESET大批量写入的复制延迟能显著下降。Router侧我建议关注两个参数一个是连接线程数max_total_connections默认值往往偏低并发一高应用会报“router: too many connections”另一个是bind_address一定别监听所有网卡用防火墙限制只在应用服务器网段内访问否则等于把所有数据库端口暴露在办公网里。6.3 弄清读写分离的边界它不解决写压力这一点必须反复强调读写分离把读流量从主库挪到了从库但主库的写能力一点都没提升。我们压测时模拟过极端场景把写请求从2400QPS提升到5000QPS主库CPU立刻又回到90%以上从库复制也开始吃紧。这说明如果瓶颈本身是写或者锁竞争加再多从库也没用。想要扩展写能力只能往三个方向走一是分库分表把不同业务域的写请求散到不同实例二是升级硬件把单机写能力往上顶三是换分布式数据库架构。对大部分中小团队来说读写分离只是架构演进的第一步别把它当成终局方案。还有一个经常被混在一起的场景把MySQL数据同步到ClickHouse或者TDengine这类分析型数据库做OLAP。很多同学以为开了读写分离后从库就能顺带当数据源供数仓抽数其实完全不是一回事。抽数同步通常会用到binlog的CDC工具比如Flink CDC、maxwell、debezium这套独立于Router路由体系你要单独搭一条同步链路。想过“借道从库”给BI跑大查询我也试过结果从库CPU瞬间被打满还是老老实实抽一份到ClickHouse里查更靠谱。7. 最后一轮调优后的个人体会项目上线到现在跑了两个月我最深的体会是MySQL 8.2的读写分离真正省心的地方不在于“自动”这两个字而在于它把官方组件、集群管理、连接路由这几件事打通了。以前搭ProxySQL路由器本身要配监控、配高可用、配读写组组复制那边还得单独维护MySQL Shell两条线并行出问题定位起来特别麻烦。现在innoDB Cluster加Router元数据是一致的切换主节点时Router自动感知这在运维层面省了非常大的工作量。如果让我给后来者一条最值得记住的建议那就是不要把读写分离的期望寄托在“完全智能”四个字上要花时间让业务侧理解事务边界。技术层面装个Router五分钟就能搞定真正决定读写分离效果上限的是业务代码里有没有正确使用只读事务以及DBA有没有把一致性要求高的查询提前分流到主库端口。另外升级到8.2之前建议先在测试环境完整做一轮“从库延迟一分钟”的演练看业务会出现多少读不到新数据的报错提前找产品商量好应对文案。这个动作听起来跟性能调优没关系但在我看来它比多调两个buffer pool参数重要得多。这套架构跑顺以后后续的扩展空间也比较明确如果哪天单库写量继续涨可以在现有InnoDB Cluster前面继续沿用Router做流量入口再把表按业务域拆到不同ClusterRouter的配置是支持多集群路由的。每一次演进都建立在已有的拓扑上不需要推翻重来这也是我选择官方方案最看重的长期价值。