转自：Python头条

背景与概述

MySQL主从复制是数据库高可用架构的基础组件。作为一名运维工程师，我在生产环境中见过太多因为主从复制问题导致的故障：数据不一致、复制延迟、连接断开、GTID冲突等等。这些问题的根源往往在于对复制原理理解不够深入。

MySQL 9.0（2026年最新版本）带来了多项复制相关改进，包括增强的GTID支持、性能更优的并行复制、以及更完善的监控接口。掌握这些原理对于构建稳定的数据库架构至关重要。

本文将从实战角度深入剖析MySQL主从复制的核心原理，详细讲解各种复制模式，分析常见故障的成因，并提供系统性的排查方法和解决方案。通过大量的实战脚本和案例，帮助读者建立完整的主从复制知识体系。

前置知识要求：

• 熟悉Linux基础命令和shell脚本
• 具备MySQL数据库基本操作经验
• 理解数据库事务ACID特性
• 了解TCP/IP网络基础

实验环境说明：

• MySQL 9.0.2（社区版，Linux）
• 操作系统：Rocky Linux 9或Ubuntu 24.04 LTS
• 采用GTID（Global Transaction Identifier）复制模式
• 半同步复制使用插件模式

1. 主从复制核心原理

1.1 主从复制架构概述

MySQL主从复制采用日志复制模式，核心流程是将主库的变更日志（Binary Log）传输到从库并在从库执行。

主从复制的基本架构：

┌─────────────────┐      ┌─────────────────┐
│   Master        │      │   Slave         │
│                 │      │                 │
│  ┌───────────┐  │      │  ┌───────────┐  │
│  │  Client   │  │      │  │  SQL      │  │
│  │  Write    │  │      │  │  Thread   │  │
│  └─────┬─────┘  │      │  └─────┬─────┘  │
│        │        │      │        │        │
│        ▼        │      │        ▼        │
│  ┌───────────┐  │      │  ┌───────────┐  │
│  │   Binlog  │──┼──────┼──│ Relaylog  │  │
│  │  (写入)   │  │ 复制  │  │  (读取)   │  │
│  └───────────┘  │      │  └─────┬─────┘  │
│                 │      │        │        │
│                 │      │        ▼        │
│                 │      │  ┌───────────┐  │
│                 │      │  │   Data   │  │
│                 │      │  │  (执行)   │  │
│                 │      │  └───────────┘  │
└─────────────────┘      └─────────────────┘

核心组件说明：

• Binary Log（Binlog）：主库记录所有变更的日志文件
• IO Thread：从库接收主库Binlog的线程
• Relay Log（中继日志）：从库本地存储接收到的Binlog内容
• SQL Thread：从库执行Relay Log中SQL的线程

1.2 复制类型详解

MySQL支持三种主要的复制类型：

异步复制（Asynchronous Replication）：

• 默认的复制模式
• 主库提交事务后立即返回客户端，不等待从库确认
• 性能最高但可能存在数据丢失风险

半同步复制（Semi-synchronous Replication）：

• 主库提交时等待至少一个从库确认接收
• 至少一个从库确认后才返回客户端
• 在数据完整性和性能之间取得平衡

全同步复制（Fully Synchronous Replication）：

• 所有从库都确认后才返回客户端
• 数据零丢失但性能影响较大
• MySQL Group Replication实现此模式

1.3 Binlog格式

Binlog有三种格式，通过binlog_format参数控制：

STATEMENT格式（基于SQL语句）：

-- binlog_format = STATEMENT
SET binlog_format = 'STATEMENT';

• 记录执行的SQL语句
• 优点：日志量小
• 缺点：某些函数（如NOW()、UUID()）可能导致数据不一致

ROW格式（基于行）：

-- binlog_format = ROW
SET binlog_format = 'ROW';

• 记录被修改行的完整内容
• 优点：数据一致性高
• 缺点：日志量可能很大

MIXED格式（混合）：

-- binlog_format = MIXED
SET binlog_format = 'MIXED';

• 默认使用STATEMENT，必要时自动切换到ROW
• 平衡了性能和一致性

MySQL 9.0推荐：生产环境使用ROW格式，特别是使用了触发器、存储过程或不确定行为的函数时。

1.4 Binlog查看命令

-- 查看当前Binlog状态
SHOWMASTERSTATUS;

-- 示例输出：
-- +------------------+----------+
-- | File             | Position |
-- +------------------+----------+
-- | mysql-bin.000123 | 45678901 |
-- +------------------+----------+

-- 查看所有Binlog文件列表
SHOWBINARYLOGS;

-- 查看Binlog事件详情
SHOWBINLOGEVENTSIN'mysql-bin.000123'FROM100LIMIT50;

-- 使用mysqlbinlog命令行工具
-- 查看远程服务器的Binlog（需要REPLICATION CLIENT权限）
mysqlbinlog --read-from-remote-server \
--host=master.example.com \
--user=repl_user \
--password \
--stop-never \
  mysql-bin.000123

-- 解析本地Binlog文件
mysqlbinlog /var/lib/mysql/mysql-bin.000123 \
--start-datetime='2026-01-01 00:00:00' \
--stop-datetime='2026-01-02 00:00:00' \
  > /tmp/binlog_events.sql

-- 查看Binlog事件详情（带详细信息）
mysqlbinlog -v -v /var/lib/mysql/mysql-bin.000123 | head -100

