mysql 主从配置2025-08-28

🎯 架构说明

Master（主库） → 写操作
   ↓
Slave1（从库1） ← 同步数据，只读
Slave2（从库2） ← 同步数据，只读

✅ 所有从库自动从主库同步 binlog，实现数据一致性。

🛠 环境准备

角色 IP 地址 server-id
Master 192.168.1.10 1
Slave1 192.168.1.11 2
Slave2 192.168.1.12 3

⚠️ 所有服务器需能互相通信，防火墙开放 3306 端口。

✅ 一、配置 Master（主库）

1. 编辑 MySQL 配置文件

# 文件路径：/etc/my.cnf 或 /etc/mysql/my.cnf
[mysqld]

# 唯一标识，主库为 1
server-id = 1

# 开启二进制日志（binlog），用于主从复制
log-bin = mysql-bin

# 设置 binlog 格式为 ROW（推荐，更安全）
# 可选：STATEMENT, ROW, MIXED
binlog-format = ROW

# 设置 binlog 过期时间（单位：天）
expire_logs_days = 7

# 单个 binlog 文件最大大小
max_binlog_size = 100M

# 每次事务提交都写入磁盘（保证数据不丢失）
sync-binlog = 1

# InnoDB 日志同步策略（与 sync-binlog 配合使用）
innodb_flush_log_at_trx_commit = 1

# 可选：限制只允许特定网段的从库连接
# bind-address = 192.168.1.10

2. 重启 MySQL 服务

systemctl restart mysql

3. 登录 MySQL，创建复制用户

-- 创建一个专用于主从复制的用户
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'repl123';

-- 授予复制权限（允许从库连接并读取 binlog）
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';

-- 刷新权限
FLUSH PRIVILEGES;

🔐 生产建议：使用强密码，限制 IP 范围。

4. 查看主库状态（记录 binlog 位置）

SHOW MASTER STATUS;

记下输出中的两个关键值（稍后从库配置需要）：

File: mysql-bin.000001
Position: 154

✅ 这两个值表示当前 binlog 的文件名和位置，从库将从这里开始同步。

✅ 二、配置 Slave1（从库1）

1. 编辑 MySQL 配置文件

# 文件路径：/etc/my.cnf 或 /etc/mysql/my.cnf
[mysqld]

# 唯一标识，不能与主库和其他从库重复
server-id = 2

# 开启中继日志（relay log），用于存储从主库读取的 binlog）
relay-log = mysql-relay-bin

# 从库只读，防止误写（非常重要）
read-only = 1

# 允许从库更新其自身的 binlog（用于级联复制，可选）
# log-slave-updates = 1

# 可选：限制只允许管理员写入（即使 read-only=0 时）
# super-read-only = 1

2. 重启 MySQL 服务

systemctl restart mysql

3. 配置从库连接主库

CHANGE MASTER TO
  MASTER_HOST='192.168.1.10',          -- 主库 IP
  MASTER_USER='repl',                  -- 复制用户名
  MASTER_PASSWORD='repl123',           -- 复制用户密码
  MASTER_LOG_FILE='mysql-bin.000001',  -- 主库 binlog 文件名（来自 SHOW MASTER STATUS）
  MASTER_LOG_POS=154;                  -- 主库 binlog 位置（来自 SHOW MASTER STATUS）

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

4. 查看从库状态

SHOW SLAVE STATUS\G

✅ 确保以下两项为 Yes：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

🔍 如果不是 Yes，查看 Last_Error 字段排查问题。

✅ 三、配置 Slave2（从库2）

配置与 Slave1 几乎完全相同，只需改 server-id。

1. 编辑 MySQL 配置文件

[mysqld]
server-id = 3
relay-log = mysql-relay-bin
read-only = 1

2. 重启 MySQL

systemctl restart mysql

3. 配置连接主库（与 Slave1 相同）

CHANGE MASTER TO
  MASTER_HOST='192.168.1.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl123',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;

START SLAVE;

4. 检查状态

SHOW SLAVE STATUS\G

