数据库教程FGMT32‑MySQL主从复制项目实施与维护05(MySQL8.4/9.7主从复制)
## 前言与教程大纲介绍
风哥教程本文面向数据库DBA、运维工程师、数据库架构师，完整覆盖两套相互独立的Linux平台MySQL主从复制环境实施：第一套为MySQL8.4一主一从复制集群，第二套为MySQL9.7一主一从复制集群。两套环境完全隔离，互不依赖。本套风哥教程所有硬件基准为64G物理内存、8CPU，操作主机统一使用`fgedu‑net‑cn1`、`fgedu‑net‑cn2`，部署路径全部将传统`/u01`替换为`/fgedudb`；数据库实例名统一为`fgedudb`，业务数据库名称`fgedudb`，业务账号`fgedu`，复制专用账号统一命名`fgedurep`。

风哥教程本文整体内容划分：第一部分为主从复制基础理论，讲解复制底层原理、复制模式、GTID机制、MySQL8.4与MySQL9.7版本差异、64G内存8CPU硬件规格参数推导、生产环境约束；第二部分为第一套环境实战，完成Linux平台MySQL8.4二进制安装、主库配置、从库初始化、GTID主从搭建、clone插件快速部署从库、计划内主从切换、半同步复制配置；第三部分为第二套独立环境实战，完整部署MySQL9.7主从复制集群，覆盖版本特有参数、认证机制、新增复制监控组件、主从验证；第四部分为主从复制日常运维、监控指标、故障排查处理；最后为风哥针对本文总结。

风哥教程本文学习目标：掌握Linux下MySQL8.4、MySQL9.7二进制标准化部署，理解GTID主从复制完整工作流程，能够完成手工搭建、clone插件快速搭建从库，完成计划内主从切换，处理IO线程、SQL线程报错、主从延迟、数据不一致等生产故障，理解8.4与9.7复制相关新特性差异。全部实战命令均基于Oracle Linux9操作系统，命令可以直接复制在测试环境执行。

## 一、MySQL主从复制基础理论
### 1.1 MySQL主从复制基础架构与工作原理
MySQL主从复制（Source‑Replica，旧称Master‑Slave）是将主库上提交的事务变更，通过二进制日志binlog传输到从库，从库回放日志完成数据同步的技术，实现读写分离、数据备份、容灾切换、数据分析离线计算等业务目标。整个复制流程涉及三类核心线程：主库端Binlog Dump线程、从库端IO线程、从库端SQL（Applier）线程。

完整工作流程：
1. 主库提交事务，事务变更写入内存，事务提交时将变更事件写入二进制日志binlog；
2. 从库IO线程建立TCP连接到主库，主库启动Binlog Dump线程读取binlog事件，通过网络发送给从库；
3. 从库IO线程接收binlog事件，写入本地中继日志relay‑log；
4. 从库SQL线程读取relay‑log，解析日志事件，在从库本地回放事务，实现数据同步；
5. 元数据存储：8.0及以后版本使用InnoDB存储复制元数据，不再使用传统文件型元数据存储，提升元数据可靠性。

复制分为异步复制、半同步复制、全同步复制三类模式。异步复制为默认模式，主库提交事务直接返回客户端，不需要等待从库接收日志，性能最优，但存在主库宕机数据丢失风险；半同步复制要求至少一台从库接收日志并落盘之后，主库才向客户端返回提交成功，可以控制数据丢失风险，会带来少量性能损耗；全同步复制仅在NDB集群支持，普通InnoDB主从不支持。

>风哥 itpux-com

### 1.2 GTID全局事务标识符复制机制理论
GTID（Global Transaction ID）全局事务ID，每一个事务在主库提交时分配唯一全局ID，格式为`UUID:事务序列号`，GTID在整个复制拓扑全局唯一。基于GTID的复制不再依赖binlog文件名和文件偏移位置，从库只需要告知主库本机已经执行完成的GTID集合，主库自动从对应事务开始推送binlog日志，极大简化搭建从库、主从切换、故障恢复的运维复杂度。

核心参数：
– `gtid_mode=ON`：开启GTID模式，主库从库必须同时开启；
– `enforce_gtid_consistency=ON`：保证只有可以被GTID安全复制的事务才可以执行，禁止不支持GTID的SQL语句；
– `log_replica_updates=ON`：从库回放事务时将变更写入本机binlog，当从库需要提升为新主库时，该参数必须开启。

线上生产环境强烈推荐使用GTID复制，不建议使用传统文件+位点复制模式。

网上搜索风哥教程可以学习全套数据库教程

### 1.3 MySQL8.4与MySQL9.7复制相关版本差异理论
MySQL8.4为长期支持LTS版本，属于稳定生产版本，复制架构继承8.0体系，支持clone插件、半同步复制，认证插件支持`caching_sha2_password`，同时兼容`mysql_native_password`用于兼容老旧客户端；废弃部分老旧复制参数，启动时错误日志输出警告，不影响实例启动。

MySQL9.7属于新版本，在复制能力上有较多更新点：
1. 彻底移除`mysql_native_password`认证插件，复制账号只能使用caching_sha2_password，老版本客户端连接复制链路会直接认证失败；
2. 新增`replica_allow_higher_version_source`参数，控制是否允许高版本主库向低版本从库复制；
3. 企业版复制监控组件下放到社区版本，包含复制应用指标、流控统计、资源管理器、主节点选举观测组件；
4. 复制applier指标可以直接观测从库回放延迟、吞吐量，无需额外第三方监控脚本；
5. 部分废弃旧参数直接会造成实例启动失败，配置文件迁移时需要清理无效参数。

