mysql中如何配置主从复制

发布时间：2021-09-27 11:48:06 作者：小新
来源：亿速云阅读：273

# MySQL中如何配置主从复制

## 1. 主从复制概述

### 1.1 什么是主从复制
MySQL主从复制（Master-Slave Replication）是指将一个MySQL数据库服务器（主服务器）的数据复制到一个或多个MySQL数据库服务器（从服务器）的过程。主服务器负责处理写操作（INSERT、UPDATE、DELETE等），而从服务器通过复制机制同步主服务器的数据变更。

### 1.2 主从复制的优势
- **读写分离**：主库负责写操作，从库负责读操作，分担数据库负载
- **数据备份**：从库可作为主库的热备份
- **高可用性**：主库故障时可快速切换到从库
- **数据分析**：可在从库上执行分析查询而不影响主库性能
- **地理分布**：可将数据复制到不同地理位置的服务器

### 1.3 主从复制原理
MySQL主从复制基于以下三个线程实现：
1. **Binlog Dump线程**（主服务器）：当从服务器连接时创建，负责发送二进制日志事件
2. **I/O线程**（从服务器）：连接到主服务器，请求主服务器发送二进制日志
3. **SQL线程**（从服务器）：读取中继日志并执行其中的SQL语句

## 2. 配置前的准备工作

### 2.1 环境要求
- 至少两台MySQL服务器（可以同一机器不同端口）
- 网络互通，防火墙开放相应端口（默认3306）
- MySQL版本兼容（建议主从版本一致或从库版本高于主库）

### 2.2 检查主从服务器状态
```sql
-- 在主从服务器上分别执行
SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE 'log_bin';

2.3 确保主库已启用二进制日志

主库必须启用二进制日志（binary log），这是复制的基础：

-- 检查是否已启用
SHOW VARIABLES LIKE 'log_bin';

如果未启用，需要在my.cnf/my.ini配置文件中添加：

[mysqld]
log-bin=mysql-bin
server-id=1  # 主库server-id必须唯一

3. 主服务器配置

3.1 修改主库配置文件

编辑主库的my.cnf或my.ini文件，添加以下配置：

[mysqld]
# 必须配置
server-id = 1               # 唯一服务器ID
log-bin = mysql-bin         # 启用二进制日志
binlog_format = ROW         # 推荐使用ROW格式
binlog_row_image = FULL     # 记录完整的行数据

# 可选配置
binlog-do-db = db1          # 指定要复制的数据库（可选）
binlog-ignore-db = mysql    # 忽略系统库
sync_binlog = 1             # 每次事务提交都同步binlog
expire_logs_days = 7        # binlog保留天数

3.2 创建复制专用用户

在主库上创建一个用于复制的用户：

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

3.3 获取主库二进制日志位置

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

记录下File和Position的值（例如：mysql-bin.000001，154），稍后配置从库时需要。

3.4 解锁主库表

UNLOCK TABLES;

4. 从服务器配置

4.1 修改从库配置文件

编辑从库的my.cnf或my.ini文件：

[mysqld]
server-id = 2               # 必须唯一且不同于主库
relay-log = mysql-relay-bin # 中继日志
log_bin = mysql-bin         # 从库也可以启用binlog（可选）
read_only = ON              # 设置从库为只读（超级用户除外）

4.2 配置复制参数

在从库上执行以下命令：

CHANGE MASTER TO
MASTER_HOST='master_host_ip',
MASTER_USER='repl',
MASTER_PASSWORD='repl_password',
MASTER_LOG_FILE='mysql-bin.000001',  -- 主库SHOW MASTER STATUS获取
MASTER_LOG_POS=154;                  -- 主库SHOW MASTER STATUS获取

4.3 启动复制

START SLAVE;

4.4 检查复制状态

SHOW SLAVE STATUS\G

重点关注以下字段： - Slave_IO_Running: Yes - Slave_SQL_Running: Yes - Seconds_Behind_Master: 0（表示已完全同步）

5. 验证主从复制

5.1 在主库创建测试数据

CREATE DATABASE test_repl;
USE test_repl;
CREATE TABLE t1(id INT PRIMARY KEY, name VARCHAR(20));
INSERT INTO t1 VALUES(1, 'test1');

5.2 在从库检查数据

SELECT * FROM test_repl.t1;

5.3 监控复制延迟

SHOW SLAVE STATUS\G
-- 查看Seconds_Behind_Master值

6. 高级配置选项

6.1 半同步复制

确保至少一个从库接收到了事务：

-- 主库安装插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;

-- 从库安装插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

6.2 多线程复制

提高从库应用日志的效率：

STOP SLAVE;
SET GLOBAL slave_parallel_workers = 4;  # 根据CPU核心数设置
START SLAVE;

6.3 GTID复制

使用全局事务ID简化故障转移：

# 主从库配置文件中添加
gtid_mode = ON
enforce_gtid_consistency = ON

配置从库使用GTID：

STOP SLAVE;
CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
START SLAVE;

7. 常见问题排查

7.1 复制中断处理

当SHOW SLAVE STATUS显示错误时：

STOP SLAVE;
-- 根据错误信息处理
SET GLOBAL sql_slave_skip_counter = 1;  # 跳过错误（谨慎使用）
START SLAVE;

7.2 主从数据不一致

使用工具检查：

pt-table-checksum --replicate=test.checksums h=master_host
pt-table-sync --replicate=test.checksums h=master_host --sync-to-master

7.3 网络中断恢复

网络恢复后，从库会自动重连主库。长时间中断可能需要重新配置：

STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.00000X', MASTER_LOG_POS=XXX;
START SLAVE;

8. 维护与管理

8.1 监控复制状态

-- 查看从库状态
SHOW SLAVE STATUS\G

-- 查看主库binlog信息
SHOW MASTER STATUS;
SHOW BINARY LOGS;

8.2 切换主从角色

停止主库写入
确保从库完全同步
将从库提升为新主库：

STOP SLAVE;
RESET MASTER;
SET GLOBAL read_only = OFF;

8.3 添加新从库

在主库备份：

mysqldump --master-data=2 -A > full_backup.sql

在新从库恢复备份
配置复制参数

9. 性能优化建议

主库使用SSD存储binlog
适当增大binlog_cache_size
从库关闭不必要的日志（如慢查询日志）
使用slave_parallel_workers提高并行复制能力
定期清理过期binlog

10. 总结

MySQL主从复制是构建高可用数据库架构的基础。通过本文的详细配置步骤，您可以成功搭建主从复制环境。实际生产环境中，建议结合监控工具（如Prometheus+Granfa）实时监控复制状态，并考虑使用MHA或Orchestrator等工具实现自动故障转移。

注意：本文基于MySQL 5.⁷⁄₈.0版本编写，不同版本配置可能略有差异。生产环境部署前请务必进行充分测试。 “`

这篇文章大约4000字，涵盖了MySQL主从复制的完整配置流程，包括原理说明、详细配置步骤、验证方法、高级配置选项、问题排查和维护建议等内容。采用Markdown格式，结构清晰，适合作为技术文档使用。