跳到主要内容

MySQL 主从复制

主从复制是 MySQL 高可用架构的基础，用于实现读写分离、数据备份和故障切换。本章将详细介绍 MySQL 复制的原理和配置方法。

复制概述​

什么是主从复制？​

主从复制是指将一台 MySQL 服务器（主库/Master）的数据自动同步到一个或多个 MySQL 服务器（从库/Slave）的机制。

┌─────────────────────────────────────────────────────────────┐
│                    主从复制架构                              │
├─────────────────────────────────────────────────────────────┤
│                                                            │
│   ┌─────────────┐         ┌─────────────┐                 │
│   │   Master    │ ──────> │   Slave 1   │                 │
│   │  (主库)     │         │  (从库)     │                 │
│   │  写 + 读    │         │   只读      │                 │
│   └─────────────┘         └─────────────┘                 │
│         │                                                       │
│         │                 ┌─────────────┐                       │
│         └───────────────> │   Slave 2   │                       │
│                           │  (从库)     │                       │
│                           │   只读      │                       │
│                           └─────────────┘                       │
│                                                            │
└─────────────────────────────────────────────────────────────┘

复制的用途​

用途说明
读写分离主库写入，从库读取，分散负载
数据备份从库作为实时备份
故障切换主库故障时切换到从库
数据分析从库用于报表分析，不影响主库
地理分布跨地域数据同步

复制类型​

MySQL 支持多种复制方式：

复制方式说明特点
异步复制默认方式主库不等待从库确认，性能高但可能丢数据
半同步复制主库等待至少一个从库确认性能和安全的平衡
全同步复制主库等待所有从库确认最安全但性能最差
GTID 复制基于全局事务标识便于管理，推荐使用

复制原理​

基于二进制日志的复制​

MySQL 复制基于二进制日志（binlog），核心流程如下：

┌─────────────────────────────────────────────────────────────┐
│                    复制原理流程                              │
├─────────────────────────────────────────────────────────────┤
│                                                            │
│   Master                                                   │
│   ┌─────────────────────────────────────────────────────┐ │
│   │  1. 事务提交                                          │ │
│   │  2. 写入 Binlog                                       │ │
│   │  3. Dump Thread 读取并发送                            │ │
│   └─────────────────────────────────────────────────────┘ │
│                          │                                      │
│                          ▼                                      │
│   Network                 │                                      │
│                          ▼                                      │
│   Slave                                                   │
│   ┌─────────────────────────────────────────────────────┐ │
│   │  4. IO Thread 接收并写入 Relay Log                    │ │
│   │  5. SQL Thread 读取并执行 Relay Log                   │ │
│   │  6. 数据同步完成                                      │ │
│   └─────────────────────────────────────────────────────┘ │
│                                                            │
└─────────────────────────────────────────────────────────────┘

三个核心线程：

  1. Binlog Dump Thread（主库）：读取 binlog 并发送给从库
  2. IO Thread（从库）：接收主库的 binlog，写入 relay log
  3. SQL Thread（从库）：读取 relay log 并执行 SQL

复制模式​

异步复制（默认）​

主库提交事务 → 写入 binlog → 立即返回成功
                        → 异步发送给从库
  • 优点：性能最高，主库不等待
  • 缺点：主库崩溃可能丢失未同步的数据

半同步复制​

主库提交事务 → 写入 binlog → 等待至少一个从库确认 → 返回成功
  • 优点：至少一份数据备份，更安全
  • 缺点：性能略有下降

全同步复制​

主库提交事务 → 写入 binlog → 等待所有从库确认 → 返回成功
  • 优点：数据最安全
  • 缺点：性能最差，需要所有从库在线

基于位置的复制配置​

主库配置​

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

# 启用二进制日志
log-bin = mysql-bin

# 日志格式（ROW 推荐）
binlog_format = ROW

# 要复制的数据库（可选，不设置则复制所有）
binlog-do-db = mydb

# 忽略的数据库（可选）
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema

# 日志过期时间
expire_logs_days = 7

# 同步到磁盘的方式
sync_binlog = 1

重启 MySQL 使配置生效：

sudo systemctl restart mysql

创建复制用户​

-- 在主库上创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';

-- 授予复制权限
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

获取主库状态​

-- 锁定主库（确保数据一致性）
FLUSH TABLES WITH READ LOCK;

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