>风哥教程 113257174

### 1.4 64G内存8CPU硬件规格参数理论推导
本套风哥教程所有my.cnf参数基准硬件规格：物理内存64GB，CPU 8核心。MySQL主库承担业务读写压力，从库承担回放binlog、读请求压力，内存参数设计原则为innodb缓冲池占用物理内存55%‑60%，预留操作系统、网络缓冲区、binlog缓存、relaylog内存开销。

核心InnoDB参数理论：
– `innodb_buffer_pool_size`设置32G；缓冲池实例`innodb_buffer_pool_instances=16`，每个实例不低于2G，降低锁竞争；
– `innodb_log_file_size=4G`，两组重做日志文件合计8G，平衡崩溃恢复时间与大事务写入性能；
– `innodb_flush_method=O_DIRECT`，Linux平台直接IO绕过操作系统文件缓存；
连接参数：`max_connections=800`，`max_connect_errors=1000`；临时表参数`tmp_table_size=2G`，`max_heap_table_size=2G`。

复制相关参数理论：
– `binlog_format=ROW`行级复制，生产强制推荐，避免statement模式带来的数据不一致风险；
– `binlog_row_image=MINIMAL`，binlog仅记录变更字段，降低binlog磁盘占用；
– `sync_binlog=1`，每次事务提交binlog刷盘，保证主库宕机binlog不丢失；
– `binlog_expire_logs_seconds=604800`，binlog保留7天自动清理，防止磁盘占满；
– `max_binlog_size=1G`，binlog文件单文件上限1G；
– 从库开启`relay_log_recovery=ON`，从库宕机重启自动修复relaylog，避免中继日志损坏；
– `replica_parallel_workers=8`，开启从库并行回放，8CPU环境设置8个并行线程，降低主从延迟。

上51CTO搜索风哥可以学习全套数据库教程

### 1.5 Linux平台MySQL目录规划理论
本套风哥教程统一替换路径`/u01`为`/fgedudb`，两套独立环境目录完全隔离。
第一套MySQL8.4环境目录规划：
– `/fgedudb/fgedudb‑base‑84`：MySQL8.4二进制程序根目录basedir
– `/fgedudb/fgedudb‑data‑84‑m`：8.4主库数据目录datadir
– `/fgedudb/fgedudb‑data‑84‑s`：8.4从库数据目录datadir
– `/fgedudb/fgedudb‑log‑84‑m`：8.4主库binlog、错误日志、慢查询日志
– `/fgedudb/fgedudb‑log‑84‑s`：8.4从库relaylog、binlog、错误日志
– `/fgedudb/fgedudb‑conf‑84`：8.4主从my.cnf配置文件目录
– `/fgedudb/fgedudb‑tmp‑84`：临时文件目录tmpdir

第二套MySQL9.7独立环境目录规划：
– `/fgedudb/fgedudb‑base‑97`：MySQL9.7二进制程序根目录
– `/fgedudb/fgedudb‑data‑97‑m`：9.7主库数据目录
– `/fgedudb/fgedudb‑data‑97‑s`：9.7从库数据目录
– `/fgedudb/fgedudb‑log‑97‑m`：9.7主库日志目录
– `/fgedudb/fgedudb‑log‑97‑s`：9.7从库日志目录
– `/fgedudb/fgedudb‑conf‑97`：9.7配置目录
– `/fgedudb/fgedudb‑tmp‑97`：9.7临时目录

操作系统层面必须创建mysql操作系统用户与用户组，所有目录属主属组设置mysql:mysql，权限设置750；目录不允许中文、空格与特殊字符；操作系统内核参数需要调整：文件句柄数、进程最大数、关闭透明大页，关闭SELinux或者配置SELinux策略，防火墙放行3306数据库端口。

>风哥数据库教程 itpux-com

### 1.6 主从复制生产环境约束理论
1. 主从实例MySQL大版本尽量保持一致，允许小版本不一致，但不建议跨大版本长期运行；MySQL9.7新增`replica_allow_higher_version_source`参数控制版本跨版本复制行为。
2. 主从库硬件配置尽量对齐，CPU、内存、磁盘IO性能差距过大会造成从库回放跟不上主库写入，产生主从延迟。
3. 主从库`server‑id`必须全局唯一，同一复制拓扑不能重复。
4. 库表字符集、排序规则必须保持一致，避免复制出现字符集报错。
5. 避免使用非事务引擎MyISAM，MyISAM引擎不支持崩溃安全复制，生产全部使用InnoDB。
6. 从库设置`read_only=ON`、`super_read_only=ON`，普通账号禁止写入从库，防止人为写入造成主从数据不一致；super_read_only会限制super权限账号写入，运维操作需要临时关闭。

## 二、第一套实战环境：Linux平台MySQL8.4主从复制集群部署
>实战环境说明
主机：`fgedu‑net‑cn1`部署MySQL8.4主库，`fgedu‑net‑cn2`部署MySQL8.4从库；操作系统Oracle Linux9；硬件规格64G内存8CPU；实例名`fgedudb`，业务库`fgedudb`，复制账号`fgedurep`；端口3306；两套环境相互独立，本章节仅操作MySQL8.4环境。

