03.MySQL 8.0.35 主从复制搭建(SOP) - CentOS 7 版

liaowenxiu2018

806人浏览 · 2026-01-24 22:21:22

liaowenxiu2018 · 2026-01-24 22:21:22 发布

📘 MySQL 8.0.35 主从复制搭建 (SOP) - CentOS 7 版

文档编号：OPS-MYSQL-002-REPLICATION
适用版本：MySQL 8.0.35
部署模式：主从复制（异步 + 半同步）
操作系统：CentOS 7.x / RedHat 7.x
文档版本：v1.0
最后更新：2026-01-23

1. 环境规划与前置检查

1.1 节点规划与角色分配

节点名称	IP 地址	角色	Server-ID	备注
mysql-master	172.16.0.119	主库 (Master/Source)	10	负责写操作
mysql-slave	172.16.0.161	从库 (Slave/Replica)	20	负责读操作

💡 说明：

主库 (Master/Source): 处理所有写操作，并将数据变更记录到二进制日志（Binlog）。

从库 (Slave/Replica): 从主库复制数据变更并应用到自身，主要用于读操作或备份。

Server-ID: 每个MySQL实例在复制拓扑中的唯一标识，必须不同。

1.2 前置检查

在开始之前，请确保：

两台服务器均已按照 [MySQL 8.0.35 单机部署 SOP] 完成安装、初始化和基础优化。
两台服务器的MySQL服务均正常运行（systemctl status mysqld）。
两台服务器的网络互通，并且防火墙已开放 3306 端口（或已关闭防火墙）。
主库和从库的时间同步（建议配置NTP）。

# 在两台服务器上分别执行，检查时间是否同步
date
# 如果时间不同步，请先同步时间，例如：
# ntpdate -u time.nist.gov

2. 主库（Master）配置

2.1 修改主库配置文件

编辑主库的 /etc/my.cnf 文件，确保以下关键参数配置正确。注意与单机部署SOP中的配置进行对比和调整。

# 在主库 (172.16.0.119) 上操作
vim /etc/my.cnf

添加/修改以下关键参数（在单机部署SOP配置的基础上）：

[mysqld]
################### 基础配置 (确保不同) ###################
server-id = 10  # 主库的唯一ID，请设置为非0值
################### Binlog 与复制 (GTID) ###################
log-bin = /datas/mysql/3306/logs/mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800  # 7天
max_binlog_size = 1G
binlog_cache_size = 32M
max_binlog_cache_size = 2G
# 开启 GTID (强烈推荐，简化复制管理)
gtid_mode = ON
enforce_gtid_consistency = ON
# 从库配置（即使当前是主库，也建议配置以支持未来角色切换）
log_replica_updates = 1  # MySQL 8.0.17+ 替代了 log_slave_updates
skip_replica_start = ON   # MySQL 8.0.22+ 替代了 skip_slave_start
# 指定 Relay Log 文件名和路径（为未来切换预留）
relay_log = /datas/mysql/3306/logs/mysql-relay-bin
relay_log_index = /datas/mysql/3306/logs/mysql-relay-bin.index
relay_log_purge = ON
max_relay_log_size = 1G

⚠️ 重要：务必确保 server-id 唯一，否则复制无法正确建立。

2.2 创建用于复制的用户

在主库上创建一个专门用于从库连接复制的用户。

# 1. 登录主库 MySQL
mysql -uroot -p'WeiC@%202601'
# 2. 创建复制用户 (REPLICATION SLAVE 权限是关键)
CREATE USER 'repl'@'%' IDENTIFIED BY 'Repl@%202601';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
# 3. 刷新权限
FLUSH PRIVILEGES;
# 4. (可选) 查看主库状态，记录 File 和 Position 值（用于非GTID模式）
SHOW MASTER STATUS;
# 如果开启了 GTID，此步的 File/Position 信息不重要，但可记录用于后续验证。

💡 说明：repl 的密码请设置为强密码。主机地址 172.16.0.161 是从库的IP，为了安全，请限制为具体的从库IP，而不是 %。

2.3 重启主库服务

使配置生效。

systemctl restart mysqld
systemctl status mysqld

3. 从库（Slave）配置

3.1 修改从库配置文件

编辑从库的 /etc/my.cnf 文件，进行以下关键修改。

# 在从库 (172.16.0.161) 上操作
vim /etc/my.cnf

添加/修改以下关键参数：

