MySQL 主从复制原理与读写分离实战

主从复制是数据库高可用和扩展性的基础。本文从binlog 原理出发，深入剖析 MySQL 主从复制的工作流程、三种复制方式（异步/半同步/延迟）、GTID 复制、复制延迟原因与处理，以及读写分离的架构设计与实战。

一、主从复制概述

1.1 为什么需要主从复制

1.2 主从复制架构

---

二、binlog 详解

2.1 binlog 的作用

2.2 binlog 三种格式

2.3 binlog 格式对比

-- 表结构
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    create_time DATETIME
);

-- 插入数据
INSERT INTO users VALUES (1, '张三', NOW());

-- STATEMENT 模式 binlog 内容
INSERT INTO users VALUES (1, '张三', NOW())

-- ROW 模式 binlog 内容
### INSERT INTO test.users
### SET
###   @1=1
###   @2='张三'
###   @3='2024-01-01 12:00:00'

2.4 binlog 文件结构

---

三、主从复制原理

3.1 复制工作流程

3.2 核心线程

3.3 复制配置

# Master 配置
[mysqld]
server-id = 1
log-bin = /var/lib/mysql/mysql-bin
binlog-format = ROW
sync-binlog = 1
max-binlog-size = 100M

# Slave 配置
[mysqld]
server-id = 2
relay-log = /var/lib/mysql/relay-log
relay-log-space-limit = 10G
read-only = ON

3.4 配置主从复制

-- Master: 创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- Master: 获取 binlog 位置
SHOW MASTER STATUS;
-- +------------------+----------+
-- | File             | Position |
-- +------------------+----------+
-- | mysql-bin.000001 | 154      |
-- +------------------+----------+

-- Slave: 配置主从复制
CHANGE MASTER TO
    MASTER_HOST = 'master-host',
    MASTER_PORT = 3306,
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'password',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 154;

-- Slave: 启动复制
START SLAVE;

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

---

四、复制方式

4.1 异步复制

4.2 半同步复制

4.3 配置半同步复制

-- 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;  -- 等待超时(ms)

-- Slave: 安装半同步插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- 重启 IO Thread
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

4.4 延迟复制

---

五、GTID 复制

5.1 GTID 概念

5.2 GTID 工作流程

5.3 GTID 配置

# Master
[mysqld]
gtid-mode = ON
enforce-gtid-consistency = ON
log-slave-updates = ON

# Slave
[mysqld]
gtid-mode = ON
enforce-gtid-consistency = ON
log-slave-updates = ON

5.4 GTID 故障转移

-- 传统方式需要指定 binlog 位置
CHANGE MASTER TO
    MASTER_LOG_FILE = 'mysql-bin.000123',
    MASTER_LOG_POS = 456;

-- GTID 方式更简单
CHANGE MASTER TO
    MASTER_AUTO_POSITION = 1;
-- MySQL 自动计算需要同步的位置

---

六、复制延迟

6.1 延迟原因

6.2 延迟监控

-- 查看复制延迟
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master: 0 表示没有延迟

-- 查看详细状态
SHOW SLAVE STATUS\G
-- Relay_Log_Pos: 应用的 Relay Log 位置
-- Exec_Master_Log_Pos: 执行的 Master binlog 位置
-- Read_Master_Log_Pos: 读取的 Master binlog 位置

-- 延迟原因分析
SHOW STATUS LIKE 'Slave%';

6.3 减少延迟的方法

6.4 并行复制配置

-- MySQL 5.7+ 并行复制
SET GLOBAL slave_parallel_workers = 4;  -- 工作线程数
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';  -- 基于组提交

-- MySQL 8.0+
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 8;

-- 配置文件中设置
[mysqld]
slave_parallel_workers = 8
slave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = ON

---

六、并行复制深度解析

6.5 并行复制原理

6.6 三种并行复制策略

6.7 LOGICAL_CLOCK 源码级解析

6.8 WRITESET 并行复制配置