### 2.1 Linux操作系统环境初始化（fgedu‑net‑cn1、fgedu‑net‑cn2两台主机）
两台主机均执行操作系统初始化操作，root用户执行。
1. 创建mysql操作系统用户组与用户：
“`bash
groupadd mysql
useradd -r -g mysql -s /sbin/nologin mysql
“`
2. 创建MySQL8.4全套目录结构（两台主机均执行）：
“`bash
mkdir -p /fgedudb/fgedudb-base-84
mkdir -p /fgedudb/fgedudb-tmp-84
mkdir -p /fgedudb/fgedudb-conf-84
# fgedu‑net‑cn1为主库，创建主库数据日志目录
mkdir -p /fgedudb/fgedudb-data-84-m
mkdir -p /fgedudb/fgedudb-log-84-m
# fgedu‑net‑cn2为从库，创建从库数据日志目录
mkdir -p /fgedudb/fgedudb-data-84-s
mkdir -p /fgedudb/fgedudb-log-84-s
“`
3. 修改目录权限属主属组：
“`bash
chown -R mysql:mysql /fgedudb
chmod -R 750 /fgedudb
“`
4. 操作系统内核参数配置，编辑/etc/security/limits.conf，增加下面内容：
“`ini
mysql soft nofile 65535
mysql hard nofile 65535
mysql soft nproc 65535
mysql hard nproc 65535
“`
5. 关闭透明大页，永久生效写入/etc/rc.local：
“`bash
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
“`
6. 防火墙放行3306端口：
“`bash
firewall-cmd –permanent –add-port=3306/tcp
firewall-cmd –reload
“`
7. SELinux设置为permissive模式，编辑/etc/selinux/config设置`SELINUX=permissive`，执行`setenforce 0`临时生效。

### 2.2 MySQL8.4二进制包解压部署（两台主机）
将MySQL8.4 Linux‑glibc二进制包上传到`/fgedudb/soft`目录，两台主机执行解压：
“`bash
cd /fgedudb/soft
tar -Jxf mysql-8.4.x-linux-glibc2.28-x86_64.tar.xz -C /fgedudb/fgedudb-base-84 –strip-components=1
chown -R mysql:mysql /fgedudb/fgedudb-base-84
# 验证版本
/fgedudb/fgedudb-base-84/bin/mysqld –version
“`

### 2.3 MySQL8.4主库（fgedu‑net‑cn1）my.cnf配置文件编写
编辑`/fgedudb/fgedudb-conf-84/my.cnf.m`，64G内存8CPU完整配置如下：
“`ini
[mysqld]
#基础路径
basedir=/fgedudb/fgedudb-base-84
datadir=/fgedudb/fgedudb-data-84-m
tmpdir=/fgedudb/fgedudb-tmp-84
socket=/fgedudb/fgedudb-data-84-m/mysql.sock
pid-file=/fgedudb/fgedudb-data-84-m/mysql.pid
port=3306
server-id=101
lower_case_table_names=1
character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci

#内存参数，硬件64G内存 8CPU
innodb_buffer_pool_size=32G
innodb_buffer_pool_instances=16
innodb_log_file_size=4G
innodb_log_files_in_group=2
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=1

#连接参数
max_connections=800
max_connect_errors=1000
max_allowed_packet=64M
tmp_table_size=2G
max_heap_table_size=2G

#binlog复制参数
log_bin=/fgedudb/fgedudb-log-84-m/fgedudb-m-bin
binlog_format=ROW
binlog_row_image=MINIMAL
sync_binlog=1
binlog_expire_logs_seconds=604800
max_binlog_size=1G

#GTID复制参数
gtid_mode=ON
enforce_gtid_consistency=ON
log_replica_updates=ON

#日志配置
log_error=/fgedudb/fgedudb-log-84-m/fgedudb-m-err.log
slow_query_log=ON
slow_query_log_file=/fgedudb/fgedudb-log-84-m/fgedudb-m-slow.log
long_query_time=2

[mysql]
default-character-set=utf8mb4

[client]
port=3306
socket=/fgedudb/fgedudb-data-84-m/mysql.sock
default-character-set=utf8mb4
“`

### 2.4 MySQL8.4主库初始化、配置systemd服务、启动实例
1. 执行数据库初始化，–defaults‑file必须放在第一个参数：
“`bash
cd /fgedudb/fgedudb-base-84/bin
./mysqld –defaults-file=/fgedudb/fgedudb-conf-84/my.cnf.m –initialize –user=mysql
“`
初始化完成，临时root密码打印控制台，若丢失，查看错误日志`/fgedudb/fgedudb-log-84-m/fgedudb-m-err.log`获取临时密码。

2. 编写systemd服务单元文件`/etc/systemd/system/mysqld‑fgedudb‑84m.service`
“`ini
[Unit]
Description=MySQL fgedudb 8.4 Master Service
After=network.target

[Service]
Type=notify
User=mysql
Group=mysql
ExecStart=/fgedudb/fgedudb-base-84/bin/mysqld –defaults-file=/fgedudb/fgedudb-conf-84/my.cnf.m
ExecReload=/fgedudb/fgedudb-base-84/bin/mysqladmin shutdown
Restart=on-failure
LimitNOFILE=65535
LimitNPROC=65535

[Install]
WantedBy=multi-user.target
“`
3. 加载systemd，启动数据库实例：
“`bash
systemctl daemon-reload
systemctl start mysqld-fgedudb-84m.service
systemctl enable mysqld-fgedudb-84m.service
systemctl status mysqld-fgedudb-84m.service
“`

