1. 从为什么要做主从说起这不是炫技是真的需要先交代一个背景。我在生产环境里维护过多套 MySQL 集群从单库扛到主从再到后来的半同步和 GTID 自动切换一路踩坑踩过来的。这个项目标题是“mysql的主从配置”但我更想先花点篇幅聊聊“为什么要做主从”。很多人一上来就照着网上的教程敲CHANGE MASTER TO敲完看到Slave_IO_Running: Yes就以为大功告成——这恰恰是问题最大的时候。MySQL 主从复制本质上就是一台主库Master把自己产生的所有变更记录写到二进制日志binlog里然后另一台或多台从库Slave通过网络拉取这些日志依次在本地重放relay log最终达到主从数据一致的状态。这套机制解决的核心问题有三个一是读写分离读流量可以打到从库上减轻主库压力二是高可用主库挂了可以手动或自动切换到从库缩短业务停机时间三是数据备份与容灾虽然不能替代真正的物理备份但至少多了一份“热数据”兜底。适合读这篇文章的人有两类。一类是刚接触 MySQL 运维准备在公司内网搭一套主从环境作为读写分离或者高可用方案的落地基础。另一类是自己用云主机搭博客、搭站数据量虽然不大但希望加一层可靠性保障顺便把 MySQL 的复制机制搞明白。不管你是哪一类这篇文章都会从原理讲起然后给出一套可以直接抄作业的配置步骤最后把我在实际运维中遇到的坑一个个拆开讲清楚。2. MySQL 主从复制的核心原理与关键机制2.1 三个线程、两个日志主从复制的骨架主从复制不是魔法它依赖的是一套固定的组件。主库上有一个 IO 线程负责把变更写入 binlog从库上有两个线程一个 IO 线程负责从主库拉取 binlog 并转储到本地的 relay log一个 SQL 线程负责读取 relay log 并顺序执行其中的 SQL。这“一主二从三个线程”构成了复制的完整闭环。理解这个闭环之后你会明白很多问题其实都能从线程状态里找到方向。比如你执行SHOW SLAVE STATUS看到Slave_IO_Running: Connecting说明从库的 IO 线程根本没连上主库问题大概率在网络、账号权限、防火墙或者 server-id 冲突上而Slave_SQL_Running: No则说明 IO 线程没问题是 SQL 线程在执行中遇到了错误比如主键冲突、表不存在、字段类型不匹配等。后面排查问题我们还会展开细说。binlog 里记录的格式也有讲究。MySQL 支持 STATEMENT、ROW、MIXED 三种格式。STATEMENT 格式记录的是原始 SQL 语句优点是日志量小缺点是某些函数在从库重放时结果可能不一致ROW 格式记录的是每一行的实际变更准确可靠但日志量会明显变大MIXED 则是在这两者之间自动切换。我在生产环境里推荐使用 ROW 格式原因很简单它的同步精度最高虽然日志量大了但换来的是一致性和可用性。至于如何取舍需要结合自己的写入并发量和磁盘压力来判断。2.2 server-id 与日志参数配置里最容易被忽略的底层逻辑主从复制要正常工作每个节点必须有一个全局唯一的 server-id。这里要说透一个很多人不理解的细节server-id不光是标识身份用的它还是 MySQL 判断日志来源的重要依据。从库在拉取 binlog 时会通过 server-id 避免“自己产生的事件又被自己拉取”避免循环复制。如果两个节点配了相同的 server-id从库逻辑上会认为日志来自自己直接跳过表现出来的现象就是主库明明有更新从库却一动不动。主库上还需要打开 binlog 开关配置合理的日志格式。除此之外有两个参数我一定会重点强调sync_binlog和innodb_flush_log_at_trx_commit。前者控制每多少次事务写入后强制把 binlog 刷盘后者控制 InnoDB 事务日志的刷盘策略。默认情况下sync_binlog 1和innodb_flush_log_at_trx_commit 1是最安全的组合每次事务提交都刷盘虽然会带来一些 IO 开销但对数据一致性的保障是最强的。做主从复制时这两项如果不设置为 1主库崩溃时很可能出现 binlog 和 InnoDB 数据不一致从库就会跟主库对不上账。从库的relay_log参数同样重要。如果你在从库上不设置中继日志的文件名MySQL 会按照主机名自动生成主机名一旦变更后续重启可能出现找不到中继日志的问题。我习惯在从库配置里显式指定relay_log路径和文件名例如/var/log/mysql/mysql-relay-bin这样日志文件的位置和前缀都可控。2.3 为什么要用 GTID 方式而非传统方式我的选型理由传统方式配置主从需要在每个从库上记录主库 binlog 的文件名和偏移量MASTER_LOG_FILE和MASTER_LOG_POS一旦主库故障切换或者从库重建位置信息就会错位。GTID全局事务标识符打破了这种脆弱的依赖——每个事务在全局被分配一个唯一 ID从库采用MASTER_AUTO_POSITION 1后会主动基于 GTID 集合追踪和补齐差距来源关系变得非常清晰切换和回切都省事很多。MySQL 5.6 版本开始支持 GTID5.7 已经相当成熟8.0 更是把它作为复制的核心特性。如果你正在从零搭建一套主从环境我建议你跳过传统方式直接启用 GTID。当然这完全不意味着传统方式没有意义——你理解传统方式的过程就是理解复制位置、读 binlog、算偏移量的过程这些底层认识在排查问题时极其管用。就算你用 GTID也要会看SHOW MASTER STATUS定位位点因为 GTID 的本质就是“位点自动化”。我实际的工作习惯是能上 GTID 的全上新环境老环境在版本和时间允许的情况下批量迁移到 GTID。原因只有一个——少操心。GTID 模式下从库追主库、重建从库、切换主从都变得非常简单人为操作失误的概率大幅下降。作为标准配置选型GTID 方式基本是所有新项目的默认答案。3. 环境准备与基础参数规划3.1 版本选择5.7 还是 8.0我在这个项目里使用了两台 CentOS 7.9 服务器MySQL 版本选择了 5.7.44。这里需要说明一下如果你在做新项目我更推荐直接使用 MySQL 8.0 系列尤其是 LTS 版本。8.0 在安全性、字符集默认值utf8mb4、窗口函数、CTE公共表表达式、原子 DDL 以及复制机制上都有大幅改进。不过很多已有的业务系统还是跑在 5.7 上而且相当一部分老代码在 8.0 的默认认证插件caching_sha2_password下会连不上——如果你的业务端驱动比较旧5.7 仍然是务实的选择。版本一旦涉及跨版本复制就要格外谨慎。比如 5.7 和 8.0 之间做复制认证插件、默认字符集、SQL 模式都有差异不是不能做而是需要考虑的细节太多。生产环境我强烈建议主从版本保持一致至少保证大版本一致小版本尽量的接近或主库不低于从库。这个原则能帮你省掉大量莫名其妙的坑。3.2 主机规划与账号规划我的规划是这样的角色主机名IP 地址server-id用途Masterdb-master192.168.10.101写入主库Slavedb-slave192.168.10.112只读从库、备份源server-id 我习惯用“1、2、3”这样的序号排列简单直观方便记忆。如果你节点很多也可以用网段加编号的方式但核心原则只有一个——全局唯一千万别重复。复制专用账号我会单独创建不会用 root更不会复用业务账号。账号名随意但权限要有边界只需要REPLICATION SLAVE和REPLICATION CLIENT这两个权限就足够了。前者是从库连接主库拉取 binlog 的必需权限后者用于执行SHOW MASTER STATUS和SHOW SLAVE STATUS这类排查命令。这和我平时给业务账号分配权限的逻辑一脉相承——最小权限原则不给不需要的权限出了问题是能查到人、能控制住面的。3.3 从库初始数据怎么来千万不要直接拷贝数据文件在启动从库复制前主备库的数据必须一致。这里有一个分水岭如果主库上有存量数据你用mysqldump导一份出来恢复到从库再启动复制这是正规做法如果你用物理文件直接拷贝比如把整个 datadir 打包拿到从库那么在文件一致性、版本兼容性、二进制日志位置对齐上都会遇到麻烦我强烈不建议这么干。mysqldump 备份加复制启动的组合拳我用过太多次操作上有个非常关键的点我会在后面第 4 节里具体演示这里先记住一个原则备份开始前要确保主库 binlog 位置和备份内容是对齐的。使用mysqldump --single-transaction --master-data2这条命令会在备份文件头部以注释的方式自动记录主库当前的 binlog 文件名和位置启动从库时可以直接使用这个位置信息。如果你的表都是 InnoDB--single-transaction可以保证备份期间数据的一致性视图不影响业务写入。4. 主库配置实操my.cnf 参数与账号授权4.1 主库关键参数配置先在主库的/etc/my.cnf中加入以下配置[mysqld] server-id 1 log-bin /var/log/mysql/mysql-bin binlog_format ROW sync_binlog 1 innodb_flush_log_at_trx_commit 1 expire_logs_days 7逐个拆解一下参数的含义和我的选择理由server-id 1主库标识必须唯一。log-bin开启 binlog 并指定文件前缀。你不需要手动去创建这个目录MySQL 会自动创建但目录所在磁盘要有足够空间。日志量大的场景下binlog 所占空间很容易被低估。binlog_format ROW选择行级复制保证数据一致性。我前面分析过格式差异这里不再赘述。sync_binlog 1每次提交事务后立即刷 binlog 到磁盘。代价是额外的 IO 开销但换来的是主库崩溃时 binlog 不丢。innodb_flush_log_at_trx_commit 1每个事务提交都刷 redo log 到磁盘。和sync_binlog 1配合组成了主库“双 1”配置最安全但性能损耗最明显。expire_logs_days 7自动清理 7 天前的 binlog。生产环境建议用binlog_expire_logs_seconds按秒控制更精细。改完配置后重启 MySQL。这一步要在维护窗口做因为重启会中断连接。然后登录 MySQL执行SHOW MASTER STATUS;你会得到类似下面的结果------------------------------------------------------------ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | ------------------------------------------------------------ | mysql-bin.000001 | 154 | | | ------------------------------------------------------------File和Position就是当前主库 binlog 的“坐标”后面启动从库时要用到。所以这段输出最好立刻记下来或贴在文档里。如果你在修改配置前已经在主库上写过业务数据这个坐标可能就是某个非 4 或非 154 的值完全正常我们只需要保证从库从这个位置开始同步即可。4.2 创建复制专用账号登录主库 MySQL执行授权语句。注意MySQL 5.7 和 8.0 的授权语法有区别。5.7 中GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl192.168.10.% IDENTIFIED BY StrongPass2024; FLUSH PRIVILEGES;8.0 中因为默认认证插件是caching_sha2_password而部分旧版客户端驱动对它有兼容问题。如果应用或复制链路中涉及旧客户端我建议显式指定mysql_native_password例如CREATE USER repl192.168.10.% IDENTIFIED WITH mysql_native_password BY StrongPass2024; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl192.168.10.%;这里账号的主机段我写的是192.168.10.%意为只允许从库网段连接不要图省事授权成%缩小暴露面。密码也尽量用高强度口令毕竟这个账号拥有拉取 binlog 的权限泄露出去相当于把数据库的全部变更记录拱手让人。4.3 存量数据导出与导入主从数据一致性的关键一步如果主库已经有业务数据先用mysqldump做一个一致性备份到从库。操作如下在主库上执行mysqldump --single-transaction --master-data2 -uroot -p --all-databases /tmp/master_dump.sql--master-data2会把主库当前的 binlog 文件与位置写入 dump 文件开头并注释掉。这样我们恢复数据到从库后可以直接从这个注释里读取主库坐标。如果忘了记录坐标后面就只能通过SHOW MASTER STATUS去查但那时主库位置可能已经往后走了一大截需要重新对齐。把 dump 文件传到从库然后在从库上执行mysql -uroot -p /tmp/master_dump.sql此时从库的数据就和主库备份时点的数据一致了。接下来我们开始配置从库并启动复制。5. 从库配置实操server-id、连接主库与启动复制5.1 从库关键参数配置在从库的/etc/my.cnf中加入[mysqld] server-id 2 log-bin /var/log/mysql/mysql-bin binlog_format ROW relay_log /var/log/mysql/mysql-relay-bin read_only ON skip_slave_start ON这里有几个点要特别解释一下log-bin从库上也可以开启 binlog。这样做的好处是如果未来要从从库再次衍生出新从库或者从库升级为主库时binlog 都已经就绪。从库开启 binlog 会带来一点性能开销但物有所值。read_only ON从库设置为只读模式普通账号无法写入。但这个只读不限制拥有 SUPER 权限的账号复制线程也不受影响。这能很大程度上防止误操作写入从库避免主从数据不一致。skip_slave_startMySQL 重启后默认会自动启动复制线程这个参数会让它不要自动启动等我们手动确认后再启动便于排查。5.2 手动配置复制链路登录从库 MySQL执行CHANGE MASTER TO MASTER_HOST192.168.10.10, MASTER_USERrepl, MASTER_PASSWORDStrongPass2024, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS154;注意MASTER_LOG_FILE和MASTER_LOG_POS必须和你在主库执行SHOW MASTER STATUS时看到的一致或者和 dump 文件头部注释里记录的一致。如果随意填写从库会从错误的位置开始拉取日志轻则同步报错重则数据错乱。需要特别提醒的是使用 GTID 模式的话就不需要手动指定文件与位置而是换成MASTER_AUTO_POSITION 1。但使用自动定位前必须保证从库已经开启了 GTID 相关参数且gtid_executed集合与主库一致。传统方式和 GTID 方式的切换并不简单生产环境里不要随意混用。然后启动复制START SLAVE;在 MySQL 8.0 中习惯上使用START REPLICA;来替代START SLAVE;两者含义一致但新语法更规范。5.7 环境下继续用START SLAVE;没有问题。5.3 验证复制状态执行SHOW SLAVE STATUS\G关键看两项Slave_IO_Running: YesSlave_SQL_Running: Yes同时Seconds_Behind_Master: 0表示当前没有延迟。这两个线程都变成 Yes还不能说明整个链路稳定更可靠的验证方式是在主库建一张测试表、插入一条数据然后去从库查询确认。我在生产环境中每次配完主从都会做一次“写入—查询”的闭环测试确认无误才算交付。如果你看到的是Slave_IO_Running: Connecting或Slave_SQL_Running: No别慌这是主从配置最经典的坑下一节的排查方法直接对号入座就行。6. 常见问题排查与实战避坑指南6.1 Slave_IO_Running 一直 Connecting怎么办这个症状出现后我会按照下面这个顺序从外到内排查网络是否通。在从库机器上执行telnet 192.168.10.10 3306看主库的 3306 端口是否可达。如果超时检查防火墙和安全组策略。复制账号是否能登录。在从库上用mysql -urepl -p -h192.168.10.10手动测试连接验证账号密码正确、授权网段匹配。如果报Access denied去主库重新授权。主从 server-id 是否冲突。在从库上连上主库后执行SHOW VARIABLES LIKE server_id;确认主从不一致。主库 binlog 是否开启。执行SHOW VARIABLES LIKE log_bin;如果结果是 OFF说明主库根本没有开启 binlog复制无从谈起。主库是否设置了skip-networking这会导致从库无法通过网络连到主库。在 MySQL 8.0 场景下还有一个高频问题默认认证插件是caching_sha2_password而从库的 MySQL 版本或驱动不兼容。解决方式就是在主库给复制账号显式指定mysql_native_password我在前面授权部分已经写好了命令。6.2 Slave_SQL_Running 突然变成 NoSQL 线程停止最常见的原因是重放 SQL 时出错。用SHOW SLAVE STATUS\G查看Last_SQL_Error字段里面有具体报错。举两个我实际遇到过的例子Duplicate entry 1 for key PRIMARY说明从库上已经存在同主键的数据通常是因为从库被手工写过数据恢复时又插入重复记录或者主库发生过导致 SQL 重放重复的操作。Table db.t_table doesnt exist主库执行了从库没有的 DDL常见于没有全量初始化就启动复制或者只备份了部分库表。解决临时问题时可以用SQL_THREAD跳过某一条错误但这只能作为应急手段。语法如下STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER 1; START SLAVE;跳过之后一定要去分析是什么原因造成的错误否则修复下一个错误后面还有第三个第四个。如果是主键冲突类问题定位到具体表从主库导出该表数据覆盖到从库再恢复 SQL 线程即可。6.3 主从数据不一致预防比补救重要得多主从数据不一致很难彻底避免尤其是在业务高峰期、SQL 语句使用非确定性函数、或者从库被误写入的情况下。这里分享我在日常运维里坚持的几个习惯从库永久开启read_only并严禁业务账号写入从库。在主库上避免使用含UUID()、NOW()等非确定性函数的语句。ROW 格式下这类问题会大幅缓解因为记录的不是函数值而是实际变更后的行。定期做数据校验。常用工具如pt-table-checksum可以对比主从数据一致性及时发现潜在问题。规范 DDL。大表 DDL 务必在业务低峰执行避免复制延迟扩大。6.4 复制延迟的排查思路与常用技巧复制延迟在 MySQL 里是个老难题了。Seconds_Behind_Master只是粗略指标真正要结合主库上 binlog 写入量和从库上 relay log 剩余量综合判断。如果主库写入量很大但从库两条线程都是 Yes 且延迟持续上涨我一般从这几个方向排查从库磁盘 IO 是否成为瓶颈。多个从库同时从同一主库拉取日志或者备份、监控、批量处理都挤在同一台从库上IO 很容易被打满。大事务拖慢了 relay log 的重放。比如主库执行了一次数百万行的 UPDATE从库也会卡在相同的位置。从库和主库的性能配置是否差距过大。主库如果是高性能磁盘加大批量内存从库如果配置较差延迟就是必然的。表结构是否完全一致。字段类型不一致、索引缺失会导致从库执行效率远低于主库。从经验上说处理复制延迟的关键是“找到延迟发生在哪个环节”。IO 线程落后是网络或主库 binlog 输出慢SQL 线程落后是从库自身执行效率的问题。对症下药不要上来就盲目加并行复制线程数。在 MySQL 5.7 和 8.0 中从库并行复制是解决 SQL 线程延迟的有效手段。可以尝试调整slave_parallel_workers 4 slave_parallel_type LOGICAL_CLOCKLOGICAL_CLOCK允许从库并行执行同一时间窗口内、相互之间没有依赖关系的事务能够显著提升重放性能。设置后重启从库 MySQL 生效然后用SHOW SLAVE STATUS观察延迟是否下降。但要注意并行复制并不适合所有场景如果从库 CPU 和 IO 已经很紧张盲目加线程反而会雪上加霜。7. 从主从到高可用后续可以怎么扩展如果你的主从架构已经稳定运行接下来必然会思考一个问题主库如果某天突然宕机了怎么让从库快速顶上最简单的方式是手动切换。停掉从库的复制线程然后把从库提升为主库。但提升从库时有个细节要处理好如果从库开启了 binlog通常直接执行STOP SLAVE; RESET MASTER;然后让其对外提供服务。如果原来的主库恢复后想重新纳入复制环境你必须把它当作一个全新的从库来配置把数据重新对齐一次。手动切换的缺点是切换时间不可控业务感知明显。再进一步就是使用 MHA、Orchestrator 这类高可用管理工具。它们会自动做故障检测、候选主库选择、日志补拉、身份切换、虚拟 IP 漂移等操作大幅缩短 RTO。团队如果运维能力较强也可以用 MySQL 自带的 Group Replication组复制搭建多主集群通过 Paxos 协议保证数据一致性但复杂度会更上一个台阶。这些扩展方案都建立在一个基础上——你现在已经把主从复制本身搞明白了。原理光滑上层建筑才能稳。8. 几个压箱底的个人经验最后再分享几条纯经验之谈都是我在实际运维中反复踩过坑后总结的。配置文件的变更一定要走版本管理。我在公司内部维护了一套 MySQL 配置文件模板每次改完都会在 Git 里留记录部署新节点时直接复用模板杜绝手工改错。主从切换不是“敲一条命令”的事要有一份完整的演练预案。延时期待、数据校验、账号权限、监控告警都得提前列好清单。我见过太多团队在真正故障时手忙脚乱最本质的原因就是没做过预案演练。搭建完主从之后别急着把监控撤掉。持续一段时间观察SHOW SLAVE STATUS里的两个 Running 和延迟值确认稳定后再纳入日常巡检即可。如果是从零学习 MySQL 复制机制亲手配一套双节点环境、故意制造几个故障再修复效果远好于读十篇文档。这篇博文里的步骤和排查方法你照着做一遍就会形成自己的判断力。