输出示例：

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      154 | mydb         | mysql            |
+------------------+----------+--------------+------------------+

记录 File 和 Position 的值，配置从库时需要使用。

从库配置​

[mysqld]
# 服务器唯一 ID（必须与主库不同）
server-id = 2

# 启用中继日志
relay-log = mysql-relay

# 启用二进制日志（可选，用于级联复制）
log-bin = mysql-bin

# 只读模式
read_only = 1
super_read_only = 1

重启 MySQL 使配置生效：

sudo systemctl restart mysql

配置从库连接​

-- 停止从库复制
STOP SLAVE;

-- 配置主库连接信息
CHANGE MASTER TO
    MASTER_HOST = '192.168.1.100',
    MASTER_PORT = 3306,
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'repl_password',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 154;

-- 启动从库复制
START SLAVE;

-- 解锁主库
-- 在主库执行
UNLOCK TABLES;

验证复制状态​

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

关键指标：

Slave_IO_Running: Yes          # IO 线程运行中
Slave_SQL_Running: Yes         # SQL 线程运行中
Seconds_Behind_Master: 0       # 与主库的延迟秒数
Last_IO_Error:                 # IO 错误信息
Last_SQL_Error:                # SQL 错误信息

如果 Slave_IO_Running 和 Slave_SQL_Running 都是 Yes，则复制正常。

GTID 复制配置​

GTID（Global Transaction Identifier）是 MySQL 5.6 引入的特性，为每个事务分配唯一标识符，简化了复制管理。

GTID 格式​

GTID = source_id:transaction_id

示例：3E11FA47-71CA-11E1-9E33-C80AA9429562:23
      ↑                              ↑
      服务器 UUID                     事务序号

主库配置​

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

# 启用二进制日志
log-bin = mysql-bin

# 日志格式
binlog_format = ROW

# 启用 GTID
gtid_mode = ON

# 强制 GTID 一致性
enforce_gtid_consistency = ON

# 日志过期时间
expire_logs_days = 7

从库配置​

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

# 启用中继日志
relay-log = mysql-relay

# 启用 GTID
gtid_mode = ON

# 强制 GTID 一致性
enforce_gtid_consistency = ON

# 只读模式
read_only = 1

配置 GTID 复制​

-- 在从库配置
CHANGE MASTER TO
    MASTER_HOST = '192.168.1.100',
    MASTER_PORT = 3306,
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'repl_password',
    MASTER_AUTO_POSITION = 1;  -- 启用 GTID 自动定位

-- 启动复制
START SLAVE;

-- 查看状态
SHOW SLAVE STATUS\G

GTID 的优势​

-- 1. 简化故障切换
-- 主库故障后，只需指向新主库即可
CHANGE MASTER TO
    MASTER_HOST = '新主库IP',
    MASTER_AUTO_POSITION = 1;

-- 2. 查看已执行的事务
SHOW MASTER STATUS;
-- Executed_Gtid_Set: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-100

-- 3. 跳过有问题的 GTID
SET GTID_NEXT = '3E11FA47-71CA-11E1-9E33-C80AA9429562:101';
BEGIN;
COMMIT;
SET GTID_NEXT = 'AUTOMATIC';

半同步复制配置​

安装插件​

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

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

-- 查看插件状态
SHOW PLUGINS;

主库配置​

-- 启用半同步复制
SET GLOBAL rpl_semi_sync_master_enabled = 1;

-- 设置等待超时时间（毫秒）
SET GLOBAL rpl_semi_sync_master_timeout = 1000;

-- 查看状态
SHOW VARIABLES LIKE 'rpl_semi_sync_master%';
SHOW STATUS LIKE 'Rpl_semi_sync_master%';

从库配置​

-- 启用半同步复制
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- 重启 IO 线程使配置生效
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

-- 查看状态
SHOW VARIABLES LIKE 'rpl_semi_sync_slave%';

配置文件方式​

[mysqld]
# 主库配置
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 1000

# 从库配置
rpl_semi_sync_slave_enabled = 1

复制管理​

查看复制状态​

-- 查看从库状态
SHOW SLAVE STATUS\G

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

-- 查看复制线程
SHOW PROCESSLIST;

-- 查看所有从库状态（主库执行）
SHOW SLAVE HOSTS;

常见问题处理​

1. IO 线程停止​