4. 登录主库，修改root密码，创建业务库、业务账号、复制专用账号`fgedurep`
“`bash
/fgedudb/fgedudb-base-84/bin/mysql -u root -p -S /fgedudb/fgedudb-data-84-m/mysql.sock
“`
执行SQL：
“`sql
ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘Fg@fgedudb2026’;
CREATE DATABASE fgedudb DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_0900_ai_ci;
CREATE USER ‘fgedu’@’%’ IDENTIFIED BY ‘Fg@fgedu2026’;
GRANT ALL PRIVILEGES ON fgedudb.* TO ‘fgedu’@’%’;
— 创建复制账号fgedurep，复制权限
CREATE USER ‘fgedurep’@’%’ IDENTIFIED BY ‘Fg@fgedurep2026’;
GRANT REPLICATION SLAVE ON *.* TO ‘fgedurep’@’%’;
FLUSH PRIVILEGES;
— 查看主库binlog与GTID状态
SHOW MASTER STATUS;
SHOW VARIABLES LIKE ‘%gtid%’;
“`

### 2.5 MySQL8.4从库（fgedu‑net‑cn2）my.cnf配置文件编写
编辑`/fgedudb/fgedudb-conf-84/my.cnf.s`，注意`server‑id=102`，必须和主库101不相同。
“`ini
[mysqld]
basedir=/fgedudb/fgedudb-base-84
datadir=/fgedudb/fgedudb-data-84-s
tmpdir=/fgedudb/fgedudb-tmp-84
socket=/fgedudb/fgedudb-data-84-s/mysql.sock
pid-file=/fgedudb/fgedudb-data-84-s/mysql.pid
port=3306
server-id=102
lower_case_table_names=1
character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci

#64G内存8CPU参数
innodb_buffer_pool_size=32G
innodb_buffer_pool_instances=16
innodb_log_file_size=4G
innodb_log_files_in_group=2
innodb_flush_method=O_DIRECT

#连接参数
max_connections=800
max_connect_errors=1000
max_allowed_packet=64M
tmp_table_size=2G
max_heap_table_size=2G

#复制相关
log_bin=/fgedudb/fgedudb-log-84-s/fgedudb-s-bin
binlog_format=ROW
binlog_row_image=MINIMAL
sync_binlog=1
binlog_expire_logs_seconds=604800
max_binlog_size=1G
relay_log=/fgedudb/fgedudb-log-84-s/fgedudb-s-relay
relay_log_recovery=ON
replica_parallel_workers=8

#GTID配置
gtid_mode=ON
enforce_gtid_consistency=ON
log_replica_updates=ON

#从库只读设置
read_only=ON
super_read_only=ON

#日志
log_error=/fgedudb/fgedudb-log-84-s/fgedudb-s-err.log
slow_query_log=ON
slow_query_log_file=/fgedudb/fgedudb-log-84-s/fgedudb-s-slow.log
long_query_time=2

[mysql]
default-character-set=utf8mb4

[client]
port=3306
socket=/fgedudb/fgedudb-data-84-s/mysql.sock
default-character-set=utf8mb4
“`

### 2.6 MySQL8.4从库初始化、systemd配置、实例启动
“`bash
cd /fgedudb/fgedudb-base-84/bin
./mysqld –defaults-file=/fgedudb/fgedudb-conf-84/my.cnf.s –initialize –user=mysql
“`
编写从库systemd单元`/etc/systemd/system/mysqld‑fgedudb‑84s.service`
“`ini
[Unit]
Description=MySQL fgedudb 8.4 Replica Service
After=network.target

[Service]
Type=notify
User=mysql
Group=mysql
ExecStart=/fgedudb/fgedudb-base-84/bin/mysqld –defaults-file=/fgedudb/fgedudb-conf-84/my.cnf.s
ExecReload=/fgedudb/fgedudb-base-84/bin/mysqladmin shutdown
Restart=on-failure
LimitNOFILE=65535
LimitNPROC=65535

[Install]
WantedBy=multi-user.target
“`
加载systemd，启动从库实例：
“`bash
systemctl daemon-reload
systemctl start mysqld-fgedudb-84s.service
systemctl enable mysqld-fgedudb-84s.service
“`
登录从库修改root本地密码：
“`bash
/fgedudb/fgedudb-base-84/bin/mysql -u root -p -S /fgedudb/fgedudb-data-84-s/mysql.sock
“`
“`sql
ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘Fg@fgedudb2026′;
“`

### 2.7 GTID模式配置主从复制，启动复制链路（fgedu‑net‑cn2从库执行）
登录从库MySQL，执行`CHANGE REPLICATION SOURCE TO`，GTID模式使用`SOURCE_AUTO_POSITION=1`，不需要填写binlog文件名与位点。
“`sql
CHANGE REPLICATION SOURCE TO
SOURCE_HOST=’fgedu-net-cn1′,
SOURCE_PORT=3306,
SOURCE_USER=’fgedurep’,
SOURCE_PASSWORD=’Fg@fgedurep2026′,
SOURCE_AUTO_POSITION=1;

— 启动复制
START REPLICA;

— 查看复制状态
SHOW REPLICA STATUS\G
“`
重点观察输出字段：
`Replica_IO_Running: Yes`
`Replica_SQL_Running: Yes`
`Seconds_Behind_Source:0`
代表复制链路正常，没有延迟。

### 2.8 主从复制数据同步验证
在主库fgedu‑net‑cn1执行业务测试SQL：
“`sql
USE fgedudb;
CREATE TABLE t_fg_test(id INT PRIMARY KEY,name VARCHAR(50));
INSERT INTO t_fg_test VALUES(1,’风哥84主从测试’);
COMMIT;
“`
在从库fgedu‑net‑cn2查询，验证数据同步：
“`sql
USE fgedudb;
SELECT * FROM t_fg_test;
“`
能够查询到插入的数据，代表GTID主从复制搭建完成。

