跳至主要內容

第22章：主从复制 ⭐⭐⭐⭐⭐ - MySQL教程 - jackssybin 的个人博客

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

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

MySQL教程 / 高可用架构

第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 主从复制原理 ⭐⭐⭐⭐⭐

三个线程：

  1. 主库：Binlog Dump线程

    • 读取binlog
    • 发送给从库

  2. 从库：I/O线程

    • 接收主库的binlog
    • 写入relay log（中继日志）

  3. 从库：SQL线程

    • 读取relay log
    • 执行SQL语句

复制流程：

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

详细步骤：

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

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'

常见原因：

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

解决方案：

-- 检查网络
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

常见原因：

  1. 主键冲突
  2. 从库被误写入数据
  3. 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自动切换

手动切换步骤：

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

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复制 ⭐⭐⭐⭐⭐
  • ✅ 主从延迟的原因和解决方案 ⭐⭐⭐⭐⭐
  • ✅ 主从故障处理
  • ✅ 主从切换（计划内和故障切换）

重点掌握：

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

面试重点：

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

下一章预告： 读写分离

练习题

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

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