MySQL主从复制-构建高可用数据库架构

一、什么是主从复制？

主从复制是指将主数据库的数据同步到一个或多个从数据库，实现数据备份、负载均衡、高可用等功能。

1.1 主从复制原理

┌─────────────────────────────────────────────────────────────────┐
│                      主从复制原理                                 │
├─────────────────────────────────────────────────────────────────┤
│                                                                  │
│                      主库（Master）                               │
│                       ┌─────────┐                                │
│                       │  写入   │                                │
│                       └────┬────┘                                │
│                            │                                     │
│                     ┌──────┴──────┐                              │
│                     │ Binary Log  │                              │
│                     └──────┬──────┘                              │
│                            │                                     │
│                            │ 同步                                 │
│                            ▼                                     │
│  ┌───────────────────────────────────────────┐                  │
│  │              IO Thread                     │                  │
│  └───────────────────────────────────────────┘                  │
│                            │                                     │
│                            ▼                                     │
│  ┌───────────────────────────────────────────┐                  │
│  │            Relay Log（中继日志）            │                  │
│  └───────────────────────────────────────────┘                  │
│                            │                                     │
│                            ▼                                     │
│                     ┌────────────┐                              │
│                     │ 从库（Slave）│                             │
│                     └────────────┘                              │
│                       ┌─────────┐                                │
│                       │ SQL执行 │                                │
│                       └────┬────┘                                │
│                            │                                     │
│                       ┌─────────┐                                │
│                       │  数据   │                                │
│                       └─────────┘                                │
│                                                                  │
└─────────────────────────────────────────────────────────────────┘

1.2 主从复制优点

优点	说明
数据备份	实时备份，防止数据丢失
读写分离	主库写，从库读，分担压力
负载均衡	多从库分散查询压力
高可用	主库故障时可切换从库
数据分析	从库可进行数据分析，不影响主库

二、主从复制架构

2.1 一主一从

┌─────────────┐         同步         ┌─────────────┐
│   主库      │ ───────────────────→ │   从库      │
│  (Master)  │                       │  (Slave)   │
│            │                       │             │
│  写入/读取 │                       │   读取      │
└─────────────┘                       └─────────────┘

2.2 一主多从

                    ┌─────────────┐
                    │   从库1     │
┌─────────────┐     ├─────────────┤     ┌─────────────┐
│   主库      │ ───→│   从库2     │ ───→│   从库3     │
│  (Master)  │     ├─────────────┤     │  (Slave)   │
│            │     │   从库N     │     └─────────────┘
│  写入/读取 │     └─────────────┘
└─────────────┘

2.3 双主复制

┌─────────────┐     双向同步      ┌─────────────┐
│   主库A    │ ←────────────────→ │   主库B    │
│            │                     │             │
│  写入/读取 │                     │  写入/读取 │
└─────────────┘                     └─────────────┘

2.4 多主一从

┌─────────────┐
│   主库1    │ ─┐
└─────────────┘  │
┌─────────────┐  │     ┌─────────────┐
│   主库2    │ ─┼────→│   从库      │
└─────────────┘  │     │  (Slave)   │
┌─────────────┐  │     └─────────────┘
│   主库N    │ ─┘
└─────────────┘

三、主从复制配置

3.1 环境说明

角色	IP	端口
Master	192.168.1.100	3306
Slave	192.168.1.101	3306

3.2 Master配置

# 1. 修改配置文件
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

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

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

# 同步的数据库（可选）
binlog_do_db = mydb

# 不同步的数据库（可选）
binlog_ignore_db = mysql

# 日志格式（ROW/statement/mixed）
binlog_format = ROW

# 开启GTID（推荐）
gtid_mode = ON
enforce_gtid_consistency = ON

# 2. 重启MySQL
sudo systemctl restart mysql

3.3 创建复制用户

-- 在Master上执行
-- 创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';

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

-- 刷新权限
FLUSH PRIVILEGES;

-- 查看Master状态
SHOW MASTER STATUS;
-- 记录 File 和 Position
-- File: mysql-bin.000001
-- Position: 1234

3.4 Slave配置

# 1. 修改配置文件
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
# 服务器ID（唯一，不能与Master相同）
server-id = 2

# 启用中继日志
relay_log = /var/log/mysql/mysql-relay-bin

# 开启GTID（与Master保持一致）
gtid_mode = ON
enforce_gtid_consistency = ON

# 只读（可选）
read_only = ON
super_read_only = ON

