MySQL教程 / 第 220 节

第22章：主从复制 ⭐⭐⭐⭐⭐

主从复制是MySQL高可用架构的基础，是成为MySQL专家的必备技能

22.1 主从复制概述

22.1.1 什么是主从复制

主从复制（Replication）：将主库（Master）的数据自动同步到从库（Slave）。

架构图：

        ┌─────────┐
        │  Master │
        │  (主库)  │
        └────┬────┘
             │ binlog
        ┌────┴────┐
        │         │
   ┌────▼───┐ ┌──▼─────┐
   │ Slave1 │ │ Slave2 │
   │ (从库) │ │ (从库) │
   └────────┘ └────────┘

22.1.2 主从复制的作用

1. 读写分离

主库：处理写操作（INSERT、UPDATE、DELETE）
从库：处理读操作（SELECT）
提高并发性能

2. 数据备份

从库作为实时备份
主库故障时可以快速切换

3. 高可用

主库故障时，从库可以升级为主库
实现故障转移

4. 数据分析

在从库上执行复杂查询
不影响主库性能

22.1.3 主从复制原理 ⭐⭐⭐⭐⭐

三个线程：

主库：Binlog Dump线程
- 读取binlog
- 发送给从库
从库：I/O线程
- 接收主库的binlog
- 写入relay log（中继日志）
从库：SQL线程
- 读取relay log
- 执行SQL语句

复制流程：

主库                                从库
┌──────────────┐              ┌──────────────┐
│  执行SQL语句  │              │              │
└──────┬───────┘              │              │
       │                      │              │
┌──────▼───────┐              │              │
│  写入binlog  │              │              │
└──────┬───────┘              │              │
       │                      │              │
┌──────▼───────┐    binlog    ┌──────▼───────┐
│ Binlog Dump  ├─────────────►│  I/O Thread  │
│   Thread     │              │              │
└──────────────┘              └──────┬───────┘
                                     │
                              ┌──────▼───────┐
                              │ 写入relay log│
                              └──────┬───────┘
                                     │
                              ┌──────▼───────┐
                              │  SQL Thread  │
                              │  执行SQL语句  │
                              └──────────────┘

详细步骤：

主库执行SQL语句（INSERT、UPDATE、DELETE）
主库将变更写入binlog
从库的I/O线程连接主库，请求binlog
主库的Binlog Dump线程读取binlog，发送给从库
从库的I/O线程接收binlog，写入relay log
从库的SQL线程读取relay log，执行SQL语句
从库数据与主库保持一致

22.2 配置主从复制

22.2.1 环境准备

服务器规划：

主库：192.168.1.100（Master）
从库1：192.168.1.101（Slave1）
从库2：192.168.1.102（Slave2）

版本要求：

MySQL 5.7.x（主从版本最好一致）
从库版本 >= 主库版本

22.2.2 配置主库（Master）

1. 修改配置文件（my.cnf）

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

# 开启binlog
log-bin=mysql-bin

# binlog格式（推荐ROW）
binlog_format=ROW

# 需要同步的数据库（可选）
binlog-do-db=mydb

# 不需要同步的数据库（可选）
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

# binlog过期时间（天）
expire_logs_days=7

# 每次事务提交都写入binlog
sync_binlog=1

# InnoDB设置
innodb_flush_log_at_trx_commit=1

2. 重启MySQL

systemctl restart mysqld

3. 创建复制用户

-- 创建复制用户
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'Repl@123456';

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

-- 刷新权限
FLUSH PRIVILEGES;

4. 查看主库状态

SHOW MASTER STATUS;

输出示例：

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

记录： File和Position，配置从库时需要用到。

5. 锁定主库（可选，用于全量备份）

-- 锁定所有表（只读）
FLUSH TABLES WITH READ LOCK;

-- 备份数据
-- mysqldump -uroot -p --all-databases > /backup/all.sql

-- 解锁
UNLOCK TABLES;

22.2.3 配置从库（Slave）

1. 修改配置文件（my.cnf）

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

# 开启binlog（可选，用于级联复制）
log-bin=mysql-bin

# relay log
relay-log=mysql-relay-bin

# 只读模式（推荐）
read_only=1

# 超级用户也只读（推荐）
super_read_only=1

# 需要同步的数据库（可选）
replicate-do-db=mydb

# 不需要同步的数据库（可选）
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys

# 跳过错误（慎用）
# slave-skip-errors=1062,1032