### 2.9 Clone插件快速搭建从库实战（MySQL8.4）
当业务数据量很大，mysqldump逻辑备份搭建从库耗时久，可以使用clone插件物理快照快速完成从库初始化，clone插件直接拷贝InnoDB物理数据文件，不需要逻辑导出导入。

1. 主库fgedu‑net‑cn1安装clone插件，创建clone专用用户：
“`sql
INSTALL PLUGIN clone SONAME ‘mysql_clone.so’;
CREATE USER ‘fgeduclone’@’%’ IDENTIFIED BY ‘Fg@fgeduclone2026’;
GRANT CLONE_ADMIN ON *.* TO ‘fgeduclone’@’%’;
FLUSH PRIVILEGES;
“`
2. 待搭建的新从库实例，初始化完成，实例正常启动，执行远程clone拉取主库数据：
“`sql
INSTALL PLUGIN clone SONAME ‘mysql_clone.so’;
CLONE INSTANCE FROM fgeduclone@fgedu-net-cn1:3306 IDENTIFIED BY ‘Fg@fgeduclone2026’;
“`
执行clone命令后，从库实例会自动关闭，覆盖datadir全部数据文件，完成之后实例自动重启。
3. clone完成后，清理auto.cnf（clone会拷贝主库uuid，从库需要生成新uuid），重启实例，再执行`CHANGE REPLICATION SOURCE TO`开启复制。

>注意：clone操作会覆盖从库datadir全部数据，生产环境操作前确认数据备份。

### 2.10 MySQL8.4半同步复制配置实战
半同步复制插件在8.4社区版内置，主库加载rpl_semi_sync_master，从库加载rpl_semi_sync_slave插件。
1. 主库执行：
“`sql
INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’;
SET GLOBAL rpl_semi_sync_master_enabled=1;
SET GLOBAL rpl_semi_sync_master_timeout=10000;
SET GLOBAL rpl_semi_sync_master_wait_for_slave_count=1;
“`
写入my.cnf[mysqld]段永久生效：
“`ini
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=10000
rpl_semi_sync_master_wait_for_slave_count=1
“`
2. 从库执行：
“`sql
INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’;
SET GLOBAL rpl_semi_sync_slave_enabled=1;
STOP REPLICA IO_THREAD;
START REPLICA IO_THREAD;
“`
写入从库my.cnf永久生效：
“`ini
rpl_semi_sync_slave_enabled=1
“`
3. 主库查看半同步状态：
“`sql
SHOW GLOBAL STATUS LIKE ‘rpl_semi_sync%’;
“`

### 2.11 MySQL8.4计划内主从切换实战（Switchover，业务维护窗口操作）
计划内切换，业务停止写入，将从库提升为新主库，原主库变为新从库。
1. 业务侧停止写入，应用停止连接数据库；在原主库执行，确保所有binlog全部推送到从库：
“`sql
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
“`
2. 在从库fgedu‑net‑cn2，确认复制无延迟，`Seconds_Behind_Source=0`，GTID集合完全追上主库。
“`sql
SHOW REPLICA STATUS\G
“`
3. 在从库停止复制，清除复制信息，关闭只读参数，提升为新主库：
“`sql
STOP REPLICA;
RESET REPLICA ALL;
SET GLOBAL read_only=OFF;
SET GLOBAL super_read_only=OFF;
“`
4. 原主库fgedu‑net‑cn1解锁表，关闭只读，配置复制指向新主库（fgedu‑net‑cn2）：
“`sql
UNLOCK TABLES;
STOP REPLICA;
RESET REPLICA ALL;
CHANGE REPLICATION SOURCE TO
SOURCE_HOST=’fgedu-net-cn2′,
SOURCE_PORT=3306,
SOURCE_USER=’fgedurep’,
SOURCE_PASSWORD=’Fg@fgedurep2026′,
SOURCE_AUTO_POSITION=1;
START REPLICA;
“`
5. 验证双向复制状态，业务应用修改连接地址指向新主库fgedu‑net‑cn2，完成计划切换。

## 三、第二套独立实战环境：Linux平台MySQL9.7主从复制集群部署
>实战说明：本套环境完全独立，不依赖MySQL8.4环境；`fgedu‑net‑cn1`部署MySQL9.7主库，`fgedu‑net‑cn2`部署MySQL9.7从库；Oracle Linux9，硬件64G内存8CPU，实例名`fgedudb`，复制账号`fgedurep`，端口3306。注意MySQL9.7不再支持mysql_native_password认证插件，全部账号默认caching_sha2_password。

>风哥 itpux-com

### 3.1 操作系统环境准备
复用操作系统内核参数、limits、防火墙、SELinux配置，创建MySQL9.7专属目录：两台主机root执行
“`bash
groupadd mysql
useradd -r -g mysql -s /sbin/nologin mysql
mkdir -p /fgedudb/fgedudb-base-97
mkdir -p /fgedudb/fgedudb-tmp-97
mkdir -p /fgedudb/fgedudb-conf-97
# fgedu‑net‑cn1主库目录
mkdir -p /fgedudb/fgedudb-data-97-m
mkdir -p /fgedudb/fgedudb-log-97-m
# fgedu‑net‑cn2从库目录
mkdir -p /fgedudb/fgedudb-data-97-s
mkdir -p /fgedudb/fgedudb-log-97-s
chown -R mysql:mysql /fgedudb
chmod -R 750 /fgedudb
“`