✅ 确保：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

✅ 四、验证主从复制

1. 在 Master 上创建测试数据

CREATE DATABASE test_replication;
USE test_replication;
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));
INSERT INTO users (name) VALUES ('Alice'), ('Bob');

2. 在 Slave1 和 Slave2 上查看

SHOW DATABASES LIKE 'test_replication';
USE test_replication;
SELECT * FROM users;

✅ 如果能看到数据，说明复制成功！

✅ 五、常见问题排查

问题 解决方法
Slave_IO_Running: No 检查网络、用户名密码、主库 binlog 是否开启
Slave_SQL_Running: No 检查是否有主键冲突、表结构不一致
从库延迟高 检查主库写压力、从库性能、网络带宽
ERROR 1201: Failed to initialize slave 确保 server-id 唯一

✅ 六、生产建议

项目 建议
✅ 定期备份 在从库上做 mysqldump 或 xtrabackup
✅ 监控延迟 使用 Seconds_Behind_Master 或 Prometheus + Grafana
✅ 读写分离 应用层将读请求路由到从库
✅ 高可用 结合 MHA / Orchestrator 实现自动切换
✅ 安全 使用 SSL 加密主从通信（可选）


以下是 整合了所有性能、安全、高可用优化项的 MySQL 一主两从完整配置，包含：

  • 基础主从复制
  • 半同步复制（Semi-Sync）
  • GTID 复制
  • 并行复制（MTS）
  • 安全参数
  • 性能调优
  • 详细中文注释

适用于 MySQL 5.7 / 8.0，可直接用于生产环境。

✅ 最终目标架构

Master（主库） → 写操作
   ↓
Slave1（从库1） ← 同步数据，只读
Slave2（从库2） ← 同步数据，只读

🛠 一、Master 主库配置（/etc/my.cnf）

[mysqld]
# ========================
# 基本设置
# ========================
# 唯一标识，主库设为 1
server-id = 1

# 开启二进制日志（主从复制的基础）
log-bin = mysql-bin

# 设置 binlog 格式为 ROW（推荐，更安全，避免复制异常）
binlog-format = ROW

# binlog 过期时间（单位：天）
expire_logs_days = 7

# 单个 binlog 文件最大大小
max_binlog_size = 100M


# ========================
# 数据安全与持久性
# ========================
# 每次事务提交都同步 binlog 到磁盘（防止主库宕机丢失数据）
sync-binlog = 1

# InnoDB 日志同步策略，与 sync-binlog 配合使用
# 值为 1：最安全（每次提交都刷日志），性能略低
innodb_flush_log_at_trx_commit = 1


# ========================
# GTID 复制（推荐）
# ========================
# 启用 GTID（全局事务 ID），便于故障切换和复制管理
gtid-mode = ON

# 强制 GTID 一致性（必须开启）
enforce-gtid-consistency = ON


# ========================
# 半同步复制（Semi-Sync）
# ========================
# 安装插件后启用，确保至少一个从库收到日志
# 防止主库宕机时数据未同步导致丢失
rpl_semi_sync_master_enabled = 1

# 等待从库确认的超时时间（毫秒），超时后退化为异步复制
rpl_semi_sync_master_timeout = 10000  # 10秒


# ========================
# 性能优化
# ========================
# 批量提交优化：延迟微秒，等待多个事务一起提交，减少 I/O
binlog_group_commit_sync_delay = 10
binlog_group_commit_sync_no_delay_count = 10


# ========================
# 安全设置（可选）
# ========================
# 限制只允许特定 IP 连接（如从库 IP 段）
# bind-address = 192.168.1.10

# 可选：启用 SSL（需配置证书）
# ssl-ca = /path/to/ca.pem
# ssl-cert = /path/to/server-cert.pem
# ssl-key = /path/to/server-key.pem

✅ 修改后重启 MySQL：systemctl restart mysql

🛠 二、Slave1 和 Slave2 通用配置（/etc/my.cnf）

两台从库配置相同，仅 server-id 不同（Slave1=2，Slave2=3）