# 2. 重启MySQL
sudo systemctl restart mysql

3.5 启动复制

-- 在Slave上执行
-- 方式1：基于Position
CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl_password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=1234;

-- 方式2：基于GTID（推荐）
CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl_password',
    MASTER_AUTO_POSITION=1;

-- 启动复制
START SLAVE;

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

-- 停止复制
STOP SLAVE;

-- 重置复制
RESET SLAVE ALL;

四、复制原理详解

4.1 复制过程

┌─────────────────────────────────────────────────────────────────┐
│                      完整复制流程                                 │
├─────────────────────────────────────────────────────────────────┤
│                                                                  │
│  1. Master执行SQL，写入数据                                      │
│     ↓                                                            │
│  2. 记录Binary Log（记录位置：1234）                            │
│     ↓                                                            │
│  3. Slave IO Thread连接Master                                    │
│     ↓                                                            │
│  4. Master发送Binary Log给Slave                                  │
│     ↓                                                            │
│  5. Slave写入Relay Log                                          │
│     ↓                                                            │
│  6. Slave SQL Thread读取Relay Log                               │
│     ↓                                                            │
│  7. 执行SQL，完成数据同步                                        │
│                                                                  │
└─────────────────────────────────────────────────────────────────┘

4.2 复制类型

类型	说明	优点	缺点
异步复制	Master不等待Slave完成	性能好	可能丢失数据
半同步复制	Master等待至少一个Slave	数据安全	有延迟
全同步复制	Master等待所有Slave	数据最安全	性能差

4.3 配置半同步复制

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

-- 启用半同步
SET GLOBAL rpl_semi_sync_master_enabled = ON;

-- 超时时间
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 = ON;

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

五、读写分离

5.1 读写分离原理

┌─────────────┐
│   客户端    │
└──────┬──────┘
       │
       ├──────────────────┬──────────────────┐
       ▼                  ▼                  ▼
┌─────────────┐    ┌─────────────┐    ┌─────────────┐
│   主库      │    │   从库1    │    │   从库2    │
│  (写入)     │    │   (读取)   │    │   (读取)   │
└─────────────┘    └─────────────┘    └─────────────┘

5.2 实现方式

-- 方式1：程序层面实现
-- 在代码中区分读写操作
-- 写入操作 -> Master
-- 读取操作 -> Slave

-- 方式2：中间件（如MySQL Router、Atlas、ShardingSphere）
-- 自动实现读写分离

-- 方式3：MySQL Connector
-- 配置Connector自动分流

5.3 示例代码

# Python读写分离示例
import pymysql

# 主库连接（写入）
master_conn = pymysql.connect(
    host='192.168.1.100',
    port=3306,
    user='app_user',
    password='password',
    database='mydb'
)

# 从库连接（读取）
slave_conn = pymysql.connect(
    host='192.168.1.101',
    port=3306,
    user='app_user',
    password='password',
    database='mydb'
)

# 写入操作 -> 主库
def write_data(sql, params):
    with master_conn.cursor() as cursor:
        cursor.execute(sql, params)
    master_conn.commit()

# 读取操作 -> 从库
def read_data(sql, params=None):
    with slave_conn.cursor() as cursor:
        cursor.execute(sql, params or [])
        return cursor.fetchall()

六、延迟复制

6.1 延迟复制配置

-- 设置延迟（秒）
CHANGE MASTER TO MASTER_DELAY = 3600;  # 延迟1小时

-- 查看延迟时间
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master: 3600

6.2 使用场景

场景	说明
误操作恢复	延迟复制，可从从库恢复误删数据
备份	从延迟从库备份，不影响主库
测试	在从库测试，不影响业务

七、GTID复制

7.1 什么是GTID？

GTID（Global Transaction ID）是全局事务ID，每个事务有一个唯一标识。

1 2	`格式：UUID:事务编号示例：a1b2c3d4-e5f6-7890-abcd-ef1234567890:1`

7.2 GTID优势

优势	说明
自动定位	不需要指定binlog文件和位置
故障恢复	更容易确定同步位置
一致性	保证事务不重复执行

7.3 GTID配置

[mysqld]
# 开启GTID
gtid_mode = ON
enforce_gtid_consistency = ON

7.4 GTID运维

-- 查看GTID状态
SHOW MASTER STATUS\G
SHOW SLAVE STATUS\G

-- 跳过错误事务
SET GTID_NEXT='a1b2c3d4-e5f6-7890-abcd-ef1234567890:1';
BEGIN;
COMMIT;
SET GTID_NEXT='AUTOMATIC';