### 3.2 MySQL9.7二进制包解压部署，两台主机执行
“`bash
cd /fgedudb/soft
tar -Jxf mysql-9.7.x-linux-glibc2.28-x86_64.tar.xz -C /fgedudb/fgedudb-base-97 –strip-components=1
chown -R mysql:mysql /fgedudb/fgedudb-base-97
/fgedudb/fgedudb-base-97/bin/mysqld –version
“`

### 3.3 MySQL9.7主库my.cnf配置文件
编辑`/fgedudb/fgedudb-conf-97/my.cnf.m`，64G内存8CPU，9.7版本移除mysql_native_password相关参数，新增复制参数。
“`ini
[mysqld]
basedir=/fgedudb/fgedudb-base-97
datadir=/fgedudb/fgedudb-data-97-m
tmpdir=/fgedudb/fgedudb-tmp-97
socket=/fgedudb/fgedudb-data-97-m/mysql.sock
pid-file=/fgedudb/fgedudb-data-97-m/mysql.pid
port=3306
server-id=201
lower_case_table_names=1
character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci

#64G内存8CPU
innodb_buffer_pool_size=32G
innodb_buffer_pool_instances=16
innodb_log_file_size=4G
innodb_log_files_in_group=2
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=1

max_connections=800
max_connect_errors=1000
max_allowed_packet=64M
tmp_table_size=2G
max_heap_table_size=2G

#binlog复制
log_bin=/fgedudb/fgedudb-log-97-m/fgedudb-m-bin
binlog_format=ROW
binlog_row_image=MINIMAL
sync_binlog=1
binlog_expire_logs_seconds=604800
max_binlog_size=1G

#GTID
gtid_mode=ON
enforce_gtid_consistency=ON
log_replica_updates=ON

#9.7新增复制参数
replica_allow_higher_version_source=OFF

#日志
log_error=/fgedudb/fgedudb-log-97-m/fgedudb-m-err.log
slow_query_log=ON
slow_query_log_file=/fgedudb/fgedudb-log-97-m/fgedudb-m-slow.log
long_query_time=2

[mysql]
default-character-set=utf8mb4

[client]
port=3306
socket=/fgedudb/fgedudb-data-97-m/mysql.sock
default-character-set=utf8mb4
“`

### 3.4 MySQL9.7主库初始化、systemd配置，启动实例
“`bash
cd /fgedudb/fgedudb-base-97/bin
./mysqld –defaults-file=/fgedudb/fgedudb-conf-97/my.cnf.m –initialize –user=mysql
“`
systemd单元文件`/etc/systemd/system/mysqld‑fgedudb‑97m.service`
“`ini
[Unit]
Description=MySQL fgedudb 9.7 Master Service
After=network.target

[Service]
Type=notify
User=mysql
Group=mysql
ExecStart=/fgedudb/fgedudb-base-97/bin/mysqld –defaults-file=/fgedudb/fgedudb-conf-97/my.cnf.m
ExecReload=/fgedudb/fgedudb-base-97/bin/mysqladmin shutdown
Restart=on-failure
LimitNOFILE=65535
LimitNPROC=65535

[Install]
WantedBy=multi-user.target
“`
加载systemd启动实例：
“`bash
systemctl daemon-reload
systemctl start mysqld-fgedudb-97m.service
systemctl enable mysqld-fgedudb-97m.service
“`
登录主库修改root密码，创建业务库、业务账号、复制账号`fgedurep`，9.7强制使用caching_sha2_password：
“`bash
/fgedudb/fgedudb-base-97/bin/mysql -u root -p -S /fgedudb/fgedudb-data-97-m/mysql.sock
“`
“`sql
ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘Fg@fgedudb2026’;
CREATE DATABASE fgedudb DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_0900_ai_ci;
CREATE USER ‘fgedu’@’%’ IDENTIFIED BY ‘Fg@fgedu2026’;
GRANT ALL PRIVILEGES ON fgedudb.* TO ‘fgedu’@’%’;
CREATE USER ‘fgedurep’@’%’ IDENTIFIED BY ‘Fg@fgedurep2026’;
GRANT REPLICATION SLAVE ON *.* TO ‘fgedurep’@’%’;
FLUSH PRIVILEGES;
SHOW MASTER STATUS;
“`

### 3.5 MySQL9.7从库my.cnf配置文件（fgedu‑net‑cn2）
`server‑id=202`与主库201不重复
“`ini
[mysqld]
basedir=/fgedudb/fgedudb-base-97
datadir=/fgedudb/fgedudb-data-97-s
tmpdir=/fgedudb/fgedudb-tmp-97
socket=/fgedudb/fgedudb-data-97-s/mysql.sock
pid-file=/fgedudb/fgedudb-data-97-s/mysql.pid
port=3306
server-id=202
lower_case_table_names=1
character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci

#64G内存8CPU参数
innodb_buffer_pool_size=32G
innodb_buffer_pool_instances=16
innodb_log_file_size=4G
innodb_log_files_in_group=2
innodb_flush_method=O_DIRECT

max_connections=800
max_connect_errors=1000
max_allowed_packet=64M
tmp_table_size=2G
max_heap_table_size=2G

#复制参数
log_bin=/fgedudb/fgedudb-log-97-s/fgedudb-s-bin
binlog_format=ROW
binlog_row_image=MINIMAL
sync_binlog=1
binlog_expire_logs_seconds=604800
max_binlog_size=1G
relay_log=/fgedudb/fgedudb-log-97-s/fgedudb-s-relay
relay_log_recovery=ON
replica_parallel_workers=8

#GTID
gtid_mode=ON
enforce_gtid_consistency=ON
log_replica_updates=ON
replica_allow_higher_version_source=OFF

#从库只读
read_only=ON
super_read_only=ON

#日志
log_error=/fgedudb/fgedudb-log-97-s/fgedudb-s-err.log
slow_query_log=ON
slow_query_log_file=/fgedudb/fgedudb-log-97-s/fgedudb-s-slow.log
long_query_time=2

[mysql]
default-character-set=utf8mb4

[client]
port=3306
socket=/fgedudb/fgedudb-data-97-s/mysql.sock
default-character-set=utf8mb4
“`

