数据库教程FGMT32‑MySQL主从复制项目实施与维护05(MySQL8.4/9.7主从复制)

举报
风哥数据库教程 发表于 2026/09/14 09:31:33 2026/09/14
【摘要】 数据库教程FGMT32‑MySQL主从复制项目实施与维护05(MySQL8.4/9.7主从复制) 前言与教程大纲介绍风哥教程本文面向数据库DBA、运维工程师、数据库架构师，完整覆盖两套相互独立的Linux平台MySQL主从复制环境实施：第一套为MySQL8.4一主一从复制集群，第二套为MySQL9.7一主一从复制集群。两套环境完全隔离，互不依赖。本套风哥教程所有硬件基准为64G物理内存、8C...

数据库教程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操作系统用户组与用户：
groupadd mysql
useradd -r -g mysql -s /sbin/nologin mysql
  1. 创建MySQL8.4全套目录结构（两台主机均执行）：
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
  1. 修改目录权限属主属组：
chown -R mysql:mysql /fgedudb
chmod -R 750 /fgedudb
  1. 操作系统内核参数配置，编辑/etc/security/limits.conf，增加下面内容：
mysql soft nofile 65535
mysql hard nofile 65535
mysql soft nproc 65535
mysql hard nproc 65535
  1. 关闭透明大页，永久生效写入/etc/rc.local：
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
  1. 防火墙放行3306端口：
firewall-cmd --permanent --add-port=3306/tcp
firewall-cmd --reload
  1. SELinux设置为permissive模式，编辑/etc/selinux/config设置SELINUX=permissive，执行setenforce 0临时生效。

2.2 MySQL8.4二进制包解压部署（两台主机）

将MySQL8.4 Linux‑glibc二进制包上传到/fgedudb/soft目录，两台主机执行解压：

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完整配置如下：

[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必须放在第一个参数：
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获取临时密码。

  1. 编写systemd服务单元文件/etc/systemd/system/mysqld‑fgedudb‑84m.service
[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
  1. 加载systemd，启动数据库实例：
systemctl daemon-reload
systemctl start mysqld-fgedudb-84m.service
systemctl enable mysqld-fgedudb-84m.service
systemctl status mysqld-fgedudb-84m.service
  1. 登录主库，修改root密码，创建业务库、业务账号、复制专用账号fgedurep
/fgedudb/fgedudb-base-84/bin/mysql -u root -p -S /fgedudb/fgedudb-data-84-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'@'%';
-- 创建复制账号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不相同。

[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配置、实例启动

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

[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，启动从库实例：

systemctl daemon-reload
systemctl start mysqld-fgedudb-84s.service
systemctl enable mysqld-fgedudb-84s.service

登录从库修改root本地密码：

/fgedudb/fgedudb-base-84/bin/mysql -u root -p -S /fgedudb/fgedudb-data-84-s/mysql.sock

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文件名与位点。

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：

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查询，验证数据同步：

USE fgedudb;
SELECT * FROM t_fg_test;

能够查询到插入的数据，代表GTID主从复制搭建完成。

2.9 Clone插件快速搭建从库实战（MySQL8.4）

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

  1. 主库fgedu‑net‑cn1安装clone插件，创建clone专用用户：
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
CREATE USER 'fgeduclone'@'%' IDENTIFIED BY 'Fg@fgeduclone2026';
GRANT CLONE_ADMIN ON *.* TO 'fgeduclone'@'%';
FLUSH PRIVILEGES;
  1. 待搭建的新从库实例，初始化完成，实例正常启动，执行远程clone拉取主库数据：
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. 主库执行：
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]段永久生效：

rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=10000
rpl_semi_sync_master_wait_for_slave_count=1
  1. 从库执行：
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永久生效：

rpl_semi_sync_slave_enabled=1
  1. 主库查看半同步状态：
SHOW GLOBAL STATUS LIKE 'rpl_semi_sync%';

2.11 MySQL8.4计划内主从切换实战（Switchover，业务维护窗口操作）

计划内切换，业务停止写入，将从库提升为新主库，原主库变为新从库。

  1. 业务侧停止写入，应用停止连接数据库；在原主库执行，确保所有binlog全部推送到从库：
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
  1. 在从库fgedu‑net‑cn2，确认复制无延迟，Seconds_Behind_Source=0，GTID集合完全追上主库。
SHOW REPLICA STATUS\G
  1. 在从库停止复制，清除复制信息，关闭只读参数，提升为新主库：
STOP REPLICA;
RESET REPLICA ALL;
SET GLOBAL read_only=OFF;
SET GLOBAL super_read_only=OFF;
  1. 原主库fgedu‑net‑cn1解锁表，关闭只读，配置复制指向新主库（fgedu‑net‑cn2）：
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;
  1. 验证双向复制状态，业务应用修改连接地址指向新主库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执行

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二进制包解压部署，两台主机执行

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相关参数，新增复制参数。

[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配置，启动实例

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

[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启动实例：

systemctl daemon-reload
systemctl start mysqld-fgedudb-97m.service
systemctl enable mysqld-fgedudb-97m.service

登录主库修改root密码，创建业务库、业务账号、复制账号fgedurep，9.7强制使用caching_sha2_password：

/fgedudb/fgedudb-base-97/bin/mysql -u root -p -S /fgedudb/fgedudb-data-97-m/mysql.sock

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不重复

[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、启动实例

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

[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

systemctl daemon-reload
systemctl start mysqld-fgedudb-97s.service
systemctl enable mysqld-fgedudb-97s.service

登录从库修改root密码：

/fgedudb/fgedudb-base-97/bin/mysql -u root -p -S /fgedudb/fgedudb-data-97-s/mysql.sock

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Fg@fgedudb2026';

3.7 MySQL9.7 GTID主从复制配置与验证

登录9.7从库执行复制配置：

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。

主库写入测试数据：

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运行指标：

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统一语法）：

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信息：

SHOW BINARY LOGS;
SHOW MASTER STATUS;

复制启停命令：

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环境下跳过单个事务：

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主从复制集群实施、运维、故障处理，满足企业生产环境主从复制交付要求。

【声明】本内容来自华为云开发者社区博主，不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息，否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MySQL 数据库
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入