主从复制这个话题在MySQL的面试题里出现频率高得离谱任何一家稍微有点规模的公司只要你岗位跟后端、数据库沾边八成都会被问到。但说句实话很多同学对它的理解停留在“主库写、从库读”这八个字上真让他从零搭一套环境或者解释一下SHOW SLAVE STATUS里那几行关键输出到底什么意思立刻就卡壳了。这篇文章我就从实际搭建的角度出发把MySQL主从复制的原理、配置、实操、排错完整过一遍。不管你是在本地用Docker练手还是准备给生产环境搭一套我的建议都适用。我自己在CentOS和Ubuntu上都搭过MySQL 5.7和8.0也都跑过文中的操作步骤和坑基本都是实测记录不是光凭文档背出来的。1. 先把主从复制的底细摸清楚1.1 主从复制到底在复制什么先说一个很多人理解错的地方MySQL主从复制复制的不是数据文件本身而是binlog二进制日志。主库上所有会改变数据内容的操作INSERT、UPDATE、DELETE、DDL都会按顺序写进一个叫binlog的日志文件。从库做的事情说白了就是跑到主库那边把这个binlog拉过来再在自己本地重新执行一遍。这个过程听起来简单但落到实现上涉及了三个线程的协作主库上的Binlog Dump线程负责把binlog内容发给从库。从库上的I/O线程负责连接主库、拉取binlog并把拉回来的日志写入从库自己的Relay Log中继日志。从库上的SQL线程负责读取Relay Log并逐条在从库上重放执行。所以你能看到从库其实不是直接应用主库的binlog而是先经过了一层中继日志。这个设计是有道理的它把“拉取日志”和“执行日志”两个动作解耦了。即使SQL线程因为某条语句执行出错卡住了I/O线程仍然可以继续从主库拉取日志把主库的最新变更先保存下来避免主从之间差距越拉越大。为了帮助你更直观地记忆把这几个角色套到一个生活场景里就清楚了主库像一个出版社binlog就是出版社每天发出的报纸清样从库那边有两个人一个人I/O线程负责每天去出版社把清样拿回来放在自己办公桌上另一个人SQL线程负责把桌上的清样一份份印出来发出去。拿报纸的人和印报纸的人互不干扰各干各的活。1.2 异步、半同步、同步三种复制模式的区别MySQL主从复制默认就是异步的而且这种模式用了很多年。异步的意思就是主库提交事务只需要自己写完binlog就算成功根本不管从库有没有收到、有没有应用。这个模式的优点是主库性能几乎没有额外损耗缺点是主从数据会有延迟窗口而且一旦主库宕机还没来得及传给从库的那部分binlog就永久丢失了。半同步复制Semisynchronous Replication是后面加进来的增强方案。它的逻辑是主库提交事务时必须等到至少一个从库确认“我已经收到binlog并写入了Relay Log”事务才算提交成功。注意这里说的是“收到并写盘”不是“执行完”。也就是说半同步保证的是日志不丢但不保证从库已经执行了这条日志。全同步复制严格来说MySQL原生没有实现需要靠MySQL Cluster或Group Replication这种方案来支持理解成本高、运维难度也大一般中小团队用不上。做技术选型的时候我的建议是如果你们团队对数据一致性要求特别高而且业务允许主库写入性能打一点折扣半同步是一个很均衡的方案。绝大多数场景我刚上手阶段就用默认的异步复制把重心放在监控和备份上反而更实在。1.3 位置点复制和GTID复制先了解各自的出处在正式开始配置之前还需要先搞清楚两种复制模式。早期MySQL主从复制是靠“文件名偏移位置”来定位同步进度的。比如我告诉你“日志文件叫mysql-bin.000003位置在154”从库就从这个位置往后拉日志。这种方式的问题在于一旦主库的binlog文件名或位置发生变化比如主库重启后日志序号跳了手动处理起来会非常痛苦。后来MySQL 5.6引入了GTIDGlobal Transaction Identifier全局事务标识符给每个事务分配了一个全局唯一的ID。从库直接用这个ID来跟踪进度就不需要关心“哪份文件里的哪个位置”这种底层细节了。GTID的引入让主从切换、故障恢复这类操作变得优雅了不少。这篇实操我以MySQL 8.0为例使用GTID模式来搭建因为8.0里GTID已经是默认开启的也代表着现在的主流做法。后面我会把两种模式的关键差异单独列个表。2. 搭建前需要想清楚的几个决定2.1 实验环境怎么准备最省事我是建议新手直接拿Docker开两个MySQL容器来练手的。原因很简单本地电脑上通常只装了一个MySQL你要做实验就得再装一个装的过程本身就够折腾一壶了还要考虑版本兼容、端口冲突、数据目录隔离各种问题。用Docker的话两个容器各跑各的互不干扰实验做完直接删掉重建干净利落。我自己实际使用的方案是这样的使用mysql:8.0官方镜像主库容器映射到宿主机的3307端口从库容器映射到宿主机的3308端口通过docker network建立一个自定义网络让两个容器互通如果你没有Docker环境直接在本地装两个MySQL实例也是可行的但记得用不同的端口和数据目录配置文件必须分开。同时还要注意两个实例的server_id必须不同这个参数如果重复从库启动复制时会直接报错退出。如果你想在生产环境搞最大的区别在于要提前确认网络连通性、防火墙端口、数据目录大小这些基础设施问题。复制原理和配置本身没有本质差别。2.2 主库配置这样写主库的my.cnf或/etc/my.cnf具体路径取决于你的安装方式里下面这些参数是最低要求[mysqld] server_id 1 log_bin mysql-bin binlog_format ROW gtid_mode ON enforce_gtid_consistency ON重点解释几个参数server_id每台实例必须唯一。这是主从之间互相识别的身份标识取值范围是1到2的32次方减1。log_bin开启二进制日志。这个不用多说没binlog就没得复制。binlog_format ROW行格式记录。相比于早期的STATEMENT格式记录SQL语句ROW格式记录的是具体被修改的行数据一致性更好也是目前官方推荐的方式。唯一的缺点是日志体积会大一些但从安全性和一致性角度这个代价是值得的。gtid_mode ON和enforce_gtid_consistency ON开启并强制GTID模式。8.0默认就是开着的如果你用的是5.7需要手动加。另外还有一个容易被忽略的参数binlog_expire_logs_seconds在5.7及之前叫expire_logs_days。它控制binlog保留多久。我建议生产环境至少保留2到3天因为你永远不知道哪天需要从binlog里找回误删的数据。但保留太久又会把磁盘占满所以最好根据你们磁盘大小和写入量来定。2.3 从库配置注意这几个点从库的配置相对简单但有几个关键差异[mysqld] server_id 2 log_bin mysql-bin binlog_format ROW gtid_mode ON enforce_gtid_consistency ON read_only ON这里最值得说的是read_only。从库通常只承担读流量直接把它设为只读是最安全的做法可以防止有人不小心在从库上执行了写操作导致主从数据不一致。但要注意read_only对超级管理员账号root是不生效的。如果你需要彻底禁止写入那得配合super_read_only一起使用。在生产环境中我会把从库的super_read_only也打开除非做维护操作时临时关掉。从库还推荐设置relay_log参数。虽然不设置也能跑MySQL会自动生成一个名字但如果你需要做中继日志故障恢复或者有多套复制链路同时跑在一个实例上显式命名会方便很多。还有一个实用的建议是设置log_slave_updates ON。这个参数让从库把自己的变更也写进binlog。如果以后你想做级联复制A - B - C或者想把从库提升为主库后它的从库还能继续同步这个参数就是必需的。3. 从零搭建一套MySQL主从复制的完整实操3.1 主库上的初始化和授权步骤假定你已经把两个MySQL实例都跑起来了主库能通过mysql -h127.0.0.1 -P3307 -uroot -p连上从库用3308端口连上。第一步在主库上创建用于复制的专用账号。我强烈不建议用root账号来跑复制权限太大一旦密码泄露后果严重。复制账号只需要REPLICATION SLAVE这一个权限就够了CREATE USER repl% IDENTIFIED BY YourStrongPassword; GRANT REPLICATION SLAVE ON *.* TO repl%; FLUSH PRIVILEGES;注意这里的%意思是允许从任意主机连接。生产环境为了安全最好把%限定成从库的实际IP比如repl192.168.1.100。第二步确认主库的binlog已经开启并记录下当前的GTID位置。GTID模式下我们不再关心File和Position字段但了解Executed_Gtid_Set还是很有用的SHOW MASTER STATUS;正常情况下你能看到类似这样的输出File: mysql-bin.000003 Position: 157 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 6d4e5a71-xxxx-xxxx-xxxx-xxxxxxxxxxxx:1-5里面的那一长串UUID就是这台实例的server_uuid数据字典里生成的冒号后面的数字表示已经执行过多少个GTID事务。第三步如果你是从已有数据开始搭建而不是两台全新实例你需要先把主库现有的数据全量备份并恢复到从库。最稳妥的方式是用mysqldumpmysqldump -h127.0.0.1 -P3307 -uroot -p --all-databases --single-transaction --master-data2 backup.sql--single-transaction表示用InnoDB事务一致性快照来备份不会锁表--master-data2会把CHANGE MASTER TO语句以注释形式写进备份文件里方便我们确认点位信息。在8.0里这个文件顶部会有类似这样的注释-- CHANGE MASTER TO MASTER_LOG_FILEmysql-bin.000003, MASTER_LOG_POS157; -- GTID enabled, GTID set: 6d4e5a71-xxxx-xxxx-xxxx-xxxxxxxxxxxx:1-5如果你是全新空库直接跳过备份恢复这一步就行。3.2 从库上的配置和启动复制从库上先把备份导入如果做了备份的话mysql -h127.0.0.1 -P3308 -uroot -p backup.sql接着在从库上执行复制配置。MySQL 8.0.23及之后版本官方把语法改成了CHANGE REPLICATION SOURCE TO旧版的CHANGE MASTER TO仍然能用但不推荐。我这里写新语法CHANGE REPLICATION SOURCE TO SOURCE_HOST 主库IP或容器名, SOURCE_PORT 3306, SOURCE_USER repl, SOURCE_PASSWORD YourStrongPassword, SOURCE_AUTO_POSITION 1;这里的关键点是SOURCE_AUTO_POSITION 1它的意思就是告诉从库我们使用GTID自动定位同步位置你不需要我手动指定binlog文件名和偏移量。这是GTID模式比传统位置点模式省心的地方。然后启动复制START REPLICA;注意老版本里这里是START SLAVE;8.0.23之后推荐用START REPLICA;但两个都能用。启动之后第一件事就是确认复制状态是不是正常的SHOW REPLICA STATUS\G重点看这几个字段Slave_IO_Running: YesI/O线程正常说明能连上主库并拉取binlog。Slave_SQL_Running: YesSQL线程正常说明中继日志在正常重放。Seconds_Behind_Master: 0从库落后主库0秒说明数据完全追平。如果Slave_IO_Running是Connecting说明网络不通、账号密码错误或者SOURCE_HOST配置有问题。如果Slave_SQL_Running是No说明重放过程中遇到了SQL执行错误Last_SQL_Error字段会给出具体的错误信息。3.3 验证同步效果的正确姿势配置完成后验证一下同步是否真的生效。在主库上建一张测试表并插入数据CREATE DATABASE test_sync; USE test_sync; CREATE TABLE user ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); INSERT INTO user(name) VALUES (张三), (李四);然后到从库上查询USE test_sync; SELECT * FROM user;如果能查到这两条记录说明同步链路已经通了。这里我强烈建议你再做一个反向测试在从库上试着插入一条数据。正常情况下会报错The MySQL server is running with the --read-only option so it cannot execute this statement。这个报错是好事说明你的从库只读配置生效了。还有一个细节值得关注此时去从库上执行SHOW REPLICA STATUS\G看到Seconds_Behind_Master依然为0说明主库的每个操作从库都及时跟上了。3.4 数据一致性校验不能省从库能同步不代表数据就百分之百一致。SQL线程重放过程中万一遇到类型转换、字符集差异、SQL模式不同可能导致结果和主库不完全一样。常用的一致性校验工具是pt-table-checksum它来自Percona Toolkit。使用前要先在从库临时把read_only关掉因为校验需要在所有实例上建临时表执行完再恢复pt-table-checksum --host主库IP --userroot --passwordxxx --databasestest_sync --nocheck-replication-filters工具会在每个表上计算校验和然后对比主从上的结果。如果输出里有DIFFS不为0的行就说明存在不一致需要进一步用pt-table-sync修复。不过要提醒一句这种工具学习成本不低新手阶段可以先用简单的计数核对比如SELECT COUNT(*)对比每个表行数等到真正有把握了再上pt工具也不迟。4. 主从复制里那些绕不开的坑4.1 为什么从库状态全是No最让人头疼的问题之一就是SHOW REPLICA STATUS里Slave_IO_Running和Slave_SQL_Running双双显示No。出现这种情况我建议按顺序排查检查网络连接。从库能不能ping通主库telnet 主库IP 3306是否通如果用的是Docker容器确认两个容器在同一个自定义网络里。检查复制账号。在主库上用repl账号手动连一次确认密码正确、权限正确mysql -h主库IP -urepl -p -e SHOW DATABASES;。检查防火墙。主库的3306端口有没有对外开放云服务器安全组是否放行了看错误日志。MySQL的错误日志会记录复制失败的详细原因通常位于数据目录下的*.err文件或者SHOW REPLICA STATUS里的Last_IO_Error字段。一个很常见的场景是你本机上之前装过MySQL端口和现在的容器端口冲突导致主库端口没起来。我遇到过好几次排查了半天最后发现主库压根没监听在预期的端口上。4.2 那些高频报错到底怎么处理下面这几个错误代码基本覆盖了主从复制最常见的故障。Error 1062主键冲突从库重放binlog时发现要插入的数据主键已经存在。原因通常是从库上被手动写入过数据或者是之前同步中断后又从错误的位置点继续复制导致部分事务重复执行。解决方法先确认从库是否被人写绕过。如果是重复执行导致的找到具体冲突的数据行手动删除从库上多余的那条然后用START REPLICA继续。如果数据差异太大考虑重新搭建从库更省事。Error 1032记录不存在从库重放UPDATE或DELETE时找不到对应的行。原因可能是从库之前漏掉了某些变更比如binlog被清理过导致数据状态和主库不一致。解决方法是先找出缺失的数据从主库导出对应表的数据补到从库然后继续复制。如果这种错误频发说明你们的复制链路健康度很差建议认真检查是不是有人为操作干扰了从库。Error 1236binlog读取失败I/O线程从主库拉binlog时失败。典型原因是binlog被purged清理了从库请求的位置已经不存在。这种情况没有太好的“续命”办法只能重新做一次全量备份恢复到从库然后重新配置复制。这也是我前面强调生产环境binlog要保留至少2到3天的原因否则从库宕机时间一旦超过了保留周期回退手段都没有。Error 1594中继日志损坏SQL线程读取中继日志时发现日志损坏。通常是磁盘故障或MySQL非正常退出导致的。处理思路是先停止复制然后清空中继日志让I/O线程重新拉取STOP REPLICA; RESET REPLICA ALL; CHANGE REPLICATION SOURCE TO ...重新配置 START REPLICA;4.3 主从延迟是怎么产生的主从延迟Seconds_Behind_Master大于0且持续增大是生产环境最常遇到的问题。常见原因有这么几类大事务比如一次性UPDATE几百万行数据从库要一条条重放速度自然跟不上。从库硬件性能差主库是8核16G从库是2核4G延迟几乎是必然的。从库上有其他繁重查询在跑比如有人把耗时特别长的统计查询放到从库上占用了大量CPU和IO资源。网络延迟高主库和从库不在一个机房拉取binlog本身就要消耗时间。排查延迟第一件事就是确认Seconds_Behind_Master数值有没有在涨。如果这个值稳定不变说明跟上了如果持续上升说明从库瓶颈不在网络而在本地执行速度。最有效的应对措施是拆分大事务、避免对全表做一次性更新、从库上合理安排批量任务的时间窗口。如果业务上允许还可以考虑升级从库配置。4.4 从库被误写入导致复制断开怎么办进行维护操作或者做实验时偶尔会忘了开read_only在从库上直接写了一条数据。这会导致后续主库对该行数据的UPDATE或DELETE重放时出现1032错误复制直接停下。遇到这种情况我的处理步骤是先看Last_SQL_Error定位到是哪个表、哪条语句执行失败。手动在从库上把那条多余的“脏数据”删掉或者改成和主库一致的状态。执行START REPLICA让SQL线程继续。干了这事之后用SHOW REPLICA STATUS确认状态已经恢复Yes。这种错误属于人为操作失误修复本身不难但最好在团队里定个规矩从库绝对禁止手动写入所有维护操作要留痕。5. 传统位置点复制和GTID复制对比5.1 两种方式各自的使用场景为了让你方便做技术选型我把两种方式的关键区别拉个表格对比项位置点复制FilePositionGTID复制定位方式文件名 偏移量全局事务编号配置复杂度需要手动记录点位自动定位配置简单主从切换需要找到新主的正确点位无需关心点位自动衔接故障恢复容易因位置对不上而出错恢复过程更平滑版本要求MySQL 5.5及更早MySQL 5.6及以上8.0默认适用建议老项目、跨版本迁移的临时方案新项目首选生产环境推荐需要特别强调的是如果你准备做一主多从的架构或者考虑用MHA、Orchestrator这类高可用工具来帮你做主从切换GTID模式几乎是必选。因为这些工具在切换时需要自动定位同步点用GTID能少掉很多麻烦。5.2 5.7和8.0搭建时的差异MySQL 5.7的配置流程和8.0很接近只在几个细节上有区别5.7默认没有开启GTID需要在配置里手动加gtid_mode ON和enforce_gtid_consistency ON。5.7用的是CHANGE MASTER TO和START SLAVE8.0.23之后才优先用CHANGE REPLICATION SOURCE TO和START REPLICA。8.0默认身份认证插件是caching_sha2_password创建复制账号时如果从库版本较老比如5.7可能需要在创建用户时指定IDENTIFIED WITH mysql_native_password BY xxx来兼容。新版本之间则没有这个问题。如果你们公司还有老版本的MySQL 5.7实例在服役搭建跨版本复制8.0主库 - 5.7从库时请务必先查一下官方文档的版本兼容矩阵。虽然8.0的主库一般不能向下复制到5.7的从库因为binlog格式有差异但5.7主库复制到8.0从库通常没问题。这些约束在动手前确认清楚能省下后面一堆排查时间。6. 搭建完之后的运维注意要点6.1 从库到底要不要做备份这个问题的答案很容易被忽略从库必须做备份。很多人觉得“从库已经有主库的全部数据了为什么还要单独备份”因为备份的核心作用是应对误删和故障恢复。如果你只在主库上做备份万一主库磁盘故障或者被人误操作你的备份数据和故障点之间的所有变化都会丢失。而从库上做备份至少有两点好处第一可以在不打扰主库的情况下随时执行备份任务。主库正在扛业务写入你跑一个全量备份会增加IO压力影响线上性能。把备份作业放到从库上主库完全无感。第二如果主库彻底坏掉了你手上有一个从库的备份配合binlog可以做数据找回即使这个从库也出了问题只要备份还在数据就有兜底。备份工具我用得比较多的是mysqldump和XtraBackup。前者适合中小型数据库逻辑备份简单可靠后者适合大数据量场景物理备份速度快支持增量备份。我的习惯是每天做一次全量备份保留最近7天同时在系统层面做一次跨机房的冷备。6.2 主从切换时最容易翻车的地方主从切换就是把原来从库的角色提升为主库让业务流量打到新主库上。这个操作听起来简单但实际操作时很容易出错。第一步是要把从库的数据追平。切换前停止主库的写入或者把主库设为只读然后等待Seconds_Behind_Master变为0。这个等待动作是很多人会跳过的跳过之后就会导致一部分数据丢失。第二步是确认从库已经执行完所有relay log里的日志。可以执行SELECT GLOBAL.gtid_executed;确认GTID集合已经包含主库上所有事务后才意味着从库的数据是完整的。第三步在从库上执行STOP REPLICA; RESET REPLICA ALL; SET GLOBAL read_only OFF; SET GLOBAL super_read_only OFF;同时还要检查从库的log_bin和log_slave_updates参数是否已经设置为ON前面配置时建议加的如果没加切换后新的主库就没有binlog后续没法再搭新从库。切换完成后记得更新应用侧的数据库连接配置指向新的主库如果之前有其他的从库也要把它们的SOURCE_HOST重新指向新主库的地址。6.3 我用这套方案过程中的一点实际体验做技术方案和写业务代码不一样数据库相关的方案没有“差不多就行”的说法。一套主从复制环境从搭好到稳定运行中间需要持续地观察和调整。我第一次在生产环境搭建主从复制时就踩过一个比较典型的坑。当时用的是半同步复制没有提前确认半同步插件在两台实例上都装好了。结果是复制链路表面上看一切正常I/O线程和SQL线程都在运行但主库提交事务时完全走的是异步逻辑半同步的可靠性保障根本没有生效。后来通过SHOW STATUS LIKE Rpl_semi_sync_master_status查状态才发现主库上的半同步状态一直是OFF。所以每次配完把关键状态逐个确认一遍比盲目相信“配置写了就生效”靠谱得多。另外监控这块也值得多说一句。主从复制不是配完就一劳永逸的它是个持续运行的链路随时可能因为网络抖动、binlog清理、磁盘满而中断。我习惯写一个简单的监控脚本每隔几分钟去执行一次SHOW REPLICA STATUS用Python或者Shell检查Slave_IO_Running和Slave_SQL_Running是否都是YesSeconds_Behind_Master是否超过阈值。一旦异常立刻告警推送。别嫌麻烦主从断了好几个小时没人发现这种事真的发生过太多次了。主从复制本身不复杂难的是把每个细节都考虑到从账号权限的收拢、只读策略的落地到binlog保留时长的规划、切换流程的演练再到日常监控的覆盖。把这套链路完整跑通一遍你对MySQL整体的运行机制都会有一个质的提升。