### 3.6 MySQL9.7从库初始化、systemd、启动实例
“`bash
cd /fgedudb/fgedudb-base-97/bin
./mysqld –defaults-file=/fgedudb/fgedudb-conf-97/my.cnf.s –initialize –user=mysql
“`
编写从库systemd单元`/etc/systemd/system/mysqld‑fgedudb‑97s.service`
“`ini
[Unit]
Description=MySQL fgedudb 9.7 Replica Service
After=network.target

[Service]
Type=notify
User=mysql
Group=mysql
ExecStart=/fgedudb/fgedudb-base-97/bin/mysqld –defaults-file=/fgedudb/fgedudb-conf-97/my.cnf.s
ExecReload=/fgedudb/fgedudb-base-97/bin/mysqladmin shutdown
Restart=on-failure
LimitNOFILE=65535
LimitNPROC=65535

[Install]
WantedBy=multi-user.target
“`
“`bash
systemctl daemon-reload
systemctl start mysqld-fgedudb-97s.service
systemctl enable mysqld-fgedudb-97s.service
“`
登录从库修改root密码：
“`bash
/fgedudb/fgedudb-base-97/bin/mysql -u root -p -S /fgedudb/fgedudb-data-97-s/mysql.sock
“`
“`sql
ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘Fg@fgedudb2026′;
“`

### 3.7 MySQL9.7 GTID主从复制配置与验证
登录9.7从库执行复制配置：
“`sql
CHANGE REPLICATION SOURCE TO
SOURCE_HOST=’fgedu-net-cn1′,
SOURCE_PORT=3306,
SOURCE_USER=’fgedurep’,
SOURCE_PASSWORD=’Fg@fgedurep2026′,
SOURCE_AUTO_POSITION=1;

START REPLICA;
SHOW REPLICA STATUS\G
“`
确认IO、SQL线程全部Yes，Seconds_Behind_Source等于0。

主库写入测试数据：
“`sql
USE fgedudb;
CREATE TABLE t_fg_97test(id INT PRIMARY KEY,info VARCHAR(100));
INSERT INTO t_fg_97test VALUES(100,’9.7主从复制测试’);
COMMIT;
“`
从库查询验证数据同步。

### 3.8 MySQL9.7复制新增监控组件实战
MySQL9.7将复制应用指标组件下放到社区版，可以直接观测复制回放性能指标，查看复制applier运行指标：
“`sql
SELECT * FROM performance_schema.replication_applier_status_by_worker;
SELECT * FROM performance_schema.replication_connection_status;
“`
通过这两张系统表，可以直接获取从库回放事务数、延迟状态、错误信息，不需要编写复杂解析脚本。

### 3.9 MySQL9.7计划内主从切换操作
操作流程逻辑与8.4大体一致，区别是9.7不支持老认证插件，切换完成后复制账号认证方式不变。
1. 业务停止写入，原主库执行`FLUSH TABLES WITH READ LOCK;`
2. 从库确认GTID完全追上，`SHOW REPLICA STATUS\G`
3. 从库停止复制，`RESET REPLICA ALL;`关闭read_only、super_read_only，提升为新主库
4. 原主库解锁，配置复制指向新主库，开启复制链路，业务切换连接地址。

## 四、MySQL主从复制日常运维、监控与故障排查
### 4.1 主从复制日常运维命令汇总
查看复制状态（8.4/9.7统一语法）：
“`sql
SHOW REPLICA STATUS\G
“`
关键监控字段说明：
1. `Replica_IO_Running`：IO线程状态，负责从主库拉取binlog；NO代表网络、账号权限、server‑id冲突、认证失败；
2. `Replica_SQL_Running`：SQL回放线程，NO代表SQL执行报错，数据冲突；
3. `Last_IO_Error`、`Last_SQL_Error`：IO线程、SQL线程详细报错信息；
4. `Seconds_Behind_Source`：从库复制延迟，单位秒；
5. `Retrieved_Gtid_Set`：从库IO线程已经接收的GTID集合；
6. `Executed_Gtid_Set`：从库SQL线程已经回放完成的GTID集合。

查看主库binlog信息：
“`sql
SHOW BINARY LOGS;
SHOW MASTER STATUS;
“`
复制启停命令：
“`sql
START REPLICA;
STOP REPLICA;
START REPLICA IO_THREAD;
STOP REPLICA IO_THREAD;
START REPLICA SQL_THREAD;
STOP REPLICA SQL_THREAD;
“`

