MySQL主从复制

原创于 2025-08-26 19:03:50 发布 · 928 阅读

CC 4.0 BY-SA版权

文章标签：

MySQL主从复制（Master-Slave Replication）是一种数据同步机制，允许将一个MySQL数据库（主库）的数据变更自动复制到一个或多个MySQL数据库（从库）。主从复制是实现MySQL高可用、读写分离和负载均衡的基础，广泛应用于生产环境中。

一、主从复制的基本概念

1. 主从复制的作用

- 数据备份：从库作为主库的热备份，提高数据安全性

- 读写分离：主库负责写入操作，从库负责读取操作，提高系统吞吐量

- 负载均衡：将读请求分散到多个从库，减轻主库压力

- 高可用：主库故障时，可将从库提升为主库，减少 downtime

- 数据分析：从库可用于数据分析，不影响主库性能

2. 主从复制的类型

- 一主一从：最简单的架构，一个主库对应一个从库

- 一主多从：一个主库对应多个从库，适用于读请求较多的场景

- 级联复制：从库同时作为其他从库的主库，减轻主库的复制压力

- 双主复制：两个库互为主从，支持双向写入（需特殊处理冲突）

3. 主从复制的局限性

- 异步复制存在数据延迟，无法保证实时一致性

- 主库写入压力大时，复制延迟可能增加

- 自动故障转移需要额外工具支持

- 跨机房复制可能受网络带宽和延迟影响

二、主从复制的原理

1. 复制的三个核心步骤

① 主库将数据变更记录到二进制日志（Binary Log）

② 从库的IO线程从主库读取二进制日志，写入本地中继日志（Relay Log）

③ 从库的SQL线程读取中继日志，执行其中的SQL语句，同步数据变更

2. 涉及的线程

- 主库：Binlog Dump线程，负责向从库发送二进制日志内容

- 从库：IO线程，负责接收主库的二进制日志并写入中继日志

- 从库：SQL线程，负责执行中继日志中的SQL语句

3. 复制格式

主从复制基于二进制日志，支持三种复制格式：

① STATEMENT（基于语句）：

记录执行的SQL语句，日志体积小，但某些函数（如NOW()、UUID()）可能导致数据不一致。

② ROW（基于行）：

记录每行数据的变化，能保证数据一致性，但日志体积较大。MySQL 5.7+默认使用此格式。

③ MIXED（混合模式）：

默认使用STATEMENT格式，对可能导致不一致的操作自动切换为ROW格式。

三、主从复制的配置步骤

1. 准备工作

① 环境要求：

- 主从库MySQL版本保持一致或从库版本高于主库

- 主从库网络互通，能访问彼此的3306端口

- 主从库数据初始状态一致

② 服务器规划示例：

- 主库（Master）：192.168.1.100，MySQL 8.0

- 从库（Slave）：192.168.1.101，MySQL 8.0

2. 主库配置

（1）修改配置文件

[mysqld]
# 启用二进制日志
log_bin = /var/log/mysql/binlog

# 服务器唯一ID（1-2^32-1）
server-id = 1

# 二进制日志格式
binlog_format = ROW

# 同步的数据库（可选，不指定则同步所有数据库）
# binlog_do_db = sales

# 忽略同步的数据库（可选）
# binlog_ignore_db = mysql
# binlog_ignore_db = information_schema

# 二进制日志过期时间（天）
expire_logs_days = 7

# 每次事务提交立即同步到磁盘（安全性高）
sync_binlog = 1

（2）重启主库服务

systemctl restart mysql

（3）创建复制用户

-- 登录主库
mysql -u root -p

-- 创建复制专用用户
CREATE USER 'repl_user'@'192.168.1.101' IDENTIFIED BY 'Repl@123456';

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

-- 刷新权限
FLUSH PRIVILEGES;

（4）获取主库二进制日志信息

-- 锁定数据库，防止数据写入（只读）
FLUSH TABLES WITH READ LOCK;

-- 查看主库状态，记录File和Position的值
SHOW MASTER STATUS;

+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |      154 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

（5）备份主库数据（初始同步）