2. GTID复制模式

2.1 GTID概念与原理

GTID（Global Transaction Identifier）是MySQL 5.6+引入的全局事务标识符，它为每个事务分配一个唯一的标识符，极大地简化了复制管理。

GTID的组成：

• Source ID：主库的唯一标识（通常是主库的server_uuid）
• Transaction ID：事务序号

格式：source_id:transaction_id示例：3E11FA47-71CA-11E1-9E33-C80AA9429562:23

GTID的优势：

• 自动化位点追踪，无需指定文件名和位置
• 简化从库切换和故障转移
• 更方便的一致性校验
• 支持自动跳过复制错误

2.2 GTID配置

# my.cnf配置文件
[mysqld]
# 启用GTID模式
gtid_mode = ON
enforce_gtid_consistency = ON

# 复制相关配置
log_slave_updates = ON
slave_parallel_type = 'LOGICAL_CLOCK'
slave_parallel_workers = 8
slave_preserve_commit_order = ON

# binlog配置
binlog_format = ROW
sync_binlog = 1

重要配置说明：

• enforce_gtid_consistency = ON：确保GTID事务性，只能使用支持GTID的语句
• log_slave_updates = ON：从库更新也要写入自己的Binlog
• slave_parallel_workers：并行复制线程数，建议设置为CPU核心数的50%-80%

2.3 GTID相关命令

-- 查看GTID执行状态
SHOWMASTERSTATUS\G
SHOWSLAVESTATUS\G

-- 查看从库已接收但未执行的GTID
SELECT * FROM performance_schema.replication_connection_status\G

-- 查看从库已执行的事务
SELECT * FROM performance_schema.replication_executed_transaction_ids\G;

-- 查看GTID持久化集合
SHOWGLOBALVARIABLESLIKE'gtid%';

-- 手动处理GTID
-- 跳过执行失败的事务
SET GTID_NEXT = '3E11FA47-71CA-11E1-9E33-C80AA9429562:100';
BEGIN;
COMMIT;
SET GTID_NEXT = 'AUTOMATIC';

-- 清空从库的GTID集合（用于重新初始化从库）
RESETSLAVEALL;
RESETMASTER;

-- 查看主库GTID状态
SHOWGLOBALVARIABLESLIKE'gtid%';

3. 复制线程详解

3.1 IO Thread工作原理

IO Thread负责从主库拉取Binlog内容，其工作流程：

1. 从库连接到主库
2. 从库发送Binlog位置请求（基于MASTER_LOG_FILE和MASTER_LOG_POS）
3. 主库发送Binlog内容给从库
4. 从库将内容写入Relay Log
5. 确认已接收的Binlog位置

IO Thread状态查看：

-- 在从库上执行
SHOWSLAVESTATUS\G

-- 关键字段说明：
-- Slave_IO_Running: IO线程运行状态
-- Slave_SQL_Running: SQL线程运行状态
-- Master_User: 复制用户名
-- Master_Host: 主库地址
-- Master_Port: 主库端口
-- Master_Log_File: IO线程正在读取的Binlog文件
-- Read_Master_Log_Pos: 已读取的Binlog位置
-- Relay_Master_Log_File: SQL线程正在读取的Binlog文件
-- Exec_Master_Log_Pos: 已执行的Binlog位置
-- Relay_Log_Space: Relay Log总大小
-- Last_IO_Error: 最近IO错误
-- Last_SQL_Error: 最近SQL错误

3.2 SQL Thread工作原理

SQL Thread（也称为applier线程）从Relay Log中读取事件并执行：

工作流程：

1. 读取Relay Log中的下一个事件
2. 解析事件内容
3. 在从库执行相应的SQL操作
4. 更新Exec_Master_Log_Pos
5. 重复直到Relay Log读取完毕

并行复制原理：

MySQL 9.0支持基于LOGICAL_CLOCK的并行复制：

-- 查看并行复制配置
SHOW VARIABLES LIKE 'slave_parallel%';
SHOW VARIABLES LIKE 'slave_preserve_commit_order';

-- 设置并行复制
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 16;

3.3 复制线程监控

-- 查看所有复制相关线程
SHOWPROCESSLIST;

-- 示例输出：
--+----+-------------+-------------+------+---------+-------+--------------------------------------------------------+------------------+
--| Id | User        | Host        | db   | Command | Time  | State                                                  | Info             |
--+----+-------------+-------------+------+---------+-------+--------------------------------------------------------+------------------+
--| 10 | system user |             | NULL | Connect | 12345 | Waiting for source to send event                      | NULL             |
--| 11 | system user |             | NULL | Connect | 12345 | Slave has read all relay log; waiting for more updates | NULL             |
--| 12 | root        | localhost    | test | Query   | 0     | starting                                               | SHOW PROCESSLIST |
--+----+-------------+-------------+------+---------+-------+--------------------------------------------------------+------------------+

-- performance_schema监控
-- 查看复制通道状态
SELECT * FROM performance_schema.replication_connection_status\G

-- 查看复制工作者线程
SELECT * FROM performance_schema.replication_applier_status_by_worker\G

-- 查看应用状态
SELECT * FROM performance_schema.replication_applier_status\G

3.4 复制线程故障排查脚本