### 4.2 主从复制日常监控指标清单
1. IO线程、SQL线程运行状态；
2. Seconds_Behind_Source复制延迟；
3. binlog磁盘剩余空间，binlog自动清理配置；
4. relaylog日志磁盘占用；
5. GTID集合对比，主库Executed_Gtid_Set与从库Executed_Gtid_Set；
6. 半同步复制状态（开启半同步环境）；
7. 从库read_only、super_read_only是否保持开启，防止人为写入；
8. 错误日志复制相关告警报错。

### 4.3 常见复制故障实战处理
#### 故障1：Replica_IO_Running=NO
常见根因：网络不通、防火墙端口拦截；复制账号密码错误；账号没有REPLICATION SLAVE权限；主从server‑id重复；MySQL9.7老客户端caching_sha2_password认证报错。
排查步骤：
1. 从库使用mysql客户端手工测试连接复制账号到主库，验证账号网络连通与认证；
2. 核对主从server‑id，复制拓扑不能重复；
3. 读取`Last_IO_Error`字段，查看详细报错；
4. MySQL9.7环境，复制链路必须支持sha2安全认证。

#### 故障2：Replica_SQL_Running=NO，SQL线程报错
典型报错1062主键冲突，1032记录不存在。
产生原因：从库已经存在对应数据，主库执行插入或者更新，回放的时候主键冲突；人为直接写入从库造成数据不一致。

GTID环境下跳过单个事务：
“`sql
STOP REPLICA;
SET GTID_NEXT=’对应的失败事务GTID’;
BEGIN;COMMIT;
SET GTID_NEXT=’AUTOMATIC’;
START REPLICA;
“`
>注意：跳过事务只作为临时应急手段，业务需要后续做数据一致性校验，pt‑table‑checksum工具校验主从数据一致性。

#### 故障3：主从复制延迟高Seconds_Behind_Source持续很大
排查方向：
1. 从库服务器CPU、IO负载过高，硬件性能不足；
2. 主库存在大事务，binlog产生大事务事件，从库回放慢；
3. 从库并行回放参数`replica_parallel_workers`配置过小；
4. 从库存在慢查询，锁等待阻塞复制SQL线程；
优化手段：拆分主库大事务，调大replica_parallel_workers，优化从库硬件IO，避免从库长事务长锁。

#### 故障4：从库宕机重启后复制报错relaylog损坏
开启参数`relay_log_recovery=ON`，实例重启会自动重新从主库拉取binlog重建relaylog，规避中继日志损坏问题，生产环境从库务必开启该参数。

### 4.4 主从数据一致性校验与修复
生产环境定期校验主从数据一致性，常用pt‑table‑checksum工具，对表做块级校验，发现不一致后使用pt‑table‑sync修复数据差异。不建议生产环境随意使用sql_slave_skip_counter跳过错误，会造成主从数据隐性不一致。

## 风哥针对本文总结
风哥教程本文完整完成两套相互独立Linux平台MySQL主从复制实战，第一套为MySQL8.4一主一从GTID主从集群，第二套为MySQL9.7一主一从GTID主从集群；两套环境互不依赖，全部配置基于64G内存8CPU企业硬件规格，统一路径`/fgedudb`，实例名`fgedudb`，业务账号`fgedu`，复制账号`fgedurep`，使用主机`fgedu‑net‑cn1`、`fgedu‑net‑cn2`完成全部部署操作。

本套风哥教程覆盖主从复制底层理论、GTID机制、8.4与9.7版本复制能力差异、操作系统环境标准化、二进制完整安装、my.cnf生产参数配置、GTID主从搭建、clone插件物理快照快速部署从库、半同步复制配置、计划内主从切换、9.7新增复制监控组件、日常运维监控指标、常见复制故障处理。

风哥针对本文做几点关键运维总结：
1. 生产环境主从复制强制使用GTID模式，binlog格式强制ROW，不要使用传统文件位点复制；从库开启`relay_log_recovery=ON`，`read_only`+`super_read_only`，杜绝人为写入从库造成数据不一致。
2. MySQL9.7彻底移除mysql_native_password认证插件，复制账号全部为caching_sha2_password，旧版本客户端、驱动会出现复制链路认证失败，部署升级前需要提前做兼容性验证。
3. 大数据量场景搭建从库优先选择clone插件物理拷贝方式，相比mysqldump逻辑备份极大缩短初始化耗时，clone操作会覆盖从库datadir，操作前做好备份。
4. 半同步复制可以降低主库宕机数据丢失风险，根据业务数据可靠性要求开启，半同步会带来少量主库性能损耗，上线前压力测试评估性能影响。
5. 计划内主从切换必须在业务维护窗口，停止业务写入，确认从库GTID完全追上之后再执行提升操作；故障切换场景需要甄别数据完整性风险，优先选择GTID集合最新的从库提升为新主库。
6. IO线程、SQL线程报错优先读取`SHOW REPLICA STATUS\G`中Last_IO_Error与Last_SQL_Error详细报错，不要盲目跳过复制错误；跳过事务属于应急手段，后续必须执行主从数据一致性校验。
7. 两套版本环境硬件参数虽然配置大体一致，但MySQL9.7废弃了更多旧参数，迁移配置文件时需要清理废弃参数，否则实例无法启动。
8. 主从复制不等于数据备份，复制不能替代物理备份与逻辑备份，生产环境必须独立做定期备份策略，复制只解决容灾、读写分离场景需求。

掌握本套风哥教程全部理论与实战操作，可以独立完成MySQL8.4、MySQL9.7主从复制集群实施、运维、故障处理，满足企业生产环境主从复制交付要求。