MySQL 8.0.35 主从复制SOP：从参数选型到故障处置全流程指南

MySQL 8.0.35 主从复制搭建(SOP)——如果你在一个维护着十几套数据库的团队待过就会明白“标准化作业程序”这几个字的含金量。主从复制本身不难搭难的是每次都搭得一模一样、参数有据可查、出了问题能按文档一步步回溯。这篇文件就是我在 CentOS 7 环境里把 MySQL 8.0.35 从零拉到主从可用的完整过程按 SOP 格式整理每一步都有执行命令、参数含义、验证方式,以及我踩过之后不想让你再踩一遍的坑。适合正要搭主从的 DBA、运维也适合自己折腾服务器、想给业务加一层读能力的后端同学参考。1. 为什么主从复制必须做成 SOP1.1 一次“搭完就忘”的教训我最早接触主从复制是五年前那时候搭完主从、show slave status 看到两个 Yes 就算完工命令是东抄一条西抄一段参数也是谁顺眼用谁。结果三个月后从库磁盘被 relay log 撑爆我翻历史记录才发现当初根本没设置 relay_log_purge甚至不知道从库的 server-id 跟另一套测试环境撞了号。那次之后我学乖了凡是重复性高、涉及多台机器的操作一律写成 SOP。主从复制恰恰是最典型的场景——主库一台、从库一台、可能还要加只读副本每台都要执行几乎相同的操作任何一步遗漏都会在后续运行中暴雷。1.2 一份合格 SOP 应该覆盖什么按我的定义SOP 不是简单把命令抄一遍而是要把“为什么这么做”也写进去。具体到主从复制至少得包含五个部分环境巡检、参数选型、主库配置、从库配置、验证与排错。环境巡检解决“这台机器能不能搭”的问题参数选型解决“用位点还是 GTID、ROW 还是 STATEMENT”的问题配置阶段是最机械的部分反而最不需要动脑。真正的分水岭在验证与排错只有把常见的 1062、1872、1236 错误处理流程也写进去这份 SOP 才算闭环。1.3 主从复制的边界它能做什么不能做什么很多刚接触的同学容易把主从复制当成万能药——既能高可用又能备份还能缓解写压力。这里我必须泼一盆冷水传统主从复制默认是异步的它在“主库崩溃但数据还没传到从库”这个时间窗口里会丢数据所以它不是高可用方案更不是备份方案。它真正擅长的是两件事一是把读流量从主库剥离出去比如报表查询、数据分析、备份任务都打到从库二是在主库需要维护时提供一个数据基本一致的冷备节点。理解了这条边界你再看后面的参数选型思路会清晰很多。2. 搭之前的环境巡检与版本决策2.1 CentOS 7 上安装 MySQL 8.0.35 的三种方式CentOS 7 自带的软件源里默认没有 MySQL 8.0最多只有 MariaDB 5.5所以第一步就得解决安装源的问题。我实际用过的有三种方式按推荐程度排官方 Yum 源安装下载 mysql80-community-release-el7 的 rpm 包装上然后 yum install mysql-community-server。优点是省事、方便用 systemctl 管理缺点是官方源在国内某些网络环境下很慢而且它默认启用 8.0 系列的最新小版本想锁定 8.0.35 需要额外配置。二进制 tar 包安装下载 mysql-8.0.35-linux-glibc2.12-x86_64.tar.xz解压到 /usr/local/mysql然后手动初始化。这种方式锁定版本最精确不依赖网络源适合内网环境也是我在这份 SOP 里最推荐的方式。Docker 容器虽然简单但生产环境如果坚持用物理机或虚拟机部署数据库容器化反而引入一层不必要的复杂度这里不展开。用 tar 包安装时有一个高频坑MySQL 8.0 初始化的命令跟 5.7 完全不一样。5.7 可以用 mysql_install_db8.0 必须用 mysqld --initialize 或者 mysqld --initialize-insecure前者会在日志里生成随机临时密码后者生成一个空密码的 root 账号。我习惯在测试环境用 --initialize-insecure省去翻日志的麻烦生产环境则老老实实用 --initialize 并第一时间改密码。2.2 巡检清单装完系统后先做这几件事在动 MySQL 之前我会花十分钟把系统层面检查一遍这几项看似跟复制无关但不出事则已出事全是连环坑时间同步主从两台机器务必用 chronyd 做时间同步。复制本身不要求时钟一致但 relay log 的落盘时间、错误日志的时间戳、以及后续排查延迟问题时时间错乱会带来极大干扰。执行 timedatectl set-ntp true 基本能搞定。server-id 唯一性这是主从复制里最基础也最容易被忽略的约束。同一个复制拓扑里所有实例的 server-id 必须不同而且尽量不要用默认值 1。我的习惯是直接用 IP 最后一段换算成数字比如 192.168.1.10 就是 110这样一眼就能看出是哪台机器。防火墙和 SELinuxCentOS 7 默认 firewalld 是开着的如果不放行 3306主库那边配置得再完美从库也连不上。测试环境直接 systemctl stop firewalld 最省事生产环境建议 firewall-cmd --permanent --add-port3306/tcp 精确放行。SELinux 同理要么配置好策略要么在确认安全的前提下 setenforce 0最怕的是两个都开着、两边各有各的拦截报错还看不出来。数据目录和日志目录的属主用 tar 包装的话/data/mysql、/var/log/mysql 这些目录必须先创建并 chown 给 mysql 用户否则 mysqld --initialize 会直接报错。这一步写进任何一份 SOP 都不丢人因为至少三分之一的新手都卡在这。2.3 复制方式选型GTID 还是传统位点MySQL 8.0 里的 GTID 已经非常成熟而且从 8.0.35 这个版本来看官方对 GTID 的支持力度明显大于传统位点。GTID 的核心价值在于每个事务都有一个全局唯一的 ID从库不需要记录“我读到主库 binlog 的哪个文件哪个位置”只要告诉主库“我执行到哪个 GTID 了”主库就能自动把缺的事务推送过来。这对切换和故障恢复是革命性的简化。相比之下传统位点方式要求你手动记录 File 和 Position一旦主从切换、或者从库落后太多导致 binlog 被清理恢复过程极其痛苦。所以在这份 SOP 里我直接采用 GTID 模式从库用 MASTER_AUTO_POSITION1 自动定位。如果你的环境里有 MySQL 5.6 或 5.7 的存量实例想要混搭那就要考虑 GTID 兼容性但今天我们讨论的是 8.0.35 全新搭建没有任何理由不用 GTID。3. 主库配置开启 binlog 与创建复制账号3.1 my.cnf 关键参数逐项说明主库的配置文件是整个复制链路的起点binlog 没开、或者格式不对后面全白搭。我用的是下面这一组参数每一行都值得你理解它为什么存在[mysqld] server-id110 log-binmysql-bin binlog_formatROW gtid_modeON enforce_gtid_consistencyON binlog_expire_logs_seconds604800 max_binlog_size256M skip_name_resolveONserver-id 前面说过保证全局唯一。log-bin 开启二进制日志文件名前缀可以自定义我用默认的 mysql-bin。binlog_formatROW 是我特别想强调的一项。MySQL 的 binlog 有三种格式STATEMENT、ROW、MIXED。STATEMENT 记录的是 SQL 语句本身日志量小但对非确定性函数比如 UUID()、NOW()在主从执行结果不一致时容易出问题ROW 记录的是每一行数据的变更前后值日志量偏大但执行结果完全确定不会因为主从数据环境细微差异导致复制错乱。主从复制这种场景我坚决用 ROW宁可磁盘多占一点也要换一个“绝对能对上”的结果。gtid_modeON 和 enforce_gtid_consistencyON 是配套的前者开启 GTID后者强制要求所有事务都能被 GTID 唯一标识否则会拒绝执行。这两个参数必须在主库从库都设置而且不能只开一半。binlog_expire_logs_seconds604800 表示 binlog 保留 7 天这是为了给从库留出足够的追平窗口。这里我要提醒一个常见误区很多人习惯用 expire_logs_days但 MySQL 8.0 中这个参数已经废弃官方推荐用 binlog_expire_logs_seconds单位是秒。如果你从老文档里抄了 expire_logs_days8.0.35 会打 warning 但不会报错容易造成“明明配置了却不生效”的错觉。skip_name_resolveON 关闭域名解析强迫所有连接都用 IP 直连避免因 DNS 抖动导致复制连接时断时续。但这个参数开了之后复制账号的授权也必须用 IP不能用域名后面我会提到。3.2 创建复制专用账号主库配置好之后重启 MySQL 让参数生效然后创建复制账号。理论上复制账号可以复用 root但生产环境千万别这么干。我习惯创建一个权限最小、名字一看就知道用途的账号CREATE USER repl192.168.1.% IDENTIFIED BY YourStrongPassword; GRANT REPLICATION SLAVE ON *.* TO repl192.168.1.%; FLUSH PRIVILEGES;这里有三点值得展开。第一MySQL 8.0 默认的认证插件是 caching_sha2_password不是老版本的 mysql_native_password。如果你从网上抄了老教程里面往往会有 IDENTIFIED WITH mysql_native_password BY在 8.0.35 里依然能跑但这是被官方标记为废弃的插件。更稳妥的做法是直接用默认插件然后确保从库连接时使用 SSL 或 RSA public key 来完成加密传输。不过内网环境很多同学嫌配置 SSL 麻烦也可以暂时用 mysql_native_password 过渡前提是你清楚它的安全边界。第二授权范围我写的是 192.168.1.%只允许内网网段连接。不要图省事写成 repl%一旦密码泄露等于把复制权限暴露给全网。第三REPLICATION SLAVE 是复制账号唯一需要的权限不需要 SELECT、不需要 ALL多一个权限都是攻击面。3.3 主库数据初始化从零开始还是从存量开始如果你是从零搭建主库上还没有业务数据那这一步可以直接跳过。但绝大多数场景是主库已经跑了一段时间、有存量数据需要在从库上把数据先追平再启动复制。我用的备份命令是mysqldump -uroot -p \ --single-transaction \ --master-data2 \ --set-gtid-purgedON \ --all-databases /backup/full_backup.sql--single-transaction 是 InnoDB 下做一致性快照的关键不加它备份过程中数据在变化恢复出来的库跟主库对不上。--master-data2 会在 dump 文件头部注释掉 CHANGE MASTER 语句方便你恢复后手动指定主库信息。--set-gtid-purgedON 会把当前主库已执行的 GTID 集合写进 dump 文件这样从库恢复后就知道自己已经执行到哪个位置启动复制时不会重复执行旧事务。大表特别多的情况下mysqldump 会很慢可以考虑用 Percona XtraBackup 做物理备份速度更快但恢复流程也复杂不少。SOP 里我默认先用 mysqldump够用且最容易理解等到单表超过 50GB 再切换物理备份方案。4. 从库配置与复制链路建立4.1 从库 my.cnf相对主库多了哪些考量从库的 my.cnf 跟主库有相似之处也有一些专属配置。我用的配置如下[mysqld] server-id120 log-binmysql-bin binlog_formatROW gtid_modeON enforce_gtid_consistencyON relay_logmysql-relay-bin relay_log_purgeON read_onlyON super_read_onlyON几点说明从库也开 log-bin很多人不理解。其实从库开启 binlog 有两个理由一是从库未来可能成为新主库比如主从切换场景二是便于在从库上继续搭建级联复制。备份任务如果打到从库上也需要 binlog 配合。relay_logmysql-relay-bin 指定中继日志文件名前缀。relay_log_purgeON 表示中继日志在执行完后自动清理这个默认就是 ON但正因为是默认值很多人不看结果中继日志撑爆磁盘的事件几乎每个月都能在论坛刷到。这里我建议在配置文件里显式写出来既是一种 SOP 的态度也让后来的人一眼能看到这个参数是被考虑过的。read_onlyON 和 super_read_onlyON 会让从库拒绝所有非超级用户的写操作这是防止有人手滑把数据写到从库的关键防线。super_read_only 比 read_only 更严格连超级用户也拦正式环境建议一起开。4.2 把备份恢复到从库从库安装完成后正式启动复制前要先恢复主库的备份mysql -uroot -p /backup/full_backup.sql恢复过程中如果遇到 max_allowed_packet 不够的错误比如某条大字段插入超过包大小限制要在 my.cnf 里临时调大max_allowed_packet256M这个错我在第一次搭复制时遇到过dump 文件里只要有一张表带大字段比如 TEXT/BLOB默认的 4M 包大小很容易爆掉。别急着改完又来一次建议直接先把参数加上再恢复。4.3 CHANGE MASTER TO 完整语句解析数据恢复完成接下来就是把从库指向主库。MySQL 8.0.35 这一代有一个明显的语法演进8.0.23 之前大家都写 MASTER_HOST、MASTER_USER8.0.23 及之后官方引入了新的 “SOURCE” 词汇体系。新语法是CHANGE REPLICATION SOURCE TO SOURCE_HOST192.168.1.10, SOURCE_USERrepl, SOURCE_PASSWORDYourStrongPassword, SOURCE_PORT3306, SOURCE_AUTO_POSITION1, SOURCE_CONNECT_RETRY10;如果你手头的老教程写的是 CHANGE MASTER TO MASTER_HOST...在 8.0.35 里也能执行但会提示这个语法已废弃。我建议新环境一律用新语法至少风格上跟官方文档保持一致。SOURCE_AUTO_POSITION1 是 GTID 复制的关键开关。打开后从库不会去指定“从哪个 binlog 文件的哪个位置开始”而是基于自己已经执行的 GTID 集合自动向主库请求缺失的事务。这比传统位点方式省心太多也基本杜绝了“位置记错导致重复执行或漏执行”的问题。SOURCE_CONNECT_RETRY10 表示网络抖动导致连接断开时每 10 秒重连一次这是默认值但显式写出来有助于后来的人理解复制链路是有自愈机制的。4.4 启动复制并验证两个关键线程执行完 CHANGE 语句后启动复制START REPLICA;注意8.0.22 之前的版本写的是 START SLAVE这个旧关键字在 8.0.35 中依然兼容可用但新写法是 START REPLICA。SOP 里我统一用新写法遇到老版本 MySQL 再对应翻译。启动后验证状态的命令SHOW REPLICA STATUS\G重点看以下几行Replica_IO_Running: Yes — IO 线程负责从主库拉取 binlog 到本地 relay log。Replica_SQL_Running: Yes — SQL 线程负责把 relay log 中的事务在从库上重放。Seconds_Behind_Master: 0 — 表示从库跟主库基本同步。如果 IO 线程不是 Yes大概率是网络不通或者账号权限问题。如果 SQL 线程不是 Yes说明数据恢复有问题或者主从数据不一致。这两个线程的状态理解我认为是判断主从健康度的基本功。这里补充一个容易踩的坑当你看到 Seconds_Behind_Master 是 NULL 而不是数字时不代表从库没有延迟而是复制线程有一边没在运行或者尚未建立连接。很多人看到 NULL 以为“没有延迟”实际上可能是复制已经断了。5. 复制链路日常体检与高频故障处理5.1 每天该盯的几个指标主从复制搭好不是终点日常监控才是。我常用的体检命令分三层第一层看复制线程状态SHOW REPLICA STATUS\G第二层看复制通道在性能表里的统计这也是 8.0 更推荐的姿势SELECT * FROM performance_schema.replication_connection_status\G SELECT * FROM performance_schema.replication_applier_status\G第三层关注从库的磁盘剩余空间。relay log 即使开了自动清理在从库延迟较大时依然可能临时占用大量磁盘所以我习惯在监控脚本里加一条磁盘阈值告警。5.2 三个高频报错的完整处理链路我把自己在实际维护中遇到过且比较有代表性的三个错误写下来都是可以复现的排错路径。错误一1062 主键冲突。SQL 线程在从库重放时插入一条记录发现主键已经存在。常见原因是有人手动往从库写了数据或者从库不是从备份恢复而是半路接入导致数据不一致。我的处理思路是先 STOP REPLICA然后 SET GLOBAL SQL_SLAVE_SKIP_COUNTER 1再 START REPLICA跳过这一条错误事务。但注意这只是止血不是治本。跳过之后要用 pt-table-checksum 或业务层面确认主从数据一致性否则后面可能连续冒出一堆 1062。错误二1872 Slave failed to initialize relay log info structure from the repository。这个报错通常出现在 relay log 的元数据损坏或者从库之前发生过非正常关机。处理方式是STOP REPLICA; RESET REPLICA ALL; CHANGE REPLICATION SOURCE TO ...; START REPLICA;RESET REPLICA ALL 会清掉复制相关的所有配置需要重新执行 CHANGE 语句。别慌按照 SOP 重来一遍就行。错误三1236 The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION 1, but the master has purged binary logs containing the slaves GTIDs。这说明从库需要的 binlog 已经被主库清理了常见于从库停机时间过长、主库 binlog 保留期太短。处理办法是把备份保留期调长或者对从库重新做一次全量恢复。这也是为什么我建议 binlog 至少保留 7 天给运维留出足够的缓冲。5.3 主库维护时的只读切换演练虽然传统主从复制不承诺高可用但它可以帮你完成“主库维护”这种计划内操作。我建议在测试环境至少演练一次手工切换流程如下主库侧确保所有事务已经提交执行 FLUSH TABLES WITH READ LOCK 短暂锁表然后记录当前 GTID 位置其实 GTID 模式下不需要记位置。从库侧等待 Seconds_Behind_Master 变成 0确保从库已经追平主库。从库侧执行 STOP REPLICA然后 SET GLOBAL read_onlyOFF把从库提升为可写的新主库。业务侧把应用连接指向新主库。原主库等维护完成后可以把原主库反向配置为新主库的从库形成角色互换。这个流程里最容易出问题的就是第 3 步——忘记关 read_only 就启动写入结果业务报错“read-only mode”。建议把这条命令写在切换脚本的醒目位置。6. 从一套 SOP 到更高阶的复制体系6.1 半同步复制什么时候值得用前面提到传统复制是异步的主库提交事务后不等待从库确认就直接返回存在数据丢失窗口。如果你的业务对数据丢失零容忍可以考虑半同步复制机制。在 8.0.35 中启用方式如下INSTALL PLUGIN rpl_semi_sync_source SONAME semisync_source.so; SET GLOBAL rpl_semi_sync_source_enabled 1;开启后主库提交事务时会等待至少一个从库确认收到 binlog才向客户端返回成功。好处是大幅缩小丢失窗口代价是从库确认的延迟会直接影响主库写入性能。我的建议是业务写压力不大、但数据敏感度极高的系统上可以用写密集的高并发场景要谨慎评估。6.2 这份 SOP 还有哪些可扩展的方向模块化是另一个方向。比如把主从复制 SOP 拆成“安装 MySQL 8.0.35”和“配置主从复制”两个独立文档前者的输出物是完整的 MySQL 实例后者的输入依赖一个干净可用的实例。这样换一个版本、换一台服务器只需要替换第一部分而不用动复制部分。我自己维护的文档体系就是这么拆的版本变更时改动面小很多。如果团队规模再大一些可以考虑引入 MHA 或者 Orchestrator 这类工具把第 5.3 节的手工切换自动化。但工具永远替代不了人对原理的理解我始终建议先把手工流程跑熟再考虑自动化否则工具一旦出故障你连手工兜底的能力都没有。6.3 最后的一点个人体会这套 SOP 前前后后帮我搭过不下二十套主从环境也帮同事处理过好多次复制中断。我的体会是主从复制最大的风险往往不是因为参数复杂而是因为流程不统一——有人用位点、有人用 GTID有人开 ROW、有人用默认 STATEMENT东拼西凑最后谁都接不上茬。把 SOP 固定下来照着执行再配上第 5 节那些故障处理预案这套东西才算真正立住了。如果你照着这份文档搭出了自己的主从环境建议在测试环境刻意断一次网、停一次主库体会一下从库是怎么重连和追平的——那种直观的理解比看十遍文档都管用。