-- 清空从库
RESET SLAVE ALL;

-- 重新建立复制
CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl_password',
    MASTER_AUTO_POSITION=1;

八、故障处理

8.1 常见错误

-- 1. 服务器ID冲突
-- 错误：Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids
-- 解决：修改server-id

-- 2. 连接失败
-- 错误：Can't connect to MySQL server
-- 解决：检查网络、防火墙

-- 3. 权限问题
-- 错误：Access denied for user 'repl'
-- 解决：检查repl用户权限

-- 4. 数据不一致
-- 错误：Relay log corruption
-- 解决：重新同步数据

8.2 数据同步

-- 方法1：mysqldump导出导入
-- Master导出
mysqldump -uroot -p --all-databases --master-data > backup.sql

-- Slave导入
mysql -uroot -p < backup.sql

-- 方法2：xtrabackup
-- 热备份工具，支持增量备份

8.3 主库故障切换

-- 1. 停止主库写入
-- 2. 确认从库同步完成
SHOW SLAVE STATUS\G
-- 确保：Seconds_Behind_Master = 0

-- 3. 提升从库为主库
STOP SLAVE;
RESET MASTER;

-- 4. 配置其他从库指向新主库
CHANGE MASTER TO
    MASTER_HOST='192.168.1.101',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl_password',
    MASTER_AUTO_POSITION=1;

九、监控与维护

9.1 监控指标

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

-- 关键指标：
-- Slave_IO_Running: IO线程运行状态
-- Slave_SQL_Running: SQL线程运行状态
-- Seconds_Behind_Master: 延迟秒数
-- Last_Error: 最后错误

-- 查看Master状态
SHOW MASTER STATUS\G

-- 查看所有线程
SHOW PROCESSLIST;

9.2 常用命令

-- 启动复制
START SLAVE;

-- 停止复制
STOP SLAVE;

-- 重置复制
RESET SLAVE;

-- 查看复制链路
SHOW SLAVE HOSTS;

-- 跳过错误
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

十、实战案例

10.1 Docker搭建主从

# 创建Master容器
docker run -d \
  --name mysql-master \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=root123 \
  -v ~/mysql/master:/var/lib/mysql \
  -e server-id=1 \
  -e log-bin=mysql-bin \
  mysql:8

# 创建Slave容器
docker run -d \
  --name mysql-slave \
  -p 3307:3306 \
  -e MYSQL_ROOT_PASSWORD=root123 \
  -v ~/mysql/slave:/var/lib/mysql \
  -e server-id=2 \
  -e relay-log=mysql-relay-bin \
  mysql:8

10.2 配置多主一从

-- Slave配置多个Master
-- MySQL 8.0.19+ 支持多源复制

CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl_password',
    MASTER_AUTO_POSITION=1,
    GET_MASTER_PUBLIC_KEY=1
FOR CHANNEL 'master-1';

CHANGE MASTER TO
    MASTER_HOST='192.168.1.101',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl_password',
    MASTER_AUTO_POSITION=1,
    GET_MASTER_PUBLIC_KEY=1
FOR CHANNEL 'master-2';

START SLAVE FOR CHANNEL 'master-1';
START SLAVE FOR CHANNEL 'master-2';

十一、常见问题

11.1 延迟太高

-- 原因：
-- 1. 从库硬件差
-- 2. 网络延迟
-- 3. 大事务
-- 4. 从库压力大

-- 解决：
-- 1. 优化从库硬件
-- 2. 减小事务大小
-- 3. 减少从库查询压力
-- 4. 使用并行复制
SET GLOBAL slave_parallel_workers = 4;

11.2 数据不一致

-- 原因：
-- 1. 强制重启
-- 2. 大事务中断
-- 3. 复制错误

-- 解决：
-- 1. 重新同步数据
-- 2. 使用pt-table-checksum检查
-- 3. 使用pt-table-sync同步

参考资料

持续更新中…欢迎收藏！

#MySQL #主从复制 #读写分离 #GTID #高可用 #教程

数据库

#MySQL #主从复制 #读写分离 #高可用 #集群

MySQL主从复制-构建高可用数据库架构

https://r0f2.my/post/16-mysql-master-slave-replication/

作者

发布于

2026年2月14日

许可协议

MySQL备份与恢复-数据安全的最后防线上一篇

MySQL事务与锁-深入理解ACID 下一篇