[mysqld]
################### 基础配置 (确保不同) ###################
server-id = 20  # 从库的唯一ID，与主库不同，且非0值
################### Binlog 与复制 (GTID) ###################
# 即使从库，也建议开启 Binlog，用于备份或级联复制
log-bin = /datas/mysql/3306/logs/mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800
max_binlog_size = 1G
# 开启 GTID (与主库保持一致)
gtid_mode = ON
enforce_gtid_consistency = ON
# 从库核心配置
read_only = 1  # 设置为只读，防止误操作写从库
super_read_only = 1  # 超级用户也只能读
# 指定 Relay Log 文件名和路径
relay_log = /datas/mysql/3306/logs/mysql-relay-bin
relay_log_index = /datas/mysql/3306/logs/mysql-relay-bin.index
relay_log_purge = ON
max_relay_log_size = 1G
# 跳过复制错误（谨慎使用，生产环境通常设为OFF以发现并解决问题）
# slave_skip_errors = all

⚠️ 重要：server-id 必须唯一。read_only 和 super_read_only 是重要的安全设置。

3.2 重启从库服务

使配置生效。

systemctl restart mysqld
systemctl status mysqld

4. 建立主从复制关系

4.1 在从库上配置复制通道

在从库上执行 CHANGE REPLICATION SOURCE TO 语句，指向主库并指定复制用户。

# 1. 登录从库 MySQL
mysql -uroot -p'WeiC@%202601'
# 2. 停止可能存在的旧复制线程（如果有）
STOP REPLICA;
# 3. 配置复制信息 (使用 GTID 模式，推荐)
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='172.16.0.119',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='Repl@%202601',
  SOURCE_PORT=3306,
  SOURCE_AUTO_POSITION=1;  -- 启用 GTID 自动定位
# (备选) 传统非GTID模式，需要手动指定 MASTER_LOG_FILE 和 MASTER_LOG_POS
-- CHANGE REPLICATION SOURCE TO
--   SOURCE_HOST='172.16.0.119',
--   SOURCE_USER='repl',
--   SOURCE_PASSWORD='Repl@%202601',
--   SOURCE_PORT=3306,
--   SOURCE_LOG_FILE='mysql-bin.00000X',  -- 在主库 SHOW MASTER STATUS; 获取
--   SOURCE_LOG_POS=XXX;                 -- 在主库 SHOW MASTER STATUS; 获取
# 4. 启动复制线程
START REPLICA;
# 5. 查看复制状态
SHOW REPLICA STATUS\G

4.2 检查复制状态

在从库上执行 SHOW REPLICA STATUS;，重点检查以下字段。

# 在从库上执行
SHOW REPLICA STATUS\G

关键状态字段检查清单：

字段	预期值	说明
`Replica_IO_Running`	Yes	I/O线程已成功连接到主库并正在请求Binlog
`Replica_SQL_Running`	Yes	SQL线程正在成功读取并执行Relay Log中的事件
`Seconds_Behind_Master`	0 或很小的值	复制延迟，0表示无延迟
`Source_Host`, `Source_User`	与配置一致	确认连接到了正确的主库和用户
`Last_Error`, `Last_SQL_Error`	(空)	无错误
`Retrieved_Gtid_Set`	主库的GTID范围	从库已接收的GTID集合
`Executed_Gtid_Set`	主库的GTID范围	从库已执行的GTID集合

⚠️ 如果 Replica_IO_Running 或 Replica_SQL_Running 为 No，请检查 Last_Error 或 Last_SQL_Error 字段，并根据错误信息进行排查（参考第7章）。

5. 配置并启用半同步复制

半同步复制是对异步复制的一种改进，主库在事务提交前会等待至少一个从库确认已接收并写入Relay Log，从而在主库故障时减少数据丢失的风险。

5.1 安装半同步复制插件

主库和从库都需要安装插件。

# ------------------ 在主库上操作 ------------------
mysql -uroot -p'WeiC@%202601'
# 安装主库插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
# 查看插件是否已安装
SHOW PLUGINS LIKE '%semi%';

# ------------------ 在从库上操作 ------------------
mysql -uroot -p'WeiC@%202601'
# 安装从库插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
# 查看插件是否已安装
SHOW PLUGINS LIKE '%semi%';

5.2 启用半同步复制

主库和从库都需要启用。

# ------------------ 在主库上操作 ------------------
mysql -uroot -p'WeiC@%202601'
# 启用半同步复制
SET GLOBAL rpl_semi_sync_master_enabled = 1;
# 设置等待超时时间（毫秒），超时后降级为异步复制
SET GLOBAL rpl_semi_sync_master_timeout = 1000;  -- 1秒
# 查看半同步变量
SHOW VARIABLES LIKE 'rpl_semi_sync%';

