MySQL双主双从搭配MyCat：高可用读写分离架构落地全解析

做后端开发的这几年数据库永远是最绕不开的那座山。刚开始业务量小单库单表随便跑等QPS一上来连接数暴涨、慢查询堆积、磁盘IO拉满你会发现大部分“应用层性能问题”最终都会绕回数据库层面去解决。最常见的优化路径就是做主从复制读写分离。但真等你要上生产环境的时候一主一从又不够用了主库挂了怎么办写流量还是单点切换靠不靠得住这时候MyCat配合双主双从架构是一个非常经典、也非常成熟的企业级解法。这篇文章不会跟你扯太多理论重点是把“双主双从高可用读写分离”这套架构从设计到落地讲清楚为什么这么搭、数据库层怎么配、MyCat的配置到底该怎么写、故障切换怎么验证、以及我在实际部署中踩过的那些坑。适合正在做系统架构升级、或者准备引入MyCat但还没动手搞过双主双从的开发者、DBA和架构师参考。我以一套典型的“双主双从MyCat”生产部署为蓝本把整个落地过程拆开来讲。内容偏向实操所有配置都是可复制的但每台机器的实际情况不同参数需要根据你的环境微调不要盲目抄作业。1. 双主双从与读写分离到底解决什么问题1.1 从单库到主从再到双主双从的演进路径很多人对数据库高可用的理解停留在“做个主从复制主挂了手动切一下”。坦白讲一主一从在业务低峰期是够用的但一旦你的系统要跑7x24小时哪怕凌晨三点挂了你也得爬起来处理这时候手动切换的代价非常高。先说说为什么从一主一从走向双主双从。单主单从的架构里主库承担所有写流量从库承担读流量。写流量一旦成为瓶颈你再怎么加从库都没用因为从库只能分担读。而且主库宕机后从库虽然可以用但需要你手动提升从库为新的主库这个操作如果业务还在持续写入很容易出现数据丢失或冲突。一主多从比一主一从好在读扩展能力更强但写单点问题依旧存在。双主双从的思路则是两个主库互为主备两个主库各自的下面再挂一个从库。这样任何一个主库出问题另外一个主库可以立刻顶上同时读流量也可以分散到四个节点上。拿一个生活化的场景类比一下单主是只有一台发电机所有电器都靠它供电一主多从是发电机外加几个UPS停电时UPS能顶一会但最终还是靠发电机双主双从则是两台发电机互为备援每台还配了独立的配电线路谁停机都不会影响整个厂房运转。1.2 双主双从架构的适用场景与不适用场景这套架构不是银弹选型前要想清楚。它最典型的应用场景是核心交易类业务、订单库、用户库这类既强调写可用性、又有较大读压力、同时无法容忍长时间宕机的系统。我参考一个典型的“某跨平台订单系统”的场景来说明。这套系统的数据库负载特点是白天高峰期查询量巨大订单写入集中在整点附近且业务要求数据库故障后5分钟内必须恢复服务。对于这种场景双主双从的高可用能力是刚好匹配的。但如果你只是一个小型内部管理系统日请求量不到几万双主双从纯属资源浪费。四台数据库节点的运维成本、复制延迟的排查难度、MyCat本身引入的中间层复杂度都会让一个原本简单的问题变复杂。早期阶段老老实实做单库定期备份比什么都强。还有一种不适用的情况是强一致性要求极高的金融级场景。主从复制本质上是异步的哪怕半同步复制也存在极小概率的延迟双主更是有脑裂风险。如果业务在任何一个时刻都不能接受数据不一致那么架构上应该考虑集群方案而不是主从复制方案。1.3 为什么选MyCat而不是其他方案数据库中间层方案并不少MyCat是目前国内用得最多、资料最全的MySQL中间件之一核心能力是分库分表和读写分离。我选它主要基于三个方面的考虑。第一它对读写分离的支持非常成熟。MyCat通过dataHost下的writeHost和readHost配置把写库和读库分开管理配合心跳检测和主从切换开关能实现故障后的自动转移。这套逻辑清晰易懂配置改动不用重启应用。第二它对业务侵入很小。应用连接的是MyCatSQL语句和业务代码不必改动。相比需要在应用层自己路由读写的方案MyCat把访问数据库的策略收拢到了中间层后续调整架构时不用每个服务都改一遍。第三它也具备多租户和分片能力。虽然双主双从架构的核心是读写分离但后续如果业务量继续上涨需要分库分表MyCat可以直接承担这一层不需要再引入新组件。当然它也有短板。MyCat本身不是数据库只做路由和转发所以复杂SQL的支持能力有限比如跨库join、跨节点事务都需要设计上规避。引入它等于在数据库前面加了一个有状态的转发节点部署上要考虑它自身的高可用。不过在企业级实战里这些代价是可以接受的关键是要在架构设计时把边界划清楚。2. 整体架构设计与数据流转全景2.1 拓扑结构与节点职责双主双从的拓扑看起来不复杂但每个节点的职责必须提前定义清楚。我采用的拓扑方案是四节点外加一个MyCat中间层节点。节点划分如下节点角色职责DB-Master-A主库A接收MyCat写入请求与主库B互为主备复制到从库ADB-Master-B主库B与主库A互为主备平时也可承担部分读请求作为写切换的目标库DB-Slave-A从库A挂在主库A下承担读请求DB-Slave-B从库B挂在主库B下承担读请求MyCat中间层统一入口按读写分离规则路由请求这里有一个很容易被忽略的点主库B在正常情况下不是纯闲置的。虽然写请求默认都落在主库A上但主库B可以承接一部分读请求。MyCat在读库选择上可以配置nodes让读流量在多个readHost甚至writeHost之间做负载均衡这样可以充分利用四台机器的性能。双主互备的关系是核心主库A和主库B互相把对方当作自己的主库两边都开启binlog任何一边的数据变更都会同步到另一边。这样当主库A故障时主库B的数据库已经是完整的数据副本MyCat只需把写请求切换过去即可完成高可用切换。2.2 读写请求的路由逻辑这套架构下MyCat路由逻辑的核心是区分writeHost和readHost。MyCat在解析客户端SQL之后通过语句类型判断是读还是写再决定往后端哪个MySQL节点转发。默认情况下写请求会集中发往第一个writeHost这是writeType0的行为。如果我们把主库A配为第一个writeHost主库B配为第二个readHost那么写操作只会走到主库A。读请求则会按照balance策略分发到可用的读节点。balance参数决定读负载的均衡方式。balance0时不开启读写分离所有请求都发往writeHostbalance1时读请求在readHost和备用的writeHost之间分摊balance2时所有读请求都随机分发到readHostbalance3时读请求只发往readHostwriteHost不承担读流量。在双主双从架构里我一般推荐balance1。这样可以保证两个主库和两个从库一共四个节点都参与读流量的分担写流量仍然只走主库A。有一点需要提醒主库B作为备写库本来就要时刻保持和主库A的数据同步它承担读请求不会有额外的同步压力还能避免读流量全部压在两个从库上造成的资源浪费。2.3 故障切换流程高可用架构的重头戏在故障切换。MyCat的自动切换机制依赖心跳检测它会定期向后端MySQL发送心跳语句默认是select user()如果连续检测失败就会触发主从切换逻辑。switchType参数就是这个机制的关键。switchType1时主库A心跳失败后MyCat会自动把主库B提升为writeHost后续写请求自动转向主库B。这个切换对应用是透明的因为应用始终连的是MyCatSQL发过去之后由MyCat决定最终落到哪台机器。但要注意MyCat层面的切换只是把“写流量入口”从主库A换到了主库B。如果主库A是真的宕机了主库B和主库A之间的主主复制链路会断开。恢复时必须把主库A重新拉起来补上宕机期间的binlog差距重新建立双向复制关系整个系统才算真正回到健康状态。如果只是网络抖动导致的心跳超时MyCat也可能发生误切换。这是我在实际中踩过的一个非常棘手的坑后面第5章会详细展开讲。2.4 双主模式的ID冲突问题必须先想清楚双主架构最容易忽略的一个问题自动递增ID冲突。两个主库都在接收写入如果两个库的auto_increment配置相同那两边各自生成的ID就会出现重复。比如主库A刚插入一条id100的记录主库B同时也插入了id100的记录双向复制一同步两边都会出现主键冲突复制链路直接中断。解决这个问题的方法是配置自增ID的偏移量和步长。标准做法是主库A配置auto_increment_offset1、auto_increment_increment2主库B配置auto_increment_offset2、auto_increment_increment2。这样主库A只会生成奇数ID主库B只会生成偶数ID两边永远不冲突。如果表的主键不是数据库自增而是由应用生成则不需要担心这个问题。但大多数业务表还是依赖自增主键所以双主模式下这个配置项几乎必配。这个细节务必在初始化的时候就配好否则等两边数据量都大了再调整自增参数会非常麻烦。3. 数据库层搭建双主双从到底怎么配3.1 环境规划与基础准备数据库层的搭建是整个架构的地基这一步如果没做对MyCat配置得再漂亮也是白搭。先规划环境以我部署的一组节点为例主机标识角色IP规划示例node-a主库A10.10.10.11node-b主库B10.10.10.12node-c从库A10.10.10.13node-d从库B10.10.10.14node-mMyCat10.10.10.15所有节点统一安装相同版本的操作系统和MySQL版本差异太大会导致复制行为不可预期。系统初始化时关闭防火墙对MySQL端口的拦截或者放行3306端口同时确保所有节点之间可以通过内网IP互相访问。还有一个容易被忽略的点所有节点的MySQL表结构和初始数据必须一致。搭建双主复制前我会先把主库A的全量数据导出一份在另外三台机器上初始化导入保证基线一致。从零开始不导入数据直接搭建也可以但生产环境切换时就可能出现某张表在主库B上不存在的问题。3.2 双主互为主备的核心参数MySQL配置层面双主和普通主从最大的不同在于两个主库都要开启binlog并且server-id不能重复。数据库层的配置文件使用/etc/my.cnf不同系统路径有差异核心配置如下。主库A的配置[mysqld] server-id 11 log-bin mysql-bin binlog_format ROW expire_logs_days 7 auto_increment_offset 1 auto_increment_increment 2 skip-name-resolve 1 max_connections 1000主库B的配置[mysqld] server-id 12 log-bin mysql-bin binlog_format ROW expire_logs_days 7 auto_increment_offset 2 auto_increment_increment 2 skip-name-resolve 1 max_connections 1000binlog_format用ROW模式的好处是复制时记录的是行变更前后镜像语义更精确即使SQL中包含不确定函数也能正确复制。expire_logs_days建议设置避免binlog无限膨胀把磁盘撑爆。两个从库的配置相对简单server-id分别设置为21和22主从复制不要求从库开启binlog但如果后续要从从库再做级联复制则建议同样开启并配置log_slave_updates1。我可以负责任地讲生产环境里我见过的从库复制中断有一大半都和binlog相关参数没配全有关不要在这上面省。3.3 从库复制链路配置主从链路的搭建依赖MySQL的用户授权和CHANGE MASTER TO语句。首先要创建一个专门用于复制的用户这是关键步骤生产环境不要用root做复制在双主模式下需要分别在主库A和主库B上执行授权。主库A上执行CREATE USER repl% IDENTIFIED BY Repl_123456; GRANT REPLICATION SLAVE ON *.* TO repl%;主库B上同样执行一遍。然后在双主之间建立互备关系。以主库A为视角在node-a上执行CHANGE MASTER TO MASTER_HOST10.10.10.12, MASTER_USERrepl, MASTER_PASSWORDRepl_123456, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS154;这条命令的含义是让主库A作为主库B的“从库”接收来自主库B的binlog。对应的还要在主库B上执行指向主库A的CHANGE MASTER TO。这里的MASTER_LOG_FILE和MASTER_LOG_POS不是固定值是你准备建立复制前在源库上通过SHOW MASTER STATUS查询出来的当前binlog坐标。这个坐标非常关键。如果填错了位点复制链路建立后会出现“找不到日志”或“日志位置无效”的错误。稳妥的做法是在主库A上先FLUSH TABLES WITH READ LOCK锁定写入然后SHOW MASTER STATUS记录坐标再导出数据。数据导入完成后再以该坐标启动复制。不过对于还未接入我的业务的新环境直接拿当前坐标即可。两个从库的配置同理从库A执行指向主库A的CHANGE MASTER TO从库B执行指向主库B的CHANGE MASTER TO。全部配置完成后分别在四个节点上执行START SLAVE开启复制线程。3.4 验证复制状态与常见初始问题复制是否建立成功不能只看进程起来没有要用SHOW SLAVE STATUS检查核心字段。这里我建议把所有节点的复制状态都检查一遍而不是只看一两个节点。SHOW SLAVE STATUS\G;重点关注两个字段Slave_IO_Running显示是否成功连接上主库并读取binlog。Slave_SQL_Running显示SQL线程是否正常执行复制事件。两个字段都必须为Yes。如果IO线程是No大概率是网络不通、用户密码不对、或MASTER_LOG_FILE/POS填错如果SQL线程是No则通常是表结构冲突、主键冲突或复制语句执行失败。还有一个容易忽略的延迟字段Seconds_Behind_Master。刚搭建完时如果主库上有大量积压的binlog这个数值会比较大需要等待它逐步追平。如果这个值长期不为0且持续增长说明从库的CPU、磁盘IO或网络带宽已经跟不上了在读写分离架构里这是致命的——从库越落后读请求读到旧数据的概率越大。我调试时习惯于做一个小验证在主库A创建一张测试表并插入数据然后分别在主库B和两个从库上查询这张表能查到就说明双向复制和单向复制都是通的。检查完毕后删掉测试表保证环境干净。4. MyCat核心配置让读写自动分流4.1 server.xml 的用户与逻辑库配置数据库层准备好之后接下来就是让MyCat承担起“交通警察”的角色。MyCat的配置集中在conf目录下核心是server.xml、schema.xml、rule.xml三个文件。server.xml负责定义MyCat自身作为MySQL代理对外暴露的用户和逻辑库。给出一份基础配置user nameappuser defaultAccounttrue property namepasswordApp_123456/property property nameschemasorderdb/property property namereadOnlyfalse/property /user把appuser当作业务应用连接MyCat使用的账号。这里的schemas指的是逻辑库名实际对应的后端物理库映射关系在schema.xml中定义。还要配置MyCat自身的系统端口和管理端口。默认情况下MyCat对外服务端口是8066管理端口是9066。生产环境建议修改默认端口减少被扫描的风险。同时可以在server.xml里设置maxLimit控制单次查询返回的最大行数防止一条不带LIMIT的SQL把后端数据库拖垮。有一说一很多新手在这里会把readOnly理解为“MyCat只允许读”。其实对于这种业务账号如果readOnly设置为true那么走MyCat的写语句会被拦截。读写分离账号一般设为false真正的只读账号可以单独建一个。4.2 schema.xml 中 dataHost 的 balance 与 switchType 选择接下来是核心中的核心schema.xml。它定义了逻辑库与真实MySQL节点之间的映射关系。先给出一份双主双从场景下的关键配置schema nameorderdb checkSQLschemafalse sqlMaxLimit100 table namet_order primaryKeyid dataNodedn_order / /schema dataNode namedn_order dataHostdtHost databaseorder_db / dataHost namedtHost maxCon100 minCon10 balance1 writeType0 dbTypemysql dbDriverjdbc switchType1 slaveThreshold100 heartbeatselect user()/heartbeat writeHost hostmasterA url10.10.10.11:3306 userappuser passwordApp_123456 readHost hostslaveA url10.10.10.13:3306 userappuser passwordApp_123456 / /writeHost writeHost hostmasterB url10.10.10.12:3306 userappuser passwordApp_123456 readHost hostslaveB url10.10.10.14:3306 userappuser passwordApp_123456 / /writeHost /dataHost逐个参数拆开讲。balance1表示开启读写分离读请求在writeHost和readHost之间做负载均衡。这里双主双从的特殊之处在于第二个writeHostmasterB也会被纳入读流量的分发范围。这是刻意为之目的是让主库B这台机器不闲着。如果你希望读流量只走从库则改用balance3。writeType0表示所有写操作都发给第一个writeHost也就是masterA。只有masterA挂了MyCat才会把写操作切到masterB。这套机制决定了正常情况下masterB只承担读请求但它的数据又必须和masterA实时保持一致因为复制链路才是它随时可以接管写操作的根本保障。switchType1表示开启主从自动切换。这里要特别说清楚当masterA心跳失败时MyCat会把masterB提升为新的写节点。我实测下来的结果切换时间通常在10秒到30秒之间取决于心跳检测间隔和连接池的刷新时间。对于大多数内部业务这个故障恢复时间是可以接受的。slaveThreshold这个参数用在上游MySQL版本相对较新的环境下表示从库延迟超过该秒数后不再把读请求分发给该从库。如果你的MySQL版本不支持心跳语句对应的方法可以忽略此项。它的意义在于避免读到延迟过大的旧数据生产环境设置100秒是比较保守的做法。4.3 启动MyCat并验证请求分发配置完成后进入bin目录启动MyCat./mycat start启动后观察logs目录下的mycat.log确认启动成功然后可以用MySQL命令行工具连接逻辑库mysql -h 127.0.0.1 -P 8066 -u appuser -pApp_123456 -D orderdb连上之后先执行一条简单的写语句再执行一条查询语句。要确认请求真的发到了期望的节点上可以在后端MySQL节点上分别开启通用日志或者通过SHOW PROCESSLIST观察来自MyCat的连接。一个比较实用的验证技巧在每个后端MySQL节点上执行SET GLOBAL general_logON然后从MyCat发一条select再看哪个节点的slow log或general log里面出现了这条查询即可判断路由是否准确。验证完成后务必关闭general_log否则日志增长会拖垮数据库。5. 故障演练主库宕机后系统如何自愈5.1 手动切换与自动切换的实验记录配置完成后最重要的不是“看起来没问题”而是真正模拟一次主库宕机验证MyCat能不能按预期切换。我在测试环境做过很多次这样的演练下面是最典型的一次过程记录。模拟前当前写主库是masterA。我先在应用端持续写入测试数据确认数据写入正常。然后直接在主库A节点上执行SHUTDOWN模拟宕机紧接着观察MyCat日志。我的实际观察是MyCat心跳检测失败后会打印类似“switch datasource”的日志随后在下一个写请求到来时把写连接切换到masterB。应用端在切换期间可能偶发一到两条连接失败这是因为连接池中部分连接到masterA的连接还没被及时清理。遇到这个情况业务侧的应用层重试机制就能覆盖掉。切换完成后查询写入是否正常。正常情况下新写入的数据会落在masterB上。但有一个关键点要注意主库B上要有完整的历史数据——这就是双主互备的意义masterB一直从masterA同步binlog所以即便masterA宕机masterB的数据也是全量的。这套故障切换对从库的影响是masterA挂掉后挂在masterA下的slaveA会失去binlog来源IO线程中断。读流量由其他节点继续兜底slaveA从数据集群中暂时退出不会拖垮整体。5.2 恢复过程中的注意事项故障恢复的顺序一定不能乱。先把宕机的masterA机器启动MySQL正常起来后它和masterB之间的复制关系已经断开需要重建。重建复制的第一步是在masterB上查看当前binlog坐标SHOW MASTER STATUS;然后在masterA上重新执行CHANGE MASTER TO指向masterB填上刚才的坐标再START SLAVE。这里容易踩的坑是旧的复制关系还残留在masterA的master info文件中最好先执行STOP SLAVE; RESET SLAVE ALL;清掉旧配置再重建新链路。复制恢复后masterA会自动从masterB把宕机期间的增量数据补回来。等masterA追平数据再手动把MyCat的写节点切回masterA或者在确认系统稳定后由MyCat自身机制完成回切。注意MyCat默认不会自动回切需要运维人员介入。我在第一次做恢复演练时犯过一个错误masterA起来后没看复制状态直接就把写流量切回去了结果新写入的数据产生了主键冲突——因为masterA宕机期间masterB已经分走了偶数ID段。后续重建了从属关系并追平数据后才恢复正常。所以恢复操作一定要检查SHOW SLAVE STATUS的两个IO/SQL线程都为Yes之后再考虑回切。6. 常见问题排查与避坑实录6.1 主从复制中断1062与relay log生产环境最常见的故障就是复制中断报错之一就是Duplicate entry xxx for key PRIMARY对应MySQL错误码1062。一般出现在双主模式下ID冲突或者手动往从库写了数据导致主库同步时主键重复。排查思路分两步先看SHOW SLAVE STATUS中的Last_SQL_Error具体报错定位是哪一条SQL冲突然后结合具体表的主键判断冲突来源。如果是自增主键冲突检查auto_increment_offset和increment是否配好如果是手动写入导致的冲突需要在从库上删除冲突数据然后重建复制关系。另一种常见问题是relay log损坏SQL线程一直报relay log相关错误。遇到这种情况START SLAVE起不来需要用RESET SLAVE ALL清掉中继日志然后在从库上重新CHANGE MASTER TO并START SLAVE。这里要注意RESET SLAVE ALL之后从库会丢失复制坐标必须重新指定否则同步起不来。6.2 读写未分离与连接池问题有一种情况我调试过很多次schema.xml配置看起来很对但实际执行select流量还是打到了主库。排查后发现是balance参数写错了。balance0就是纯关闭读写分离所有请求都走writeHost。所以不要以为配置了readHost就等于开启读写分离balance的值才是开关。还有一类问题是连接池导致的。MyCat和后端MySQL之间的连接池maxCon设置得太小时高并发下连接会被占满大量SQL排队等待表现为应用端响应时间急剧上升。我一般把maxCon设置为后端MySQL可承载连接数的60%左右minCon保持10到20避免空闲时也占用过多数据库连接。6.3 从库延迟抖动的处理从库延迟可以说是读写分离架构最头疼的运维问题之一。延迟的根源通常是三种从库机器磁盘IO性能差、主库上存在大量大事务、或者从库查询负载太高等。排查时先区分是哪种情况。SHOW SLAVE STATUS里Seconds_Behind_Master持续增大先看从库所在机器的IO负载如果IO压力大优先考虑从库与主库的硬件配置对齐。如果有大事务比如一次性更新几十万行的UPDATE建议拆分或改写SQL。如果是查询负载高则需要增加从库节点或者优化慢查询。MyCat侧的兜底手段是把slaveThreshold这个参数配好延迟超过阈值的从库会被临时摘除在读分发名单之外保证应用不会读到明显过期的数据。这个参数不是万能的但对大多数场景能起到止损作用。最后再分享一个实战中体会很深的经验这套架构里最容易出问题的其实不是MyCat本身而是底层的复制链路维护。MyCat配置好之后日常运维的核心是把主从复制监控做好尤其是binlog坐标、SQL线程状态、复制延迟这三个指标。建议把这套监控接入到告警体系里任何线程中断都能第一时间收到通知问题就能控制在业务可感知之前。双主双从高可用读写分离架构前期一次性搭好的成本并不高难的是后续每一次故障都能冷静、有步骤地处理。祝大家开局顺利最好永远不要用到故障切换。