MySQL 主从复制完整实战指南：从零搭建到生产运维
目录
- 一、概述：什么是主从复制
- 二、核心原理：数据是怎么"跑"到从库的
- 三、主库配置：让数据有源可溯
- 四、从库配置：让数据如影随形
- 五、功能测试：验证复制是否"靠谱"
一、概述：什么是主从复制
1.1 核心定义
MySQL 主从复制（Replication） 是指数据从一个 MySQL 数据库服务器（称为主库/Master）复制到一个或多个 MySQL 数据库服务器（称为从库/Slave）的过程。
用一个生活化的类比：
主库就像出版社的总编室，所有内容都在这里定稿；从库就像全国各地的分印厂，总编室一有新内容，分印厂就照着印一份。读者可以去任意一家分印厂取报纸，不用都挤到总编室去。
1.2 四大核心应用价值
| 价值 | 说明 | 业务场景举例 |
|---|---|---|
| 读写分离 | 主库只处理写操作，从库分担读操作，突破单机性能瓶颈 | 电商大促：下单走主库，商品列表、订单查询走从库 |
| 数据冗余 | 从库是主库的实时副本，主库故障时从库可快速接管 | 主库宕机，30 秒内切换到从库，业务无感知 |
| 异地容灾 | 从库部署在不同城市/机房，抵御区域性灾难 | 机房断电、光缆被挖断，异地从库继续提供服务 |
| 数据备份 | 在从库上执行备份，不影响主库性能 | 每晚在从库做全量备份，主库正常处理业务 |
1.3 适用业务场景
主从复制不是银弹，适合这些场景：
- 读多写少：如新闻门户、商品详情页、报表系统
- 需要高可用：如支付系统、订单系统，不能容忍长时间宕机
- 数据分析：在从库跑复杂 SQL，不影响线上业务
- 滚动升级：先在从库升级 MySQL 版本，验证无误后再切主
不适合的场景：
- 写多读少（如日志收集系统），主库本身压力就大，从库帮助有限
- 强一致性要求极高（如金融交易），异步复制有延迟，可能读到旧数据
1.4 基础架构示意图
+------------------+ +------------------+
| 客户端/应用 | | 客户端/应用 |
| (写操作: INSERT | | (读操作: SELECT |
| UPDATE/DELETE) | | SHOW/查询) |
+--------+---------+ +--------+---------+
| |
v v
+------------------+ +------------------+
| 主库 Master | | 从库 Slave 1 |
| 192.168.1.100 | | 192.168.1.101 |
| | | |
| - 处理所有写请求 |-------->| - 处理读请求 |
| - 记录 Binlog | 复制流 | - 重放中继日志 |
| - 生成复制事件 | | - 保持数据同步 |
+------------------+ +------------------+
|
| 级联复制（可选）
v
+------------------+
| 从库 Slave 2 |
| 192.168.1.102 |
| （异地容灾节点） |
+------------------+关键角色说明：
- 主库（Master）：数据的唯一写入点，负责记录所有变更到 Binlog
- 从库（Slave）：从主库拉取 Binlog，在本地重放，保持数据一致
- 复制流（Replication Stream）：主从之间传输 Binlog 的网络通道
二、核心原理：数据是怎么"跑"到从库的
2.1 三个核心线程
MySQL 主从复制依赖三个线程协同工作，缺一不可：
主库 Master 从库 Slave
+----------------+ +------------------+
| | 1. 发送 Binlog | |
| Binlog Dump |<-----------------| I/O Thread |
| Thread | | |
| (主库) | 2. 返回 Binlog | (从库) |
| |----------------->| |
+----------------+ +--------+---------+
|
| 3. 写入 Relay Log
v
+--------------+
| Relay Log |
| (中继日志) |
+--------------+
|
| 4. 读取并执行
v
+--------------+
| SQL Thread |
| (从库) |
| 重放 SQL |
+--------------+2.1.1 Binlog Dump Thread（主库）
- 职责：当从库 I/O 线程连接上来时，主库会启动一个 Binlog Dump 线程，专门负责给这个从库发送 Binlog 事件
- 特点：每个从库对应一个独立的 Dump 线程，互不干扰
- 类比：就像出版社的专职快递员，只负责给某一家分印厂送报纸
2.1.2 I/O Thread（从库）
- 职责：主动连接主库，请求 Binlog，接收后写入本地 Relay Log
- 状态：
Connecting→Waiting for master to send event→Queueing master event - 类比：分印厂的收报员，每天去总编室取最新报纸，先堆在仓库里
2.1.3 SQL Thread（从库）
- 职责：读取 Relay Log 中的事件，按顺序在从库上执行，使数据与主库一致
- 特点：单线程执行（MySQL 5.6 之前），MySQL 5.7+ 支持多线程并行复制
- 类比：分印厂的印刷工人，从仓库取出报纸，在本地印刷机上重新印一遍
2.2 完整复制流程（时间线拆解）
假设主库执行了一条 UPDATE users SET name = '李四' WHERE id = 1001，完整流程如下：
| 时间 | 步骤 | 主库动作 | 网络传输 | 从库动作 |
|---|---|---|---|---|
| T1 | 1 | 执行 UPDATE，将变更写入 Binlog 缓存 | ||
| T2 | 2 | 事务提交时，Binlog 缓存刷入磁盘文件 | ||
| T3 | 3 | Binlog Dump 线程读取 Binlog | ||
| T4 | 4 | 通过网络发送 Binlog 事件 | ||
| T5 | 5 | I/O 线程接收事件，写入 Relay Log | ||
| T6 | 6 | SQL 线程读取 Relay Log | ||
| T7 | 7 | SQL 线程执行相同的 UPDATE | ||
| T8 | 8 | 从库数据与主库一致 |
关键理解：
- 主库不等待从库确认，T2 完成后就向客户端返回成功（异步复制）
- 从库的数据更新总是滞后于主库，这个延迟叫
Seconds_Behind_Master
2.3 Binlog 三种格式对比
Binlog 是复制的"载体"，格式决定了记录什么、怎么记录。
| 维度 | STATEMENT | ROW | MIXED |
|---|---|---|---|
| 记录内容 | 原始 SQL 语句 | 每行数据的变更前后值 | 混合：一般 SQL 用 STATEMENT，特殊函数用 ROW |
| 日志体积 | 小 | 大（尤其批量更新） | 中等 |
| 主从一致性 | 有风险（如 UUID()、NOW() 函数） | 最安全 | 较好 |
| 恢复灵活性 | 差 | 好（可解析出具体数据） | 较好 |
| MySQL 版本要求 | 所有版本 | 5.1+ | 5.1+ |
| 生产推荐 | 不推荐 | 强烈推荐 | 一般不用 |
2.3.1 STATEMENT 格式示例
# 主库执行
UPDATE users SET name = '李四' WHERE id = 1001;
# Binlog 记录（就是这条 SQL 本身）
UPDATE users SET name = '李四' WHERE id = 1001;问题：如果从库执行时 name 字段有其他触发器/函数影响，结果可能不同。
2.3.2 ROW 格式示例
# 主库执行
UPDATE users SET name = '李四' WHERE id = 1001;
# Binlog 记录（记录每一行的变更）
### UPDATE `test`.`users`
### WHERE
### @1=1001
### @2='张三'
### SET
### @1=1001
### @2='李四'优势：精确记录每行数据的变化，从库重放时结果绝对一致。
2.3.3 如何选择？
- MySQL 5.7+：默认就是 ROW，不用改
- 需要数据闪回/恢复：必须 ROW，才能用 binlog2sql 等工具解析
- 磁盘空间紧张：可以考虑 STATEMENT，但要接受一致性风险
- 混合场景：MIXED 是折中，但生产环境建议直接 ROW
2.4 两种复制模式
2.4.1 异步复制（Asynchronous Replication）
客户端 主库 网络 从库
| | | |
|--UPDATE----->| | |
| |--写 Binlog-------->| |
|<--OK---------| | |
| | |--Binlog------->|
| | | |--执行
| | |<--ACK----------|- 特点：主库执行完就返回，不等从库确认
- 优点：性能最好，延迟最低
- 缺点：主库宕机时，已提交但未同步到从库的数据会丢失
- 适用：对数据一致性要求不极致，追求性能的场景
2.4.2 半同步复制（Semi-synchronous Replication）
客户端 主库 网络 从库
| | | |
|--UPDATE----->| | |
| |--写 Binlog-------->| |
| | |--Binlog------->|
| | | |--执行
| | |<--ACK----------|
|<--OK---------| | |- 特点：主库至少等待一个从库确认收到 Binlog，才向客户端返回成功
- 优点：数据安全性大幅提升，主库宕机时数据不丢失（至少有一个从库有）
- 缺点：增加网络往返延迟，性能下降约 10%-30%
- 开启方式：需要安装插件（
rpl_semi_sync_master和rpl_semi_sync_slave）
2.4.3 如何选择？
| 场景 | 推荐模式 | 理由 |
|---|---|---|
| 金融、支付 | 半同步 | 数据不能丢，性能牺牲可接受 |
| 电商、社交 | 异步 + 定期校验 | 性能优先，偶尔延迟可接受 |
| 数据分析 | 异步 | 分析结果对实时性要求低 |
| 跨地域容灾 | 异步 | 网络延迟大，半同步会拖累主库 |
三、主库配置：让数据"有源可溯"
3.1 配置文件详解（my.cnf / my.ini）
创建或编辑主库配置文件：
ini
[mysqld]
# ============ 基础配置 ============
# MySQL 服务端口
port = 3306
# 数据文件存储路径
datadir = /data/mysql/data
# 字符集
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
# ============ 复制核心配置 ============
# 开启 Binlog，指定文件路径和前缀
# 必须是独立磁盘分区，不要和 datadir 放一起
log_bin = /data/mysql/logs/binlog/mysql-bin
# Binlog 格式：ROW（生产必选）
binlog_format = ROW
# Binlog 过期时间：秒（默认 30 天，建议 7-15 天）
# 根据磁盘空间和备份策略调整
binlog_expire_logs_seconds = 604800 # 7 天
# 单个 Binlog 文件大小：默认 1G，建议保持默认
max_binlog_size = 1073741824
# Binlog 缓存大小：事务未提交时先写缓存
binlog_cache_size = 4194304 # 4M，大事务场景建议调大
# 每个事务的 Binlog 缓存上限
binlog_stmt_cache_size = 4194304
# Binlog 同步策略：每次提交都刷盘（最安全）
# 0：由操作系统决定，性能最好但可能丢数据
# 1：每次提交刷盘（生产推荐）
# N：每 N 次提交刷一次
sync_binlog = 1
# Binlog 校验：确保 Binlog 完整性
binlog_checksum = CRC32
# ============ 服务器标识 ============
# Server ID：主从架构中每个实例必须唯一
# 建议用 IP 最后一段 + 端口号，如 1003306
server_id = 1003306
# ============ 复制过滤规则（可选）============
# 只复制指定数据库（多个用逗号分隔）
# 注意：使用此参数后，未列出的数据库不会被复制
# binlog_do_db = ecommerce,order_center
# 忽略指定数据库（多个用逗号分隔）
# 推荐：排除 mysql、sys、information_schema、performance_schema
binlog_ignore_db = mysql
binlog_ignore_db = sys
binlog_ignore_db = information_schema
binlog_ignore_db = performance_schema
# 忽略指定表（不推荐在主库设置，建议在从库用 replicate-ignore-table）
# binlog_ignore_table = ecommerce.log_table
# ============ GTID 配置（推荐）============
# 开启 GTID（全局事务标识），方便主从切换和故障恢复
gtid_mode = ON
enforce_gtid_consistency = ON
# ============ 半同步复制（可选，金融场景推荐）============
# 需要安装插件：INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
# rpl_semi_sync_master_enabled = 1
# rpl_semi_sync_master_timeout = 1000 # 1 秒，超时后降级为异步
# ============ 性能与安全 ============
# 最大连接数
max_connections = 2000
# InnoDB 缓冲池大小：建议物理内存的 50%-70%
innodb_buffer_pool_size = 8G
# 错误日志
log_error = /data/mysql/logs/error/mysql-error.log
# 慢查询日志
slow_query_log = 1
slow_query_log_file = /data/mysql/logs/slow/mysql-slow.log
long_query_time = 13.2 创建复制账号
原则：最小权限，只给必要的权限。
sql
-- 登录主库
mysql -u root -p
-- 创建复制专用账号
-- 只允许从库 IP 访问，禁止其他 IP
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'Repl@2024!Strong';
-- 只授予 REPLICATION SLAVE 权限，这是复制所需的最小权限
-- 不要授予 ALL PRIVILEGES，避免安全风险
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
-- 刷新权限
FLUSH PRIVILEGES;
-- 查看创建的账号
SELECT user, host, plugin FROM mysql.user WHERE user = 'repl';账号安全建议：
sql
-- 如果允许任意 IP（测试环境方便，生产环境禁止）
CREATE USER 'repl'@'%' IDENTIFIED BY 'Repl@2024!Strong';
-- 生产环境建议限制到具体从库 IP
CREATE USER 'repl'@'192.168.1.101' IDENTIFIED BY 'Repl@2024!Strong';
CREATE USER 'repl'@'192.168.1.102' IDENTIFIED BY 'Repl@2024!Strong';3.3 重启并验证主库
bash
# 1. 检查配置文件语法
mysqld --defaults-file=/etc/my.cnf --validate-config
# 2. 重启 MySQL
systemctl restart mysqld
# 3. 验证 Binlog 是否开启
mysql -u root -p -e "SHOW VARIABLES LIKE 'log_bin';"
# 输出：+---------------+-------+
# | Variable_name | Value |
# +---------------+-------+
# | log_bin | ON |
# +---------------+-------+
# 4. 验证 Server ID
mysql -u root -p -e "SHOW VARIABLES LIKE 'server_id';"
# 输出：+---------------+---------+
# | Variable_name | Value |
# +---------------+---------+
# | server_id | 1003306 |
# +---------------+---------+
# 5. 验证 Binlog 文件是否生成
ls -lh /data/mysql/logs/binlog/
# 应该能看到 mysql-bin.000001 等文件3.4 锁表获取一致性快照
目的：导出初始全量数据时，确保数据的一致性（即导出期间主库数据不变化）。
sql
-- 1. 登录主库
mysql -u root -p
-- 2. 锁表：全局读锁，所有写操作被阻塞
-- 注意：这个锁会阻塞所有写入，尽量在业务低峰期执行
FLUSH TABLES WITH READ LOCK;
-- 3. 获取主库当前 Binlog 文件名和位置
-- 这个信息是从库启动复制的"起点坐标"
SHOW MASTER STATUS\G输出解读：
*************************** 1. row ***************************
File: mysql-bin.000003 <-- 当前使用的 Binlog 文件名
Position: 123456789 <-- 当前写入位置（偏移量）
Binlog_Do_DB: <-- 只复制的数据库（配置 binlog_do_db 时显示）
Binlog_Ignore_DB: mysql,sys,... <-- 忽略的数据库
Executed_Gtid_Set: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5 <-- GTID 集合（开启 GTID 时显示）关键信息：
File+Position：从库CHANGE MASTER TO时需要用到- 如果开启了 GTID，
Executed_Gtid_Set更重要，MASTER_LOG_FILE和MASTER_LOG_POS可以省略
sql
-- 4. 保持这个会话不要断开！锁会随会话断开而释放
-- 另开一个终端，执行数据导出
-- 5. 导出数据（在另一个终端执行）
mysqldump -u root -p \
--single-transaction \ -- 如果是 InnoDB，用单事务代替锁表（推荐）
--master-data=2 \ -- 记录 Binlog 位置（注释形式）
--all-databases \ -- 导出所有数据库
--routines \ -- 导出存储过程和函数
--events \ -- 导出事件
--triggers \ -- 导出触发器
--hex-blob \ -- 二进制字段用十六进制导出
> /backup/full_backup_$(date +%Y%m%d).sql
-- 6. 导出完成后，回到第一个会话，释放锁
UNLOCK TABLES;InnoDB 推荐方案（无需锁表）：
bash
# 如果所有表都是 InnoDB，可以用单事务快照，无需长时间锁表
mysqldump -u root -p \
--single-transaction \ -- 开启一致性快照
--master-data=2 \ -- 在备份文件中记录 Binlog 位置
--all-databases \
--routines --events --triggers --hex-blob \
> /backup/full_backup_$(date +%Y%m%d).sql查看备份文件中的 Binlog 位置：
bash
# 备份文件开头会有类似这样的注释
head -50 /backup/full_backup_20240315.sql | grep "MASTER_LOG"
# 输出：
# CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=123456789;四、从库配置：让数据"如影随形"
4.1 从库配置文件（my.cnf / my.ini）
ini
[mysqld]
# ============ 基础配置 ============
port = 3306
datadir = /data/mysql/data
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
# ============ 复制核心配置 ============
# Server ID：必须与主库不同，且从库之间也不能重复
# 建议用 IP 最后一段 + 端口号
server_id = 1013306
# 开启中继日志（Relay Log）
# 默认是开启的，建议显式指定路径
relay_log = /data/mysql/logs/relay/mysql-relay-bin
# 中继日志过期时间（秒）
# 从库 SQL 线程执行完后，中继日志会被自动删除
relay_log_purge = 1
# ============ 从库只读模式 ============
# 普通用户只能读，不能写（防止误操作从库导致数据不一致）
read_only = 1
# 注意：超级用户（SUPER 权限）不受 read_only 限制
# 如果应用程序用 root 连接从库，仍然可以写入！
# 解决方案：MySQL 5.7+ 使用 super_read_only = 1
super_read_only = 1
# ============ 从库 Binlog（级联复制用）============
# 如果这个从库还要作为其他从库的主库（级联复制），需要开启 Binlog
# 并设置 log_slave_updates，把从中继日志重放的 SQL 也写入自己的 Binlog
log_bin = /data/mysql/logs/binlog/mysql-bin
binlog_format = ROW
log_slave_updates = 1
# 注意：如果不做级联复制，从库可以不开启 Binlog，节省磁盘 I/O
# ============ 复制过滤规则（在从库设置更灵活）============
# 只复制指定数据库
# replicate_do_db = ecommerce
# 忽略指定数据库
replicate_ignore_db = mysql
replicate_ignore_db = sys
replicate_ignore_db = information_schema
replicate_ignore_db = performance_schema
# 只复制指定表（更精细的控制）
# replicate_do_table = ecommerce.orders
# replicate_do_table = ecommerce.users
# 忽略指定表（常用：不同步日志表、临时表）
# replicate_ignore_table = ecommerce.slow_log
# replicate_wild_ignore_table = ecommerce.log_%
# ============ 延迟复制（可选）============
# 让从库延迟 N 秒应用主库的 Binlog
# 用途：防止误删数据，可以立即在从库找回
# 范围：0 - 2147483647 秒
# 生产建议：延迟 1-6 小时（3600-21600 秒）
# CHANGE MASTER TO MASTER_DELAY = 3600;
# ============ GTID 配置 ============
gtid_mode = ON
enforce_gtid_consistency = ON
# ============ 性能配置 ============
max_connections = 2000
innodb_buffer_pool_size = 8G
# 从库可以设置更大的缓冲池，因为主要是读操作
innodb_buffer_pool_size = 12G
# 错误日志
log_error = /data/mysql/logs/error/mysql-error.log
# 慢查询日志（从库也需要，用于分析读性能）
slow_query_log = 1
slow_query_log_file = /data/mysql/logs/slow/mysql-slow.log
long_query_time = 14.2 导入主库备份数据
bash
# 1. 在从库上创建目录
mkdir -p /data/mysql/logs/binlog /data/mysql/logs/relay
# 2. 从主库拷贝备份文件到从库
scp root@192.168.1.100:/backup/full_backup_20240315.sql /backup/
# 3. 从库上导入数据
mysql -u root -p < /backup/full_backup_20240315.sql
# 4. 导入完成后，重启从库使配置生效
systemctl restart mysqld4.3 配置主从连接（CHANGE MASTER TO）
sql
-- 登录从库
mysql -u root -p
-- 配置主库连接信息
CHANGE MASTER TO
MASTER_HOST = '192.168.1.100', -- 主库 IP
MASTER_PORT = 3306, -- 主库端口
MASTER_USER = 'repl', -- 复制账号
MASTER_PASSWORD = 'Repl@2024!Strong', -- 复制账号密码
MASTER_LOG_FILE = 'mysql-bin.000003', -- 主库 SHOW MASTER STATUS 中的 File
MASTER_LOG_POS = 123456789, -- 主库 SHOW MASTER STATUS 中的 Position
MASTER_CONNECT_RETRY = 10; -- 连接失败重试间隔（秒）
-- 如果开启了 GTID，使用以下方式（推荐）：
-- CHANGE MASTER TO
-- MASTER_HOST = '192.168.1.100',
-- MASTER_PORT = 3306,
-- MASTER_USER = 'repl',
-- MASTER_PASSWORD = 'Repl@2024!Strong',
-- MASTER_AUTO_POSITION = 1; -- 自动根据 GTID 定位参数详解：
| 参数 | 说明 | 注意事项 |
|---|---|---|
MASTER_HOST | 主库 IP 或域名 | 确保从库能 ping 通主库 |
MASTER_PORT | 主库端口 | 默认 3306 |
MASTER_USER | 复制账号 | 必须有 REPLICATION SLAVE 权限 |
MASTER_PASSWORD | 复制账号密码 | 建议定期更换 |
MASTER_LOG_FILE | Binlog 文件名 | 非 GTID 模式必须 |
MASTER_LOG_POS | Binlog 偏移量 | 非 GTID 模式必须 |
MASTER_AUTO_POSITION | GTID 自动定位 | GTID 模式设为 1 |
MASTER_CONNECT_RETRY | 重连间隔 | 网络不稳定时设小一点 |
MASTER_DELAY | 延迟复制秒数 | 可选，用于数据保护 |
4.4 启动复制线程
sql
-- 启动复制
START SLAVE;
-- 如果只启动某个线程（调试用）
START SLAVE IO_THREAD; -- 只启动 I/O 线程
START SLAVE SQL_THREAD; -- 只启动 SQL 线程
-- 停止复制
STOP SLAVE;
-- 重置复制配置（谨慎！会删除所有复制配置和中继日志）
RESET SLAVE ALL;4.5 查看复制状态（SHOW SLAVE STATUS）
sql
SHOW SLAVE STATUS\G关键输出字段解读：
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.100
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 123456789
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 123456
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes <-- 【关键】I/O 线程状态
Slave_SQL_Running: Yes <-- 【关键】SQL 线程状态
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,sys,...
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0 <-- 最近错误码（0 表示无错误）
Last_Error: <-- 最近错误信息
Skip_Counter: 0
Exec_Master_Log_Pos: 123456789 <-- SQL 线程已执行到的主库位置
Relay_Log_Space: 123456789
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0 <-- 【关键】复制延迟（秒）
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0 <-- I/O 线程错误码
Last_IO_Error: <-- I/O 线程错误信息
Last_SQL_Errno: 0 <-- SQL 线程错误码
Last_SQL_Error: <-- SQL 线程错误信息
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1003306
Master_UUID: 3e11fa47-71ca-11e1-9e33-c80aa9429562
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0 -- 延迟复制设置
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-5
Executed_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-5
Auto_Position: 0 -- 是否使用 GTID 自动定位
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0核心字段速查表：
| 字段 | 正常值 | 异常排查 |
|---|---|---|
Slave_IO_Running | Yes | No 或 Connecting：检查网络、账号密码、主库 Binlog |
Slave_SQL_Running | Yes | No：检查 Last_SQL_Error，通常是主键冲突、表不存在 |
Seconds_Behind_Master | 0 或较小值 | 持续增大：从库性能不足或网络慢 |
Last_IO_Errno | 0 | 1045：密码错误；2003：网络不通；1236：Binlog 位置错误 |
Last_SQL_Errno | 0 | 1062：主键冲突；1146：表不存在；1054：字段不存在 |
Read_Master_Log_Pos | 持续增长 | 不增长：I/O 线程没收到新 Binlog |
Exec_Master_Log_Pos | 持续增长 | 不增长：SQL 线程卡住 |
Retrieved_Gtid_Set | 与主库一致 | 差距大：I/O 线程拉取慢 |
Executed_Gtid_Set | 接近 Retrieved | 差距大：SQL 线程执行慢 |
五、功能测试：验证复制是否"靠谱"
5.1 基础数据同步测试
步骤 1：解锁主库（如果之前锁表了）
sql
-- 在主库执行
UNLOCK TABLES;步骤 2：主库创建测试数据
sql
-- 主库执行
-- 创建测试数据库
CREATE DATABASE IF NOT EXISTS replication_test;
-- 使用测试库
USE replication_test;
-- 创建测试表
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 插入测试数据
INSERT INTO users (username, email) VALUES
('张三', 'zhangsan@test.com'),
('李四', 'lisi@test.com'),
('王五', 'wangwu@test.com'),
('赵六', 'zhaoliu@test.com'),
('钱七', 'qianqi@test.com');
-- 查看数据
SELECT * FROM users;步骤 3：从库验证数据同步
sql
-- 从库执行
USE replication_test;
-- 应该能看到与主库完全一致的数据
SELECT * FROM users;
-- 检查表结构是否同步
SHOW CREATE TABLE users\G
-- 检查数据库列表
SHOW DATABASES;步骤 4：测试 DML 同步
sql
-- 主库执行：更新
UPDATE users SET email = 'zhangsan_new@test.com' WHERE username = '张三';
-- 主库执行：删除
DELETE FROM users WHERE username = '钱七';
-- 从库验证
SELECT * FROM users;
-- 张三的邮箱应该已更新
-- 钱七应该已被删除步骤 5：测试 DDL 同步
sql
-- 主库执行：修改表结构
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- 主库执行：创建新表
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
amount DECIMAL(10,2),
status ENUM('pending', 'paid', 'shipped') DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 主库执行：插入数据到新表
INSERT INTO orders (user_id, amount, status) VALUES
(1, 99.99, 'paid'),
(2, 199.50, 'pending');
-- 从库验证
SHOW TABLES;
SELECT * FROM orders;
SHOW CREATE TABLE users\G -- 应该有 phone 字段5.2 异常场景测试
场景 1：主库宕机后从库状态
bash
# 1. 模拟主库宕机（在主库服务器执行）
systemctl stop mysqld
# 2. 从库查看状态
mysql -u root -p -e "SHOW SLAVE STATUS\G"预期结果：
Slave_IO_Running: Connecting <-- 或 No
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'repl@192.168.1.100:3306'解释：I/O 线程尝试重连，但主库已不可达。
bash
# 3. 恢复主库
systemctl start mysqld
# 4. 从库观察自动恢复
mysql -u root -p -e "SHOW SLAVE STATUS\G"预期结果：
Slave_IO_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:关键点：网络恢复后，从库会自动重连并继续复制，无需人工干预。
场景 2：网络中断后自动重连
bash
# 1. 模拟网络中断（在从库服务器执行）
iptables -A INPUT -s 192.168.1.100 -j DROP
# 2. 观察从库状态（等待约 60 秒）
mysql -u root -p -e "SHOW SLAVE STATUS\G"预期结果：
Slave_IO_Running: Connecting
Last_IO_Error: error connecting to masterbash
# 3. 恢复网络
iptables -D INPUT -s 192.168.1.100 -j DROP
# 4. 观察从库自动重连
mysql -u root -p -e "SHOW SLAVE STATUS\G"预期结果：Slave_IO_Running 恢复为 Yes，Seconds_Behind_Master 先增大后降为 0。
场景 3：主库写入，验证从库延迟
sql
-- 主库执行大批量写入，制造延迟
USE replication_test;
-- 创建大表
CREATE TABLE big_table (
id INT PRIMARY KEY AUTO_INCREMENT,
data VARCHAR(1000),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 插入 10 万条数据
INSERT INTO big_table (data)
SELECT REPEAT('x', 500)
FROM information_schema.tables t1,
information_schema.tables t2
LIMIT 100000;
-- 同时在从库观察延迟
mysql -u root -p -e "SHOW SLAVE STATUS\G" | grep Seconds_Behind_Master预期结果：Seconds_Behind_Master 从 0 开始增长，主库写入完成后逐渐降为 0。
5.3 复制异常排查手册
异常 1：I/O 线程启动失败（Slave_IO_Running = No）
| 错误码 | 错误信息 | 原因 | 解决方案 |
|---|---|---|---|
| 1045 | Access denied for user | 密码错误或账号不存在 | 检查主库 mysql.user 表，重置密码 |
| 2003 | Can't connect to MySQL server | 网络不通或主库未启动 | telnet 主库IP 3306 测试，检查防火墙 |
| 1236 | Could not find first log file name | Binlog 文件名或位置错误 | 重新执行 SHOW MASTER STATUS 获取正确值 |
| 1201 | Could not initialize master info | 主从 server-id 重复 | 修改从库 server_id 为唯一值 |
排查步骤：
bash
# 1. 检查从库错误日志
tail -50 /data/mysql/logs/error/mysql-error.log
# 2. 检查主库是否允许复制账号连接
mysql -h 192.168.1.100 -u repl -p -e "SELECT 1"
# 3. 检查主库防火墙
iptables -L -n | grep 3306
firewall-cmd --list-ports
# 4. 检查主从 server-id 是否重复
mysql -u root -p -e "SHOW VARIABLES LIKE 'server_id';"异常 2：SQL 线程启动失败（Slave_SQL_Running = No）
| 错误码 | 错误信息 | 原因 | 解决方案 |
|---|---|---|---|
| 1062 | Duplicate entry | 主键冲突（从库已有相同数据） | 跳过该事务或删除冲突数据 |
| 1146 | Table doesn't exist | 从库缺少表 | 从主库导出表结构导入 |
| 1054 | Unknown column | 从库缺少字段 | 检查是否有人误操作从库 |
| 1032 | Can't find record | 要更新的记录在从库不存在 | 数据不一致，需重新同步 |
跳过单个事务（谨慎使用）：
sql
-- 传统模式（基于文件位置）
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; -- 跳过 1 个事务
START SLAVE;
-- GTID 模式（推荐）
STOP SLAVE;
SET GTID_NEXT = '3e11fa47-71ca-11e1-9e33-c80aa9429562:6'; -- 指定要跳过的事务
BEGIN; COMMIT; -- 执行空事务
SET GTID_NEXT = 'AUTOMATIC';
START SLAVE;修复主键冲突的通用方案：
sql
-- 1. 查看冲突的表和主键值
SELECT * FROM replication_test.users WHERE id = 冲突的主键值;
-- 2. 删除冲突数据（或更新）
DELETE FROM replication_test.users WHERE id = 冲突的主键值;
-- 3. 重启 SQL 线程
STOP SLAVE SQL_THREAD;
START SLAVE SQL_THREAD;异常 3：复制延迟过大（Seconds_Behind_Master 持续增长）
排查步骤：
sql
-- 1. 查看从库状态
SHOW SLAVE STATUS\G
-- 关注：Read_Master_Log_Pos 与 Exec_Master_Log_Pos 的差距
-- 2. 查看从库进程列表，是否有大查询阻塞
SHOW PROCESSLIST;
-- 3. 查看从库性能指标
SHOW GLOBAL STATUS LIKE 'Innodb_rows%';
SHOW GLOBAL STATUS LIKE 'Threads%';常见原因与解决：
| 原因 | 现象 | 解决方案 |
|---|---|---|
| 主库大事务 | Seconds_Behind_Master 突增后缓慢下降 | 拆分为小事务，避免 DELETE FROM 大表 |
| 从库硬件差 | 持续高延迟 | 升级从库 CPU/内存/磁盘 |
| 从库有慢查询 | 特定时间段延迟高 | 优化慢查询，或设置 read_only 减少从库负载 |
| 网络带宽不足 | 延迟与主库写入量成正比 | 升级网络，或开启 Binlog 压缩 |
| 从库单线程回放 | Exec_Master_Log_Pos 增长慢 | 开启并行复制（MySQL 5.7+） |
开启并行复制（MySQL 5.7+）：
sql
-- 从库配置
STOP SLAVE;
-- 基于 LOGIC_CLOCK 的并行复制（推荐）
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 8; -- 建议设置为 CPU 核心数
START SLAVE;
-- 查看并行复制状态
SHOW VARIABLES LIKE 'slave_parallel%';5.4 复制一致性校验
bash
# 使用 pt-table-checksum 校验主从数据一致性（Percona Toolkit）
# 在主库执行
pt-table-checksum \
--host=192.168.1.100 \
--user=root \
--password='your_password' \
--databases=replication_test \
--tables=users,orders \
--recursion-method=processlist \
--no-check-binlog-format
# 输出示例：
# TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
# 03-15T10:30:00 0 0 5 0 1 0 0.123 users
# 03-15T10:30:00 0 0 2 0 1 0 0.045 orders字段说明：
ERRORS：校验时发生的错误数DIFFS：数据不一致的块数（0 表示一致）ROWS：表的总行数DIFF_ROWS：不一致的行数
发现不一致后的修复：
bash
# 使用 pt-table-sync 修复（先打印 SQL，不实际执行）
pt-table-sync \
--print \
--host=192.168.1.100 \
--user=root \
--password='your_password' \
--databases=replication_test \
--tables=users \
--sync-to-master
# 确认无误后，实际执行（去掉 --print，加 --execute）
pt-table-sync \
--execute \
--host=192.168.1.100 \
--user=root \
--password='your_password' \
--databases=replication_test \
--tables=users \
--sync-to-master附录：常用命令速查
主库命令
sql
-- 查看 Binlog 状态
SHOW MASTER STATUS\G
-- 查看所有 Binlog 文件
SHOW BINARY LOGS;
-- 查看 Binlog 事件
SHOW BINLOG EVENTS IN 'mysql-bin.000001';
-- 查看当前连接的从库
SHOW SLAVE HOSTS;
-- 查看复制账号
SELECT user, host FROM mysql.user WHERE Repl_slave_priv = 'Y';从库命令
sql
-- 查看复制状态
SHOW SLAVE STATUS\G
-- 启动/停止复制
START SLAVE;
STOP SLAVE;
START SLAVE IO_THREAD;
START SLAVE SQL_THREAD;
-- 修改复制配置
CHANGE MASTER TO MASTER_HOST = '...';
-- 查看从库只读状态
SHOW VARIABLES LIKE 'read_only';
SHOW VARIABLES LIKE 'super_read_only';
-- 查看延迟复制设置
SHOW SLAVE STATUS\G | grep SQL_Delay运维命令
bash
# 查看主从复制进程
ps aux | grep -E "mysql.*(dump|slave)"
# 监控复制延迟（每 2 秒刷新）
watch -n 2 "mysql -u root -p'password' -e 'SHOW SLAVE STATUS\G' | grep Seconds_Behind"
# 查看 Binlog 增长速率
mysqladmin -u root -p extended-status | grep Binlog文档版本：v1.0
适用版本：MySQL 5.7 / 8.0
最后更新：2024-03-15
建议：生产环境部署前，请在测试环境完整验证所有步骤