[mysqld]
# ========================
# 基本设置
# ========================
# 唯一标识，不能与主库和其他从库重复
server-id = 2    # Slave2 改为 3

# 开启中继日志（存储从主库读取的 binlog）
relay-log = mysql-relay-bin

# 从库只读，防止误写（非常重要）
read-only = 1

# 超级用户也只读（进一步防止误操作）
super-read-only = 1


# ========================
# GTID 复制
# ========================
# 启用 GTID
gtid-mode = ON
enforce-gtid-consistency = ON


# ========================
# 并行复制（Multi-Threaded Slave, MTS）
# ========================
# 使用逻辑时钟并行复制（基于事务组，效率高）
slave-parallel-type = LOGICAL_CLOCK

# 设置并行工作线程数（建议 4~8，根据 CPU 核数调整）
slave-parallel-workers = 4

# 将主从复制信息存储在表中（更安全）
master-info_repository = TABLE
relay-log-info_repository = TABLE

# 启用 relay log 自动恢复（防止崩溃后出错）
relay_log_recovery = ON


# ========================
# 半同步复制（从库端）
# ========================
# 启用半同步从库插件
rpl_semi_sync_slave_enabled = 1


# ========================
# 安全设置
# ========================
# 可选：启用 SSL（需证书）
# ssl-ca = /path/to/ca.pem
# ssl-cert = /path/to/client-cert.pem
# ssl-key = /path/to/client-key.pem

✅ 修改后重启 MySQL：systemctl restart mysql

🔐 三、主库创建复制用户（主库执行）

-- 创建专用复制用户，限制 IP 范围
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'StrongPass123!';

-- 授予复制权限
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';

-- 刷新权限
FLUSH PRIVILEGES;

🔁 四、从库配置复制（Slave1 和 Slave2 执行）

-- 停止旧的复制（如有）
STOP SLAVE;

-- 配置复制（使用 GTID，无需指定 File 和 Position）
CHANGE MASTER TO
  MASTER_HOST = '192.168.1.10',           -- 主库 IP
  MASTER_USER = 'repl',                   -- 复制用户名
  MASTER_PASSWORD = 'StrongPass123!',     -- 密码
  MASTER_AUTO_POSITION = 1,               -- 启用 GTID 自动定位
  MASTER_SSL = 0;                         -- 如果启用 SSL，改为 1 并配置证书

-- 启动复制
START SLAVE;

✅ 五、验证复制状态（从库执行）

SHOW SLAVE STATUS\G

✅ 确保以下关键项：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0        -- 延迟为 0 表示同步正常
Retrieved_Gtid_Set: ...         -- 有 GTID 记录
Executed_Gtid_Set: ...          -- 已执行 GTID

🔍 如果 Slave_IO_Running 为 Connecting，检查网络、用户权限、防火墙。

🚀 六、启用半同步插件（首次需手动执行）

主库执行：

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
-- 已在配置中启用，重启后自动加载

从库执行：

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
-- 重启 IO 线程使生效
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

✅ 七、总结：这套配置的优势

优化项 效果
✅ GTID 复制更智能，切换更简单
✅ 半同步 防止数据丢失
✅ 并行复制 提升从库同步速度
✅ read-only 防止从库误写
✅ sync-binlog + innodb_flush 保证数据持久性
✅ relay_log_recovery 防止 relay log 损坏
✅ master-info_repository = TABLE 更安全的元数据存储

📦 后续建议

  1. 部署 ProxySQL 实现读写分离
  2. 使用 MHA / Orchestrator 实现自动故障转移
  3. 在 Slave 上做备份（如 xtrabackup）
  4. 监控系统：Prometheus + Grafana + mysqld_exporter

如果你需要我：

  • 生成 自动化部署脚本（Shell/Ansible）
  • 提供 MHA 配置模板
  • 提供 ProxySQL 读写分离配置
  • 打包为 Docker Compose 方案

欢迎告诉我，我可以为你定制完整的 MySQL 高可用解决方案 😊

©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容