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 切换主从角色

  1. 停止主库写入
  2. 确保从库完全同步
  3. 将从库提升为新主库:
STOP SLAVE;
RESET MASTER;
SET GLOBAL read_only = OFF;

8.3 添加新从库

  1. 在主库备份:
mysqldump --master-data=2 -A > full_backup.sql
  1. 在新从库恢复备份
  2. 配置复制参数

9. 性能优化建议

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

10. 总结

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

注意:本文基于MySQL 5.78.0版本编写,不同版本配置可能略有差异。生产环境部署前请务必进行充分测试。 “`

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

推荐阅读:
  1. 配置mysql5.7主从复制
  2. MySQL GTID 主从复制的原理及配置

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql

上一篇:如何实现iptables防火墙只允许指定ip连接指定端口、访问指定网站

下一篇:如何解决ssh encountered 1 errors during the transfer错误

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》