MySQL主从复制原理与实战排错指南

前言

主从复制是MySQL高可用架构的基础。无论是读写分离、数据备份，还是故障切换，都离不开主从复制。然而，复制延迟、数据不一致、主从切换失败等问题也常常让运维人员头疼。本文将深入剖析MySQL复制的底层原理，并提供完整的问题排查思路。

一、复制原理深度解析

1.1 复制架构

Master                              Slave
┌───────────────────────┐          ┌───────────────────────┐
│  Client Write         │          │                       │
│      │                │          │                       │
│      ▼                │          │                       │
│  Binary Log ──────────┼──dump ──▶│  IO Thread            │
│  (binlog)             │  线程    │      │                │
└───────────────────────┘          │      ▼                │
                                   │  Relay Log            │
                                   │      │                │
                                   │      ▼                │
                                   │  SQL Thread ──▶ 数据  │
                                   └───────────────────────┘

1.2 三种复制格式

格式	原理	优点	缺点
STATEMENT	记录SQL语句	日志小	不确定性函数可能不一致
ROW	记录行变化	精确	日志大（UPDATE全表）
MIXED	自动选择	平衡	复杂度高

# 推荐使用ROW格式（MySQL 5.7.7+ 默认）
binlog_format = ROW
binlog_row_image = FULL     # 记录完整行

1.3 GTID复制

GTID（Global Transaction Identifier）是MySQL 5.6引入的全局事务标识，简化了主从切换。

GTID格式: server_uuid:transaction_id
示例:     3E11FA47-71CA-11E1-9E33-C80AA9429562:1-23

-- 开启GTID
SET GLOBAL gtid_mode = ON;
SET GLOBAL enforce_gtid_consistency = ON;

GTID优势：

切换时无需手动指定binlog位置
自动跳过已执行的事务
支持多源复制

二、搭建主从复制

2.1 Master配置

# my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
expire_logs_days = 7
max_binlog_size = 1G

# GTID
gtid_mode = ON
enforce_gtid_consistency = ON

# 半同步复制插件
plugin-load = "rpl_semi_sync_master=semisync_master.so"
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 1000  # ms

2.2 创建复制用户

CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;

2.3 Slave配置与初始化

# my.cnf
[mysqld]
server-id = 2
relay-log = relay-bin
log-bin = mysql-bin         # 如果Slave还要做其他Slave的Master
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
read_only = ON              # 只读
log_slave_updates = ON      # 记录从Master接收的更新

-- 基于GTID配置复制
CHANGE MASTER TO
    MASTER_HOST = '192.168.1.10',
    MASTER_PORT = 3306,
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'repl_password',
    MASTER_AUTO_POSITION = 1;  -- 使用GTID自动定位

START SLAVE;

-- 查看复制状态
SHOW SLAVE STATUS\G

2.4 关键状态字段

SHOW SLAVE STATUS\G
-- 重点关注：
-- Slave_IO_Running: Yes      ← IO线程状态
-- Slave_SQL_Running: Yes     ← SQL线程状态
-- Seconds_Behind_Master: 0   ← 复制延迟
-- Retrieved_Gtid_Set         ← 已接收的GTID
-- Executed_Gtid_Set          ← 已执行的GTID
-- Last_IO_Error              ← 最近的IO错误
-- Last_SQL_Error             ← 最近的SQL错误

三、半同步复制

3.1 异步 vs 半同步

异步复制:
  Client → Master → (事务提交) → 返回Client
                   └→ binlog异步发送到Slave
  风险: 主库宕机可能丢失已提交但未同步的事务

半同步复制:
  Client → Master → binlog写入 → 等待至少一个Slave确认 → 返回Client
  保障: 至少一个Slave收到binlog后才返回

3.2 半同步配置

-- Master
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 = 1000;

-- Slave
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- 重启IO线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

-- 查看状态
SHOW STATUS LIKE 'Rpl_semi_sync%';

四、复制延迟排查

4.1 延迟原因分析

-- 查看延迟
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master 不为0即存在延迟

-- 查看SQL线程正在执行什么
SELECT * FROM performance_schema.replication_applier_status_by_worker;

常见原因：

