最近在忙一个线上项目单机MySQL扛不住读写压力了CPU经常飙到80%以上慢查询一个接一个。团队商量了一下决定上主从复制架构一套读写分离的方案先把读流量分流出去。折腾了几天把主从复制从原理到落地整个过了一遍踩了不少坑。这篇文章就基于这次实操把MySQL主从复制的核心原理、搭建步骤、坑点排查全部梳理一遍给正准备搞主从复制的同学做个参考。这篇文章适合谁刚接触MySQL主从复制的新手可以顺着思路把原理和搭建流程搞明白已经在用主从复制但遇到问题不好排查的同学可以直接跳到第四章对照排查需要做高可用方案选型的同学重点看第一章和第五章的对比分析。1. 主从复制到底解决了什么问题先说结论主从复制不是万能的但它确实是目前MySQL架构演进中最基础、最成熟的一步。理解了它能解决什么、不能解决什么你才能真正用好它。1.1 读写分离把主库的压力卸下来主从复制最直接的价值就是读写分离。典型的业务场景是写操作占总流量的20%以下读操作占80%以上。如果所有请求都打到一台MySQL上读请求会逐渐挤占CPU和IO资源写操作的响应时间也跟着变长。我接手这个项目的时候数据库的读写比例大概在1比5左右高峰期QPS到了3000多单实例已经明显吃紧。通过主从复制架构把读请求全部转发到从库主库专心处理写操作效果立竿见影。说到读写分离很多人有个误区以为主从复制搭好之后应用代码自动就会把读请求发到从库。实际上主从复制只是让从库有了和主库一致的数据路由逻辑需要你在应用层或者中间件层实现。常见的做法有这么几种在应用代码里配置多数据源通过AOP或者拦截器根据SQL类型自动路由使用中间件像ProxySQL、MySQL Router在SQL层做读写分流使用分布式数据库中间件比如ShardingSphere直接内置了读写分离功能1.2 数据容灾多一份副本多一条退路某次磁盘损坏导致数据丢失的事件让我印象很深。当时因为没做任何复制只能拿前一天凌晨的备份来恢复丢了十几个小时的数据业务影响非常大。有了主从复制从库相当于一份实时的数据副本。主库挂了可以快速把从库提升为主库业务中断时间从小时级缩到分钟级。虽然这个过程需要人为介入不能算自动容灾但已经把RTO恢复时间目标缩短了一个量级。1.3 从库还能做什么报表、分析、备份除了读写分离从库还有不少实用场景报表查询。很多报表SQL特别重动不动就要全表扫描在主库上跑绝对拖垮线上业务。放到从库上随便跑反正影响不到主库。数据备份。我现在的备份策略就是从从库拉物理备份完全不影响主库的IO。开发联调。给开发同学分一个从库账号去查数据避免他们误操作触碰主库。注意从库并不是无限制扩展的。每个从库都会增加主库的复制负担IO和网络开销从库数量超过一定规模比如10个以上主库的复制线程本身就会成为瓶颈。1.4 主从复制不是银弹它解决不了的问题说完优点泼盆冷水。主从复制解决不了下面这些问题数据一致性。主从复制本质上是异步的默认情况下主库提交事务和从库应用事务之间存在延迟。你刚写入一条数据立刻去读从库可能读不到。写入瓶颈。写入能力受限于主库单机的硬件上限主从复制不解决写扩展的问题。如果写流量也很大得考虑分库分表或者NewSQL方案。自动故障转移。传统主从复制方案里主库挂了不会自动把从库顶上需要借助MHA、Orchestrator之类的高可用组件或者购买商业版MySQL的Group Replication。我见过不少团队把主从复制当成了万能药结果上线后发现从库数据延迟、主从数据不一致反而比单机更麻烦。所以搞主从复制之前先想清楚你的核心诉求是什么。是读压力大还是需要容灾明确目标再动手。2. 复制原理拆解数据是怎么从主库跑到从库的搞懂原理再动手遇到问题才不慌。MySQL主从复制的核心机制说复杂也复杂说简单也简单就是一套基于日志的“传话”机制。2.1 三个关键角色和两类日志主从复制链路里有三个角色主库Master、从库Slave、以及连接它们的IO线程和SQL线程可理解为两条传输通道。两类日志是主库的二进制日志binlog和从库的中继日志relay log。整个过程可以用一句话概括从库的主线程IO线程去主库拉取binlog写到本地的relay log里然后从库的SQL线程读取relay log并顺序执行里面的SQL把数据变更回放到从库上。我把整个流程拆成下面几步应用在主库上执行SQL提交事务同时产生对应的binlog事件。主库的dump线程负责响应从库的请求把binlog事件推送给从库。从库的IO线程接收binlog事件写入从库本地的relay log文件。从库的SQL线程读取relay log按顺序执行其中的事务完成数据回放。执行完毕后从库更新自己的位点信息准备拉取下一条日志。整个过程都是异步的主库不会等从库确认这也是主从延迟的根本原因。2.2 binlog的三种格式statement、row和mixedbinlog的格式决定了主库记录的是SQL语句还是数据行变更这个看起来不起眼的参数实际影响非常大。statement格式记录原始的SQL语句。优点是日志量小缺点是某些非确定性函数如NOW()、UUID()在不同机器上执行结果不一致复制容易出错。row格式记录每一行数据的变更前后值。优点是复制最安全、一致性强缺点是日志量大一条UPDATE更新100万行会生成100万条变更事件。mixed格式混合模式默认使用statement遇到不确定函数或其他特殊情况自动切换为row。我在生产环境一律推荐row格式。虽然日志会大一些磁盘占用多一些但换来的是数据一致性太值了。之前在一个项目里用过statement格式某个凌晨跑批量任务用了一个带UUID()的存储过程结果从库的数据跟主库对不上了排查了大半天最后就是从statement改成row才解决。2.3 复制的三种模式异步、全同步、半同步这是选型绕不开的话题直接对比给你看复制模式主库行为数据一致性性能影响适用场景异步复制事务提交后立即返回不等待从库响应可能丢失数据无影响读写分离、报表场景全同步复制等待所有从库提交后才返回强一致很大不可用基本不用于生产半同步复制至少一个从库收到binlog后返回基本不丢数据略有影响要求不丢数据的核心业务关于半同步复制多说一句半同步的“半”是指折中了异步和全同步主库事务提交后需要等待至少一个从库确认已经收到binlog注意是收到不要求应用完成才返回客户端提交成功。这样能保证主库即使挂了已确认的事务至少存在于一个从库的relay log里不会丢。我在这次架构升级中直接配置的是半同步复制因为业务对资金数据一致性要求比较高丢数据的后果承担不起。性能损耗在可接受范围内实测延迟大约增加了8%-12%。2.4 复制延迟的根源两个线程和三条瓶颈很多人问为什么从库老是延迟其实就是三个环节的瓶颈主库生成binlog的速度。如果主库并发写入极高生成的binlog事件量太大dump线程推送不过来。从库IO线程写relay log的IO能力。磁盘慢IO线程就会积压。从库SQL线程回放的速度。单线程回放大事务比如一次DELETE百万行时延迟会肉眼可见地飙升。MySQL 5.6之后提供了并行复制MTS通过将SQL线程拆分成多个worker线程把不同库/不同表的事务并行回放能显著缓解延迟问题。我建议所有用5.7及以上版本的同学都把并行复制打开。提示并行复制不是万能的它只能缓解因回放效率导致的延迟。如果是主库写入量实在太大导致的延迟本质上要从业务层考虑分库分表复制层面的优化就无能为力了。3. 实操搭建一主一从环境配置全过程理论讲完下面进入实操。我们在生产环境用的是MySQL 8.0这里以8.0为例。准备两台服务器一台主库Master假设IP为192.168.1.101一台从库Slave假设IP为192.168.1.102操作系统都是LinuxMySQL版本保持一致这一点很重要跨大版本复制能少踩很多坑。3.1 主库配置核心参数和授权操作主库需要开启binlog配置一个server-id再授权一个复制专用的账号。# /etc/my.cnf 主库配置 [mysqld] server-id 101 log_bin /var/log/mysql/mysql-bin.log binlog_format ROW # 如果只需要复制某个业务库可以配置下面两个参数 # binlog_do_db your_db_name # binlog_ignore_db mysql # 每次事务提交后立即刷盘保证binlog不丢 sync_binlog 1配置完修改后重启MySQL让参数生效sudo systemctl restart mysqld主库上创建复制专用账号CREATE USER repl192.168.1.% IDENTIFIED BY YourStrongPassword; GRANT REPLICATION SLAVE ON *.* TO repl192.168.1.%; FLUSH PRIVILEGES;注意复制账号只需要REPLICATION SLAVE权限不要给ALL PRIVILEGES最小权限原则在数据库账号管理里同样适用。查看主库当前状态记录File和Position值这是从库开始复制的起点SHOW MASTER STATUS\G记录类似下面的信息*************************** 1. row *************************** File: mysql-bin.000003 Position: 1549782 Binlog_Do_DB: Binlog_Ignore_DB:3.2 从库配置从零开始建立同步链路从库配置相对复杂一些关键是先做一次全量数据初始化再启动复制。# /etc/my.cnf 从库配置 [mysqld] server-id 102 log_bin /var/log/mysql/mysql-bin.log binlog_format ROW # 从库开启binlog是为了级联复制或者做从库备份按需配置 # 下面两个参数可以过滤不需要复制的库 replicate_ignore_db mysql # 指定并行复制线程数8个对大多数场景足够 slave_parallel_workers 8重启从库MySQL之后在主库上用mysqldump建一个初始快照。这里有两个关键参数# --master-data2 会在备份文件里记录主库的binlog位置后续可以直接用 # --single-transaction 保证innodb的备份一致性 # --set-gtid-purgedOFF 如果是基于位点复制需要关掉gtid mysqldump -u root -p --single-transaction --master-data2 --set-gtid-purgedOFF --databases your_db_name your_db.sql把备份文件拷贝到从库然后导入scp your_db.sql root192.168.1.102:/data/ mysql -u root -p /data/your_db.sql在从库MySQL里执行复制配置。注意如果是通过mysqldump带--master-data生成的备份备份文件开头会有CHANGE MASTER TO语句和注释掉的MASTER_LOG_FILE/MASTER_LOG_POS可以直接抄过来CHANGE MASTER TO MASTER_HOST 192.168.1.101, MASTER_USER repl, MASTER_PASSWORD YourStrongPassword, MASTER_LOG_FILE mysql-bin.000003, MASTER_LOG_POS 1549782; START SLAVE;启动复制后查看复制状态SHOW SLAVE STATUS\G如果输出中这两个值是YES说明同步链路已经正常工作Slave_IO_Running: Yes Slave_SQL_Running: Yes3.3 上半同步复制多几行配置换来数据安全上面搭建的是默认的异步复制模式。如果我们想要半同步在MySQL 8.0里配置非常方便主库和从库都要装插件并启用主库执行INSTALL PLUGIN rpl_semi_sync_master SONAME semisync_master.so; SET GLOBAL rpl_semi_sync_master_enabled 1; SET GLOBAL rpl_semi_sync_master_timeout 1000; -- 超时时间1秒从库执行INSTALL PLUGIN rpl_semi_sync_slave SONAME semisync_slave.so; SET GLOBAL rpl_semi_sync_slave_enabled 1; STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;注意最后一步从库需要重启IO线程才能激活半同步插件。配置完成后在主库执行SHOW STATUS LIKE Rpl_semi_sync_master_status;如果返回的值是ON说明半同步复制已生效。提示rpl_semi_sync_master_timeout这个参数很关键它表示等待从库确认的超时时间。如果从库一直不确认超过这个时间后主库会自动降级为异步复制不会阻塞业务写入。超时时间设置太短比如100毫秒可能频繁降级太长比如10秒又会让写事务响应变慢建议根据网络延迟实测调整一般500ms到2s之间比较合理。3.4 基于GTID的复制比位点复制更省心刚才演示的是基于日志文件和位点的复制还有一个更先进的方案叫GTID复制。GTID全局事务标识符是每个事务在主库上生成时分配的唯一ID从库可以通过GTID自动追数据不需要手动指定日志文件和位置避免了位点绑定带来的切换麻烦。开启GTID需要修改主库从库的配置文件[mysqld] gtid_mode ON enforce_gtid_consistency ON然后从库连接方式变为CHANGE MASTER TO MASTER_HOST 192.168.1.101, MASTER_USER repl, MASTER_PASSWORD YourStrongPassword, MASTER_AUTO_POSITION 1; START SLAVE;GTID复制的好处在于切换方便主从切换后新从库不需要重新找位点它知道自己已经执行过哪些GTID会跳过这些事务继续往后同步。对于后续做高可用主从切换的同学强烈建议直接用GTID模式。不过我和团队实际操作下来的体感是如果是第一次搭主从先按位点复制把链路跑通再切换GTID会比较平滑。否则一下子引入GTID出了问题排查面更大。4. 常见问题与排查技巧实录搭建过程中必然遇到问题这块我踩过不少坑按出现频率从高到低整理出来每个都配了排查思路。4.1 复制线程起不来Slave_IO_Running是Connecting怎么办这是最常见的问题。IO线程连不上主库状态就是Connecting。第一步检查网络。看从库能不能连通主库的3306端口telnet 192.168.1.101 3306第二步确认账号和权限。在主库上执行SELECT user,host FROM mysql.user WHERE userrepl;如果host限制是192.168.1.%而从库的IP是192.168.1.102就没问题。注意MySQL的host匹配是精确匹配优先如果同时存在%和192.168.1.%可能会命中你意料之外的那条规则。第三步检查主库是否开启了binlogSHOW VARIABLES LIKE log_bin;如果值是OFF那说明主库根本没开binlog复制无从谈起。第四步看主库的防火墙和SELinux这俩经常是“元凶”。生产上建议把数据库服务器之间的3306端口在防火墙里限定来源放行SELinux如果没把握配置有时直接临时放宽容许或者设成宽松模式验证一次确认是它的问题后再做精细配置。我就遇到过一次从库的IO线程怎么也连不上主库排查了一圈最后发现是防火墙没放行来源IP。4.2 主从延迟飙升Seconds_Behind_Master突然变成几千这个问题的排查我总结了几个步骤先用这个命令定位延迟发生在哪一环SHOW SLAVE STATUS\G关注三个数据Master_Log_File: mysql-bin.000009 Read_Master_Log_Pos: 382105466 Relay_Log_File: relay-bin.000012 Relay_Log_Pos: 92177644如果Read_Master_Log_Pos和Master_Log_Pos差距很大说明IO线程拉取日志的速度跟不上主库新产生的日志量通常是网络带宽或磁盘IO瓶颈。如果Relay_Log_Pos和Read_Master_Log_Pos差距很大说明SQL线程执行速度跟不上需要检查是否有大事务或者并行复制参数是否没生效。针对SQL线程慢常见原因一个是单线程回放大事务解决办法就是把并行复制打开并适当调大worker数量另一个是主库上有长时间未提交的写事务导致从库需要等它结束。大事务是主从延迟的“幕后黑手”。我之前遇到一次从库延迟达到上亿位点的情况查了一圈发现是凌晨有个定时任务在主库上跑了一次DELETE一次性删除几千万条历史数据生成的binlog事件量巨大从库根本追不上。从那以后我定了一条规矩任何批量DML必须分批执行每批处理一万行间隔几秒再继续。这个习惯保住了很多次数据库的稳定运行。另外如果从库硬件配置比主库低比如磁盘是机械盘、CPU核数少延迟也很难追平。主从硬件尽量同规格这是最容易被低估的一点。4.3 主从数据不一致怎么发现和修复数据不一致的原因很多用了非row格式的binlog、从库被手动写入过数据虽然从库默认只读但REPLICATION权限的账号不受限、版本不一致导致的语义差异等。排查手段用一张表做对比。比如核心业务表orders-- 主库执行 SELECT COUNT(*) FROM orders; CHECKSUM TABLE orders;-- 从库执行 SELECT COUNT(*) FROM orders; CHECKSUM TABLE orders;如果两边checksum不一致就说明数据确实有差异。修复的方法比较粗暴但有效重新基于主库做一次全量备份恢复到从库然后再继续同步。对一致性要求极高的场景可以考虑定期用pt-table-checksum和pt-table-sync这类工具做校验修复。这两个工具属于Percona Toolkit不依赖特定环境是通用的开源工具生产实践中用的非常多。注意从库一旦发现无法自动解决的问题最快的恢复路径往往不是去“改”从库数据而是重做。因为从库只是一个副本重做成本相比排查一个由于手工修改导致的复杂问题是低很多的。别犹豫直接重做。4.4 从库被写入数据导致同步中断MySQL从库如果不配置只读被手工写入数据可能会和正在回放的事务冲突导致SQL线程报错并停止。SQL线程报错后错误的SQL已经执行并记录到了relay log里如果直接跳过可能导致数据漏应用或者不一致所以“跳过错误”这个操作要非常谨慎。稳妥的做法是先确认报错类型再决定是跳过单条事务还是重新初始化从库。比如在从库的whose表里多了一条id100的数据回放时INSERT本身就冲突。如果确认跳过的是对业务无影响的错误比如误操作导致的重复插入可以用这个方法跳过STOP SLAVE SQL_THREAD; SET GLOBAL sql_slave_skip_counter 1; START SLAVE SQL_THREAD;但是如果这类错误出现的频率高或者无法判断影响面直接重新搭建从库是最保险的方案。另外从库务必开启只读模式[mysqld] read_only 1 super_read_only 1super_read_only这个参数值得强调普通账号对从库的写操作会被拒绝就连SUPER权限的账号也会被挡在写操作之外。这样从库就基本不会被意外写入了。4.5 典型问题速查表异常现象可能原因处理建议Slave_IO_Running: Connecting网络不通、账号错误、主库binlog未开启按4.1的四步排查法逐项检查Slave_SQL_Running: NoSQL线程执行失败事务冲突或错误SQL结合报错和后续事务判断是跳过还是重做从库Seconds_Behind_Master持续增长大事务、从库硬件弱、并行复制未开启分批处理DML、升级从库硬件、开启并行复制主从数据不一致binlog不是ROW格式、从库被写入校验checksum后用工具修复或重做从库切换主从后复制不生效未正确配置新主库的binlog和账号确认新主库开启binlog、创建复制账号、重新CHANGE MASTER5. 主从复制的进阶从“能跑”到“好用”复制链路跑起来了只是第一步。从“能跑”到“好用”中间还差很多功力和配置。有一些很重要的优化点是基于我自己的经验总结出来的特别值得你参考。5.1 主从延迟不可完全消除但可以把影响降下来用主从复制就不得不认真考虑延迟问题。很多业务对读操作的一致性要求没那么高但对特定数据比如支付结果、账号信息要求必须读到最新。怎么低成本地处理这种延迟我在项目里用了这样一套平衡方案。对于强一致性要求的少量读请求强制路由到主库执行对于大部分可以接受秒级延迟的读请求自由路由到从库。具体可以借助中间件实现比如在数据访问层给SQL打标签或者干脆在业务代码里分两层数据源。不需要把整个系统做成分库分表那种重架构很多时候一个强制主库路由的注解就能解决问题。5.2 并行复制和参数调优实测下来最有用的几个设置MySQL 5.7及以上版本的并行复制配置得当能让从库的回放效率翻倍。关键的参数和常用配置如下[mysqld] # 开启基于库级别的并行复制5.7的MTS功能 slave_parallel_type LOGICAL_CLOCK slave_parallel_workers 16 # 如果一行数据要并行复制数据库必须开行级并行否则还是会串行。LOGICAL_CLOCK是基于事务提交的时间戳来并行不同事务只要在时间窗口内没有依赖关系就可以并行应用。slave_parallel_workers线程数不能盲目设置太大通常设置为从库CPU核心数的一半到三分之二比较合适。我们跑下来的经验是从库8核16G的配置workers设16比设4的延迟改善要明显得多。另外一个容易忽略的参数是binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count这两个参数可以让主库把多个事务的binlog合并刷盘提高主库的写吞吐量。但它会牺牲一点点响应时间需要根据业务场景权衡。我一般不会盲目调这两个参数除非确实有大批量写入又对延迟不敏感的场景。5.3 监控主从状态要盯的指标清单主从复制不是配好就能一直安稳跑下去日常监控是必须的。至少要盯这几个指标Slave_IO_Running和Slave_SQL_Running两个线程是否正常这个一眼就能看到。Seconds_Behind_Master从库延迟的秒数超过阈值就报警。Master_Log_File和Read_Master_Log_Pos的差值IO线程是否在持续拉取新日志。从库磁盘空间relay log如果因为异常中止复制而没有及时清理会一直累积占满磁盘。主库binlog大小如果binlog增长太快要关注是否有大事务。我们用的是自研的监控脚本每隔30秒采集一次SHOW SLAVE STATUS的关键字段超过阈值推送到告警群。另外我自己还加了每天定时对比主从核心表checksum的任务虽然不能覆盖所有行但能提前暴露出大批量不一致的问题。5.4 主从切换演练别等事故来了才练主从复制的最终目标之一是在主库故障时快速切换到从库。但如果你没演练过切换真出事的时候大概率手忙脚乱。建议步骤在从库上执行STOP SLAVE;确保不再接收新的变更。确认从库的Seconds_Behind_Master已经为0也就是数据追平了。在从库上开启读写SET GLOBAL read_only OFF;把应用的数据源连接切到新主库。原主库恢复后把它作为新主库的从库加入集群或者干脆重做。整个切换过程中最关键的是第2步一定要等从库追平再切换否则会丢数据。如果用了半同步复制追平的时间会大大缩短但也要确认一下。我建议每季度做一次切换演练把切换时间、丢数据情况、应用影响全部记录下来。周密的演练计划是最好的保险因为你用了主从架构就等于欠下了这笔“切换能力”的账早晚要还的。结尾主从复制这套架构我实际用下来的体会是单机MySQL撑不住时它是性价比极高的第一步但你要清楚它的能力边界异步复制会有延迟自动切换需要外部组件写扩展需要分库分表。把这些边界摸清楚就不会在故障发生时手足无措。如果这篇文章能帮你少踩一个坑或者让你在上线前多想一步监控和切换的事那这个整理就值了。最后再分享一个小技巧配置任何复制参数之前先想清楚它在你这个具体场景里的目的是什么别照搬网上配置因为生产环境每个参数都有它的体感差异和代价。