# ------------------ 在从库上操作 ------------------
mysql -uroot -p'WeiC@%202601'
# 启用半同步复制
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
# 重启从库的I/O线程以使配置生效
STOP REPLICA IO_THREAD;
START REPLICA IO_THREAD;
# 查看半同步变量
SHOW VARIABLES LIKE 'rpl_semi_sync%';

💡 提示：以上命令是临时生效，重启MySQL后会失效。要永久生效，请将以下配置添加到各自的 /etc/my.cnf 文件的 [mysqld] 部分后重启MySQL。
主库 /etc/my.cnf 添加：
plugin-load = "rpl_semi_sync_master=semisync_master.so"
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 1000
从库 /etc/my.cnf 添加：
plugin-load = "rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_slave_enabled = 1

5.3 验证半同步复制状态

# ------------------ 在主库上查看状态 ------------------
mysql -uroot -p'WeiC@%202601' -e "SHOW STATUS LIKE 'Rpl_semi_sync_master%';"

关键状态指标说明：

状态变量	预期值	说明
`Rpl_semi_sync_master_status`	ON	半同步复制是否处于激活状态
`Rpl_semi_sync_master_yes_tx`	计数增加	已成功通过半同步方式提交的事务数
`Rpl_semi_sync_master_no_tx`	计数	超时降级为异步复制的事务数（理想情况下应为0或很少）
`Rpl_semi_sync_master_clients`	>= 1	当前已连接并启用半同步的从库数量

# ------------------ 在从库上查看状态 ------------------
mysql -uroot -p'WeiC@%202601' -e "SHOW STATUS LIKE 'Rpl_semi_sync_slave%';"

关键状态指标说明：

状态变量	预期值	说明
`Rpl_semi_sync_slave_status`	ON	半同步复制是否处于激活状态

💡 测试：在主库上创建一个数据库或表，然后观察主库的 Rpl_semi_sync_master_yes_tx 计数是否增加，从库上是否同步成功。

6. 主从复制验证与监控

6.1 数据同步验证

在主库上创建测试数据：

mysql -uroot -p'WeiC@%202601' -e "CREATE DATABASE replication_test; USE replication_test; CREATE TABLE test_table (id INT, name VARCHAR(20)); INSERT INTO test_table VALUES (1, 'from_master');"

在从库上验证数据：
```
mysql -uroot -p'WeiC@%202601' -e "SELECT * FROM replication_test.test_table;"
```
预期结果：应能查询到主库插入的数据 (1, 'from_master')。

6.2 复制延迟监控

定期在从库上执行以下命令监控延迟。


mysql -uroot -p'WeiC@%202601' -e "SHOW REPLICA STATUS\G" | grep -E "Seconds_Behind_Master|Source_Bind"

0：表示无延迟。
持续大于0：表示存在延迟，需排查原因（如网络慢、从库负载高、大事务等）。

6.3 半同步状态监控

定期在主库上执行以下命令监控半同步状态。

mysql -uroot -p'WeiC@%202601' -e "SHOW STATUS LIKE 'Rpl_semi_sync_master%';"

关注 Rpl_semi_sync_master_status 是否为 ON。
关注 Rpl_semi_sync_master_no_tx 的值，频繁增长意味着半同步经常超时降级为异步，需优化网络或调整超时时间。

7. 常见问题排查

7.1 复制 I/O 线程报错：`Could not find first log file name in binary log index file`

原因：通常是由于主库的Binlog被意外清理（如执行了 PURGE BINARY LOGS 或 RESET MASTER），而从库还请求旧的Binlog文件。
解决：

# 在从库上操作
mysql> STOP REPLICA;
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='172.16.0.119', SOURCE_USER='repl', SOURCE_PASSWORD='Repl@%202601', SOURCE_PORT=3306, SOURCE_AUTO_POSITION=1;
mysql> START REPLICA;

如果使用GTID，SOURCE_AUTO_POSITION=1 会自动处理这个问题。如果非GTID模式，需重新获取主库 SHOW MASTER STATUS 中的 File 和 Position。

7.2 复制 SQL 线程报错：`Duplicate entry '...' for key 'PRIMARY'`

原因：主从数据不一致，从库已存在该记录。可能是从库被意外写入了数据，或之前跳过错误导致。
解决：