原因	现象	解决
大事务	偶发延迟	拆分事务
无主键表	row格式下全表扫描	添加主键
Slave性能不足	持续延迟	升级硬件/并行复制
锁等待	SQL线程等待	优化锁竞争
网络问题	IO线程频繁重连	优化网络

4.2 并行复制

-- MySQL 5.7+ 基于LOGICAL_CLOCK的并行复制
-- Slave配置
STOP SLAVE SQL_THREAD;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 4;
START SLAVE SQL_THREAD;

-- 查看并行复制状态
SELECT * FROM performance_schema.replication_applier_status_by_worker;

4.3 延迟监控

-- 主库查询所有从库延迟
SELECT * FROM mysql.slave_master_info;

-- 使用pt-heartbeat监控延迟
# 在主库创建心跳表并定时更新
pt-heartbeat --user=root --password=pass -D percona --create-table --update
# 在从库监控延迟
pt-heartbeat --user=root --password=pass -D percona --monitor

五、常见故障修复

5.1 复制中断

-- 错误: 1062 Duplicate entry (主键冲突)
-- 解决方案1: 跳过该事务
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

-- 解决方案2: 删除重复数据后重试
STOP SLAVE;
DELETE FROM table WHERE id = 12345;
START SLAVE;

-- 基于GTID跳过（需先注入空事务）
STOP SLAVE;
SET GTID_NEXT = 'uuid:123';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START SLAVE;

5.2 数据一致性校验

# 使用pt-table-checksum校验
pt-table-checksum \
    --host=192.168.1.10 \
    --user=root \
    --password=pass \
    --databases=mydb \
    --replicate=percona.checksums

# 查看差异
SELECT db, tbl, SUM(this_cnt) AS total_rows, 
       SUM(this_cnt) - SUM(master_cnt) AS diff
FROM percona.checksums
WHERE master_cnt <> this_cnt OR master_crc <> this_crc
GROUP BY db, tbl;

5.3 使用pt-table-sync修复

# 先打印差异（不执行）
pt-table-sync --print \
    --sync-to-master h=192.168.1.20,u=root,p=pass \
    --databases=mydb

# 执行修复
pt-table-sync --execute \
    --sync-to-master h=192.168.1.20,u=root,p=pass \
    --databases=mydb

六、主从切换流程

6.1 计划内切换

#!/bin/bash
# switchover.sh

OLD_MASTER="192.168.1.10"
NEW_MASTER="192.168.1.20"

# 1. 确保原主库只读
mysql -h $OLD_MASTER -e "SET GLOBAL read_only = ON;"
mysql -h $OLD_MASTER -e "SET GLOBAL super_read_only = ON;"

# 2. 等待从库同步完成
echo "等待从库同步..."
sleep 5
mysql -h $NEW_MASTER -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master"

# 3. 停止新主库的复制
mysql -h $NEW_MASTER -e "STOP SLAVE; RESET SLAVE ALL;"

# 4. 新主库取消只读
mysql -h $NEW_MASTER -e "SET GLOBAL read_only = OFF; SET GLOBAL super_read_only = OFF;"

# 5. 其他从库CHANGE MASTER到新主库
# ... (遍历所有从库执行CHANGE MASTER)

echo "主从切换完成，新主库: $NEW_MASTER"

总结

MySQL主从复制是运维的核心技能：

原理要懂：理解binlog、relay log、GTID的工作机制
监控要全：延迟、错误、数据一致性一个不能少
预案要练：主从切换脚本要经过实际演练验证
工具要用：pt-table-checksum/table-sync等工具能大幅提高效率

MySQL主从复制原理与实战排错指南

MySQL主从复制原理与实战排错指南

前言

一、复制原理深度解析

1.1 复制架构

1.2 三种复制格式

1.3 GTID复制

二、搭建主从复制

2.1 Master配置

2.2 创建复制用户

2.3 Slave配置与初始化

2.4 关键状态字段

三、半同步复制

3.1 异步 vs 半同步

3.2 半同步配置

四、复制延迟排查

4.1 延迟原因分析

4.2 并行复制

4.3 延迟监控

五、常见故障修复

5.1 复制中断

5.2 数据一致性校验

5.3 使用pt-table-sync修复

六、主从切换流程

6.1 计划内切换

总结

相关文章

MySQL性能调优深度解析：从配置到SQL优化

PostgreSQL高可用架构实战：从流复制到Patroni