怎么搭建MySQL主从复制经典架构

发布时间：2021-09-16 16:01:19 作者：chen
来源：亿速云阅读：165

# 怎么搭建MySQL主从复制经典架构

## 一、主从复制核心概念

### 1.1 什么是主从复制
MySQL主从复制（Master-Slave Replication）是指将一个MySQL数据库服务器（主库）的数据复制到一个或多个MySQL服务器（从库）的过程。主库处理所有写操作（INSERT/UPDATE/DELETE），从库通过复制线程异步获取主库的变更。

### 1.2 典型应用场景
- **读写分离**：主库写，从库读
- **数据备份**：从库作为热备份
- **高可用基础**：故障转移的基础架构
- **数据分析**：在从库执行耗时查询

## 二、环境准备

### 2.1 服务器规划
| 角色   | IP地址       | 版本要求       |
|--------|-------------|----------------|
| Master | 192.168.1.1 | MySQL 5.7+     |
| Slave  | 192.168.1.2 | 与主库版本一致 |

### 2.2 必要配置检查
确保主从服务器：
1. 网络互通（建议内网）
2. 时间同步（NTP服务）
3. 防火墙开放3306端口
4. server_id 必须唯一

## 三、主库配置详解

### 3.1 修改my.cnf配置
```ini
[mysqld]
# 必须配置项
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL

# 推荐配置项
sync_binlog = 1
expire_logs_days = 7
binlog_group_commit_sync_delay = 100

3.2 创建复制账号

CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'Repl@1234';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;

3.3 获取主库状态

SHOW MASTER STATUS;

记录返回的File（如mysql-bin.000001）和Position（如154）值

四、从库配置步骤

4.1 修改my.cnf配置

[mysqld]
server-id = 2
relay_log = mysql-relay-bin
read_only = ON
log_slave_updates = ON
skip_slave_start = ON

4.2 建立复制连接

CHANGE MASTER TO
MASTER_HOST='192.168.1.1',
MASTER_USER='repl',
MASTER_PASSWORD='Repl@1234',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;

4.3 启动复制线程

START SLAVE;

五、验证与监控

5.1 检查复制状态

SHOW SLAVE STATUS\G

关键指标检查： - Slave_IO_Running: Yes - Slave_SQL_Running: Yes - Seconds_Behind_Master < 5

5.2 常用监控命令

-- 主库查看二进制日志
SHOW BINARY LOGS;

-- 从库查看复制错误
SHOW SLAVE ERRORS;

-- 性能监控
SELECT * FROM performance_schema.replication_applier_status_by_worker;

六、故障处理指南

6.1 常见问题解决

问题1：主键冲突

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

问题2：网络中断恢复

-- 主库重新获取位置
SHOW MASTER STATUS;

-- 从库重新配置
STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
START SLAVE;

6.2 数据一致性校验

使用pt-table-checksum工具：

pt-table-checksum --nocheck-replication-filters --replicate=test.checksums h=192.168.1.1

七、架构优化建议

7.1 半同步复制配置

# 主库添加
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

7.2 多线程复制

slave_parallel_workers = 4
slave_parallel_type = LOGICAL_CLOCK

八、总结

MySQL主从复制搭建的关键步骤： 1. 正确配置server_id和二进制日志 2. 创建专用复制账号 3. 确保初始数据一致性 4. 准确记录binlog位置 5. 持续监控复制延迟

建议在生产环境部署后： - 设置定期备份验证 - 配置监控告警 - 定期进行故障演练

注意：本文基于MySQL 5.7版本编写，不同版本可能存在参数差异。建议在测试环境验证后再部署到生产环境。 “`

这篇文章共计约1250字，采用Markdown格式编写，包含： 1. 层级分明的章节结构 2. 配置代码块和SQL示例 3. 关键注意事项提示 4. 实际运维中的实用技巧 5. 表格形式的参数说明