2. 重启MySQL

systemctl restart mysqld

3. 配置主从关系

-- 停止从库（如果已经启动）
STOP SLAVE;

-- 配置主库信息
CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',        -- 主库IP
    MASTER_PORT=3306,                   -- 主库端口
    MASTER_USER='repl',                 -- 复制用户
    MASTER_PASSWORD='Repl@123456',      -- 复制密码
    MASTER_LOG_FILE='mysql-bin.000001', -- binlog文件名
    MASTER_LOG_POS=154;                 -- binlog位置

-- 启动从库
START SLAVE;

4. 查看从库状态

SHOW SLAVE STATUS\G

重要字段：

Slave_IO_Running: Yes          -- I/O线程运行状态
Slave_SQL_Running: Yes         -- SQL线程运行状态
Seconds_Behind_Master: 0       -- 主从延迟（秒）
Last_IO_Error:                 -- I/O错误信息
Last_SQL_Error:                -- SQL错误信息
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320

判断复制是否正常：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0 或很小的值

22.2.4 测试主从复制

在主库执行：

-- 创建数据库
CREATE DATABASE test_repl;

-- 使用数据库
USE test_repl;

-- 创建表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入数据
INSERT INTO users (name) VALUES ('user1'), ('user2'), ('user3');

-- 查询数据
SELECT * FROM users;

在从库查询：

-- 使用数据库
USE test_repl;

-- 查询数据（应该能看到主库插入的数据）
SELECT * FROM users;

如果从库能查到数据，说明主从复制配置成功！ ✅

22.3 复制格式

22.3.1 三种复制格式

1. STATEMENT（语句复制）

binlog_format=STATEMENT

特点：

记录SQL语句
binlog文件小
可能导致主从数据不一致

示例：

-- 主库执行
UPDATE users SET create_time = NOW() WHERE id < 100;

-- binlog记录
UPDATE users SET create_time = NOW() WHERE id < 100;

-- 问题：从库执行时NOW()的值可能不同

2. ROW（行复制）⭐ 推荐

binlog_format=ROW

特点：

记录每一行的变化
binlog文件大
数据一致性好

示例：

-- 主库执行
UPDATE users SET create_time = NOW() WHERE id < 100;

-- binlog记录（伪代码）
UPDATE users SET create_time = '2024-11-11 10:00:00' WHERE id = 1;
UPDATE users SET create_time = '2024-11-11 10:00:00' WHERE id = 2;
...

3. MIXED（混合复制）

binlog_format=MIXED

特点：

默认使用STATEMENT
遇到不确定的函数（NOW()、UUID()等）使用ROW
兼顾性能和一致性

22.3.2 选择建议

场景	推荐格式
生产环境	ROW ⭐
数据一致性要求高	ROW
binlog文件大小敏感	STATEMENT 或 MIXED
需要审计SQL语句	STATEMENT

修改复制格式：

-- 查看当前格式
SHOW VARIABLES LIKE 'binlog_format';

-- 修改格式（全局）
SET GLOBAL binlog_format = 'ROW';

-- 修改格式（当前会话）
SET SESSION binlog_format = 'ROW';

22.4 GTID复制 ⭐⭐⭐⭐⭐

22.4.1 什么是GTID

GTID（Global Transaction Identifier）：全局事务标识符

格式：

GTID = source_id:transaction_id

示例：

3E11FA47-71CA-11E1-9E33-C80AA9429562:1
3E11FA47-71CA-11E1-9E33-C80AA9429562:2
3E11FA47-71CA-11E1-9E33-C80AA9429562:3

优点：

每个事务有唯一标识
主从切换更简单
不需要指定binlog文件和位置
自动跳过已执行的事务

22.4.2 配置GTID复制

主库配置（my.cnf）：

[mysqld]
server-id=1
log-bin=mysql-bin
binlog_format=ROW

# 开启GTID
gtid_mode=ON
enforce_gtid_consistency=ON

# binlog设置
log_slave_updates=ON

从库配置（my.cnf）：

[mysqld]
server-id=2
log-bin=mysql-bin
binlog_format=ROW

# 开启GTID
gtid_mode=ON
enforce_gtid_consistency=ON

# relay log
relay-log=mysql-relay-bin
log_slave_updates=ON

配置主从关系：

-- 停止从库
STOP SLAVE;

