把主从复制和读写分离一次搞定这事听起来不难实际操作后你会发现坑不少。最近一个业务模块的查询压力上来了单库MySQL在写入一多慢查询直接冒头。我干脆把架构升级成“MySQL主从同步 Mycat2中间层读写分离”写操作全部走主库读操作由Mycat自动切到从库。整体改造用了不到一天之后主库负载肉眼可见地降了下来。这篇文章就把我完整的操作步骤、配置参数和排坑记录整理出来给正在折腾同类架构的朋友一个直接能参考的方案。内容适合有MySQL基础、想把主从复制和读写分离落地的人。你会看到先怎么配置主从同步、再用Mycat2把读流量分流以及我最容易踩的几个坑。如果你完全没接触过Mycat也能照着这份流程从零把它跑起来。1. 架构设计与思路拆解1.1 读写分离到底解决什么问题MySQL一台实例扛所有读写通常在并发量还没那么高的时候没什么问题。可一旦读请求变多或者出现了几个大查询磁盘IO和CPU容易先顶不住写操作也被拖慢。这时候最直接的思路不是盲目升级机器配置而是把读和写分开写仍然交给主库读交给从库让两台机器各干各的。读写分离解决的不是“数据量太大”的问题而是“读写相互争抢资源”的问题。如果单库的数据量已经十几亿那得靠分库分表但如果只是读多写少、查询吃CPU读写分离是性价比最高的第一步。MySQL的主从复制本来就支持异步同步数据我们再把SQL路由层加上去就能让应用层无感知地读写分流。1.2 主从同步 中间层分流的整体结构这套架构分两层。底层是MySQL主从复制主库开启binlog从库通过IO线程拉取binlog日志然后由SQL线程把日志中的事务在本地重放最终两个库的数据保持一致。上层是Mycat2它对外暴露一个MySQL协议端口应用连接Mycat就像连接一台普通MySQL一样。Mycat内部把连接池拆成写连接池和读连接池通过解析SQL语句类型来做路由select走读节点insert/update/delete走写节点。读节点和写节点不一定是一台物理机器。正常部署下写节点指向主库读节点指向一个或多个从库。Mycat会根据配置的balance参数决定读请求怎么分配。配置得当以后从库越多读扩展性越好主库只负责写入和一部分强制读请求压力曲线会非常平稳。1.3 为什么我这次选Mycat2而不是别的方案选型的时候我也纠结了一阵。直接改应用代码用多数据源也可以但业务代码里要到处塞Transactional和动态数据源注解侵入性太强。用ShardingSphere-JDBC也可以但项目里已经是老服务改动面太大。Mycat这类中间件方案的核心理念是“应用无感知”数据库连接串一改应用代码不用动对老系统特别友好。选Mycat2而不选1.x主要是看中它对MySQL 8的兼容更好驱动也换成了JDBC模式配置上支持JSON和传统XML两套风格。社区里关于1.x的教程很多但2.x在某些细节上改了不少网上资料比较乱所以这次我把2.x的读写分离配置完整写出来给大家省点时间。2. MySQL主从同步先把地基打牢读写分离的前提是主从数据一致。如果从库同步断了好几天Mycat再把读流量分过去业务数据全乱套。所以第一步一定是先把主从复制稳定跑起来再谈中间层。2.1 环境准备与初始状态确认我用了两台Linux服务器分别装好MySQL 8.0。主库IP假设是192.168.10.10从库IP假设是192.168.10.11。端口都是默认3306。先做三件基础检查确认两台机器的MySQL都能启动版本尽量一致。如果主库8.0、从库5.7虽然也能复制但参数行为和binlog格式差异会带来很多潜在问题。确认从库当前是干净状态最好是没有业务数据的全新实例。如果从库已经有一套数据后面要先做备份恢复不能直接挂复制。确认server-id不能相同。server-id是MySQL实例在复制拓扑里的唯一标识两台都是默认的1肯定不行。检查命令SHOW VARIABLES LIKE server_id; SHOW VARIABLES LIKE log_bin;如果log_bin是OFF那主从复制无从谈起必须先开启binlog。2.2 主库开启binlog并创建复制账号修改主库的MySQL配置文件我这边是/etc/my.cnf有些系统是/etc/my.cnf.d/mysql-server.cnf。在[mysqld]下加这些参数[mysqld] server-id1 log-binmysql-bin binlog_formatROW binlog_expire_logs_seconds604800 max_binlog_size100M解释一下关键点server-id1主从拓扑里的唯一编号主库可以设为1。log-binmysql-bin开启binlog生成的日志文件统一叫mysql-bin.000001这种格式。binlog_formatROW行级复制。ROW模式比STATEMENT模式更安全不会因为函数计算不一致导致主从数据差异。缺点是binlog体积更大但现代硬件完全能接受。binlog_expire_logs_seconds604800binlog保留7天按秒算。旧版本用的是expire_logs_days7MySQL 8.0里这个参数已经废弃建议用新写法。max_binlog_size100M单个binlog文件大小满了就滚动生成新文件。改完配置文件重启MySQLsystemctl restart mysqld登录主库创建专门用于复制的账号CREATE USER repl192.168.10.% IDENTIFIED WITH mysql_native_password BY Repl123456; GRANT REPLICATION SLAVE ON *.* TO repl192.168.10.%; FLUSH PRIVILEGES;注意两点。第一账号权限只需要REPLICATION SLAVE不要给超管权限。第二MySQL 8默认认证插件是caching_sha2_password老版本客户端和某些复制通道可能不兼容这里显式指定mysql_native_password能省去后面一堆认证报错。接着查看主库当前binlog位置SHOW MASTER STATUS;输出大概是------------------------------------------------------------------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | ------------------------------------------------------------------------------- | mysql-bin.000003 | 654 | | | | -------------------------------------------------------------------------------File和Position要记下来待会从库配置要用。如果在配置主库前已经有业务在写入SHOW MASTER STATUS拿到的位置只能保证从这一刻开始的变更被同步之前的数据必须手动搬过去否则数据缺了一大块。2.3 从库配置复制通道修改从库的MySQL配置文件[mysqld] server-id2从库可以不开启binlog但如果这台从库以后还要作为更低一层的主库就得再开log_slave_updates1。我现在只需要它当从库所以只改server-id。重启从库MySQL然后登录从库执行CHANGE MASTER TO MASTER_HOST192.168.10.10, MASTER_PORT3306, MASTER_USERrepl, MASTER_PASSWORDRepl123456, MASTER_LOG_FILEmysql-bin.000003, MASTER_LOG_POS654; START SLAVE;执行完以后检查复制状态SHOW SLAVE STATUS\G重点看这几项Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0Slave_IO_Running: Yes表示IO线程能正常连上主库并拉取binlogSlave_SQL_Running: Yes表示SQL线程能正常执行复制日志。这两个只要有一个是No复制就是不正常的。Seconds_Behind_Master: 0表示当前没有延迟。如果从库之前有数据必须先做一次全量同步。比较标准的操作是主库用mysqldump导出同时记录binlog位置再把备份导入从库最后按备份时的位置配置复制。具体命令mysqldump -uroot -p --single-transaction --master-data2 --all-databases backup.sql--master-data2会在备份文件头部注释掉当时的binlog文件名和位置导入从库后去文件里找CHANGE MASTER TO那一行直接用里面的MASTER_LOG_FILE和MASTER_LOG_POS配置复制通道。这样数据起点才是连贯的。2.4 主从同步验证复制通道起来后别急着配Mycat先做一次数据验证。我在主库建一个测试库和表CREATE DATABASE demo_db; USE demo_db; CREATE TABLE t_user ( id INT PRIMARY KEY, name VARCHAR(50) ); INSERT INTO t_user VALUES (1, zhangsan);然后去从库查询SELECT * FROM demo_db.t_user;能查到(1, zhangsan)说明复制链路是通的。再多测几轮更新和删除确认不只是insert能同步update和delete也能正常重放。这里我想特别强调主从同步验证不能只看第一次插入成功就完事。后面配置Mycat时如果从库的账号权限、网络策略有问题读流量照样会失败。所以建议连续执行几种不同类型的DML确认从库数据始终跟主库一致。3. Mycat2读写分离把流量分流落地主从同步通了以后我们开始搭Mycat2。Mycat2对外连接端口是8066应用层只需要改连接地址SQL不用改。3.1 Mycat2的安装和启动我习惯用二进制包部署简单、可控。下载解压后整个目录就是mycat。确认服务器有JDK 8或以上版本因为Mycat2是Java写的。java -version然后启动cd mycat ./bin/startup.sh启动完成后看一下日志tail -f logs/mycat.log看到类似“startup successfully”或者没有ERROR日志就说明起来了。再用端口检查确认ss -lntp | grep 8066看到8066端口监听中说明Mycat已经对外提供服务。Mycat2在配置层面有兼容传统XML的模式安装包自带conf/schema.xml、conf/server.xml、conf/rule.xml这些文件。我这个版本可以用schema.xml来定义读写分离这也是社区资料最全、排错最方便的方式。如果你手头的Mycat2是纯JSON配置风格核心思路一样只是把writeHost/readHost换成两个独立的数据源配置再挂到同一个replica下面效果等价。3.2 配置逻辑库和读写分离打开conf/schema.xml先定义逻辑库、逻辑表和数据节点。我要把之前建的demo_db.t_user表暴露成逻辑库TESTDB下的t_user表配置如下?xml version1.0? !DOCTYPE mycat:schema SYSTEM schema.dtd mycat:schema xmlns:mycathttp://io.mycat/ schema nameTESTDB checkSQLschemafalse sqlMaxLimit100 table namet_user primaryKeyid dataNodedn1/ /schema dataNode namedn1 dataHosthostM1 databasedemo_db / dataHost namehostM1 maxCon100 minCon10 balance1 writeType0 dbTypemysql dbDriverjdbc switchType1 heartbeatselect 1/heartbeat writeHost hosthostM urljdbc:mysql://192.168.10.10:3306/demo_db?useSSLfalseallowPublicKeyRetrievaltrueserverTimezoneAsia/Shanghai userroot password你的主库密码 readHost hosthostS urljdbc:mysql://192.168.10.11:3306/demo_db?useSSLfalseallowPublicKeyRetrievaltrueserverTimezoneAsia/Shanghai userroot password你的从库密码 / /writeHost /dataHost /mycat:schema逐个拆解关键参数。checkSQLschemafalse应用连接Mycat时用的是逻辑库名TESTDB如果SQL里带了库名Mycat不会自动去处理。建议设false让应用把TESTDB当作一个“数据库实例”来用。sqlMaxLimit100Mycat对没有LIMIT的select会加一个默认限制防止一次拉取太多数据把中间层搞挂。业务上有大查询需要自行在SQL里写LIMIT。dataNode namedn1 dataHosthostM1 databasedemo_db逻辑数据节点真正对应物理库demo_db连接的是上面定义的hostM1。balance1读写分离开关的核心。Mycat的balance参数有几种取值balance取值读请求路由策略0不开启读写分离所有读走writeHost1所有读请求都分给readHostwriteHost不承担读压力2读请求在writeHost和readHost之间随机分发3读请求分发到readHostwriteHost不参与读但是readHost压力过大时会用writeHost兜底我这里用balance1读流量全部打从库让主库只吃写请求。writeType0所有的写操作都路由到writeHost。标准的主从模式固定用0。switchType1主库挂了以后Mycat自动把写请求切到从库。如果业务不允许自动切换怕出现“从库接写请求后数据不一致”可以设-1禁掉自动切换由运维手动恢复。heartbeatselect 1/heartbeat心跳检测语句Mycat用它来判断数据节点是否存活。URL里的参数也需要注意。useSSLfalse避免Mycat和MySQL建立SSL握手时出现奇怪问题allowPublicKeyRetrievaltrue是因为MySQL 8的caching_sha2_password认证在非SSL连接下需要先获取公钥不加这个参数连接会直接报Public Key Retrieval is not allowed。3.3 配置Mycat用户逻辑库配好了还需要一个能登录Mycat的用户。在conf/server.xml里增加user namemycat defaultAccounttrue property namepasswordMycat123/property property nameschemasTESTDB/property /user这里定义的用户是Mycat自己认证的用户不是MySQL的物理账号。应用将来连接Mycat时用的就是mycat和Mycat123Mycat再拿schema.xml里配好的root账号去连真实的MySQL主从库。如果你的Mycat2版本是JSON风格的配置用户文件通常在conf/users/mycat.user.json{ username: mycat, password: Mycat123, schemas: [TESTDB] }两种方式都要记得修改后重启Mycat或者用Mycat的管理端口在线刷新配置。我图省事直接重启。3.4 通过Mycat连接测试读写重启Mycat后用MySQL客户端连一下mysql -h127.0.0.1 -P8066 -umycat -pMycat123 TESTDB能进到MySQL命令行就说明Mycat起了作用。先做个简单查询SHOW DATABASES; USE TESTDB; SELECT * FROM t_user;能看到主库里的测试数据说明逻辑库和数据节点映射成功。再执行写操作INSERT INTO t_user VALUES (2, lisi); UPDATE t_user SET namewangwu WHERE id1;然后去物理从库查看SELECT * FROM demo_db.t_user;能看到更新后的数据说明写操作走了主库并且通过主从复制同步到了从库。到这里读写分离链路已经跑通。3.5 验证读请求是否真的落在从库这一步很多人会跳过但恰恰是最重要的。只靠“查询能返回数据”看不出读到底走了主库还是从库万一Mycat配置没生效所有读还压在主库上那读写分离就白做了。我有两个土办法验证。方法一看状态计数。在主库和从库分别执行FLUSH STATUS;然后通过Mycat连续执行几十次SELECT * FROM t_user;最后分别到主库和从库查看Com_selectSHOW GLOBAL STATUS LIKE Com_select;如果我的配置生效从库的Com_select会明显增加主库几乎不变。方法二开通用日志。在从库执行SET GLOBAL general_log ON; SET GLOBAL general_log_file /tmp/mysql_general.log;再通过Mycat执行几次SELECT然后去从库的/tmp/mysql_general.log里看有没有来自Mycat的连接发起的查询。如果有说明读流量确实落到了从库。验证完记得把从库的general_log关掉否则日志文件会膨胀得非常快。4. 常见问题与排查实践这套架构搭完线上运行了一段时间后陆陆续续遇到了一些典型问题。我把高频问题按类别整理一下方便你以后照着排查。4.1 主从复制状态异常最常看到的是SHOW SLAVE STATUS\G里某个线程是No。Slave_IO_Running: Connecting或者No一般是网络不通、复制账号不对、主库防火墙拦截。先做两件事第一在从库上手工用mysql命令连一次主库mysql -h192.168.10.10 -urepl -pRepl123456连不上就查网络和账号连得上再看IO线程第二看从库的Last_IO_Error字段报错信息会直接告诉你是1045权限问题还是2003连接超时。Slave_SQL_Running: No通常是SQL线程在执行复制日志时遇到了冲突。最常见的是Last_SQL_Errno: 1062主键冲突。比如从库已经有一行id1的数据主库又insert了id1复制就停了。解决方法STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER 1; START SLAVE;但注意这只是在跳过当前一条错误事务如果原因没有消除后面还会继续报错。治本的方法是把主从数据重新核对不一致的表用数据校验工具找到差异并修复。另一个高频错误是Got fatal error 1236 from master when reading data from binary log。这通常是因为从库要的binlog文件已经被主库清理了或者MASTER_LOG_POS配错了。解决办法是回到主库执行SHOW MASTER STATUS获取当前最新位置重新配置复制通道。如果业务数据量大最好直接重建从库不要硬追旧binlog。4.2 Mycat连不上MySQL物理库Mycat启动正常、8066端口也在监听但执行SQL时报Connection refused或者Access denied这时候要去检查schema.xml里的物理连接信息。Access denied for user root...说明物理账号密码错了或者MySQL没有允许Mycat所在机器的IP访问。给MySQL账号授权时要覆盖Mycat服务器的来源IP。Public Key Retrieval is not allowed这个只在连接MySQL 8时出现解决方法是给JDBC URL加上allowPublicKeyRetrievaltrue。Communications link failure一半原因是网络不通一半原因是空闲连接被MySQL主动断开Mycat连接池里的连接成了死连接。可以给URL加上autoReconnecttrue或者调整MySQL的wait_timeout让连接空闲时间大于Mycat的保活间隔。4.3 主从延迟变大读写分离架构下最怕的就是从库延迟。延迟一高应用刚写入的数据立刻去读结果读到旧值业务上可能就出问题了。主从延迟常见原因有三个从库机器配置比主库差SQL重放慢主库有大批量事务或者DDL从库单线程执行跟不上从库还承担了其他复杂查询和复制SQL线程抢资源。MySQL 8.0默认是单线程复制可以改成并行复制。配置[mysqld] slave_parallel_typeLOGICAL_CLOCK slave_parallel_workers4LOGICAL_CLOCK表示基于事务的提交逻辑进行并行回放同一个binlog里没有依赖关系的事务可以并发执行对延迟改善很明显。修改后重启从库生效。但如果主库一个大事务灌进去并行复制也白搭。所以对于大批量更新尽量分批执行不要一条UPDATE影响几十万行。监控延迟我不建议只看Seconds_Behind_Master它有时候并不准。更精确的做法是用pt-heartbeat在主库定时更新心跳表在从库计算心跳时间差能更真实地反映延迟。4.4 刚写入的数据查不到这是读写分离最容易让业务方跳脚的问题。写操作走主库读操作走从库主从复制是异步的正常情况下延迟几十毫秒但如果刚刚insert完立刻select从库还没同步过去就会查不到数据。我有三个处理手段第一业务上把“写完立即要读”的请求强制走主库。Mycat支持在SQL前加注释路由比如/*#mycat:db_typemaster*/ SELECT * FROM t_user WHERE id 100;这样即使默认配置读走从库这条查询也会强制走主库保证实时一致性。第二在Mycat的逻辑表上设置slaveThreshold或者配合读写分离策略对实时性要求极高的表不做从库读。具体配置根据你的Mycat版本略有差异核心是给这类表单独建数据节点不走读流量。第三把业务场景拆开强一致性的操作走主库容忍最终一致性的查询走从库。比如订单详情确认页可以走主库历史订单列表、统计报表这种走从库。4.5 主库故障后Mycat如何切换我在配置里设置了switchType1作用是主库心跳失败后Mycat自动把写请求切换到从库。但这只是在中间层层面做切换底层如果只有一台从库主库挂了以后所谓“切换”就是让从库变成新的写节点。要注意的是Mycat感知到的是“物理连接失败”如果主库只是网络抖动或者停止响应MySQL本身没挂Mycat可能会误判。所以实际生产环境我会配合MHA或者Orchestrator来管理MySQL主从的自动提升当主库真正宕机后MHA把从库提升为新主然后通过VIP切换让Mycat的writeHost指向新主。光靠Mycat自动切换可以应对临时故障但替代不了完善的MySQL高可用方案。另外读写分离场景下如果从库承担了读流量主库故障后这台从库既要做新主又要扛住所有读压力会非常大。如果读量很大建议至少配两个从库主库故障时还能留一个继续分担读压力。实测中的一些体会整套项目下来我最想提醒的还是那句话配置完一定要做路由验证。我见过不止一次同事说“读写分离已经配好了”结果一查Com_select从库的计数纹丝不动所有读还在主库上。原因往往是balance参数写成了0或者schema.xml里的dataHost层级配错了。这些坑不通过实际流量验证根本发现不了。另外不要一上来就把所有读流量都切到从库。尤其是刚开始做读写分离的系统可以先让balance2让主库分担一部分读压力同时观察从库延迟和Mycat的响应时间稳定后再切到balance1。数据库架构的调整稳妥永远比炫技重要。这套“MySQL主从Mycat2读写分离”的组合帮我扛住了当前几倍的查询压力如果你也遇到类似的瓶颈完全可以按着这个流程自己搭一套试试。