#!/bin/bash
# 文件名：check_mysql_replication.sh
# 功能：检查MySQL主从复制状态

MYSQL_USER="root"
MYSQL_PASSWORD="your_password"
MYSQL_HOST="localhost"
MYSQL_PORT="3306"

echo"=========================================="
echo"MySQL主从复制状态检查 - $(date '+%Y-%m-%d %H:%M:%S')"
echo"=========================================="

# 获取复制状态
SLAVE_STATUS=$(mysql -u${MYSQL_USER} -p${MYSQL_PASSWORD} \
  -h${MYSQL_HOST} -P${MYSQL_PORT} \
  -e "SHOW SLAVE STATUS\G" 2>/dev/null)

if [ -z "$SLAVE_STATUS" ]; then
    echo"【注意】该服务器不是从库或无法连接到MySQL"
    exit 1
fi

# 解析关键状态
IO_RUNNING=$(echo"$SLAVE_STATUS" | grep "Slave_IO_Running:" | awk '{print $2}')
SQL_RUNNING=$(echo"$SLAVE_STATUS" | grep "Slave_SQL_Running:" | awk '{print $2}')
SECONDS_BEHIND=$(echo"$SLAVE_STATUS" | grep "Seconds_Behind_Master:" | awk '{print $2}')
LAST_ERROR=$(echo"$SLAVE_STATUS" | grep "Last_Error:" | awk -F: '{print $2}')
MASTER_HOST=$(echo"$SLAVE_STATUS" | grep "Master_Host:" | awk '{print $2}')
MASTER_LOG=$(echo"$SLAVE_STATUS" | grep "Master_Log_File:" | awk '{print $2}')
READ_POS=$(echo"$SLAVE_STATUS" | grep "Read_Master_Log_Pos:" | awk '{print $2}')
RELAY_LOG=$(echo"$SLAVE_STATUS" | grep "Relay_Master_Log_File:" | awk '{print $2}')
EXEC_POS=$(echo"$SLAVE_STATUS" | grep "Exec_Master_Log_Pos:" | awk '{print $2}')
GTID_MODE=$(mysql -u${MYSQL_USER} -p${MYSQL_PASSWORD} \
  -h${MYSQL_HOST} -P${MYSQL_PORT} \
  -e "SHOW VARIABLES LIKE 'gtid_mode'" 2>/dev/null | awk '{print $2}')

echo""
echo"【1】复制通道状态："
echo"----------------------------------------"
echo"  IO线程状态: ${IO_RUNNING:-Unknown}"
echo"  SQL线程状态: ${SQL_RUNNING:-Unknown}"
echo"  复制延迟: ${SECONDS_BEHIND:-Unknown} 秒"
echo"  GTID模式: ${GTID_MODE:-Unknown}"

echo""
echo"【2】主库信息："
echo"----------------------------------------"
echo"  主库地址: ${MASTER_HOST:-Unknown}"
echo"  读取Binlog: ${MASTER_LOG:-Unknown}"
echo"  读取位置: ${READ_POS:-Unknown}"

echo""
echo"【3】执行状态："
echo"----------------------------------------"
echo"  执行Binlog: ${RELAY_LOG:-Unknown}"
echo"  执行位置: ${EXEC_POS:-Unknown}"

echo""
echo"【4】错误信息："
echo"----------------------------------------"
if [ -n "$LAST_ERROR" ]; then
    echo"  Last Error: $LAST_ERROR"
else
    echo"  无错误"
fi

# 判断状态并告警
echo""
echo"【5】健康检查结果："
echo"----------------------------------------"

if [ "$IO_RUNNING" != "Yes" ]; then
    echo"  [严重] IO线程未运行"
fi

if [ "$SQL_RUNNING" != "Yes" ]; then
    echo"  [严重] SQL线程未运行"
fi

if [ "$SECONDS_BEHIND" != "0" ] && [ "$SECONDS_BEHIND" != "NULL" ] && [ -n "$SECONDS_BEHIND" ]; then
    if [ "$SECONDS_BEHIND" -gt 300 ]; then
        echo"  [警告] 复制延迟超过5分钟"
    elif [ "$SECONDS_BEHIND" -gt 60 ]; then
        echo"  [注意] 复制延迟超过1分钟"
    fi
fi

if [ -n "$LAST_ERROR" ]; then
    echo"  [严重] 存在复制错误"
fi

if [ "$IO_RUNNING" == "Yes" ] && [ "$SQL_RUNNING" == "Yes" ] && [ -z "$LAST_ERROR" ]; then
    echo"  [正常] 复制状态正常"
fi

4. 常见复制架构

4.1 一主一从架构

最基础的复制架构，适用于中小规模部署：

# 主库配置 (my.cnf)
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
sync_binlog = 1
gtid_mode = ON
enforce_gtid_consistency = ON

# 从库配置 (my.cnf)
[mysqld]
server-id = 2
log-bin = mysql-bin
relay-log = relay-bin
gtid_mode = ON
enforce_gtid_consistency = ON
read_only = ON
super_read_only = ON

搭建一主一从步骤：

-- 1. 在主库创建复制用户
CREATEUSER'repl'@'%'IDENTIFIEDBY'repl_password';
GRANTREPLICATIONSLAVE, REPLICATIONCLIENTON *.* TO'repl'@'%';
FLUSHPRIVILEGES;