-- MySQL 8.0 配置
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 16;

-- WRITESET 额外配置
SET GLOBAL transaction_write_set_extraction = 'XXHASH64';
SET GLOBAL slave_parallel_type = 'WRITESET';
SET GLOBAL slave_parallel_workers = 16;

-- 验证配置
SHOW VARIABLES LIKE 'slave_parallel%';
SHOW VARIABLES LIKE 'transaction_write_set_extraction';

-- 监控并行复制状态
SHOW STATUS LIKE 'Slave_parallel_%';
-- Slave_parallel_threads: 活跃线程数
-- Slave_parallel_retries: 重试次数

6.9 并行复制参数调优

---

六·续、主从数据校验与修复

6.10 为什么需要数据校验

6.11 pt-table-checksum 使用

# 安装 Percona Toolkit
# apt install percona-toolkit

# 在主库执行校验
pt-table-checksum h=master-host,u=root,p=password \
    --nocheck-replication-filters \
    --replicate=test.checksums \
    --databases=myapp

# 参数说明:
# --replicate: 结果写入哪个表
# --nocheck-replication-filters: 不检查复制过滤器
# --databases: 指定数据库

# 在从库检查差异
pt-table-sync --print h=slave-host,u=root,p=password \
    --replicate=test.checksums \
    --database=myapp

# 直接修复不一致
pt-table-sync --execute h=slave-host,u=root,p=password \
    --replicate=test.checksums

6.12 手动修复不一致数据

-- 方法1: 直接同步
DELETE FROM slave_db.users WHERE id = 123;
INSERT INTO slave_db.users SELECT * FROM master_db.users WHERE id = 123;

-- 方法2: 基于 GTID 跳过错误
STOP SLAVE;
SET gtid_next = 'source_id:transaction_id';
BEGIN;
COMMIT;
SET gtid_next = 'AUTOMATIC';
START SLAVE;

-- 方法3: 重新同步表
-- 从主库导出
mysqldump -h master -u root -p myapp users > users.sql

-- 导入从库
mysql -h slave -u root -p myapp < users.sql

---

七、读写分离

7.1 读写分离架构

7.2 读写分离实现方式

7.3 ShardingSphere-JDBC 配置

// Maven 依赖
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-core</artifactId>
    <version>5.3.0</version>
</dependency>

// 配置
spring:
  datasource:
    master:
      jdbc-url: jdbc:mysql://master:3306/db?useSSL=false
      driver-class-name: com.mysql.cj.jdbc.Driver
    slave:
      jdbc-url: jdbc:mysql://slave:3306/db?useSSL=false
      driver-class-name: com.mysql.cj.jdbc.Driver

// 读写分离配置
spring:
  shardingsphere:
    datasource:
      names: master,slave
      master:
        type: com.zaxxer.hikari.HikariDataSource
      slave:
        type: com.zaxxer.hikari.HikariDataSource
    master-slave:
      data-sources:
        ds-master:
          master-data-source-name: master
          slave-data-source-names: slave
          load-balance-algorithm-type: round_robin
      config:
        read-write-split:
          data-sources:
            ds-master:
              type: Static
              props:
                write-data-source-name: master
                read-data-source-names: slave
          load-balancers:
            roundRobin:
              type: ROUND_ROBIN

7.4 手动读写分离

// 数据源选择器
@Component
public class DataSourceSelector {
    
    @Autowired
    @Qualifier("masterDataSource")
    private DataSource masterDataSource;
    
    @Autowired
    @Qualifier("slaveDataSource")
    private DataSource slaveDataSource;
    
    public DataSource getDataSource(boolean isWrite) {
        return isWrite ? masterDataSource : slaveDataSource;
    }
}

// 使用示例
@Service
public class UserService {
    
    @Autowired
    private DataSourceSelector dataSourceSelector;
    
    @Transactional
    public void saveUser(User user) {
        DataSource ds = dataSourceSelector.getDataSource(true);
        // 使用 masterDataSource
    }
    