-- 检查错误信息
SHOW SLAVE STATUS\G
-- 查看 Last_IO_Error 和 Last_IO_Errno

-- 常见原因：
-- 1. 网络连接问题
-- 2. 复制用户权限不足
-- 3. 主库 binlog 已被删除

-- 解决方法：
-- 1. 检查网络
-- 2. 验证用户权限
-- 3. 重新同步数据

2. SQL 线程停止​

-- 检查错误信息
SHOW SLAVE STATUS\G
-- 查看 Last_SQL_Error 和 Last_SQL_Errno

-- 常见原因：
-- 1. 主键冲突
-- 2. 数据不一致
-- 3. 违反约束

-- 解决方法 1：跳过错误
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

-- 解决方法 2：跳过指定 GTID（GTID 模式）
SET GTID_NEXT = 'gtid_value';
BEGIN;
COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START SLAVE;

3. 复制延迟​

-- 查看延迟
SHOW SLAVE STATUS\G
-- 查看 Seconds_Behind_Master

-- 常见原因：
-- 1. 从库硬件性能差
-- 2. 大事务
-- 3. 单线程复制

-- 优化方法：
-- 1. 启用多线程复制
STOP SLAVE;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 4;
START SLAVE;

-- 2. 使用并行复制（MySQL 5.7+）
-- 在配置文件中设置
[mysqld]
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4

重置复制​

-- 停止复制
STOP SLAVE;

-- 重置复制位置（不清空 relay log）
RESET SLAVE;

-- 重置并清空 relay log
RESET SLAVE ALL;

-- 重新配置复制
CHANGE MASTER TO ...;
START SLAVE;

主从切换​

计划内切换​

-- 1. 确保从库已同步
-- 在从库执行
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master 应为 0

-- 2. 停止从库复制
STOP SLAVE;
RESET SLAVE ALL;

-- 3. 修改从库为读写模式
SET GLOBAL read_only = 0;
SET GLOBAL super_read_only = 0;

-- 4. 修改应用连接到新主库

-- 5. 将旧主库配置为新从库（可选）
-- 在旧主库执行
CHANGE MASTER TO ...;
START SLAVE;

故障切换​

-- 1. 确认主库不可用

-- 2. 选择最新的从库作为新主库
-- 检查各从库的 Relay_Master_Log_File 和 Exec_Master_Log_Pos
SHOW SLAVE STATUS\G

-- 3. 停止新主库的复制
STOP SLAVE;
RESET SLAVE ALL;

-- 4. 设置新主库为读写模式
SET GLOBAL read_only = 0;
SET GLOBAL super_read_only = 0;

-- 5. 配置其他从库指向新主库
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST = '新主库IP', ...;
START SLAVE;

读写分离​

应用层实现​

// Java 示例：使用不同数据源
public class DataSourceRouter {
    private DataSource masterDataSource;
    private DataSource slaveDataSource;
    
    public DataSource getDataSource(boolean isWrite) {
        return isWrite ? masterDataSource : slaveDataSource;
    }
}

中间件实现​

常用中间件：

中间件说明
MySQL RouterMySQL 官方路由器
ProxySQL高性能代理
MyCat国产数据库中间件
ShardingSphereApache 开源项目

ProxySQL 配置示例​

-- 添加主库
INSERT INTO mysql_servers (hostgroup_id, hostname, port) 
VALUES (10, '192.168.1.100', 3306);

-- 添加从库
INSERT INTO mysql_servers (hostgroup_id, hostname, port) 
VALUES (20, '192.168.1.101', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) 
VALUES (20, '192.168.1.102', 3306);

-- 配置路由规则
INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup) 
VALUES (1, '^SELECT', 20);
INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup) 
VALUES (2, '.*', 10);

-- 生效配置
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;

小结​

本章我们学习了：

  1. 复制原理：基于 binlog 的复制机制
  2. 复制类型：异步、半同步、GTID 复制
  3. 配置方法：基于位置和 GTID 的复制配置
  4. 复制管理：状态监控和问题处理
  5. 主从切换：计划内切换和故障切换
  6. 读写分离：应用层和中间件实现

练习​

  1. 配置一主一从的 GTID 复制
  2. 配置半同步复制并验证
  3. 模拟复制延迟并分析原因
  4. 练习主从切换操作

参考资源​