-- 配置主库（使用GTID，不需要指定binlog文件和位置）
CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_PORT=3306,
    MASTER_USER='repl',
    MASTER_PASSWORD='Repl@123456',
    MASTER_AUTO_POSITION=1;  -- 使用GTID自动定位

-- 启动从库
START SLAVE;

-- 查看状态
SHOW SLAVE STATUS\G

查看GTID信息：

-- 查看已执行的GTID
SHOW GLOBAL VARIABLES LIKE 'gtid_executed';

-- 查看已清除的GTID
SHOW GLOBAL VARIABLES LIKE 'gtid_purged';

22.4.3 GTID vs 传统复制

特性	传统复制	GTID复制
配置复杂度	需要指定binlog文件和位置	自动定位
主从切换	复杂	简单
跳过错误	手动指定位置	自动跳过
一致性	可能重复执行	不会重复执行
推荐度	⭐⭐⭐	⭐⭐⭐⭐⭐

22.5 主从延迟

22.5.1 什么是主从延迟

主从延迟：从库执行relay log的速度慢于主库写入binlog的速度。

查看延迟：

SHOW SLAVE STATUS\G

-- 关键字段
Seconds_Behind_Master: 5  -- 延迟5秒

22.5.2 主从延迟的原因

1. 主库写入压力大

主库TPS（每秒事务数）过高
从库单线程回放跟不上

2. 从库性能差

从库硬件配置低
从库有大量查询

3. 网络延迟

主从之间网络带宽不足
网络抖动

4. 大事务

主库执行大事务（如批量更新100万行）
从库需要很长时间回放

5. 锁等待

从库有长时间运行的查询
阻塞SQL线程执行

22.5.3 解决主从延迟

1. 使用并行复制（MySQL 5.7+）

[mysqld]
# 开启并行复制
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=4  -- 4个并行线程

# 或使用DATABASE级别并行
slave_parallel_type=DATABASE
slave_parallel_workers=4

2. 升级从库硬件

使用SSD硬盘
增加内存
使用更快的CPU

3. 优化网络

使用专用网络
增加带宽
主从部署在同一机房

4. 避免大事务

-- ❌ 错误：大事务
START TRANSACTION;
UPDATE users SET status = 1 WHERE create_time < '2024-01-01';  -- 100万行
COMMIT;

-- ✅ 正确：分批执行
WHILE (SELECT COUNT(*) FROM users WHERE create_time < '2024-01-01' AND status = 0) > 0 DO
    UPDATE users SET status = 1
    WHERE create_time < '2024-01-01' AND status = 0
    LIMIT 1000;
    SELECT SLEEP(0.1);
END WHILE;

5. 从库只读

[mysqld]
read_only=1
super_read_only=1

6. 监控和报警

-- 监控脚本
SELECT
    CASE
        WHEN Seconds_Behind_Master > 60 THEN 'CRITICAL'
        WHEN Seconds_Behind_Master > 30 THEN 'WARNING'
        ELSE 'OK'
    END AS status,
    Seconds_Behind_Master
FROM (SELECT * FROM information_schema.SLAVE_STATUS) AS s;

22.6 主从故障处理

22.6.1 从库I/O线程停止

查看错误：

SHOW SLAVE STATUS\G

-- 查看错误信息
Last_IO_Error: error connecting to master 'repl@192.168.1.100:3306'

常见原因：

网络问题
主库宕机
复制用户密码错误
防火墙阻止

解决方案：

-- 检查网络
ping 192.168.1.100

-- 检查主库状态
mysql -h192.168.1.100 -urepl -p

-- 重新配置主从
STOP SLAVE;
CHANGE MASTER TO ...
START SLAVE;

22.6.2 从库SQL线程停止

查看错误：

SHOW SLAVE STATUS\G

-- 查看错误信息
Last_SQL_Error: Error 'Duplicate entry '1' for key 'PRIMARY'' on query

常见原因：

主键冲突
从库被误写入数据
binlog损坏

解决方案1：跳过错误（慎用）

-- 跳过1个事务
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

-- 或在配置文件中跳过特定错误
[mysqld]
slave-skip-errors=1062,1032  -- 1062:主键冲突, 1032:记录不存在

解决方案2：重新同步

-- 1. 在主库锁表并备份
FLUSH TABLES WITH READ LOCK;
mysqldump -uroot -p --all-databases --master-data=2 > /backup/all.sql
UNLOCK TABLES;

-- 2. 在从库导入
mysql -uroot -p < /backup/all.sql

