MySQL 主从重建操作手册

MySQL 主从重建操作手册※

一、环境说明※

1.1 服务器信息※

角色IP 地址端口数据目录
主库（Master）192.168.1.1003306/var/lib/mysql
从库（Slave）192.168.1.1013306/var/lib/mysql

1.2 软件版本※

  • MySQL 版本：8.0.x
  • 操作系统：Linux

二、主库（Master）配置※

2.1 修改主库配置文件※

编辑 /etc/my.cnf 或 /etc/mysql/my.cnf：

[mysqld]
# 服务器唯一ID，主从必须不同
server-id = 1

# 启用二进制日志
log-bin = mysql-bin

# 二进制日志格式（推荐 ROW）
binlog_format = ROW

# 需要同步的数据库（可选，不配置则同步所有）
# binlog-do-db = database_name

# 不需要同步的数据库（可选）
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys

# 二进制日志过期时间（天）
expire_logs_days = 7

# 主从复制相关
max_binlog_size = 1G
sync_binlog = 1

# GTID 模式（推荐，MySQL 5.6+）
gtid_mode = ON
enforce_gtid_consistency = ON

2.2 重启主库※

# systemd 系统
sudo systemctl restart mysqld

# 或
sudo systemctl restart mysql

2.3 创建复制用户※

登录主库 MySQL：

-- 创建复制用户
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'StrongPassword123!';

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

-- 刷新权限
FLUSH PRIVILEGES;

2.4 查看主库状态※

SHOW MASTER STATUS;

记录输出结果：

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      156 |              | mysql,...        |
+------------------+----------+--------------+------------------+

⚠️ 重要：记录 File 和 Position 值，后续配置从库需要使用

2.5 备份主库数据（全量数据）※

方法一：使用 mysqldump※

# 锁表备份（确保数据一致性）
mysqldump -uroot -p \
  --all-databases \
  --single-transaction \
  --master-data=2 \
  --flush-logs \
  --routines \
  --triggers \
  --events \
  > /backup/master_backup_$(date +%Y%m%d_%H%M%S).sql

# 如果只备份特定数据库
mysqldump -uroot -p \
  --databases database_name \
  --single-transaction \
  --master-data=2 \
  --flush-logs \
  --routines \
  --triggers \
  --events \
  > /backup/database_backup_$(date +%Y%m%d_%H%M%S).sql

方法二：使用 XtraBackup（推荐大型数据库）※

# 全量备份
xtrabackup --backup \
  --target-dir=/backup/xtrabackup_$(date +%Y%m%d_%H%M%S) \
  --user=root \
  --password=your_password

三、从库（Slave）配置※

3.1 修改从库配置文件※

编辑 /etc/my.cnf 或 /etc/mysql/my.cnf：

[mysqld]
# 服务器唯一ID，必须与主库不同
server-id = 2

# 启用中继日志
relay-log = mysql-relay-bin

# 从库只读（可选，防止误操作）
read_only = 1
super_read_only = 1

# 跳过复制错误（慎用！）
# slave-skip-errors = 1062,1053,1146

# GTID 模式
gtid_mode = ON
enforce_gtid_consistency = ON

# 从库更新记录到 binlog（用于级联复制）
log_slave_updates = 1

# 中继日志相关
max_relay_log_size = 1G
relay_log_recovery = 1

3.2 重启从库※

sudo systemctl restart mysqld

3.3 恢复主库备份到从库※

方法一：使用 mysqldump 备份文件※

# 传输备份文件到从库
scp /backup/master_backup_*.sql root@192.168.1.101:/tmp/

# 在从库上恢复
mysql -uroot -p < /tmp/master_backup_*.sql

方法二：使用 XtraBackup 备份※

# 准备备份
xtrabackup --prepare --target-dir=/backup/xtrabackup_xxx

# 停止从库 MySQL
systemctl stop mysqld

