MySQL 主从复制与读写分离

主从复制是 MySQL 高可用架构的基础，读写分离提升系统吞吐量。

主从复制原理

复制流程

SQL

┌──────────────┐                    ┌──────────────┐
│    Master    │                    │    Slave     │
├──────────────┤                    ├──────────────┤
│              │  Binlog Dump     ─▶│  I/O Thread  │
│  Binlog      │◀─────────────────  │              │
│              │                    │  Relay Log   │
│              │                    │              │
│              │                    │  SQL Thread  │
│              │                    │      ↓       │
│  Data        │                    │  Data        │
└──────────────┘                    └──────────────┘

复制线程：

Binlog Dump Thread：主库线程，发送 binlog 到从库
I/O Thread：从库线程，接收 binlog 写入 relay log
SQL Thread：从库线程，执行 relay log 中的事件

复制位置记录

SQL

-- 主库记录
Master_Log_File: 当前 binlog 文件
Read_Master_Log_Pos: 已读取的位置

-- 从库记录
Relay_Log_File: relay log 文件
Relay_Log_Pos: relay log 执行位置
Exec_Master_Log_Pos: 已执行的主库位置

复制模式对比

三种复制模式

模式	说明	数据安全	性能
异步复制	主库提交后即返回，不等待从库	低	高
半同步复制	主库等待至少一个从库确认	中	中
组复制 (MGR)	多主/单主集群，Paxos 协议	高	低

1. 异步复制（默认）

SQL

Master 提交事务 ─▶ 返回成功 ─▶ Binlog 发送到 Slave

数据丢失风险：主库宕机时，未同步的 binlog 丢失

2. 半同步复制

ini

Master 提交事务 ─▶ 等待 Slave ACK ─▶ 返回成功
                    │
                    ├─ 超时（rpl_semi_sync_master_timeout）
                    │   则降级为异步
                    │
                    └─ 收到 ACK，确认复制完成

配置半同步复制：

SQL

-- 主库安装插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

-- 从库安装插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

-- 主库配置
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000;  -- 1秒超时
SET GLOBAL rpl_semi_sync_master_wait_for_slave_count = 1;  -- 等待1个从库

-- 从库配置
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- 重启从库复制
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

3. MySQL Group Replication (MGR)

ini

-- 安装插件
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

-- 配置参数
SET GLOBAL group_replication_group_name = 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee';
SET GLOBAL group_replication_start_on_boot = ON;
SET GLOBAL group_replication_local_address = '192.168.1.101:33061';
SET GLOBAL group_replication_group_seeds = '192.168.1.101:33061,192.168.1.102:33061';

-- 启动组复制
START GROUP_REPLICATION;

主从复制配置

主库配置

SQL

[mysqld]
# 服务器唯一ID
server-id = 1

# 开启 binlog
log-bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL

# 必要参数
gtid_mode = ON
enforce_gtid_consistency = ON

# 复制用户
# CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
# GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

SQL

-- 创建复制用户
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;

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

从库配置

Java

[mysqld]
# 服务器唯一ID
server-id = 2

# 开启 relay log
relay-log = relay-bin
relay-log-index = relay-bin.index

# GTID 配置
gtid_mode = ON
enforce_gtid_consistency = ON

# 只读模式
read_only = ON
super_read_only = ON

# 并行复制
slave_parallel_workers = 4
slave_parallel_type = LOGICAL_CLOCK

SQL

-- 配置复制（GTID 模式）
CHANGE MASTER TO
    MASTER_HOST = '192.168.1.101',
    MASTER_PORT = 3306,
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'repl_password',
    MASTER_AUTO_POSITION = 1;

-- 启动复制
START SLAVE;

-- 查看状态
SHOW SLAVE STATUS\G

关键状态指标

SQL

SHOW SLAVE STATUS\G

-- 关键字段
Slave_IO_Running: Yes          -- I/O 线程运行状态
Slave_SQL_Running: Yes         -- SQL 线程运行状态
Seconds_Behind_Master: 0       -- 复制延迟秒数
Retrieved_Gtid_Set: ...        -- 已接收的 GTID
Executed_Gtid_Set: ...         -- 已执行的 GTID
Last_IO_Error: ...             -- I/O 错误信息
Last_SQL_Error: ...            -- SQL 错误信息

读写分离实现

应用层实现

Java

// Java 示例：根据操作类型选择数据源
public Connection getConnection(String operationType) {
    if (operationType.equals("write")) {
        return masterDataSource.getConnection();
    } else {
        return slaveDataSource.getConnection();
    }
}