-- 2. 主库备份（带Binlog位置）
mysqldump -u root -p \
--single-transaction \
--source-data=2 \
--routines \
--triggers \
--events \
--all-databases > full_backup.sql

-- 3. 在从库恢复备份
mysql -u root -p < full_backup.sql

-- 4. 配置从库连接主库
CHANGEMASTERTO
  MASTER_HOST = 'master.example.com',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'repl_password',
  MASTER_PORT = 3306,
  MASTER_AUTO_POSITION = 1;  -- 使用GTID

-- 5. 启动复制
STARTSLAVE;

-- 6. 验证复制状态
SHOWSLAVESTATUS\G

4.2 一主多从架构

通过一个主库带动多个从库，实现读写分离和负载均衡：

# 主库配置
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON

# 从库1配置
[mysqld]
server-id = 21
gtid_mode = ON
enforce_gtid_consistency = ON
read_only = ON

# 从库2配置
[mysqld]
server-id = 22
gtid_mode = ON
enforce_gtd_consistency = ON
read_only = ON

读写分离示例（使用ProxySQL）：

# ProxySQL配置
# 添加主库和从库
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (0, 'master.example.com', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, 'slave1.example.com', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, 'slave2.example.com', 3306);

# 配置用户
INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('app_user', 'app_pass', 0);

# 配置读写规则
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, comment) VALUES
(1, 1, '^SELECT.*FOR UPDATE', 0, '写优先的SELECT'),
(2, 1, '^SELECT', 1, '读操作发送到从库');

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

4.3 级联复制架构

从库作为其他从库的主库，减轻主库压力：

# 主库 -> 从库A -> 从库B和从库C

# 从库A配置（需要开启log_slave_updates）
[mysqld]
server-id = 2
log-bin = mysql-bin
log_slave_updates = ON
gtid_mode = ON
enforce_gtid_consistency = ON

# 从库B和从库C配置
[mysqld]
server-id = 3  # 或 4
gtid_mode = ON
enforce_gtid_consistency = ON

4.4 双主架构

两台服务器互为主从，适用于需要双向写入的场景：

# 服务器A配置
[mysqld]
server-id = 1
log-bin = mysql-bin
gtid_mode = ON
enforce_gtid_consistency = ON
auto_increment_offset = 1
auto_increment_increment = 2

# 服务器B配置
[mysqld]
server-id = 2
log-bin = mysql-bin
gtid_mode = ON
enforce_gtid_consistency = ON
auto_increment_offset = 2
auto_increment_increment = 2

双主注意事项：

• 必须使用auto_increment_offset和auto_increment_increment避免主键冲突
• 写入冲突需要应用层处理
• 建议只在一端写入，另一端只用于故障切换

5. 半同步复制

5.1 半同步复制原理

半同步复制在主库提交时等待至少一个从库确认接收Binlog：

主库提交事务
    │
    ▼
写入Binlog
    │
    ▼
等待从库IO线程接收（可配置超时）
    │
    ▼
返回客户端提交成功

超时机制：

• rpl_semi_sync_master_timeout：等待从库确认的超时时间（默认10000毫秒）
• 超时后自动切换为异步复制
• rpl_semi_sync_master_wait_point：等待点配置

5.2 半同步复制配置

-- 安装半同步插件（主库）
INSTALLPLUGIN rpl_semi_sync_master SONAME'semisync_master.so';

-- 安装半同步插件（从库）
INSTALLPLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so';

-- 主库配置
SETGLOBAL rpl_semi_sync_master_enabled = 1;
SETGLOBAL rpl_semi_sync_master_timeout = 10000;  -- 10秒
SETGLOBAL rpl_semi_sync_master_wait_for_slave_count = 1;

-- 从库配置
SETGLOBAL rpl_semi_sync_slave_enabled = 1;

-- 重启从库IO线程使配置生效
STOPSLAVE IO_THREAD;
STARTSLAVE IO_THREAD;

-- 持久化配置到my.cnf
[mysqld]
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 10000
rpl_semi_sync_master_wait_for_slave_count = 1
rpl_semi_sync_slave_enabled = 1

5.3 半同步复制监控

-- 主库监控
SHOWGLOBALSTATUSLIKE'rpl_semi_sync%';

-- 关键指标说明：
-- Rpl_semi_sync_master_clients: 已连接的半同步从库数量
-- Rpl_semi_sync_master_no_tx: 从库未确认的事务数
-- Rpl_semi_sync_master_yes_tx: 从库已确认的事务数
-- Rpl_semi_sync_master_no_times: 进入异步复制的次数
-- Rpl_semi_sync_master_status: 半同步复制状态（ON/OFF）

-- 查看详细状态
SHOWVARIABLESLIKE'rpl_semi_sync%';

-- 性能视图监控
SELECT * FROM performance_schema.global_status
WHERE VARIABLE_NAME LIKE'rpl_semi_sync%';

6. 常见故障排查

6.1 故障排查流程

┌─────────────────────────────────────────────────────┐
│ 1. 发现复制异常                                      │
│    - 监控告警                                        │
│    - 应用写入失败                                    │
│    - SHOW SLAVE STATUS发现异常                       │
└─────────────────────┬───────────────────────────────┘
                      │
                      ▼