# 清空数据目录
rm -rf /var/lib/mysql/*

# 恢复备份
xtrabackup --copy-back --target-dir=/backup/xtrabackup_xxx

# 修复权限
chown -R mysql:mysql /var/lib/mysql

# 启动从库
systemctl start mysqld

3.4 配置主从同步※

登录从库 MySQL：

方式一：基于 GTID（推荐）※

-- 停止从库复制（如果已启动）
STOP SLAVE;

-- 重置从库
RESET SLAVE ALL;

-- 配置主库连接信息
CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_PORT=3306,
  MASTER_USER='repl',
  MASTER_PASSWORD='StrongPassword123!',
  MASTER_AUTO_POSITION=1;  -- 使用 GTID 自动定位

-- 启动从库复制
START SLAVE;

方式二：基于 binlog 位置※

-- 停止从库复制
STOP SLAVE;

-- 重置从库
RESET SLAVE ALL;

-- 配置主库连接信息（使用之前记录的 File 和 Position）
CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_PORT=3306,
  MASTER_USER='repl',
  MASTER_PASSWORD='StrongPassword123!',
  MASTER_LOG_FILE='mysql-bin.000001',  -- 之前记录的 File
  MASTER_LOG_POS=156;                   -- 之前记录的 Position

-- 启动从库复制
START SLAVE;

四、验证主从同步※

4.1 检查从库状态※

SHOW SLAVE STATUS\G

重点关注以下字段：

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.100
                  Master_User: repl
                  Master_Port: 3306
             Slave_IO_Running: Yes    -- ✅ 必须为 Yes
            Slave_SQL_Running: Yes    -- ✅ 必须为 Yes
          Seconds_Behind_Master: 0    -- ✅ 延迟时间（0 为最佳）
                 Last_IO_Error:       -- ❌ 应为空
                Last_SQL_Error:       -- ❌ 应为空

✅ 同步正常标志：

  • Slave_IO_Running: Yes
  • Slave_SQL_Running: Yes
  • Seconds_Behind_Master: 0（或较小值）
  • 无错误信息

4.2 测试数据同步※

在主库执行：

-- 创建测试数据库
CREATE DATABASE test_replication;

USE test_replication;

-- 创建测试表
CREATE TABLE test_table (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入测试数据
INSERT INTO test_table (name) VALUES ('Test 1'), ('Test 2'), ('Test 3');

-- 查询数据
SELECT * FROM test_table;

在从库查询：

USE test_replication;
SELECT * FROM test_table;

如果从库能查到相同数据，说明同步成功！

五、常见问题排查※

5.1 IO 线程未运行※

现象： Slave_IO_Running: No

可能原因：

  • 网络连接问题
  • 主库连接信息错误
  • 复制用户权限不足
  • 防火墙阻止

解决方法：

# 检查网络连通性
ping 192.168.1.100

# 检查端口连通性
telnet 192.168.1.100 3306

# 检查防火墙
sudo firewall-cmd --list-all

# 在主库开放端口
sudo firewall-cmd --permanent --add-port=3306/tcp
sudo firewall-cmd --reload

# 测试复制用户连接
mysql -h 192.168.1.100 -u repl -p

5.2 SQL 线程未运行※

现象： Slave_SQL_Running: No

可能原因：

  • 主从数据不一致
  • SQL 执行错误
  • binlog 位置错误

解决方法：

-- 查看详细错误
SHOW SLAVE STATUS\G

-- 如果是数据冲突，可以跳过错误（慎用！）
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

-- 或使用 GTID 跳过
STOP SLAVE;
SET GTID_NEXT='UUID:transaction_id';  -- 替换为实际值
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';
START SLAVE;

5.3 主从延迟过大※

现象： Seconds_Behind_Master 值很大

优化方法：

-- 启用并行复制（MySQL 5.6+）
STOP SLAVE;

SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 4;  -- 根据 CPU 核心数调整
SET GLOBAL slave_preserve_commit_order = ON;

START SLAVE;

5.4 UUID 冲突※

现象： 错误信息包含 "Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs"

解决方法：

# 在从库上重新生成 UUID
sudo systemctl stop mysqld
sudo rm -f /var/lib/mysql/auto.cnf
sudo systemctl start mysqld

六、主从切换操作※

6.1 计划内切换（从库提升为主库）※

-- 1. 在原主库上设置为只读
SET GLOBAL read_only = 1;
SET GLOBAL super_read_only = 1;

-- 2. 在原从库上等待同步完成
SHOW SLAVE STATUS\G
-- 确认 Seconds_Behind_Master = 0

-- 3. 停止从库复制
STOP SLAVE;
RESET SLAVE ALL;

-- 4. 将原从库设置为可写
SET GLOBAL read_only = 0;
SET GLOBAL super_read_only = 0;

-- 5. 原主库配置为新从库
CHANGE MASTER TO
  MASTER_HOST='192.168.1.101',  -- 新主库 IP
  MASTER_USER='repl',
  MASTER_PASSWORD='StrongPassword123!',
  MASTER_AUTO_POSITION=1;

START SLAVE;

七、监控和维护※

7.1 日常检查脚本※

#!/bin/bash
# check_replication.sh

MYSQL_USER="root"
MYSQL_PASS="your_password"

# 检查从库状态
result=$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master")

echo "$result"

# 检查是否异常
if echo "$result" | grep -q "Slave_IO_Running: No"; then
  echo "❌ IO 线程未运行！"
  exit 1
fi

if echo "$result" | grep -q "Slave_SQL_Running: No"; then
  echo "❌ SQL 线程未运行！"
  exit 1
fi

echo "✅ 主从同步正常"

7.2 配置告警※

# 添加到 crontab，每 5 分钟检查一次
*/5 * * * * /path/to/check_replication.sh || mail -s "MySQL 主从异常" admin@example.com

八、备注事项※

⚠️ 重要提醒※

  • ✅ 操作前务必做好完整备份
  • ✅ 主从服务器时间必须同步（建议使用 NTP）
  • ✅ 确保主从服务器的 MySQL 版本兼容
  • ✅ 生产环境建议使用 GTID 模式
  • ⚠️ slave-skip-errors 慎用，可能导致数据不一致
  • ⚠️ 从库默认只读，避免直接写入数据
  • ⚠️ 定期检查二进制日志占用空间

📚 参考资源※

最后更新：2026年2月4日

“您的支持是我持续分享的动力”

微信收款码
微信
支付宝收款码
支付宝
目录