// Spring 事务注解
@Transactional(readOnly = true)  // 自动路由到从库
public User getUser(Long id) {
    return userDao.selectById(id);
}

@Transactional  // 默认路由到主库
public void updateUser(User user) {
    userDao.update(user);
}

中间件实现

中间件	说明
ProxySQL	自动解析 SQL，路由到对应主机组
MySQL Router	MySQL 官方路由组件
MaxScale	MariaDB 出品，功能强大
ShardingSphere	客户端侧读写分离

ProxySQL 读写分离配置

SQL

-- 写操作路由主库（hostgroup 10）
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^INSERT|^UPDATE|^DELETE|^REPLACE', 10, 1);

-- 读操作路由从库（hostgroup 20）
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (2, 1, '^SELECT', 20, 1);

复制延迟处理

延迟原因

原因	说明
大事务	单事务执行时间过长
从库负载高	从库查询压力大
网络延迟	主从之间网络慢
单线程复制	SQL Thread 单线程执行

优化方案

1. 并行复制

SQL

-- MySQL 5.7+ 并行复制
SET GLOBAL slave_parallel_workers = 8;
SET GLOBAL slave_parallel_type = LOGICAL_CLOCK;
SET GLOBAL slave_preserve_commit_order = ON;

2. 分库分表减少单库压力

SQL

大事务拆分：
┌──────────────┐          ┌──────────────┐
│  大事务      │   拆分   │  小事务1     │
│  100万行     │  ────▶  │  1万行       │
│              │          │  小事务2     │
│              │          │  ...         │
└──────────────┘          └──────────────┘

3. 从库延迟强制路由主库

SQL

// 关键业务强制读主库
public Order getOrder(Long orderId) {
    // 订单状态等关键数据必须读主库
    return masterDataSource.query(orderId);
}

// 非关键业务可读从库
public Order getOrderSnapshot(Long orderId) {
    return slaveDataSource.query(orderId);
}

4. 多级复制

SQL

Master ─▶ Slave1（实时复制）
           │
           └─▶ Slave2（延迟复制，分担读取压力）
           │
           └─▶ Slave3（延迟复制，分担读取压力）

text

-- 延迟复制配置（延迟 1 小时）
CHANGE MASTER TO
    MASTER_HOST = '192.168.1.101',
    MASTER_DELAY = 3600;  -- 延迟秒数

START SLAVE;

复制故障处理

IO 线程故障

text

-- 常见错误：连接失败、认证失败
-- 处理步骤
STOP SLAVE;
-- 检查复制用户权限、网络连通性
-- 重新配置
CHANGE MASTER TO MASTER_PASSWORD = 'new_password';
START SLAVE;

SQL 線程故障

text

-- 常见错误：主键冲突、数据不一致
-- 处理步骤
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;  -- 跳过 1 个事件
START SLAVE;

-- 或使用 GTID 跳过
SET GTID_NEXT = 'aaaa-bbbb-cccc-dddd-eeee:123';
BEGIN;
COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START SLAVE;

主从切换

text

-- 1. 停止原主库写入
FLUSH TABLES WITH READ LOCK;

-- 2. 确认从库同步完成
SHOW SLAVE STATUS;
-- Seconds_Behind_Master = 0

-- 3. 从库提升为主库
STOP SLAVE;
RESET SLAVE ALL;
SET GLOBAL read_only = OFF;
SET GLOBAL super_read_only = OFF;

-- 4. 其他从库指向新主库
CHANGE MASTER TO MASTER_HOST = '192.168.1.102';
START SLAVE;

监控指标

text

-- 复制状态监控
SELECT
    variable_name,
    variable_value
FROM performance_schema.replication_connection_status
WHERE channel_name = '';

-- 复制延迟监控
SELECT
    variable_value AS seconds_behind_master
FROM performance_schema.replication_applier_status_by_coordinator
WHERE channel_name = '';

-- 复制线程状态
SHOW PROCESSLIST;
-- Id: xxx, User: system user, Command: Connect

要点总结

复制原理：Binlog Dump → I/O Thread → Relay Log → SQL Thread
三种模式：异步（默认）、半同步（推荐）、组复制（高可用）
GTID 复制：推荐使用，简化配置和故障恢复
读写分离：应用层或中间件实现，写主读从
复制延迟：并行复制、大事务拆分、关键业务读主库
故障处理：跳过错误事件、主从切换流程

📝 发现内容有误？点击此处直接编辑