-- 另开一个终端，备份数据
mysqldump -u root -p --all-databases --routines --events --master-data=2 > master_backup.sql

-- 解锁主库
UNLOCK TABLES;

3. 从库配置

（1）修改配置文件

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

# 启用中继日志
relay_log = /var/log/mysql/relaylog

# 中继日志索引文件
relay_log_index = /var/log/mysql/relaylog.index

# 从库只读（可选，防止误写入）
read_only = 1

# 忽略复制错误（生产环境不推荐）
# slave_skip_errors = all

# 中继日志自动清理
relay_log_purge = 1

（2）重启从库服务

systemctl restart mysql

（3）恢复主库备份数据

-- 将主库备份文件复制到从库，然后恢复
mysql -u root -p < master_backup.sql

（4）配置主从连接信息

-- 登录从库
mysql -u root -p

-- 停止从库复制进程
STOP SLAVE;

-- 配置主库信息
CHANGE MASTER TO
MASTER_HOST = '192.168.1.100',
MASTER_USER = 'repl_user',
MASTER_PASSWORD = 'Repl@123456',
MASTER_LOG_FILE = 'binlog.000001',  -- 主库SHOW MASTER STATUS显示的File
MASTER_LOG_POS = 154;  -- 主库SHOW MASTER STATUS显示的Position

-- MySQL 8.0+使用 caching_sha2_password 认证时可能需要：
-- GET_MASTER_PUBLIC_KEY = 1

-- 启动从库复制进程
START SLAVE;

4. 验证复制状态

-- 在从库执行
SHOW SLAVE STATUS\G

-- 关键参数检查（需均为Yes）：
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes

-- 其他重要参数：
-- Seconds_Behind_Master: 0  -- 复制延迟（0表示无延迟）
-- Master_Log_File: 当前读取的主库日志文件
-- Read_Master_Log_Pos: 当前读取的主库日志位置
-- Relay_Master_Log_File: 当前执行的中继日志文件
-- Exec_Master_Log_Pos: 当前执行的中继日志位置

四、主从复制的管理与维护

1. 常用管理命令

-- 停止从库复制
STOP SLAVE;

-- 启动从库复制
START SLAVE;

-- 重置从库配置（删除复制信息，保留中继日志）
RESET SLAVE;

-- 完全重置从库（删除复制信息和中继日志）
RESET SLAVE ALL;  -- MySQL 5.5.16+

-- 临时停止IO线程
STOP SLAVE IO_THREAD;

-- 临时启动IO线程
START SLAVE IO_THREAD;

-- 临时停止SQL线程
STOP SLAVE SQL_THREAD;

-- 临时启动SQL线程
START SLAVE SQL_THREAD;

2. 增加新的从库

① 选择一个已有的从库作为数据源，避免影响主库

② 从现有从库备份数据，获取其复制位置信息

③ 新从库恢复备份，配置复制指向主库或级联复制指向现有从库

3. 主从切换（主库故障时）

① 手动切换步骤：

-- 1. 确保从库已同步所有数据
STOP SLAVE IO_THREAD;
SHOW PROCESSLIST;  -- 等待SQL线程执行完毕

-- 2. 将从库提升为主库
RESET SLAVE ALL;

-- 3. 配置应用程序连接新主库
-- 4. 其他从库重新指向新主库
CHANGE MASTER TO
MASTER_HOST = '新主库IP',
...;

② 自动切换：使用工具如MHA（Master High Availability）、Orchestrator等实现自动故障转移

4. 复制过滤

通过配置只复制特定数据库或表，减少复制数据量：

① 主库过滤（只记录特定数据库到binlog）：

[mysqld]
# 只记录sales数据库
binlog_do_db = sales

# 忽略mysql数据库
binlog_ignore_db = mysql

② 从库过滤（只应用特定数据库的日志）：

[mysqld]
# 只应用sales数据库
replicate_do_db = sales

# 忽略mysql数据库
replicate_ignore_db = mysql

# 只应用特定表
replicate_do_table = sales.orders

# 忽略特定表
replicate_ignore_table = sales.logs

# 使用通配符匹配表
replicate_wild_do_table = sales.ord%
replicate_wild_ignore_table = sales.%_log

