MySQL主从复制实战：原理、GTID配置与排错指南

做后端开发这些年MySQL 几乎是绕不开的存储底座。平时单机跑着没什么感觉一旦业务量上来或者你负责的系统开始有高可用这三个字的要求主从复制立马就会摆到面前。我最早接触主从配置是在一个读写分离的项目里当时照着一篇老教程机械地敲命令结果折腾了整整两天不是从库 IO 线程连不上就是 SQL 线程报 1062 主键冲突最后连 binlog 位点都搞丢了只能重来。后来把原理彻底吃透才发现主从配置本身并不难难的是理解它每一步在干什么、出了问题怎么定位。这篇文章就围绕 MySQL 主从复制的完整配置过程把原理、实操步骤、验证方法、日常维护和排错经验一次性说清楚。不管你是刚入行的运维还是需要自己搭库的后端按照下面的思路走一遍基本能避开我当年踩过的坑。1. 主从复制到底在解决什么问题先说点实在的。很多人一听到主从复制第一反应是让两个库数据一样这个理解没错但过于片面。主从复制本质上解决的是数据冗余和读写负载分离这两件事而不是高可用。1.1 读多写少的业务场景大多数互联网业务都是读多写少商品详情页每天被刷几百万次下单操作可能只有几万次。如果把所有读请求都压到一台 MySQL 上CPU 和 IO 迟早被打满。这时候主从复制就能派上用场主库只处理写请求从库分担读请求应用层根据 SQL 类型做路由。这样既降低了主库压力又让系统容量可以横向扩展——加一台从库就多一份读能力。1.2 数据备份与容灾主从复制的另一个价值在于容灾。主库物理机损坏、磁盘故障、机房断电这些极端情况谁都不想遇到但谁也不能保证不会发生。只要有从库实时同步着数据主库挂了之后可以立刻把从库提升为新主库业务中断时间能压缩到分钟级甚至秒级。这比每天凌晨备份一次出事了恢复昨天的数据要靠谱得多。1.3 主从不是万能的需要提前泼一盆冷水主从复制不等于高可用。它解决的是数据有备份和读能力扩展如果你需要的是自动故障转移、脑裂防护这种能力那得靠 MHA、Orchestrator 这类高可用组件来完成单纯的复制配置做不到。另外主从复制是异步的从库数据在任意时刻都可能比主库落后一点所以它不适合对一致性要求极高的场景比如账户余额查询。理解了这些你就明白为什么要有主从、什么场景该用主从。下面进入正题。2. 配置前的选型思考GTID 还是 binlog位点在动手之前必须做一个关键决策用 GTID 复制还是传统的 binlog位点复制。这一步选错了后面维护会非常痛苦。2.1 两种复制方式的原理对比传统方式是靠文件名偏移量定位同步位点。比如主库当前的 binlog 是mysql-bin.000012位置在 345从库就会告诉主库我从mysql-bin.000012的 345 位置开始给我发数据。GTIDGlobal Transaction Identifier则是给每个事务一个全局唯一 ID从库只要记录我执行到哪个 GTID 了主库就知道该发哪些事务给它。GTID 本质上是一个自动化的位点管理机制它解决了传统方式最头疼的问题找位点。2.2 为什么我推荐直接上 GTID传统方式最大的痛点是 failover 场景下的位点查找。主库挂了你想把从库提升成主库其他从库要重新指向它就得先在新主库上找到正确的 binlog 文件名和位置这是一件非常容易出错的事。GTID 模式下完全不用管这些只要指定MASTER_AUTO_POSITION1复制位点自动协商。另一个痛点是跳过错误。传统的SET GLOBAL sql_slave_skip_counter 1方式在 GTID 下变了变成了注入空事务逻辑更清晰定位也更容易。MySQL 5.7 以后 GTID 已经很成熟了5.7 和 8.0 都默认支持在线开启5.6 时代开了 GTID 就没法关5.7 开始可以动态开关。如果你还在用 5.6可能要考虑升级。如果你用的是云数据库 RDS 类的产品它通常默认就是 GTID。所以新项目我强烈建议直接用 GTID。2.3 什么时候继续用传统方式有一种情况可以继续用传统方式你用的是极老的 MySQL 版本5.5 或更早这种情况下没有 GTID 可选。另外如果公司内部有一套非常成熟的手工维护脚本全部基于 binlog 文件名位点写的那改成 GTID 需要同步改造脚本这种情况下可以分批迁移不急于一时。3. 主库配置实操参数、账号与数据导出这部分是干货中的干货。我假设有两台服务器主库 IP 假设为 192.168.1.10从库为 192.168.1.11MySQL 版本统一为 8.0.x。双方都用 Linux 系统。3.1 主库配置文件修改编辑主库的/etc/my.cnf不同发行版/安装方式路径略有差异可以用mysql --help | grep my.cnf查找在[mysqld]段下增加[mysqld] server-id 1 log-bin mysql-bin binlog_format ROW gtid_mode ON enforce_gtid_consistency ON log_slave_updates ON逐项解释server-id主从集群内每个节点的唯一标识不能重复。主库设为1从库建议设为2后面再加从库依次递增。log-bin开启二进制日志。这是复制的前提主库所有数据变更都会写入 binlog。binlog_format ROW行级复制记录每一行数据的变化。相比 Statement 格式ROW 格式更安全不会出现同一个函数在主从执行结果不一致的问题。gtid_mode和enforce_gtid_consistency开启 GTID 的核心开关两个必须同时设置。log_slave_updates从库记录自己的 binlog。如果这台机器将来可能由从库变主库或者后面要级联复制A→B→C这个必须开。8.0 中该参数默认开启5.7 需要手动确认。改完配置后重启主库 MySQLsystemctl restart mysqld3.2 创建复制专用账号复制账号需要REPLICATION SLAVE权限。这里强调一点永远不要用 root 账号做复制权限最小化是基本功。用 root 做复制一旦从库被入侵主库也跟着完蛋。CREATE USER repl192.168.1.% IDENTIFIED BY YourStrongPasswd; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl192.168.1.%; FLUSH PRIVILEGES;REPLICATION SLAVE是从库连接主库拉取 binlog 的权限REPLICATION CLIENT用于执行SHOW MASTER STATUS、SHOW SLAVE STATUS这类命令、监控复制状态。主机限制我写成了网段192.168.1.%比%安全得多。3.3 数据一致性的核心问题很多人配置主从失败都是栽在这一步先有数据后有复制。如果主库已经有存量数据了而你直接配置从库开始同步从库会和主库对不上——因为新事务是在旧数据的基础上产生的从库没有那些旧数据重放时必然报错。所以标准流程是先把主库现有的数据导出来导入到从库然后再启动复制。备份工具我推荐mysqldump简单直接。执行的时候有几个关键参数mysqldump -uroot -p --single-transaction --set-gtid-purgedON --all-databases --master-data2 dump.sql--single-transaction在 InnoDB 引擎下通过开启事务拿到一致性快照备份过程中不影响线上写入。--set-gtid-purgedON导出时把当前主库已经执行过的 GTID 集合写进备份文件里这样从库导入后就知道我之前同步到哪了。--master-data2在备份文件里记录主库 binlog 位点信息虽然 GTID 模式下不是必需但多一份信息排查问题时很有用。注意--all-databases会包含系统库。如果你的库很小也可以只导出业务库。另外如果主库有大量数据几十 GB 甚至更大mysqldump会比较慢可以考虑用 Percona XtraBackup 做物理备份但那个工具的介绍是另一个话题了这里先用mysqldump保证流程能跑通。数据导出后把这个 dump.sql 传到从库服务器上。4. 从库配置实操导入数据与启动复制链路现在轮到从库这边了。整个过程分三步改配置、导数据、启动复制。4.1 从库配置修改编辑从库的/etc/my.cnf同样在[mysqld]段下[mysqld] server-id 2 log-bin mysql-bin binlog_format ROW gtid_mode ON enforce_gtid_consistency ON log_slave_updates ON read_only ON注意server-id必须和主库不同。read_only ON是让从库拒绝非 super 权限的写操作防止业务误写入导致主从不一致。提示read_only对拥有 SUPER 权限的用户无效如果是专职 DBA 管理可以不用管如果开发人员也有账号记得别给他们 SUPER 权限。改完重启从库systemctl restart mysqld4.2 导入全量备份把主库传来的 dump.sql 导入从库mysql -uroot -p dump.sql这一步做完从库应该已经有了和主库一致的历史数据。可以用SHOW DATABASES;简单确认一下业务库名是否存在。4.3 配置复制链路在从库上执行CHANGE MASTER TO语句8.0 之后的写法稍有差异MASTER_HOST等参数名带上了MASTER_前缀这里用 8.0 的标准写法CHANGE MASTER TO MASTER_HOST192.168.1.10, MASTER_PORT3306, MASTER_USERrepl, MASTER_PASSWORDYourStrongPasswd, MASTER_AUTO_POSITION1;关键点是MASTER_AUTO_POSITION1这告诉从库别告诉我从哪个位点开始你根据我已有的 GTID 集合自动找。启动后从库会先和主库交换 GTID 集合主库会把从库缺失的所有事务发给它。然后启动复制START SLAVE;4.4 验证复制是否正常运行状态检查命令SHOW SLAVE STATUS\G重点看两个线程Slave_IO_Running: Yes Slave_SQL_Running: Yes两个都必须是 Yes。IO 线程负责从主库拉 binlogSQL 线程负责把拉回来的日志在从库上重放。这俩就像一个搬运工和一个施工队哪个停了复制就停了。另外两个字段也值得看Seconds_Behind_Master从库落后主库的秒数。0 是最理想状态。Retrieved_Gtid_Set和Executed_Gtid_Set已接收和已执行的 GTID 集合。如果两个集合越来越大且不断更新说明在持续同步。做一次写入验证在主库建个测试表插几条数据在从库查一下能不能马上看到。这里我建议用业务库来测避免只在测试库验证、上线之后才发现业务库没同步的尴尬。5. 复制状态监控与两个常见误区复制搭好了不等于一劳永逸。主从复制在日常运行中需要持续监控我最怕看到的现象是同事隔了一个月才来看一次SHOW SLAVE STATUS结果发现线程早就停了主从数据差了几十万行还不知道从什么时候开始的。5.1 需要重点盯的输出字段SHOW SLAVE STATUS\G的输出有不少行日常巡检我一般只看这几个字段说明关注点Slave_IO_RunningIO 线程状态必须为 YesSlave_SQL_RunningSQL 线程状态必须为 YesLast_IO_Errno / Last_IO_ErrorIO 线程最近错误不为 0 时必须处理Last_SQL_Errno / Last_SQL_ErrorSQL 线程最近错误不为 0 时必须处理Seconds_Behind_Master同步延迟秒数持续增大需重点关注Master_Log_File / Read_Master_Log_Pos已拉取到的 binlog 位点和主库对比可确认滞后量Retrieved_Gtid_Set / Executed_Gtid_SetGTID 执行进度确认是否持续增长日常巡检可以用脚本定期跑SHOW SLAVE STATUS把Slave_IO_Running和Slave_SQL_Running的结果抓出来做告警。监控平台如果能直连数据库也可以用SHOW SLAVE STATUS的结果做指标采集。5.2 Seconds_Behind_Master 是 0不等于完全实时这里有个新手常见的误读。Seconds_Behind_Master计算的是从库当前执行时间与主库当前时间之差但它有个天然的缺陷如果 IO 线程已经停了很久SQL 线程执行完了所有已拉取的日志这个值会显示为 0让你以为同步正常。实际上主库早就产生了新数据只是从库压根没拉。所以判断同步状态不能只看延迟秒数。更可靠的标准是对比 GTID检查从库的Executed_Gtid_Set是否和主库的Executed_Gtid_Set用SHOW MASTER STATUS查看相同。如果相同说明从库确实执行到了主库的最新事务。5.3 别把主从延迟完全归咎于网络排除网络因素后从库延迟的一个大坑是大事务。比如在主库执行了一条UPDATE影响了几百万行这条语句在 binlog 里可能是一个巨大的事务从库重放需要很长时间。主库执行只要几秒从库可能要执行几分钟延迟瞬间拉大。用 ROW 格式后这个大事务在 binlog 里的体积更大因为每一行变更都要记录。针对这种情况没有特别取巧的办法只能从业务层面控制大事务比如分批更新。另一个常见原因是从库硬件配置比主库差如果主库是 32 核 128G从库只有 4 核 8G延迟几乎是必然的。从库硬件不说和主库完全一致至少差距不能太大。6. 从零到一踩过的坑遇到这些错误怎么排查主从复制的排错是我最想分享的部分。配置过程本身是机械性的但排错需要真正理解机制。下面几个错误是出现频率最高的逐一说明。6.1 主键冲突Error 1062现象Slave_SQL_Running: NoLast_SQL_Error报Duplicate entry xxx for key PRIMARY。原因基本就一个从库上有数据而主库也恰好有这条数据重放时发生冲突。常见场景是从库被业务误写入过数据或者从库是从某个时间点的备份恢复的恢复后又手动插入过本该由主库同步的数据。处理思路先确认冲突数据的来源。如果是从库误写导致的那要清理掉从库上的脏数据然后重启 SQL 线程STOP SLAVE; SET GTID_NEXT冲突事务的GTID; BEGIN; COMMIT; SET GTID_NEXTAUTOMATIC; START SLAVE;这是 GTID 模式下跳过单个事务的标准姿势——注入一个空事务让 GTID 集合假装已经执行过。注意这种方式只能用于确定这一条事务不需要执行的情况绝不能批量跳过否则主从数据会永久不一致。6.2 日志丢失或找不到Error 1236 / 13114现象IO 线程报错Last_IO_Error提示找不到 binlog 文件或者Got fatal error 1236。原因最常见的是从库停机时间过长主库的 binlog 已经清理掉了。MySQL 的 binlog 有保留期expire_logs_days或 8.0 的binlog_expire_logs_seconds如果从库断连时间超过了保留期从库请求的 binlog 文件已经不存在主库自然会报错。处理方案这种情况下从库只能重新搭建。把主库当前数据重新导一次从库重新CHANGE MASTER TO然后START SLAVE。这提醒我们一个在实际运维中的教训主库的 binlog 保留时间一定要大于从库可能的最大断连时间。尤其是那些不常被关注的从库很多人只在故障时才想起来看它一眼如果 binlog 刚好被清了就只能全量重搭。6.3 IO 线程一直显示 Connecting现象Slave_IO_Running: ConnectingLast_IO_Error显示连接超时或被拒绝。按顺序排查这几项网络连通性telnet 192.168.1.10 3306看端口通不通。账号权限确认主库上repl账号存在、主机限制配置正确。从库服务器实际出口 IP 是否在账号允许的网段内——这个很容易被忽略跨云环境下出口 IP 不是你登录那台机器的内网 IP。防火墙和安全组云服务器的安全组是否放行了 3306 端口Linux 本机防火墙firewalld/iptables是否拦截。主库server-id是否配置binlog 是否开启。SHOW MASTER STATUS如果返回空说明主库没开 log-bin。6.4 从库只读设置引发的坑有同事问过我我明明设置了read_only ON为什么业务还能在从库上写入原因read_only只限制普通用户拥有 SUPER 权限的用户不受限制。如果业务账号不小心有了 SUPER 权限写入照样畅通无阻。8.0 还提供了super_read_only这个连 SUPER 用户都限制写入适合在从库上开启。但使用前要确认是否有管理任务需要临时写入从库比如某些监控系统要写心跳表super_read_only会把它一起挡住。7. 进阶配置半同步复制与多源复制的取舍基础主从跑通之后很多人会想更进一步。这里聊两个常见的增强方案。7.1 半同步复制Semisynchronous Replication前面反复强调MySQL 默认的异步复制存在延迟和丢失风险。半同步复制的作用是主库在提交事务时会等待至少一个从库确认已经接收到 binlog才向客户端返回成功。这能显著降低主库宕机但事务尚未到达从库导致的数据丢失风险。启用方式MySQL 8.0INSTALL PLUGIN rpl_semi_sync_master SONAME semisync_master.so; INSTALL PLUGIN rpl_semi_sync_slave SONAME semisync_slave.so; SET GLOBAL rpl_semi_sync_master_enabled 1; SET GLOBAL rpl_semi_sync_slave_enabled 1;注意半同步并不是 100% 强一致。如果在等待确认的过程中超时主库会退化为异步模式继续提交。也就是说它是在可用性和一致性之间做了折中。另外如果从库只有一个且它就挂在半同步里那这个从库挂了主库写入性能会受到影响等待超时期间所有写事务阻塞。所以生产环境中至少要有两个从库或者做好半同步超时参数的调优。7.2 多源复制有些场景需要把多个业务库汇聚到一个分析库MySQL 5.7 开始支持多源复制即一个从库从多个主库同步数据。配置上和单源几乎一样只是每个复制通道要有不同的名字CHANGE MASTER TO MASTER_HOST192.168.1.10, ... FOR CHANNEL channel_a; CHANGE MASTER TO MASTER_HOST192.168.1.12, ... FOR CHANNEL channel_b; START SLAVE FOR CHANNEL channel_a; START SLAVE FOR CHANNEL channel_b;多源复制有个隐患如果不同主库上有相同库名的业务库重放时会发生覆盖冲突。所以多源复制一般用于不同业务库合并而不是相同库合并。7.3 读写分离的正确落地方式复制搭好之后应用层怎么做读写分离这是另一个常见问题。从代码层面来看最简单的做法是配置多数据源——写操作走主库读操作走从库。如果是 Java 生态可以借助中间件或者框架的读写分离能力。更彻底的做法是引入数据库代理代理层自动解析 SQL 并路由到合适的实例应用层无感知。但无论哪种方式都要接受一个问题从库数据有延迟刚写完主库的数据立刻去从库查可能查不到。针对这个可以在应用层对强一致场景做特殊处理比如强制读主库。8. 关于数据一致性验证与日常巡检清单最后分享一套我在实际维护中固定下来的巡检清单。主从搭建完、运行一周后我会按下面的顺序检查一次之后每个月例行执行。8.1 数据一致性巡检工具层面MySQL 官方生态里有pt-table-checksumPercona Toolkit 家族它能对主从数据做一致性校验找出哪些表的数据不一致。它的原理是在主库上按行做 checksum 计算把计算结果和从库对比。这个工具跑起来有一定开销建议在业务低峰期执行。手工巡检时可以对关键大表做COUNT(*)对比。但这里提醒一下COUNT(*)只对比了行数行数一致不代表数据一致——某一行数据被改了但行数没变这种差异COUNT(*)发现不了。所以重要表的校验还是推荐pt-table-checksum或者定期抽样的CHECKSUM TABLE。8.2 日常巡检清单我自己的巡检表大概长这样检查SHOW SLAVE STATUS\G确认两个线程是 Yes。检查Last_IO_Error和Last_SQL_Error是否为空。检查Executed_Gtid_Set是否等于主库SHOW MASTER STATUS的Executed_Gtid_Set。检查磁盘空间binlog 增长是否异常。从库开启 binlog 的情况下空间占用会一直增长。检查主从机器的负载和慢查询从库延迟很多时候是慢查询抢占了 IO。周期性的数据一致性校验先用pt-table-checksum有差异再找具体表。8.3 主库 binlog 保留时长这个是最容易忽略的隐患。主库的 binlog 如果保留太短从库断连时间稍长就会触发前文说的 1236 错误。如果保留太长磁盘空间又受不了。8.0 中可以通过binlog_expire_logs_seconds设置保留时长比如设置 7 天604800 秒。这个值要根据你从库的最大容忍停机时间来决定而不是凭感觉定。配置示例[mysqld] binlog_expire_logs_seconds 6048008.4 从库宕机后的恢复流程从库机器重启后如果 MySQL 是开机自启动的通常复制线程也会自动恢复。但也有恢复不了的情况尤其是异常断电或磁盘错误。这时候按照下面的步骤处理登录从库执行SHOW SLAVE STATUS\G看报错信息。如果是网络闪断导致的 IO 线程中断执行START SLAVE;即可通常会自动重连。如果 SQL 线程报错根据错误类型决定是跳过事务还是重新搭建。如果 IO 线程报 1236binlog 文件不存在这种情况只能重搭从库做法就是本文第 4 节的全量流程重新走一遍。8.5 重搭从库的正确姿势重搭从库时有些细节可以提高效率先记录当前主库的 GTID 集合用mysqldump全量备份导入从库后启动复制。因为 dump 文件里带了SET GLOBAL GTID_PURGED信息从库启动复制时会自动从 GTID 集合之后的位置开始拉取无需手动指定位点。这里特别提醒如果备份文件很大导入耗时长可以先把 dump 文件用 gzip 压缩再传到从库网络传输会快很多gzip dump.sql scp dump.sql.gz root192.168.1.11:/data/ gunzip dump.sql.gz mysql -uroot -p dump.sql我在实际项目中用这套流程重搭过多次从库最小的库几十秒搞定几十 GB 的库压缩后传输也能明显节省时间成本。主从配置不是敲完命令就结束的事情它更考验的是持续维护的能力。希望这篇文章能把原理到实操的每个环节都讲透让你在配置主从时不只是照着命令抄而是知道每一步为什么这么做、出了问题时往哪个方向排查。如果严格按照上面的流程走下来再遇到主从问题你应该能在几分钟内定位到根因而不是像我当年那样抓瞎一整天。