┌─────────────────────────────────────────────────────┐
│ 2. 收集诊断信息                                      │
│    - SHOW SLAVE STATUS\G                            │
│    - SHOW PROCESSLIST                               │
│    - SHOW GLOBAL VARIABLES                           │
│    - 查看错误日志                                    │
└─────────────────────┬───────────────────────────────┘
                      │
                      ▼
┌─────────────────────────────────────────────────────┐
│ 3. 分析错误类型                                      │
│    - IO线程错误                                      │
│    - SQL线程错误                                     │
│    - 复制延迟过大                                    │
│    - GTID冲突                                       │
└─────────────────────┬───────────────────────────────┘
                      │
                      ▼
┌─────────────────────────────────────────────────────┐
│ 4. 执行修复措施                                      │
│    - 根据错误类型采取对应方案                         │
│    - 测试验证                                        │
│    - 更新监控                                        │
└─────────────────────────────────────────────────────┘

6.2 场景一：IO线程连接失败

问题现象：

Slave_IO_Running: Connecting
Last_IO_Error: error connecting to master 'repl@master:3306' - retry-time: 60  retries: 2

排查步骤：

# 1. 检查网络连通性
ping master.example.com
telnet master.example.com 3306

# 2. 检查主库端口是否开放
nc -zv master.example.com 3306

# 3. 测试从库到主库的连接
mysql -h master.example.com -u repl -p -e "SELECT 1"

# 4. 检查主库复制用户权限
mysql -u root -p -e "SHOW GRANTS FOR 'repl'@'%';"

解决方案：

-- 方案1：检查并修复复制账号权限
CREATEUSER'repl'@'slave.example.com'IDENTIFIEDBY'repl_password';
GRANTREPLICATIONSLAVE, REPLICATIONCLIENTON *.* TO'repl'@'slave.example.com';
FLUSHPRIVILEGES;

-- 方案2：修改从库的主库地址
STOPSLAVE;
CHANGEMASTERTO
  MASTER_HOST = 'master.example.com',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'repl_password',
  MASTER_PORT = 3306,
  MASTER_AUTO_POSITION = 1;
STARTSLAVE;

-- 方案3：检查主库最大连接数
SHOWVARIABLESLIKE'max_connections';
SHOWSTATUSLIKE'Threads_connected';
-- 如果连接数已满，需要等待或增加max_connections

6.3 场景二：SQL线程错误

问题现象：

Last_SQL_Error: Could not execute Update_rows event on table test.t1;
  Can't find record in 't1', Error_code: MY-001033

排查步骤：

-- 1. 查看详细错误
SHOWSLAVESTATUS\G

-- 2. 跳过错误事务（GTID模式）
STOPSLAVE;
SET gtid_next = 'source_id:transaction_id';
BEGIN;
COMMIT;
SET gtid_next = 'AUTOMATIC';
STARTSLAVE;

-- 3. 或者跳过多个事务
-- 找到出错的GTID，设为executed
SET GTID_EXECUTED = 'source_id:transaction_id-1';
STARTSLAVE;

-- 4. 使用pt-table-checksum检查数据一致性
pt-table-checksum h=master,u=checksum,p=check_pass \
--databases=test --tables=t1

解决方案：

-- 方案1：手动修复数据不一致
-- 对比主从数据，修复差异

-- 方案2：重新初始化从库
-- 如果不一致严重，需要重新从主库备份恢复

-- 方案3：使用pt-table-sync修复
pt-table-sync h=master,u=root,p=pass \
  h=slave,u=root,p=pass \
--databases=test --tables=t1 \
--execute

-- 方案4：跳过错误继续复制（谨慎使用）
STOPSLAVE;
SET @@SESSION.SQL_LOG_BIN = 0;
-- 手动处理问题
SET @@SESSION.SQL_LOG_BIN = 1;
STARTSLAVE;

6.4 场景三：复制延迟过大

问题现象：

Seconds_Behind_Master: 3600  # 延迟1小时

排查步骤：

-- 1. 检查从库负载
SHOWPROCESSLIST;
-- 观察SQL线程状态和CPU使用率

-- 2. 检查从库Binlog写入
SHOWGLOBALSTATUSLIKE'binlog%';
SHOWGLOBALSTATUSLIKE'relay%';

-- 3. 检查网络延迟
-- 从库执行
SELECT
  (@@Rpl_semi_sync_master_avg_trans_packet_size /
   @@Rpl_semi_sync_master_transactions_waited) AS avg_packet_size;

-- 4. 查看并行复制状态
SHOWVARIABLESLIKE'slave_parallel%';
SELECT * FROM performance_schema.replication_applier_status_by_worker\G;

-- 5. 检查从库IO写入性能
SHOWGLOBALSTATUSLIKE'Innodb_log_waits';
SHOWGLOBALSTATUSLIKE'Innodb_os_log_written%';

解决方案：

-- 方案1：增加并行复制线程
STOPSLAVE;
SETGLOBAL slave_parallel_workers = 16;
SETGLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SETGLOBAL slave_preserve_commit_order = ON;
STARTSLAVE;

-- 方案2：优化从库查询性能
-- 检查慢查询日志
SHOWGLOBALVARIABLESLIKE'slow_query%';
SHOWGLOBALVARIABLESLIKE'long_query_time';

-- 方案3：调整主库Binlog写入策略
SETGLOBAL sync_binlog = 1000;  -- 适当降低刷盘频率