定位并分析错误：在从库上执行 SHOW REPLICA STATUS\G 查看 Last_SQL_Error，找到导致错误的SQL语句。
手动处理数据：根据实际情况，在从库上删除或修改冲突的记录，使其与主库预期状态一致。

跳过该错误（谨慎操作，仅适用于测试或数据可丢失的场景）：

mysql> STOP REPLICA SQL_THREAD;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;  -- MySQL 8.0.22+ 替代了 SET GLOBAL sql_slave_skip_counter
mysql> START REPLICA SQL_THREAD;

GTID模式下，跳过一个事务的方法更复杂，通常需要注入空事务：

mysql> STOP REPLICA;
mysql> SET GTID_NEXT='master-bin.00000X:XXX';  -- 错误事务的GTID，从 Last_SQL_Error 获取
mysql> BEGIN; COMMIT;
mysql> SET GTID_NEXT='AUTOMATIC';
mysql> START REPLICA;

7.3 半同步复制状态为 OFF

原因：可能是从库的半同步插件未启用、I/O线程未运行、或超时时间太短。
解决：

检查从库是否安装并启用了半同步插件：

mysql> SHOW PLUGINS LIKE '%semi%';
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync_slave_enabled';

确保从库的I/O线程正在运行：

mysql> SHOW REPLICA STATUS\G | grep "Replica_IO_Running"

调整主库的超时时间：

mysql> SET GLOBAL rpl_semi_sync_master_timeout = 10000;  -- 调整为10秒

7.4 从库无法连接主库（`ERROR 1130: Host '...' is not allowed to connect to this MySQL server`）

原因：主库上的复制用户 repl 的主机权限设置不正确，或防火墙阻止了连接。
解决：

检查用户权限：在主库上执行：

mysql> SELECT user, host FROM mysql.user WHERE user='repl';

确保从库的IP 172.16.0.161 在允许的 host 列表中。如不是，则需重新授权：

mysql> DROP USER 'repl'@'...';
mysql> CREATE USER 'repl'@'172.16.0.161' IDENTIFIED BY 'Repl@%202601';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.16.0.161';
mysql> FLUSH PRIVILEGES;

检查防火墙：确保主库的防火墙（firewalld 或 iptables）开放了3306端口，或者临时关闭防火墙测试。

7.5 主从数据不一致

原因：从库被意外写入、跳过错误操作、或未正常开启复制。
解决：

使用 pt-table-checksum 工具（Percona Toolkit）进行主从数据一致性校验。
使用 pt-table-sync 工具修复不一致的数据。
重新搭建从库：如果数据差异巨大且无法修复，最可靠的方法是重新从主库备份并搭建从库。

📋 附录

A. 主从复制核心参数速查表

参数	主库	从库	作用与说明
server-id	必须配置且唯一	必须配置且唯一	复制拓扑中的唯一标识符。
log-bin	建议开启	建议开启	开启二进制日志，是复制的基础。从库开启用于级联复制。
binlog_format	ROW	ROW	复制格式，ROW格式最安全可靠。
gtid_mode	ON	ON	启用全局事务ID，简化复制管理和故障恢复。
enforce_gtid_consistency	ON	ON	强制GTID一致性，确保事务安全。
read_only	OFF	ON	从库设置只读，防止误写入。
super_read_only	OFF	ON	从库设置超级用户只读。
relay_log	可选	建议配置	指定中继日志文件路径和名称。
log_replica_updates	ON	ON	从库是否记录自己的数据变更到Binlog。
rpl_semi_sync_master_enabled	1	-	主库启用半同步复制。
rpl_semi_sync_slave_enabled	-	1	从库启用半同步复制。

B. 常用运维SQL命令速查表

操作	SQL命令	说明
查看主库状态	`SHOW MASTER STATUS;`	查看主库Binlog文件和位置（非GTID模式）。
查看从库状态	`SHOW REPLICA STATUS\G`	查看从库复制线程运行状态和延迟。
查看GTID执行情况	`SHOW VARIABLES LIKE 'gtid%';` `SHOW VARIABLES LIKE 'gtid_executed';`	查看GTID相关变量和已执行的GTID。
启动复制线程	`START REPLICA;`	启动复制线程。
停止复制线程	`STOP REPLICA;`	停止复制线程。
重置从库复制信息	`RESET REPLICA ALL;`	清除所有复制配置和 Relay Log，需重新配置。
跳过一个复制错误（非GTID）	`SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START REPLICA SQL_THREAD;`	跳过一个错误事务（谨慎使用）。