    public User getUser(Long id) {
        DataSource ds = dataSourceSelector.getDataSource(false);
        // 使用 slaveDataSource
    }
}

7.5 读写分离注意事项

---

八、面试高频问题

8.1 MySQL 主从复制原理？

主从复制原理：

1. Master 端
   - 事务提交时，将写操作记录到 binlog
   - Dump 线程读取 binlog，发送给 Slave

2. Slave 端
   - IO Thread: 接收 binlog，写入 relay-log
   - SQL Thread: 读取 relay-log，执行 SQL

3. 复制模式
   - 异步复制: 不等待从库确认
   - 半同步复制: 等待至少一个从库确认
   - 延迟复制: 从库延迟 N 秒复制

8.2 binlog 有哪几种格式？区别？

┌─────────────┬────────────────────────────────┬───────────────────────┐
│   格式        │         记录内容                  │       优缺点           │
├─────────────┼────────────────────────────────┼───────────────────────┤
│ STATEMENT   │ SQL 语句本身                     │ 日志量小，可能不一致   │
│ ROW         │ 数据行的变化                     │ 日志量大，绝对一致     │
│ MIXED       │ 混合模式，默认 STATEMENT         │ 平衡方案               │
└─────────────┴────────────────────────────────┴───────────────────────┘

建议使用 ROW 格式，确保数据一致性。

8.3 如何处理主从复制延迟？

1. 架构层面
   - 从库使用 SSD
   - 从库独立部署，不与应用竞争资源
   - 使用并行复制

2. 参数调优
   - slave_parallel_workers
   - slave_parallel_type = LOGICAL_CLOCK

3. 业务层面
   - 强制关键业务走主库
   - 读写分离架构中，读从库前先判断延迟

4. 监控
   - 监控 Seconds_Behind_Master
   - 设置告警

8.4 什么是 GTID？有什么优势？

GTID = Global Transaction ID = source_id:transaction_id

组成：
- source_id: Master 的 UUID
- transaction_id: 事务序号

优势：
1. 自动定位
   - 不需要指定 binlog 文件和位置
   - CHANGE MASTER TO MASTER_AUTO_POSITION = 1

2. 简化故障转移
   - 新从库不需要知道 binlog 位置
   - 自动同步缺失的事务

3. 一致性保证
   - 每个事务有唯一 ID
   - 不会重复执行或遗漏

8.5 读写分离的常见问题？

1. 复制延迟
   - 刚写入的数据立即读取可能不存在
   - 解决方案: 强制关键业务走主库

2. 数据不一致
   - 主从数据可能短暂不一致
   - 解决方案: 使用半同步复制

3. 事务问题
   - 事务中先写后读必须走主库
   - 解决方案: @Transactional 注解路由到主库

4. 自增主键问题
   - 多主架构需要使用统一 ID 生成器
   - 解决方案: Snowflake 算法

---

九、总结

9.1 复制方式对比

方式	性能	安全性	配置复杂度
异步复制	高	低	简单
半同步复制	中	高	中等
延迟复制	高	中	简单
GTID 复制	中	高	简单

9.2 优化建议

主从复制优化建议：

1. 网络优化
   - 主从网络延迟低
   - 使用千兆网络

2. 硬件优化
   - 从库使用 SSD
   - 从库独立磁盘

3. 参数优化
   - 并行复制
   - 合理设置 buffer

4. 监控
   - 监控复制延迟
   - 监控从库状态

9.3 架构建议

生产环境架构建议：

1. 一主多从
   - 1 个主库
   - 2-3 个从库用于读写分离
   - 1 个从库用于备份

2. 半同步复制
   - 保证数据安全性
   - 配置 wait_timeout

3. 读写分离
   - 读操作分散到从库
   - 写操作走主库
   - 关键业务强制主库

4. 高可用
   - MHA / Orchestrator
   - 自动故障切换
   - VIP 漂移