-- 方案4：使用多源复制分散负载
CHANGEMASTERTO
  MASTER_HOST = 'master1.example.com',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'repl_password',
  MASTER_AUTO_POSITION = 1
FOR CHANNEL 'master1';

6.5 场景四：GTID相关错误

问题现象：

Got fatal error 1236 from master IO thread:
  The slave is connecting using GTID position (source_id:12345, interval_start:100),
  but the master has purged binary logs containing transactions that the slave needs.

解决方案：

-- 方案1：重新获取主库Binlog位置
-- 在主库查看当前的Executed_Gtid_Set
SHOWMASTERSTATUS;

-- 在从库重新设置GTID位置
STOPSLAVE;
RESETSLAVEALL;
CHANGEMASTERTO
  MASTER_HOST = 'master.example.com',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'repl_password',
  MASTER_AUTO_POSITION = 1;
STARTSLAVE;

-- 方案2：如果主库Binlog已被清除，需要重新备份
-- 使用mysqldump备份并包含GTID位置
mysqldump -u root -p \
--single-transaction \
--source-data=2 \
--all-databases > backup_with_gtid.sql

-- 恢复后自动恢复GTID位置
mysql -u root -p < backup_with_gtid.sql

GTID冲突处理：

-- 查看从库GTID执行状态
SELECT * FROM performance_schema.replication_connection_status\G

-- 查看已执行的GTID
SELECT @@GLOBAL.gtid_executed;

-- 查看主库GTID状态
SHOW MASTER STATUS;

-- 手动处理GTID空洞（主库事务被跳过导致）
SET GTID_EXECUTED = 'source_id:1-200';  -- 手动标记已执行

7. 数据一致性校验

7.1 数据校验工具

pt-table-checksum使用：

#!/bin/bash
# 文件名：checksum_verify.sh
# 功能：使用pt-table-checksum校验主从数据一致性

PT_TABLE_CHECKSUM="/usr/bin/pt-table-checksum"
MYSQL_OPTS="--user=root --password=your_password"

echo"=========================================="
echo"MySQL主从数据一致性校验 - $(date '+%Y-%m-%d %H:%M:%S')"
echo"=========================================="

# 1. 创建checksum表（在所有库执行）
$PT_TABLE_CHECKSUM$MYSQL_OPTS \
  --replicate=test.checksums \
  --create-replicate-table

# 2. 在主库执行校验
$PT_TABLE_CHECKSUM$MYSQL_OPTS \
  --replicate=test.checksums \
  --databases=test \
  --tables='orders,customers,products' \
  --no-check-binlog-format

# 3. 查看校验结果
mysql -u root -p -e "
SELECT
  db,
  tbl,
  this_crc,
  master_crc,
  this_cnt,
  master_cnt,
  DIFFS
FROM
  test.checksums
WHERE
  DIFFS > 0 OR master_crc IS NULL;
"

# 4. 使用pt-table-sync同步差异
# 先在从库执行（只检查不修复）
$PT_TABLE_CHECKSUM$MYSQL_OPTS \
  h=master \
  --replicate=test.checksums \
  --print

# 实际同步（修复差异）
# $PT_TABLE_CHECKSUM $MYSQL_OPTS \
#   h=master \
#   h=slave \
#   --replicate=test.checksums \
#   --execute

7.2 手动数据对比脚本

#!/bin/bash
# 文件名：manual_data_compare.sh
# 功能：手动对比主从表数据

MASTER_HOST="master.example.com"
SLAVE_HOST="slave.example.com"
USER="root"
PASSWORD="your_password"
DATABASE="test"
TABLE="orders"

echo"对比表: ${DATABASE}.${TABLE}"