五、主从复制常见问题与解决方案

1. 复制延迟

表现：Seconds_Behind_Master值持续增大

解决方案：

- 优化主库写入性能，减少大事务

- 升级从库硬件，提高SQL线程执行速度

- 使用多线程复制（MySQL 5.6+）

[mysqld]
# 启用多线程复制
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4  # 并行线程数，根据CPU核心数调整

- 减少从库压力（关闭不必要的日志、避免在从库执行大查询）

2. 复制中断（Slave_IO_Running或Slave_SQL_Running为No）

① IO线程中断：

常见原因：网络问题、主库binlog文件丢失、复制用户权限问题

解决方案：

-- 检查错误日志
SHOW GLOBAL VARIABLES LIKE 'log_error';

-- 重新配置主从连接
STOP SLAVE;
CHANGE MASTER TO ...;  -- 重新指定正确的MASTER_LOG_FILE和MASTER_LOG_POS
START SLAVE;

② SQL线程中断：

常见原因：主从数据不一致、从库表结构与主库不同、权限问题

解决方案：

-- 查看错误信息
SHOW SLAVE STATUS\G  -- 查看Last_Error字段

-- 方法1：跳过错误（临时应急，不推荐）
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

-- 方法2：重新同步数据（推荐）
STOP SLAVE;
-- 从主库重新备份并恢复数据
START SLAVE;

3. 主从数据不一致

检测工具：

- pt-table-checksum（Percona Toolkit）：检查主从数据一致性

pt-table-checksum --host=主库IP --user=root --password=密码 --databases=sales

- pt-table-sync：修复数据不一致

pt-table-sync --execute --sync-to-master 从库IP --user=root --password=密码

4. 主库binlog日志过大

解决方案：

-- 配置自动过期
expire_logs_days = 7  # 保留7天日志

-- 手动删除
PURGE BINARY LOGS BEFORE '2023-10-01 00:00:00';

-- 限制单个binlog大小
max_binlog_size = 100M

六、高级复制特性

1. GTID复制（MySQL 5.6+）

GTID（Global Transaction ID）是全局唯一的事务ID，简化复制配置和故障转移。

配置方法：

[mysqld]
# 主库和从库都需配置
server-id = 1  # 主库1，从库2
gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = /var/log/mysql/binlog
binlog_format = ROW

# 从库额外配置
relay_log = /var/log/mysql/relaylog

配置复制：

-- 从库执行
STOP SLAVE;
RESET SLAVE ALL;

CHANGE MASTER TO
MASTER_HOST = '192.168.1.100',
MASTER_USER = 'repl_user',
MASTER_PASSWORD = 'Repl@123456',
MASTER_AUTO_POSITION = 1;  # 使用GTID自动定位

START SLAVE;

2. 半同步复制（Semi-sync Replication）

半同步复制确保主库事务提交前，至少有一个从库已接收并写入中继日志，提高数据安全性。

配置方法：

-- 主库安装插件
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;  # 超时时间（毫秒）

-- 从库启用
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- 重启从库IO线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

3. 多源复制（MySQL 5.7+）

多源复制允许一个从库从多个主库同步数据，适用于数据汇总场景。

配置方法：

-- 从库配置多个主库
CHANGE MASTER TO
MASTER_HOST = '192.168.1.100',
MASTER_USER = 'repl_user1',
MASTER_PASSWORD = 'Pass123',
MASTER_LOG_FILE = 'binlog.000001',
MASTER_LOG_POS = 154
FOR CHANNEL 'master1';  -- 指定通道名

CHANGE MASTER TO
MASTER_HOST = '192.168.1.102',
MASTER_USER = 'repl_user2',
MASTER_PASSWORD = 'Pass456',
MASTER_LOG_FILE = 'binlog.000001',
MASTER_LOG_POS = 154
FOR CHANNEL 'master2';

-- 启动所有通道的复制
START SLAVE FOR CHANNEL 'master1';
START SLAVE FOR CHANNEL 'master2';

-- 查看特定通道状态
SHOW SLAVE STATUS FOR CHANNEL 'master1'\G