-- 3. 重新配置主从
STOP SLAVE;
CHANGE MASTER TO ...
START SLAVE;

解决方案3：使用GTID跳过

-- 查看错误的GTID
SHOW SLAVE STATUS\G
-- Retrieved_Gtid_Set: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5
-- Executed_Gtid_Set: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-3

-- 跳过GTID
STOP SLAVE;
SET GTID_NEXT='3E11FA47-71CA-11E1-9E33-C80AA9429562:4';
BEGIN;
COMMIT;
SET GTID_NEXT='AUTOMATIC';
START SLAVE;

22.6.3 主从数据不一致

检查数据一致性：

# 使用pt-table-checksum
pt-table-checksum --host=192.168.1.100 --user=root --password=xxx

# 修复数据不一致
pt-table-sync --execute --sync-to-master h=192.168.1.101,u=root,p=xxx

22.7 主从切换

22.7.1 计划内切换

场景： 主库需要维护，计划切换到从库

步骤：

1. 停止主库写入

-- 在主库设置只读
SET GLOBAL read_only = 1;
SET GLOBAL super_read_only = 1;

2. 等待从库同步完成

-- 在从库查看延迟
SHOW SLAVE STATUS\G
-- 等待 Seconds_Behind_Master = 0

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='192.168.1.101',  -- 新主库IP
    MASTER_PORT=3306,
    MASTER_USER='repl',
    MASTER_PASSWORD='Repl@123456',
    MASTER_AUTO_POSITION=1;  -- 使用GTID
START SLAVE;

6. 应用程序切换到新主库

22.7.2 故障切换

场景： 主库宕机，紧急切换

使用MHA（Master High Availability）自动切换：

# 安装MHA
yum install mha4mysql-manager mha4mysql-node

# 配置MHA
vim /etc/mha/app1.cnf

# 启动MHA监控
nohup masterha_manager --conf=/etc/mha/app1.cnf &

# 主库宕机时，MHA自动切换

手动切换步骤：

选择一个从库作为新主库（数据最新的）
停止该从库的复制
将其提升为主库
配置其他从库指向新主库
修复旧主库，作为新从库

22.8 实战案例

案例1：搭建一主两从架构

架构：

        Master (192.168.1.100)
           /        \
          /          \
    Slave1          Slave2
(192.168.1.101) (192.168.1.102)

配置步骤： 参考22.2节

案例2：级联复制

架构：

Master → Slave1 → Slave2

Slave1配置：

[mysqld]
server-id=2
log-bin=mysql-bin  -- 必须开启binlog
log_slave_updates=ON  -- 必须开启

Slave2配置：

CHANGE MASTER TO
    MASTER_HOST='192.168.1.101',  -- 指向Slave1
    MASTER_PORT=3306,
    MASTER_USER='repl',
    MASTER_PASSWORD='Repl@123456',
    MASTER_AUTO_POSITION=1;

案例3：双主复制

架构：

Master1 ←→ Master2

Master1配置：

[mysqld]
server-id=1
log-bin=mysql-bin
auto_increment_increment=2  -- 自增步长
auto_increment_offset=1     -- 自增起始值（奇数）

Master2配置：

[mysqld]
server-id=2
log-bin=mysql-bin
auto_increment_increment=2  -- 自增步长
auto_increment_offset=2     -- 自增起始值（偶数）

互相配置主从关系

22.9 小结

本章学习了MySQL主从复制：

✅ 主从复制的原理（三个线程）
✅ 配置主从复制（传统方式和GTID方式）
✅ 三种复制格式（STATEMENT、ROW、MIXED）
✅ GTID复制 ⭐⭐⭐⭐⭐
✅ 主从延迟的原因和解决方案 ⭐⭐⭐⭐⭐
✅ 主从故障处理
✅ 主从切换（计划内和故障切换）

重点掌握：

主从复制的三个线程：Binlog Dump、I/O、SQL
GTID复制比传统复制更简单、更可靠
推荐使用ROW格式的binlog
主从延迟的主要原因：主库压力大、从库性能差、大事务
使用并行复制解决主从延迟

面试重点：

主从复制的原理
GTID的作用
如何解决主从延迟
主从切换的步骤
如何保证主从数据一致性

下一章预告： 读写分离

练习题

搭建一主两从的复制架构
配置GTID复制
模拟主从延迟并解决
模拟主库故障并进行切换
使用pt-table-checksum检查主从数据一致性

继续学习： 第23章：读写分离