MySQL用了这么多年真正让我意识到主从配置不是加分项而是必选项的是一次线上事故——某天凌晨一个同事连错环境一条UPDATE没带WHERE条件直接把用户表全量更新了。当时数据库只有一个实例备份恢复花了将近两小时业务直接瘫痪。那次之后我就把主从复制列为所有线上MySQL实例的标配。这篇博客就围绕mysql的主从配置把从原理到实操、再到运维踩坑的完整链路讲清楚适合刚接手数据库运维的开发者也适合准备搭建读写分离或高可用架构的团队参考。主从配置本质上解决的三个问题读写分离把查询压力分流到从库、容灾主库挂了之后还能从从库拉起数据、数据备份窗口可以在从库做备份而不影响线上。如果你目前只有单机MySQL业务量不大也不敏感可能感受不到它的价值但当CPU到峰值、磁盘IO打满、或者误操作需要找回数据时你会庆幸当初多花了一个小时配好主从。1. 别等数据库挂了才想起主从三种典型场景1.1 读写分离是主从最朴素的用途MySQL的单机性能瓶颈绝大多数情况下不是CPU计算而是磁盘IO和锁竞争。读多写少是互联网业务的常态一点几万个请求里可能九成是SELECT。这些查询全部压到主库上主库每次行锁、表锁、binlog刷盘都要跟一堆慢查询抢资源。搭建从库之后把报表查询、数据分析、后台管理类的读操作全部路由到从库主库的压力能降一个数量级。但读写分离有个隐藏前提允许短暂的数据延迟。因为主从复制是异步的主库提交事务之后binlog传到从库并回放需要时间。如果你做一个写完立刻读自己刚写的数据的功能必须强制走主库或者接受在极端情况下读到旧数据。很多团队第一次做读写分离时没意识到这一点上线后发现用户刚发完评论列表里看不到一片吐槽。解决方案也很简单ORM层按需配置写操作和强一致性读走主库弱一致性读走从库。1.2 高可用切换离不开多副本这个前提单点故障是数据库架构里最不能碰的底线。物理机宕机、断电、磁盘损坏任何一个问题发生没有副本就意味着从备份恢复到可用状态的时间以小时计算。有了从库之后主库发生灾难时可以把业务流量切到从库恢复时间从小时级降到分钟级。在主从架构上做高可用需要在从库之上叠加一套切换机制。比如常见的主库故障自动切换方案核心动作就是检测主库心跳丢失后提升从库为新的主库。这里最关键的细节是从库数据必须尽量追平主库。如果用异步复制且主库故障前binlog还没来得及传到从库切换后就会丢失最后一段时间的数据。这也是半同步复制和组复制存在的意义后面我会专门讲。1.3 日常备份和离线分析也可以挪到从库很多团队的备份策略是每天凌晨在服务器上mysqldump一次全量数据。如果直接在唯一的主库上跑备份mysqldump对线上性能的影响非常明显——大量物理读和临时表操作会把主库IO吃满。把备份任务放到从库主库的正常业务完全不受影响。同样数据仓库抽取、报表统计、慢查询分析这些重IO任务也适合放到从库。从库即使被这些任务拖垮也不会拖累线上写入。我在实际运维中甚至会把从库单独部署在配置较低的机器上专门承接这类脏活累活主库则保持在高端硬件上专注服务在线业务。提示做这类规划时要注意从库磁盘空间通常需要比主库更大因为从库要承接备份文件和临时表空间。2. 主从复制跑得通的底层逻辑binlog与三线程接力2.1 binlog复制的数据源理解主从复制最核心的是理解binlog二进制日志。MySQL在主库上记录所有引起数据变更的语句和事务信息到binlog从库就是靠读取并重放这份日志来保持数据一致的。binlog有三种格式STATEMENT、ROW、MIXED。STATEMENT记的是原始SQL日志量小但某些函数和不确定性操作在从库回放时会计算出不同结果ROW记录的是每行数据的具体变更前后值最安全日志量大MIXED是两者的混合。我强烈建议使用ROW格式尤其是在从库上做数据恢复时ROW格式能精确知道哪些行被改了。MySQL 8.0默认就是ROW如果你还在用5.7且没改过检查一下很多默认配置还是STATEMENT。还有一个参数容易被忽略binlog_row_image。它控制ROW格式下记录的字段范围默认FULL记录所有字段。如果你为了省日志量把它改成MINIMAL只记录被修改的字段从库重放时会少很多判断依据某些基于binlog做数据回滚的工具比如解析binlog生成反向SQL的工具也会失效。省那点日志量不值当保持FULL。2.2 IO线程、SQL线程和Relay Log的中转站从库上有两个核心线程在后台工作。IO线程负责连接主库把主库binlog的内容拉到从库写入从本地的relay log中继日志SQL线程负责读取relay log并在从库上按顺序重放完成数据更新。在MySQL 5.6之前SQL线程只有一个这意味着从库回放是单线程的。主库是多线程并发写入从库却只能一个一个串行回放这就是从库延迟的根本原因。5.6之后引入了多线程复制slave_parallel_workers可以把一个库内的事务按不同worker并行回放。但要注意并行回放不能随便开——必须基于相同数据库实例级别的组提交数据库调度复杂。生产环境如果延迟不严重先设置4个并发线程观察效果不要盲目加到几十个。从MySQL 5.5开始复制链路中还加入了半同步复制机制。异步复制下主库提交事务时只要本地成功了就返回客户端成功不管从库有没有收到半同步复制则要求主库等待至少一个从库确认收到binlog后才提交返回。这个机制显著缩小了主库故障时可能丢失数据的时间窗口。3. 搭建前必看参数取舍与版本选型3.1 版本与引擎选型避免从第一步就埋雷建议主库和从库使用完全相同的MySQL大版本最好是同一个小版本。为什么强调版本一致性我见过一个案例主库是5.7从库是8.0配置好之后复制链路经常莫名中断原因是8.0对binlog默认的校验规则发生了变化从库拉取5.7的binlog解析失败。还有一些旧版本生成的sql_mode在回放时会造成兼容性问题。复制协议虽然尽力向前兼容但跨大版本的坑太多不值得在生产环境冒险。存储引擎方面线上业务表统一使用InnoDB。MyISAM不支持事务且复制时对DML和DDL的处理跟InnoDB的行为有差异主从切换后容易出数据不一致的问题。如果库里还残留MyISAM表在配置主从之前用一条命令统一检查并转换SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE ENGINE InnoDB;3.2 主从基础参数每个参数都不是随便写的主库端核心参数参数推荐值含义server-id全局唯一数字每个实例必须不同复制链路靠它区分log-binmysql-bin开启binlog文件名前缀binlog_formatROW行级复制保证一致性expire_logs_days7~14自动清理过期binlog注意磁盘占用max_binlog_size256M~1G单个binlog文件上限从库端核心参数参数推荐值含义server-id全局唯一数字不能和主库及其他从库重复relay-logmysql-relay-bin中继日志前缀read_only1开启后从库只允许主库复制写入relay_log_recovery1崩溃后自动丢弃未完成的relay log并重新拉取skip_slave_start0实例重启后自动继续复制server-id要单独强调。很多初学者配置主从失败排查半天才发现两台机器的server-id都是默认的1复制链路根本建立不起来。另外read_only只对普通账号生效拥有SUPER权限的账号依然能写从库所以就算开着read_only也不要到处乱用管理员账号操作从库。max_binlog_size如果配得太小binlog文件数量会很多从库IO线程要频繁请求新文件且SHOW MASTER STATUS返回的文件号和位置会经常变化定位复制位点时会有点烦。也可以在主库设置binlog_group_commit_sync_delay等参数优化组提交效率但这两个参数影响的是复制延时的源头属于进阶调优项新手可以先不管。注意修改server-id和log-bin等参数需要重启MySQL实例才能生效建议在业务低峰期操作并提前评估重启影响。4. 从零到一主从配置完整实操4.1 主库端设置账号权限与当前位点我自己搭建主从的顺序是固定的先改主库配置、建复制账号、锁表查位点再改从库配置、恢复数据、配置复制串。每个步骤做完都先验证一步避免最后一起排错无从下手。第一步在主库配置文件/etc/my.cnf的[mysqld]段加上前面表格里的主库参数然后重启MySQL。检查binlog是否真的开启了SHOW VARIABLES LIKE log_bin; -- 预期: log_bin ON SHOW MASTER STATUS\G -- 预期能返回文件号和位置例如 File: mysql-bin.000012, Position: 3421第二步创建专用的复制账号。复制账号权限只需要REPLICATION SLAVE不要给all privileges安全原则是最小权限CREATE USER repl192.168.10.% IDENTIFIED BY StrongPassword_2024; GRANT REPLICATION SLAVE ON *.* TO repl192.168.10.%; FLUSH PRIVILEGES;账号host按实际网段限制除非跨网段情况否则不要用%放开所有来源。复制密码不要用简单密码因为它会明文出现在从库的CHANGE MASTER TO语句和连接日志中。第三步一致性问题。主库如果持续有写入直接备份拿到的数据集和记录的binlog位置是对不上的。标准做法是先用FLUSH TABLES WITH READ LOCK把主库的写入锁住FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; -- 记下输出里的 File 和 Position然后在另一个终端做物理备份或mysqldump。备份完成后执行UNLOCK TABLES释放主库锁。注意读锁期间不要拖太长mysqldump全量备份如果是上TB的大库这个方案就不实际需要改用Percona XtraBackup在线物理备份它可以在不锁库的情况下做一致性快照并自动读取binlog位点。4.2 从库初始化宁可慢不要快从库先装好相同的MySQL版本和配置参数注意把server-id改成与主库不同的值。然后搭建复制链路有两种主流思路。思路一冷备份迁移。把主库全库数据用mysqldump导出再导入从库。适合数据量不大的场景几十GB以内操作直观mysqldump -uroot -p --single-transaction --master-data2 --all-databases backup.sql--master-data2会在dump文件的注释位置自动写入主库当时的binlog文件和位置这比手动记录方便得多。--single-transaction通过InnoDB的MVCC机制保证导出过程中读到的是一致快照不会锁住业务写入。思路二物理备份迁移。用xtrabackup --backup备份主库数据目录再xtrabackup --prepare应用到从库对应目录。速度快适合大库但是操作步骤更复杂而且要求主从的版本完全一致。对新手来说数据量不大就先走mysqldump路线把流程跑通了再说。4.3 从库执行接入指令CHANGE MASTER TO的细节数据同步到从库之后登录从库MySQL执行接入命令。这里如果用了mysqldump的--master-data2可以直接查看dump文件头部注释定位位点CHANGE MASTER TO MASTER_HOST 192.168.10.11, MASTER_USER repl, MASTER_PASSWORD StrongPassword_2024, MASTER_LOG_FILE mysql-bin.000012, MASTER_LOG_POS 3421;这里的MASTER_LOG_FILE和MASTER_LOG_POS必须与备份那一刻的SHOW MASTER STATUS完全对应。位置填错一个字节都不行填大了从库会跳过部分事务导致数据缺失填小了则会重复执行一堆已经执行过的binlog大概率直接报主键冲突。执行后启动复制并检查状态START SLAVE; SHOW SLAVE STATUS\G最需要关注的四行输出Slave_IO_Running: YesIO线程连接主库正常Slave_SQL_Running: YesSQL线程回放正常Seconds_Behind_Master: 0复制延迟为0Last_Errno/Last_Error非0则报错原因在这里从MySQL 8.0.22开始START SLAVE改成了START REPLICASHOW SLAVE STATUS改成了SHOW REPLICA STATUS老命令依然兼容可用但会在日志里提示弃用。4.4 验证复制链路写一条数据看效果我每次搭建完成都会做一个小实验来验证整个链路是真的通了而不只是看着SHOW SLAVE STATUS的输出都是Yes。在主库建一个临时库写几条数据然后到从库查-- 主库执行 CREATE DATABASE repl_test; USE repl_test; CREATE TABLE t1 (id INT PRIMARY KEY, val VARCHAR(100)); INSERT INTO t1 VALUES (1, hello), (2, world);等一两秒在从库执行USE repl_test; SELECT * FROM t1;能查到这两条数据就说明链路通了。测完把临时库DROP掉复制会自动同步删除。之所以要做这个验证是因为IO和SQL线程都显示Yes的情况偶尔也会是假象比如binlog位点填的是旧的SQL线程已经把所有relay log回放完毕但主库新写的binlog根本没被拉过来这种情况下状态虽正常数据却不同步。5. 运维中必然踩的坑复制中断的排查思路5.1 IO线程断开的排查链路Slave_IO_Running一旦显示为Connecting或No说明从库拉取binlog的连接出了问题。排查顺序我建议固定为网络连通性 - 复制账号权限 - 主库binlog是否存在。网络问题最常见也最常被忽略。从库服务器先手动连一次主库的3306端口telnet 192.168.10.11 3306如果通不了检查防火墙和安全组策略。很多团队数据库服务器走内网IP配置时用内网IP但防火墙放行的却是业务网段导致复制连接被随机丢弃。网络没问题再看账号权限。主库执行SELECT User, Host FROM mysql.user WHERE User repl;确认账号存在且Host配置正确。然后用从库上的MySQL客户端主动用复制账号连一次主库验证密码是否正确mysql -h 192.168.10.11 -u repl -p账号验证通过后看主库binlog是否还在。如果主库设置了很短的expire_logs_days而从库断连时间又超过了这个天数从库请求的binlog文件已经被主库清理IO线程会报Got fatal error 1236之类的错误这种情况需要重新全量初始化一次从库因为缺失的binlog已经无法补救了。5.2 SQL线程报错的恢复与跳过Slave_SQL_Running变No基本是SQL线程回放binlog时遇到了错误。最常见的两种错误码1062 Duplicate entry重复插入。典型的位点重复比如CHANGE MASTER TO的位点偏前或之前曾跳过复制后又重新接回。1032 Cant find record更新或删除的行不存在。典型的从库数据与主库不一致比如有人违反read_only往从库写了数据。遇到这类错误第一步是看SHOW SLAVE STATUS\G的Last_Error字段确认是哪个库哪个表的哪条语句出错。如果是确凿的误操作数据不一致可以选择跳过该事务继续复制STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER 1; START SLAVE;SQL_SLAVE_SKIP_COUNTER1表示跳过下一个错误事务。这个操作一定要慎用——跳过之后从库会永久缺失这一条数据后续如果该数据被其他操作引用还会引发更大的不一致。我的原则是跳过之前必须确认这条错误是脏数据还是正常数据。脏数据误写入从库的数据导致的冲突可以跳过正常业务数据必须停下修复。SQL线程错误后relay log里那些已经拉下来但还没执行的事务会堆积在本地。如果错误一直不处理磁盘空间会被relay log占满。处理完成后通常建议执行一次RESET SLAVE或者让SQL线程自动清空已处理的relay log避免磁盘告警。提示如果主从数据不一致已经很严重最稳妥的方式不是一点点修而是重新做一次全量数据同步并重配复制。省事但绝对安全的方案没有之一。5.3 延迟治理复制不能无限追不上主库Seconds_Behind_Master长期大于几十秒就属于需要介入的延迟了。延迟的来源按优先级排查首先看主库是不是有大批量写操作。一次UPDATE几十万行的任务执行5分钟这5分钟里从库SQL线程在追赶一个巨大的binlog事务期间从库查到的都是旧数据。这种大事务不只是产生延迟还会让主库的binlog文件瞬间膨胀从库relay log同样暴增。生产建议大批量语句拆成小批次执行控制单事务影响行数。其次看从库服务器负载。SQL线程是单线程回放历史的时代已经过去了但如果并发参数没调对从库依然只有一个线程干活。检查SHOW VARIABLES LIKE slave_parallel_workers;如果值是0把它改成4~8之间。注意该参数的改动只需重启复制线程或者重启从库实例不需要重新初始化数据。加大并发后原来一直延迟很久赶不上的从库通常能在几分钟内追平。最后看从库磁盘IO。如果从库同时还在跑备份任务磁盘带宽被占用复制回放的效率会大幅下降。把备份任务调度到复制延迟较小的时段或者给备份任务做IO限速都能改善。6. 进阶GTID与半同步让主从更省心6.1 为什么推荐GTID从位点追杀里解脱传统主从配置用MASTER_LOG_FILE和MASTER_LOG_POS定位复制起点这在切换和重建从库时非常痛苦。比如从库A宕机你新加一台从库B想让它直接从主库拉数据就必须手动找到主库当前binlog位点填入CHANGE MASTER TO稍有偏差复制就出问题。GTIDGlobal Transaction Identifier从根本上解决了这个问题。每个事务在主库执行时会被分配一个全局唯一的ID从库通过MASTER_AUTO_POSITION 1告诉主库我已经执行到哪些事务了把后面的事务发给我。你完全不需要关注binlog文件名和位置只要配置自动定位。启用GTID需要主从库配置文件中加入gtid_mode ON enforce_gtid_consistency ON然后从库CHANGE MASTER使用CHANGE MASTER TO MASTER_HOST 192.168.10.11, MASTER_USER repl, MASTER_PASSWORD StrongPassword_2024, MASTER_AUTO_POSITION 1; START SLAVE;GTID模式下的主从切换也很直观如果主库故障要把从库提升为主库直接执行STOP SLAVE; RESET MASTER;再开放写权限就够了新主库的GTID集合自然包含旧主库已经执行的全部事务不会有遗漏。6.2 半同步复制牺牲一点性能换来数据不丢纯异步复制有一个天然的窗口主库提交事务并把binlog发给从库这期间如果主库宕机从库没收到这些事务切换到从库就会丢数据。虽然这个窗口通常只有几毫秒但对于资金类系统、订单系统来说任何数据丢失都不能接受。半同步复制semi-sync replication通过插件方式实现主库在事务提交时必须等待至少一个从库确认收到binlog后才向客户端返回成功。这样主库返回成功时该事务的binlog必然已经存在于至少一台从库上。如果从库始终不确认超过超时时间(rpl_semi_sync_master_timeout)后会降级为异步复制避免从库故障拖死主库。这个退化机制要理解清楚半同步不是100%主从无延迟而是尽量缩小丢失窗口。启用半同步前确认插件文件存在MySQL 5.7和8.0都内置了semisync插件。配置plugin_load_add rpl_semi_sync_mastersemisync_master.so;rpl_semi_sync_slavesemisync_slave.so rpl_semi_sync_master_enabled 1 rpl_semi_sync_slave_enabled 1 rpl_semi_sync_master_timeout 3000配置完成后重启主从实例从库还要再STOP SLAVE; START SLAVE;一次让复制线程重新加载半同步状态。通过SHOW STATUS LIKE Rpl_semi_sync_master_status可确认是否已开启。半同步复制的代价是每次事务提交都要多一次网络往返等待写入性能会有一定下降通常可以控制在5%~10%以内。如果业务对写入延迟极其敏感可以只在核心库或者跨机房的从库上开启不必全局铺开。7. 最后聊聊我踩过的几个细节坑主从配置本身不复杂但生产环境的复杂往往来自细节。第一个坑是binlog格式不统一。主库如果之前是MIXED格式后来只改了ROW格式但没有把已经执行过的不兼容事务处理掉从库可能会在某个老事务上报错。改格式最好配合全量重建从库而不是在一个已有复制链路上直接改。第二个坑是从库提升为主库的角色转换。把从库提升为主库后如果原主库恢复又直接把它接回复制链路会发生主主冲突——两台都能写数据互为副本很快出现主键冲突。正确处理是原主库降级为从库并且做好read_only管控禁止出现双写。第三个坑是复制监控。SHOW SLAVE STATUS\G不会自动告诉你故障必须有监控报警兜底。最简单的方案是写定时任务每分钟检查一次状态检测到IO线程或SQL线程不是Yes立即告警。主从故障后拖得越久修复成本越高最后可能只能重新全量同步数据。做mysql主从配置这件事我个人的体会是不要把它当成一次性的搭建任务而要当成一个持续的运维习惯。从配置完成那一刻起定期检查延迟、检查binlog保留时长、演练从库提升流程这些才真正决定主从架构能不能在危机时刻扛住。如果你刚开始接触照着本文的步骤把一套测试环境搭起来、再故意制造几个故障练手排错一次完整的实操胜过十遍文档阅读。