“463-MySQL(mycat读写分离实践)”这个项目编号估计是很多人从某个课程或实验清单里扒下来的。但你别小看这几个字它背后其实是数据库高并发架构里最经典、最实用的一套组合拳MySQL主从复制 MyCat中间件 读写流量分离。如果你正在做企业级应用或者准备把系统从单库扛压升级到多库分担那这个实践基本是绕不开的一课。先说说我为什么推荐用MyCat做读写分离而不是直接用代码层面的多数据源比如Spring读写分离路由或者干脆上云数据库的只读实例。原因很简单MyCat对业务代码完全透明。你的后端应用只需要连一个MyCat地址SQL发过去之后由MyCat自己判断是发到主库执行还是从库执行。这意味着你不需要改一行业务代码就能实现读压力的横向卸载对于老项目改造来说是极其友好的。当然MySQL自带的主从复制是底层前提MyCat负责的是上层的流量分发策略。这套方案特别适合什么场景MySQL主库的读QPS已经打到3000以上慢查询开始增多但你暂时没有预算或人力去拆分微服务、引入庞大中间件体系的中小型团队。用一个MyCat中间件把几个只读从库利用起来投入小、见效快而且踩坑的痕迹已经非常多可靠度是有保障的。1. 整体设计读写分离架构的核心思路与选型分析1.1 为什么要做读写分离以及为什么选MyCat我见过太多团队一开始单库扛得住后来业务一涨第一反应是升级MySQL配置、加缓存Redis。但缓存只能挡热点读对于一些定期统计、复杂关联查询、报表类的读请求缓存根本没有办法全覆盖。这个时候读压力依然会直击数据库主库的CPU和IO就会飙起来。有人说直接上分库分表不就行了话没错但分库分表是从根上改变数据访问方式代价极大。绝大多数业务70%以上都是读操作真正需要分库分表的临界点远比你想象得高。你完全可以在分库分表之前先把读流量从主库摘掉让主库专心写从库承担读——这就是读写分离的价值。而选择MyCat而不是自己在应用层做路由最大的理由是透明性。MyCat作为一个数据库中间件伪装成MySQL服务器接在你的业务和应用之间。应用连接的是MyCat的端口MyCat再通过配置好的数据源把请求转发给真实的MySQL主从实例。整个过程对业务来说是黑盒不需要改动SQL或者代码逻辑。这一点对于存量项目极为重要尤其是那种几十张表、上千行SQL的历史代码代码里到处是JdbcTemplate或者Hibernate的Mapper你不可能去给每个查询加路由注解。1.2 主从复制 MyCat的基本链路你要理解读写分离的效果先要在大脑里画一条完整的数据链路业务应用发起SQL请求连接的是MyCat的逻辑库。MyCat解析SQL类型如果是insert、update、delete这类写操作就走主库如果是select、show这类读操作就走从库。主库写入后通过MySQL二进制日志binlog把变更同步给一个或多个从库从库应用日志完成数据更新。应用下一次读请求过来时MyCat把它分发到从库从库返回刚写入的数据。这条链路里MySQL主从复制是“数据最终一致性”的保障MyCat是“流量分发策略”的载体。两者缺一不可。这里有一个关键知识点读写分离不是严格实时的强一致。因为二进制日志的同步存在延迟哪怕你主从在同一个机房、千兆网络延迟通常在毫秒到几十毫秒级别。如果你做的是金融、库存扣减这类强一致业务读写分离后要特别注意比如通过“主库直连通道”或者把关键链路强制路由到主库。1.3 单从和多从架构如何权衡我在模拟项目X里实践时最开始搭的是一主一从后来因为某个报表业务查得太凶又扩了一台从库。扩从库的过程非常简单在MyCat的schema.xml里加一个writeHost或者readHost节点重启或热加载配置就生效了。单从和多从的区别不仅仅是并发能力的提升更重要的是可用性。如果你只有一个从库它挂了MyCat会把读流量全部甩回主库主库压力瞬间恢复容易发生雪崩。如果你有两个以上从库一个从库挂掉MyCat还能把读流量均分到剩余的从库上影响面要小很多。当然从库也不是越多越好。从库越多主库的binlog分发压力和网络开销就越大而且每个从库都要占用磁盘和内存。我的经验是读多写少的业务两到三个从库是性价比最高的区间如果单从库的读QPS仍在两三千以内其实没必要急着扩容。2. 环境搭建与核心配置拆解2.1 MySQL主从复制的搭建步骤MyCat本身不承担数据同步所以第一步永远是先把MySQL主从复制跑通。这一步网上教程很多但有几个细节容易踩坑。主库和从库的my.cnf需要做如下配置主库配置示例[mysqld] server-id 1 log-bin mysql-bin binlog-format ROW binlog-do-db db_user从库配置示例[mysqld] server-id 2 relay-log relay-bin read_only 1注意几个点server-id必须全局唯一主从不能一样binlog-do-db如果设置了代表只同步指定的库这样做的好处是减少网络传输坏处是后续如果新增业务库会漏同步read_only 1在从库上强制开启防止有人手滑在从库写入数据导致复制线程报错。在从库上执行复制命令CHANGE MASTER TO MASTER_HOST192.168.1.101, MASTER_USERrepl, MASTER_PASSWORDRepl123, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS154; START SLAVE; SHOW SLAVE STATUS\G;这里最关键的是MASTER_LOG_FILE和MASTER_LOG_POS。我见过无数人在这两步配错原因是他们在主库执行FLUSH TABLES WITH READ LOCK后没有记录SHOW MASTER STATUS的输出结果binlog文件名和位置搞错了复制始终起不来。正确做法是锁定主库写操作后立刻执行SHOW MASTER STATUS把这个输出记下来再去解锁。2.2 MyCat的下载与基本目录结构MyCat目前社区里最常用的版本是1.6系列。虽然官方有一些2.x版本但生产环境中1.6依然是绝对主流因为它的文档多、踩坑案例多、社区讨论充分。我个人的建议是如果没有特殊需求先不要盲目追新1.6的稳定度和资料丰富度都更适合做生产实践。解压后的目录结构大概是这样的mycat/ ├── bin/ # 启动脚本 ├── conf/ # 核心配置文件 │ ├── schema.xml # 逻辑库、数据源配置 │ ├── server.xml # 用户权限、系统参数 │ └── rule.xml # 分片规则读写分离可以不重点改 ├── logs/ # 运行日志 └── lib/ # 依赖jar包2.3 server.xmlMyCat的逻辑用户配置在配置任何数据源之前先搞清楚server.xml里user标签的含义。MyCat的用户和MySQL用户不是一回事它只是MyCat自己认识的一个登录账号你可以随便起名。但这个用户最终会映射到真实的MySQL用户。我常用的配置是这样的user namemycat_user property namepasswordMyCat123/property property nameschemaslogic_db/property property namereadOnlyfalse/property /user注意readOnly这个属性很关键。如果你把它设为true那么通过这个用户连接MyCat后任何写操作都会被拒绝这在一些需要严格隔离读写权限的场景下很有用。但在普通的读写分离实践里我们通常要用同一个用户连接MyCat让MyCat在底层自动分流所以readOnly保持false即可。2.4 schema.xml读写分离的核心战场schema.xml是整个MyCat配置里最核心、最容易出错的一个文件。在配置读写分离时你需要在这一层定义逻辑库、逻辑表、真实数据源之间的对应关系。一个最小化的读写分离配置如下mycat:schema xmlns:mycathttp://io.mycat/ schema namelogic_db checkSQLschematrue sqlMaxLimit100 table nameuser primaryKeyid dataNodedn1 / /schema dataNode namedn1 dataHosthost1 databasedb_user / dataHost namehost1 maxCon50 minCon10 balance1 writeType0 dbTypemysql dbDrivernative heartbeatselect user()/heartbeat writeHost hosthostM1 url192.168.1.101:3306 userroot passwordRoot123 readHost hosthostS1 url192.168.1.102:3306 userroot passwordRoot123 weight1 / /writeHost /dataHost /mycat:schema我逐个关健属性解释一下为什么这样配。balance是整个读写分离的开关它决定了读流量是否走从库。在MyCat 1.6里balance1表示所有读操作都交给从库处理主库只负责写操作。注意这里说的是“所有读操作”包括select、show、explain这些都会走从库。如果你希望部分读操作强制走主库MyCat可以用注解/*!mycat:db_typemaster*/来指定这个功能在实际业务里非常实用后面我会专门讲。writeType0是MyCat的写模式0表示所有写操作都发给第一个writeHost如果配置了多个writeHost也只会发给第一个除非它挂了才会切换到第二个。这在读写分离场景下是合理的因为MySQL主从架构里同一时刻只有一个主库。heartbeat是MyCat对后端MySQL实例发起的心跳探测语句。它必须是一个在后端MySQL上能正常执行的简单SQL。这里是select user()表示MyCat会周期性检查后端库是否存活。如果你把这个语句写成一个复杂查询一旦查询失败MyCat会误判节点不可用导致读写分离直接失效。等一等这里有一个必须特别强调的坑readHost必须放在writeHost标签内部这是MyCat的语法规定。在MyCat的语义里readHost是挂在某个writeHost下面的附属节点。如果你把读库写在writeHost外部或者用另一种并列的dataHost方式配置加载时不会报错但读写分离不会生效——因为MyCat根本不认为那两个dataHost之间有任何主从关系。我刚开始实践的时候就在这里犯过傻配置看着没问题但select始终打到主库后来查日志才发现readHost的位置放错了。2.5 重量级补充多从库权重配置如果你有两台从库希望它们按比例承接读流量例如性能强的那台承担70%读请求弱的那台承担30%可以在readHost上设置weight属性readHost hosthostS1 url192.168.1.102:3306 userroot passwordRoot123 weight7 / readHost hosthostS2 url192.168.1.103:3306 userroot passwordRoot123 weight3 /这个权重是MyCat在选择读节点时的一个重要依据。实际压测中它确实能够把读请求按约7:3的比例打过去。但要注意MyCat的负载均衡不是精确到每一百个请求就精确70/30而是通过加权随机算法去分配样本量足够大的时候比例会比较接近。如果你追求绝对精确的轮流分发那得换其他中间件了。3. 实践操作从零开始搭建MyCat读写分离环境3.1 测试环境清单我这里用三台阿里云ECS类似的机器来演示用完全虚拟的IP代替角色IP软件MySQL主库192.168.1.101MySQL 5.7MySQL从库1192.168.1.102MySQL 5.7MySQL从库2192.168.1.103MySQL 5.7MyCat中间件192.168.1.110MyCat 1.6.7操作系统都是LinuxCentOS 7风格MySQL统一用5.7。因为MyCat对MySQL 8.0的兼容性在1.6版本中存在一些问题尤其在认证方式上caching_sha2_password默认加密方式会导致连接失败。如果你只有MySQL 8.0环境需要在my.cnf里设置default_authentication_pluginmysql_native_password才可能连上。3.2 step by stepMyCat安装与配置在MyCat机器上先解压安装包tar -zxvf Mycat-server-1.6.7.1-release-20190627104126-linux.tar.gz mv mycat /usr/local/mycat然后修改conf/server.xml把默认的user替换为上面提到的配置同时注意把privileges里的DML权限控制注释掉否则某些操作可能被MyCat自己的权限检查拦截。接下来是schema.xml按照上面的配置写入。这里有一个细节如果你的应用不需要走分片只是纯读写分离那么schema.xml里甚至可以不配置table节点直接让逻辑库透传到后端的物理库这样MyCat就只做流量转发不需要处理分片逻辑。具体做法是schema namelogic_db checkSQLschematrue sqlMaxLimit100 /schema注意这里和分库分表场景的区别。如果配置了table节点那么MyCat必须通过rule.xml里的分片规则去路由这个表否则会报错。读写分离场景下没有分片需求所以schema里留空反而是最稳的做法。此时MyCat会把SQL原样转发到对应的dataNode然后dataNode里的balance规则决定最终走主还是走从。3.3 启动MyCat与验证读写分离是否生效配置完成后启动MyCatcd /usr/local/mycat/bin ./mycat start查看启动状态./mycat status首次启动如果报错不要猜直接去看日志tail -f /usr/local/mycat/logs/wrapper.log然后用MySQL命令行客户端连接MyCatmysql -umycat_user -pMyCat123 -h192.168.1.110 -P8066登录成功后依次做两个验证第一个验证随便执行一条写操作比如INSERT INTO user (id, name) VALUES (1, test);然后立刻去主库和从库分别查一下这条数据是否存在。主库肯定有从库如果复制正常也会在几毫秒后出现。第二个验证执行读操作SELECT * FROM user;这一步你很难直接通过客户端看出来实际打到了哪个库。我常用的方法是在MySQL从库上开启通用日志general_log或者在从库执行SHOW PROCESSLIST看是否有来自MyCat机器IP的连接正在执行查询。更简单直接的方法是在MyCat的日志里观察。打开logs/mycat.logMyCat会在每次SQL路由时打印类似这样的信息route sql select * from user route result dn1但日志级别默认可能不会打印出路由目标节点需要调整conf/log4j2.xml里的日志级别。我在实践中更喜欢用从库的general_log来做验证因为MyCat的日志在高压下输出量太大反而干扰判断。在从库执行SET GLOBAL general_log ON; SET GLOBAL general_log_file /tmp/mysql_general.log;然后去刷几个查询再来看从库的general日志文件里是不是多了一堆来自MyCat机器IP的查询语句。如果是说明读流量确实被分发到了从库读写分离已经生效。3.4 设置强制走主库的SQL注解在业务场景里有一种情况很膈应用户注册后立即登录此时注册数据刚写入主库binlog还没同步到从库用户的登录查询很可能查不到自己刚注册的信息。这就是读写分离典型的“刚写就读”一致性问题。MyCat提供了一个注解语法可以强制某条SQL走主库/*!mycat:db_typemaster*/ SELECT * FROM user WHERE id 1;把db_typemaster写在SQL的前缀位置MyCat解析到这条注解后就会忽略balance配置直接把这条请求路由到主库。这个功能在订单、支付、登录等关键链路上几乎是必须的否则你会遇到很多“薛定谔的注册成功”有时候刷新一下就登录成功有时候怎么都登不进去坑得非常难受。如果你的业务框架是MyBatis或JPA想对某个Mapper方法强制走主库可以把注解写在方法上的Select注解里。如果你用的是MyBatis-Plus还可以在方法上实现一个自定义的拦截器判断操作类型后自动加上这个注解但那样会额外增加代码复杂度。我的建议是只在真正需要强一致的几个接口上手动添加别全局铺开。3.5 验证读负载均衡的效果用sysbench模拟压力是验证读写分离最直观的方式。我在测试时用了一个非常简单的做法在MyCat机器上写了一个脚本循环执行10000次SELECT SLEEP(0.01);。然后在主库和两台从库上分别查看当前连接数和QPS。我实测的数据大致是主库QPS从原来的2000多降到了200多主要是一些写操作和强制走主库的读操作。两个从库QPS各800多加起来覆盖了之前主库的大部分读负载。主库CPU使用率从85%降到35%。慢查询数量显著下降尤其是一些大表的报表查询因为分流之后单库并发降低锁竞争减少单条SQL的执行时间也稳定下来。这说明读写分离不仅仅是“把请求均分”还能通过降低单实例的并发压力间接优化那些此前因为高并发被打爆的慢查询。4. 常见问题与排查技巧实录4.1 读写分离配置了但读流量没有走从库如果一切配置看起来都对但读流量始终打在主库优先按以下顺序排查第一确认balance的值。balance1才是开启读写分离的标准值。如果你是从某些老教程里复制来的配置可能会看到balance0这种情况不会启用读写分离。第二确认readHost的位置。它必须被嵌套在writeHost节点里面。如果放错层级MyCat能正常启动但不会把读请求路由到从库。第三确认从库的访问账号能从MyCat机器连过去。很多时候不是MyCat配置错了而是MySQL从库上创建的用户权限不允许远程登录。在从库执行GRANT ALL ON *.* TO root192.168.1.% IDENTIFIED BY Root123;然后手动用mysql客户端从MyCat机器连接从库IP如果能连上才排除了网络和权限问题。第四确认心跳是否成功。如果MyCat对从库的心跳语句失败它会认为从库“挂掉”从而把所有流量都切回主库。查看logs/mycat.log搜heartbeat关键字看是否有心跳超时的记录。4.2 主从复制出现延迟导致读取不到最新数据主从延迟是读写分离绕不开的坎。我的处理思路有三个第一在关键强一致链路上使用/*!mycat:db_typemaster*/强制走主库这是最快速止损的方案。第二优化主从复制的同步效率。检查从库的relay log是否有积压适当调大从库的slave_parallel_workers参数。在MySQL 5.7里可以通过配置slave_parallel_workers 4 slave_parallel_type LOGICAL_CLOCK来启用并行复制通常能把同步延迟从几百毫秒压到几十毫秒以内。但要注意LOGICAL_CLOCK并行复制依赖主库的binlog格式为ROW这个前面已经配置过了。第三从架构层面规避。如果业务对数据实时性要求极高根本接受不了任何延迟那这些业务就不应该走读写分离的从库而是单独使用主库或者引入缓存。这不是MyCat的锅是架构边界没划清楚。4.3 连接数打满或者连接池报错MyCat自身也有连接数上限。dataHost里的maxCon和minCon控制的是MyCat与后端MySQL之间的连接池。maxCon50代表每个dataHost最多保持50个连接到后端的MySQL实例。如果你的业务并发突然上来后端连接数满会导致MyCat报NoHostAvailableException。这时候有两个选择调大maxCon或者优化代码里的数据库连接池配置。我遇到过最奇葩的情况是应用端的数据库连接池最大连接数设置成200而MyCat到后端的maxCon只有50导致连接被MyCat卡死应用端报超时。调大maxCon到200后问题立刻消失。4.4 配置修改后不生效很多人都知道MyCat可以热加载配置用mycat管理命令执行reload即可./mycat console然后在管理端执行reload config_all;这个命令可以重新加载所有配置文件。但要注意热加载不是万能的有些结构变更比如修改了schema里的dataNode映射关系可能要求重启MyCat才能完全生效。我的建议是在测试环境先热加载一次然后用查询验证如果发现配置没生效就直接重启MyCat。重启虽然粗暴但能保证配置加载是干净的。生产环境切配置前一定先备份原文件并选择业务低峰期操作。4.5 从库磁盘被binlog写满这是一个很容易被忽略的运维问题。从库开启了log_slave_updates后它会把从主库同步过来的变更也记录到自己的binlog里时间一长磁盘很容易被撑满。这在从库只是作为只读副本时非常浪费。如果你确定从库不会被二次级联复制可以在从库的my.cnf中关闭log_slave_updates或者定期执行PURGE BINARY LOGS清理。具体清理命令PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;把清理周期和备份策略对齐别把历史日志全删了否则出问题后无法追溯。5. 我的实战经验与进阶建议真正的生产环境远比上面这套最小化实践复杂。我个人的经验是MyCat读写分离项目能跑通只是第一步后续有三个方向非常值得深入第一个方向把读写分离和分库分表结合起来。很多系统单表数据量过了千万级之后即使读分流出去了单个从库的查询压力仍然大。这时候你可以利用MyCat的rule.xml把同一个逻辑表切到多个分片每个分片再挂自己的主从复制。架构上会变成“每个分片一主一从”的形态复杂度比纯读写分离高一个量级但这也是MyCat框架最强大的地方。第二个方向接入MySQL半同步复制提升一致性。如果你对一致性要求高到不愿接受异步复制带来的延迟可以在主从之间开启半同步复制。MySQL 5.7里安装semisync_master.so和semisync_slave.so插件配置半同步复制参数。这样做的代价是主库提交需要等待从库ACK写延迟会稍微上升但换取的是从库数据基本实时一致。第三个方向把MyCat接入一些监控体系。比如通过自定义脚本定期采集MyCat状态页面我在一些社区看到过相关实现本质都是通过MyCat的管理端口获取心跳、连接数等信息接入群里告警。这样当某个从库心跳失败时你能第一时间感知而不是等到用户反馈“数据怎么一直不变”才发现问题。最后再分享一个小技巧。在用MyCat做读写分离的时候不要把balance属性一刀切。某些场景下你希望读流量也分一部分给主库避免从库被压死那可以设置balance2它表示读流量在主库和从库之间随机分发。生产环境如果从库性能明显弱于主库这个配置会让整体吞吐更均衡。读写分离不是终点它是你从单库迈向分布式数据库架构的第一块跳板。只要把主从复制和MyCat这套逻辑吃透后面再接触分库分表、数据迁移、多活容灾你会发现自己已经有了非常扎实的底层认知。实践出真知多搭几套环境多踩几个坑这些东西就真正变成你自己的了。