# 获取主库数据
MASTER_DATA=$(mysql -h$MASTER_HOST -u$USER -p$PASSWORD -N -e "
SELECT COUNT(*), SUM(id), SUM(amount) FROM ${DATABASE}.${TABLE};
")

# 获取从库数据
SLAVE_DATA=$(mysql -h$SLAVE_HOST -u$USER -p$PASSWORD -N -e "
SELECT COUNT(*), SUM(id), SUM(amount) FROM ${DATABASE}.${TABLE};
")

echo"主库: $MASTER_DATA"
echo"从库: $SLAVE_DATA"

if [ "$MASTER_DATA" == "$SLAVE_DATA" ]; then
    echo"[一致] 主从数据一致"
    exit 0
else
    echo"[不一致] 主从数据存在差异"
    exit 1
fi

8. 故障切换与恢复

8.1 主库故障切换流程

#!/bin/bash
# 文件名：failover_master.sh
# 功能：MySQL主库故障切换脚本

NEW_MASTER="slave1.example.com"
OLD_MASTER="master.example.com"
MYSQL_USER="root"
MYSQL_PASSWORD="your_password"

echo"=========================================="
echo"MySQL主库故障切换"
echo"将 $OLD_MASTER 切换到 $NEW_MASTER"
echo"=========================================="

# 1. 确认所有从库已完成复制
echo"[1] 检查从库复制状态..."

for SLAVE in slave1 slave2 slave3; do
    # 这里需要根据实际情况修改主机名
    echo"检查 $SLAVE ..."

    # 获取从库的Master_Log_File和Read_Master_Log_Pos
    # 假设通过SSH访问
    # SLAVE_STATUS=$(ssh $SLAVE "mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e 'SHOW SLAVE STATUS\G'")

    # 这里简化处理，实际应检查每个从库
done

# 2. 停止所有从库的复制
echo"[2] 停止所有从库复制..."
# ssh slave1 "mysql -u$MYSQL_USER -p$PASSWORD -e 'STOP SLAVE;'"

# 3. 在新主库上执行提升操作
echo"[3] 提升新主库..."
mysql -h$NEW_MASTER -u$MYSQL_USER -p$MYSQL_PASSWORD -e "
  STOP SLAVE;
  RESET SLAVE ALL;
  SET GLOBAL read_only = OFF;
  SET GLOBAL super_read_only = OFF;
"

# 4. 配置其他从库指向新主库
echo"[4] 配置其他从库连接新主库..."
# ssh slave2 "mysql -u$MYSQL_USER -p$PASSWORD -e \"
#   CHANGE MASTER TO
#     MASTER_HOST='$NEW_MASTER',
#     MASTER_USER='repl',
#     MASTER_PASSWORD='repl_password',
#     MASTER_AUTO_POSITION=1;
#   START SLAVE;
# \""

# 5. 更新应用连接配置
echo"[5] 切换完成，请更新应用数据库连接配置"

8.2 从库故障恢复

#!/bin/bash
# 文件名：recover_slave.sh
# 功能：恢复故障从库

SLAVE_HOST="slave2.example.com"
MASTER_HOST="master.example.com"
BACKUP_DIR="/backup/mysql"
MYSQL_USER="root"
MYSQL_PASSWORD="your_password"

echo"=========================================="
echo"MySQL从库恢复"
echo"=========================================="

# 1. 停止从库复制
echo"[1] 停止从库复制..."
mysql -h$SLAVE_HOST -u$MYSQL_USER -p$MYSQL_PASSWORD -e "STOP SLAVE;"

# 2. 从主库获取最新备份
echo"[2] 获取主库最新备份..."
# 使用mysqldump进行增量备份
mysqldump -h$MASTER_HOST -u$MYSQL_USER -p$MYSQL_PASSWORD \
  --single-transaction \
  --source-data=2 \
  --routines \
  --triggers \
  --events \
  --all-databases > ${BACKUP_DIR}/full_backup_$(date +%Y%m%d_%H%M%S).sql

# 3. 恢复备份到从库
echo"[3] 恢复备份到从库..."
mysql -h$SLAVE_HOST -u$MYSQL_USER -p$MYSQL_PASSWORD < ${BACKUP_DIR}/full_backup_latest.sql

# 4. 重新配置复制
echo"[4] 重新配置复制..."
mysql -h$SLAVE_HOST -u$MYSQL_USER -p$MYSQL_PASSWORD -e "
  RESET SLAVE ALL;
  CHANGE MASTER TO
    MASTER_HOST='$MASTER_HOST',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl_password',
    MASTER_PORT=3306,
    MASTER_AUTO_POSITION=1;
  START SLAVE;
"

# 5. 验证复制状态
echo"[5] 验证复制状态..."
sleep 5
mysql -h$SLAVE_HOST -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master"

9. 复制监控脚本汇总

9.1 综合监控脚本

#!/bin/bash
# 文件名：mysql_replication_monitor.sh
# 功能：MySQL主从复制综合监控

MYSQL_USER="root"
MYSQL_PASSWORD="your_password"
ALERT_EMAIL="dba@example.com"
LOG_FILE="/var/log/mysql/replication_monitor.log"

echo"$(date '+%Y-%m-%d %H:%M:%S') - 复制监控检查" >> $LOG_FILE

# 获取复制状态
SLAVE_STATUS=$(mysql -u${MYSQL_USER} -p${MYSQL_PASSWORD} \
  -e "SHOW SLAVE STATUS\G" 2>/dev/null)

if [ -z "$SLAVE_STATUS" ]; then
    echo"该服务器不是从库" >> $LOG_FILE
    exit 0
fi

# 解析关键指标
IO_RUNNING=$(echo"$SLAVE_STATUS" | grep "Slave_IO_Running:" | awk '{print $2}')
SQL_RUNNING=$(echo"$SLAVE_STATUS" | grep "Slave_SQL_Running:" | awk '{print $2}')
SECONDS_BEHIND=$(echo"$SLAVE_STATUS" | grep "Seconds_Behind_Master:" | awk '{print $2}')
LAST_ERROR=$(echo"$SLAVE_STATUS" | grep "Last_Error:" | awk -F: '{print $2}')
LAST_IO_ERROR=$(echo"$SLAVE_STATUS" | grep "Last_IO_Error:" | awk -F: '{print $2}')
MASTER_LOG=$(echo"$SLAVE_STATUS" | grep "Master_Log_File:" | awk '{print $2}')
RELAY_LOG=$(echo"$SLAVE_STATUS" | grep "Relay_Master_Log_File:" | awk '{print $2}')

# 检查复制延迟
check_lag() {
    if [ "$SECONDS_BEHIND" == "NULL" ] || [ -z "$SECONDS_BEHIND" ]; then
        return 1
    fi
    if [ $SECONDS_BEHIND -gt 300 ]; then
        return 2  # 严重
    elif [ $SECONDS_BEHIND -gt 60 ]; then
        return 1  # 警告
    fi
    return 0
}

# 输出状态
echo"IO线程: $IO_RUNNING" >> $LOG_FILE
echo"SQL线程: $SQL_RUNNING" >> $LOG_FILE
echo"延迟: ${SECONDS_BEHIND}秒" >> $LOG_FILE

# 检查问题并告警
ALERT_MSG=""

if [ "$IO_RUNNING" != "Yes" ]; then
    ALERT_MSG="${ALERT_MSG}[严重] IO线程未运行: $LAST_IO_ERROR\n"
fi

if [ "$SQL_RUNNING" != "Yes" ]; then
    ALERT_MSG="${ALERT_MSG}[严重] SQL线程未运行: $LAST_ERROR\n"
fi

check_lag
LAG_STATUS=$?

if [ $LAG_STATUS -eq 2 ]; then
    ALERT_MSG="${ALERT_MSG}[严重] 复制延迟超过5分钟: ${SECONDS_BEHIND}秒\n"
elif [ $LAG_STATUS -eq 1 ]; then
    ALERT_MSG="${ALERT_MSG}[警告] 复制延迟超过1分钟: ${SECONDS_BEHIND}秒\n"
fi

# 发送告警
if [ -n "$ALERT_MSG" ]; then
    echo -e "$ALERT_MSG" >> $LOG_FILE
    # 邮件告警
    # echo -e "$ALERT_MSG" | mail -s "MySQL Replication Alert" $ALERT_EMAIL
    # 或者调用其他告警系统
fi

echo"检查完成" >> $LOG_FILE

9.2 延迟监控脚本

#!/bin/bash
# 文件名：check_replication_lag.sh
# 功能：专门监控复制延迟

MYSQL_USER="root"
MYSQL_PASSWORD="your_password"

echo"MySQL复制延迟检查 - $(date '+%Y-%m-%d %H:%M:%S')"

# 获取延迟信息
LAG=$(mysql -u${MYSQL_USER} -p${MYSQL_PASSWORD} \
  -N -e "SHOW SLAVE STATUS" 2>/dev/null | awk '{print $40}')

if [ -z "$LAG" ] || [ "$LAG" == "NULL" ]; then
    echo"[错误] 无法获取延迟信息或该实例不是从库"
    exit 2
fi

echo"当前延迟: ${LAG}秒"

if [ $LAG -gt 300 ]; then
    echo"[严重] 延迟超过5分钟"
    exit 2
elif [ $LAG -gt 60 ]; then
    echo"[警告] 延迟超过1分钟"
    exit 1
else
    echo"[正常] 延迟在可接受范围内"
    exit 0
fi

10. 最佳实践总结

10.1 配置最佳实践

# 主库推荐配置
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
sync_binlog = 1
gtid_mode = ON
enforce_gtid_consistency = ON
max_connections = 2000
innodb_flush_log_at_trx_commit = 1

# 从库推荐配置
[mysqld]
server-id = 2
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8
slave_preserve_commit_order = ON
read_only = ON
super_read_only = ON
slave_skip_errors = ddl_exist_errors  # 可选

10.2 运维检查清单

-- 每日检查项
SHOWSLAVESTATUS\G
SHOWPROCESSLIST
SHOWGLOBALSTATUSLIKE'rpl%'

-- 每周检查项
SELECT * FROM performance_schema.replication_applier_status_by_worker\G
SHOWGLOBALSTATUSLIKE'Innodb_log_waits%'

-- 每月检查项
-- 执行 pt-table-checksum 数据一致性校验
-- 清理旧的Relay Log
PURGEBINARYLOGSBEFOREDATE_SUB(NOW(), INTERVAL7DAY);

-- 备份Binlog位置
SHOWMASTERSTATUS

10.3 常见问题速查表

10.4 重要参数说明

11. 总结

11.1 核心要点回顾

复制原理：

• MySQL主从复制基于Binlog日志传输
• IO线程负责从主库拉取Binlog
• SQL线程负责在从库执行SQL
• GTID模式简化了复制管理

复制模式选择：

• 异步复制：性能优先，允许少量数据丢失
• 半同步复制：平衡数据安全和性能
• GTID复制：简化管理，支持自动故障切换

故障排查关键：

• IO线程问题通常与网络连接相关
• SQL线程问题通常与数据一致性相关
• 延迟问题需要综合分析主从库性能

监控重点：

• Slave_IO_Running和Slave_SQL_Running状态
• Seconds_Behind_Master延迟
• Last_Error错误信息
• 并行复制工作线程状态

11.2 进一步学习建议

推荐资源：

• MySQL 9.0官方文档：Replication
• 《高性能MySQL》第四版
• MySQL Server Blog（官方博客）

实践建议：

• 在测试环境模拟各种故障场景
• 熟练使用pt-table-checksum和pt-table-sync工具
• 建立完善的复制监控体系
• 定期进行故障切换演练

通过本文的系统学习，应该能够全面理解MySQL主从复制的原理，掌握常见故障的排查方法，并具备生产环境主从架构的运维能力。主从复制是高可用数据库架构的基础，需要持续学习和实践。

版权申明：内容来源网络，版权归原创者所有，如有侵权请联系删除

想了解更多干货，可通过下方扫码关注

可扫码添加上智启元官方客服微信👇