MySQL主从复制技术详解

---

以下为MySQL主从复制的详细技术文档，涵盖定义、工作原理、配置步骤、案例及注意事项。

---

一、定义与概念

MySQL主从复制（Master-Slave Replication）是一种基于日志的数据同步机制，允许将一个MySQL数据库服务器（主库）的数据实时复制到一个或多个其他服务器（从库）。核心特点包括：

• 异步复制：默认模式下，主库不等待从库确认写入即提交事务。
• 单向同步：数据只能从主库流向从库。
• 多级扩展：支持链式复制（主→从→从）架构。
• 读写分离：主库处理写操作，从库承担读请求。

典型应用场景：

1. 数据冗余与灾备
2. 负载均衡（读写分离）
3. 实时数据分析
4. 零停机升级（先升级从库再切换）

---

二、工作原理与流程

1. 核心组件

组件	角色说明
Binary Log (binlog)	主库记录所有数据变更的日志文件
Relay Log	从库临时存储主库binlog的中转日志
I/O Thread	从库线程，负责拉取主库binlog
SQL Thread	从库线程，负责执行relay log中的操作
Binlog Dump Thread	主库线程，响应从库的日志请求

2. 工作流程图解

+-------------------+           +-------------------+
|     Master        |           |     Slave         |
|                   |           |                   |
|  Binlog Dump      | <-----+   |   I/O Thread      |
|  Thread (主线程)   |       |   | (拉取binlog)       |
|                   |       |   |                   |
|  Binary Log       |       +---+   Relay Log       |
| (记录数据变更)     |           | (中转日志)         |
|                   |           |                   |
|                   |           |   SQL Thread      |
|                   |           | (执行SQL操作)      |
+-------------------+           +-------------------+

3. 详细工作流程

1. 主库写入：事务提交时，主库将数据变更写入binlog
2. 从库请求：从库I/O线程连接主库，请求增量binlog
3. 日志传输：主库的Binlog Dump线程发送binlog事件
4. 日志中继：从库将接收的binlog写入relay log
5. 数据重放：从库SQL线程读取relay log并执行SQL操作
6. 状态同步：从库更新master.info记录同步位置点

---

三、配置步骤详解

1. 主库配置

步骤1：修改my.cnf

[mysqld]
server-id = 1             # 全局唯一ID（主从不能重复）
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW       # 推荐使用ROW格式
expire_logs_days = 7      # 自动清理7天前的日志

步骤2：创建复制专用账户

CREATE USER 'repl'@'%' IDENTIFIED BY 'SecurePass123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

步骤3：锁定数据库并获取状态

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

记录输出中的File和Position值（如mysql-bin.000001和107）

步骤4：数据备份

mysqldump -uroot -p --all-databases > full_backup.sql

步骤5：解锁主库

UNLOCK TABLES;

---

2. 从库配置

步骤1：修改my.cnf

[mysqld]
server-id = 2             # 不同于主库的ID
relay_log = /var/lib/mysql/relay-bin
read_only = 1            # 从库设为只读（super用户仍可写）

步骤2：导入初始数据

mysql -uroot -p < full_backup.sql

步骤3：启动复制链路

CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='repl',
  MASTER_PASSWORD='SecurePass123!',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=107;

START SLAVE;

步骤4：验证复制状态

SHOW SLAVE STATUS\G

确认以下参数：

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Seconds_Behind_Master: 0（表示无延迟）

---

四、配置案例演示

场景描述

• 主库IP：192.168.1.100
• 从库IP：192.168.1.101
• 同步所有数据库

具体操作记录

主库操作：

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      785 |              |                  |
+------------------+----------+--------------+------------------+

从库配置命令：

CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_PORT=3306,
  MASTER_USER='repl',
  MASTER_PASSWORD='SecurePass123!',
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=785;

测试数据同步：

1. 在主库创建测试表：

CREATE TABLE test.replication_test (
  id INT PRIMARY KEY,
  data VARCHAR(20)
);
INSERT INTO test.replication_test VALUES(1, 'sync_test');

2. 在从库查询验证：

SELECT * FROM test.replication_test;

---

五、注意事项与高级管理

1. 关键注意事项

• 网络稳定性：主从间需保持低延迟网络连接
• 版本兼容：建议主从使用相同大版本（如5.7.x）
• 数据一致性：确保初始备份数据完整
• 安全加固：
  • 使用SSL加密复制通道
  • 限制repl账户的访问IP
• 监控指标：
  • Seconds_Behind_Master（延迟秒数）
  • Slave_SQL_Running_State
  • Relay_Log_Space（中继日志大小）

2. 常见问题处理

问题1：主键冲突错误

Error 'Duplicate entry '100' for key 'PRIMARY'' on query

解决方案：

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

问题2：主从数据不一致

# 使用pt-table-checksum校验数据一致性
pt-table-checksum --user=root --password=xxx --host=master_ip --databases=db1

3. 高级配置选项

半同步复制（Semi-Sync）

# 主库配置
plugin_load = "rpl_semi_sync_master=semisync_master.so"
rpl_semi_sync_master_enabled = 1

# 从库配置
plugin_load = "rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_slave_enabled = 1

GTID复制模式

[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON

配置命令：

CHANGE MASTER TO 
  MASTER_AUTO_POSITION = 1;

---

六、监控与维护方案

1. 日常监控脚本

SHOW SLAVE STATUS\G
SHOW PROCESSLIST;
SHOW MASTER STATUS;

2. 自动告警设置

监控以下指标并设置阈值告警：

• 复制延迟 > 60秒
• SQL/IO线程状态异常
• 中继日志堆积超过10GB

3. 日志清理策略

# 自动清理过期binlog
PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;

# 重置中继日志
RESET SLAVE ALL;

---

七、架构扩展方案

1. 级联复制架构

Master → Slave1 → Slave2

2. 双主复制架构

# 两台服务器均配置：
auto_increment_increment = 2
auto_increment_offset = 1  # 另一台设为2

3. 多源复制（MySQL 5.7+）

CHANGE MASTER TO 
  MASTER_HOST='master1' FOR CHANNEL 'ch1';
CHANGE MASTER TO 
  MASTER_HOST='master2' FOR CHANNEL 'ch2';

---

通过以上配置和管理策略，MySQL主从复制可实现高效可靠的数据同步。实际生产环境中，建议结合监控系统和定期演练，确保复制架构的稳定性和快速故障恢复能力。