MySQL 主从复制完全指南※

本指南涵盖 MySQL 主从复制的完整生命周期：从安装部署、配置启用、日常使用、监控维护到故障处理和主从切换。

目录※

• 一、安装准备
• 二、配置主从复制
• 三、使用和管理
• 四、同步机制
• 五、主从切换
• 六、故障处理
• 七、常见问题
• 八、最佳实践

一、安装准备※

1.1 环境规划※

服务器配置※

硬件要求※

• CPU: 4 核心以上
• 内存: 8GB 以上（根据数据量调整）
• 磁盘: SSD 推荐，至少预留数据量 2 倍空间
• 网络: 千兆网络，延迟 < 1ms

1.2 MySQL 安装※

方法一：YUM 安装（CentOS/RHEL）※

# 添加 MySQL 官方仓库
sudo rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el8-1.noarch.rpm

# 安装 MySQL Server
sudo yum install -y mysql-community-server

# 启动 MySQL
sudo systemctl start mysqld
sudo systemctl enable mysqld

# 查看初始密码
sudo grep 'temporary password' /var/log/mysqld.log

方法二：APT 安装（Ubuntu/Debian）※

# 更新软件源
sudo apt update

# 安装 MySQL Server
sudo apt install -y mysql-server

# 启动 MySQL
sudo systemctl start mysql
sudo systemctl enable mysql

方法三：二进制包安装（通用）※

# 下载 MySQL 二进制包
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.35-linux-glibc2.28-x86_64.tar.xz

# 解压
tar xvf mysql-8.0.35-linux-glibc2.28-x86_64.tar.xz
sudo mv mysql-8.0.35-linux-glibc2.28-x86_64 /usr/local/mysql

# 创建用户和组
sudo groupadd mysql
sudo useradd -r -g mysql -s /bin/false mysql

# 创建数据目录
sudo mkdir -p /data/mysql
sudo chown -R mysql:mysql /data/mysql

# 初始化数据库
cd /usr/local/mysql
sudo bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql

# 创建配置文件
sudo cp support-files/my-default.cnf /etc/my.cnf

# 启动 MySQL
sudo bin/mysqld_safe --user=mysql &

1.3 初始化配置※

# 安全配置向导
sudo mysql_secure_installation

# 配置项说明：
# - 设置 root 密码
# - 删除匿名用户
# - 禁止 root 远程登录（可选）
# - 删除 test 数据库
# - 刷新权限表

1.4 环境准备※

时间同步（必须）※

# 安装 NTP
sudo yum install -y chrony  # CentOS/RHEL
sudo apt install -y chrony  # Ubuntu/Debian

# 配置时间服务器
sudo vim /etc/chrony.conf
# 添加：
# server ntp.aliyun.com iburst

# 启动并同步
sudo systemctl start chronyd
sudo systemctl enable chronyd

# 检查同步状态
chronyc sources -v

防火墙配置※

# firewalld（CentOS/RHEL）
sudo firewall-cmd --permanent --add-port=3306/tcp
sudo firewall-cmd --reload

# ufw（Ubuntu/Debian）
sudo ufw allow 3306/tcp
sudo ufw reload

# iptables
sudo iptables -A INPUT -p tcp --dport 3306 -j ACCEPT
sudo iptables-save > /etc/iptables/rules.v4

系统参数优化※

# 编辑系统限制
sudo vim /etc/security/limits.conf

# 添加：
mysql soft nofile 65535
mysql hard nofile 65535
mysql soft nproc 65535
mysql hard nproc 65535

# 内核参数优化
sudo vim /etc/sysctl.conf

# 添加：
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_max_syn_backlog = 8192
vm.swappiness = 10

# 生效
sudo sysctl -p

二、配置主从复制※

2.1 主库（Master）配置※

步骤 1：修改配置文件※

编辑 /etc/my.cnf：

[mysqld]
# ========== 基础配置 ==========
# 数据目录
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

# 端口
port = 3306

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# ========== 主从复制核心配置 ==========
# 服务器唯一 ID（主从不能相同）
server-id = 1

# 启用二进制日志
log-bin = /var/lib/mysql/mysql-bin
log-bin-index = /var/lib/mysql/mysql-bin.index

# 二进制日志格式
# ROW: 记录数据行变化（推荐，最安全）
# STATEMENT: 记录 SQL 语句（体积小但可能不一致）
# MIXED: 混合模式
binlog_format = ROW

# 二进制日志过期时间（天）
expire_logs_days = 7
# MySQL 8.0+ 使用：
binlog_expire_logs_seconds = 604800  # 7天

# 单个 binlog 文件大小
max_binlog_size = 1G

# 每次事务提交立即同步到磁盘（1=最安全，0=性能最好）
sync_binlog = 1

# ========== GTID 配置（推荐）==========
# 启用 GTID 模式
gtid_mode = ON

# 强制 GTID 一致性
enforce_gtid_consistency = ON

# ========== 复制过滤配置 ==========
# 需要同步的数据库（可选，不配置则全部同步）
# binlog-do-db = myapp
# binlog-do-db = myapp2

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

# ========== 性能优化 ==========
# InnoDB 缓冲池大小（建议设为物理内存的 60-70%）
innodb_buffer_pool_size = 4G

# 日志刷盘策略（1=最安全）
innodb_flush_log_at_trx_commit = 1

# 日志文件大小
innodb_log_file_size = 512M

# 连接数
max_connections = 500

# 查询缓存（MySQL 8.0 已移除）
# query_cache_size = 64M
# query_cache_type = 1

# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# 错误日志
log-error = /var/log/mysql/error.log

步骤 2：重启主库※

sudo systemctl restart mysqld

# 检查启动状态
sudo systemctl status mysqld

# 查看错误日志
sudo tail -f /var/log/mysql/error.log

步骤 3：创建复制用户※

-- 登录主库
mysql -uroot -p

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

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

-- 刷新权限
FLUSH PRIVILEGES;

-- 查看用户
SELECT user, host FROM mysql.user WHERE user='repl';

步骤 4：记录主库状态※

-- 查看主库状态
SHOW MASTER STATUS;

/*
输出示例：
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      157 |              | mysql,...        |                   |
+------------------+----------+--------------+------------------+-------------------+
*/

-- ⚠️ 记录 File 和 Position，配置从库时需要

步骤 5：全量备份数据※

# 方案一：mysqldump（适合中小型数据库）
mysqldump -uroot -p \
  --all-databases \
  --single-transaction \
  --flush-logs \
  --master-data=2 \
  --routines \
  --triggers \
  --events \
  --hex-blob \
  --set-gtid-purged=ON \
  | gzip > /backup/master_full_$(date +%Y%m%d_%H%M%S).sql.gz

# 方案二：XtraBackup（适合大型数据库，热备份）
xtrabackup --backup \
  --user=root \
  --password=yourpassword \
  --target-dir=/backup/xtrabackup_$(date +%Y%m%d_%H%M%S)

# 传输到从库服务器
scp /backup/master_full_*.sql.gz root@192.168.1.101:/tmp/

2.2 从库（Slave）配置※

步骤 1：修改配置文件※

编辑 /etc/my.cnf：

[mysqld]
# ========== 基础配置 ==========
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
port = 3306
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# ========== 从库核心配置 ==========
# 服务器唯一 ID（每个从库不同）
server-id = 2  # 第二个从库使用 3, 4...

# 中继日志配置
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index

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

# 从库只读（防止误写入）
read_only = 1
super_read_only = 1

# 中继日志恢复（从库重启自动恢复）
relay_log_recovery = 1

# 单个中继日志大小
max_relay_log_size = 1G

# ========== GTID 配置 ==========
gtid_mode = ON
enforce_gtid_consistency = ON

# ========== 复制过滤（可选）==========
# 仅复制特定数据库
# replicate-do-db = myapp

# 忽略特定数据库
# replicate-ignore-db = test

# 基于表的过滤
# replicate-do-table = myapp.users
# replicate-ignore-table = myapp.temp_table

# 基于通配符的过滤
# replicate-wild-do-table = myapp.%
# replicate-wild-ignore-table = myapp.temp_%

# ========== 并行复制（性能优化）==========
# 从库并行应用 SQL
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4  # 根据 CPU 核心数调整
slave_preserve_commit_order = ON

# ========== 跳过错误（慎用！）==========
# 跳过特定错误代码（可能导致数据不一致）
# slave-skip-errors = 1062,1053,1146
# 1062: 主键冲突
# 1053: Server shutdown
# 1146: Table doesn't exist

# ========== 性能配置 ==========
innodb_buffer_pool_size = 4G
innodb_flush_log_at_trx_commit = 2  # 从库可适当放宽
max_connections = 500

# 日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log-error = /var/log/mysql/error.log

步骤 2：恢复主库备份※

# 方案一：恢复 mysqldump 备份
gunzip < /tmp/master_full_*.sql.gz | mysql -uroot -p

# 方案二：恢复 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

# 启动 MySQL
systemctl start mysqld

步骤 3：配置主从关系※

-- 登录从库
mysql -uroot -p

-- 停止从库（如果正在运行）
STOP SLAVE;

-- 重置从库
RESET SLAVE ALL;

-- ========== 方式一：基于 GTID（推荐）==========
CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_PORT=3306,
  MASTER_USER='repl',
  MASTER_PASSWORD='Repl@Pass2024!',
  MASTER_AUTO_POSITION=1;  -- 使用 GTID 自动定位

-- ========== 方式二：基于 binlog 位置 ==========
CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_PORT=3306,
  MASTER_USER='repl',
  MASTER_PASSWORD='Repl@Pass2024!',
  MASTER_LOG_FILE='mysql-bin.000001',  -- 之前记录的值
  MASTER_LOG_POS=157;                   -- 之前记录的值

-- 启动从库复制
START SLAVE;

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

2.3 验证主从同步※

-- 在从库检查状态
SHOW SLAVE STATUS\G

/*
重点字段：
  Slave_IO_Running: Yes       ✅ IO 线程运行
  Slave_SQL_Running: Yes      ✅ SQL 线程运行
  Seconds_Behind_Master: 0    ✅ 无延迟
  Last_IO_Error:              ✅ 无 IO 错误
  Last_SQL_Error:             ✅无 SQL 错误
*/

-- 在主库创建测试数据
-- (主库)
CREATE DATABASE test_repl;
USE test_repl;
CREATE TABLE test (id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO test VALUES (1, 'test1'), (2, 'test2');

-- 在从库验证数据
-- (从库)
USE test_repl;
SELECT * FROM test;  -- 应该能看到相同数据

-- 清理测试数据
-- (主库)
DROP DATABASE test_repl;

三、使用和管理※

3.1 日常操作命令※

主库操作※

-- 查看主库状态
SHOW MASTER STATUS;

-- 查看二进制日志列表
SHOW BINARY LOGS;

-- 查看二进制日志事件
SHOW BINLOG EVENTS IN 'mysql-bin.000001' LIMIT 10;

-- 刷新二进制日志（生成新文件）
FLUSH LOGS;

-- 清理指定日志之前的 binlog
PURGE BINARY LOGS TO 'mysql-bin.000010';

-- 清理指定日期之前的 binlog
PURGE BINARY LOGS BEFORE '2026-01-01 00:00:00';

-- 查看从库连接状态
SHOW SLAVE HOSTS;

-- 查看 GTID 执行集合
SHOW GLOBAL VARIABLES LIKE 'gtid_executed';

从库操作※

-- 查看从库状态（最常用）
SHOW SLAVE STATUS\G

-- 启动/停止从库复制
START SLAVE;
STOP SLAVE;

-- 仅启动/停止 IO 线程
START SLAVE IO_THREAD;
STOP SLAVE IO_THREAD;

-- 仅启动/停止 SQL 线程
START SLAVE SQL_THREAD;
STOP SLAVE SQL_THREAD;

-- 重置从库（清除中继日志）
RESET SLAVE;       -- 保留连接信息
RESET SLAVE ALL;   -- 清除所有复制信息

-- 跳过一个错误事务
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

-- 临时关闭从库只读（维护用）
SET GLOBAL read_only = 0;
-- 操作完成后恢复
SET GLOBAL read_only = 1;

-- 查看中继日志
SHOW RELAYLOG EVENTS IN 'mysql-relay-bin.000001' LIMIT 10;

3.2 监控指标※

核心监控脚本※

#!/bin/bash
# monitor_replication.sh - MySQL 主从监控

MYSQL_USER="root"
MYSQL_PASS="your_password"
MYSQL_CMD="mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e"

echo "========== MySQL 主从复制状态 =========="
echo "检查时间: $(date '+%Y-%m-%d %H:%M:%S')"
echo ""

# 检查 IO 线程
IO_RUNNING=$($MYSQL_CMD "SHOW SLAVE STATUS\G" | grep "Slave_IO_Running:" | awk '{print $2}')
echo "IO 线程状态: $IO_RUNNING"

# 检查 SQL 线程
SQL_RUNNING=$($MYSQL_CMD "SHOW SLAVE STATUS\G" | grep "Slave_SQL_Running:" | awk '{print $2}')
echo "SQL 线程状态: $SQL_RUNNING"

# 检查延迟
DELAY=$($MYSQL_CMD "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master:" | awk '{print $2}')
echo "复制延迟: $DELAY 秒"

# 检查错误
IO_ERROR=$($MYSQL_CMD "SHOW SLAVE STATUS\G" | grep "Last_IO_Error:" | cut -d':' -f2-)
SQL_ERROR=$($MYSQL_CMD "SHOW SLAVE STATUS\G" | grep "Last_SQL_Error:" | cut -d':' -f2-)

if [ "$IO_RUNNING" != "Yes" ]; then
    echo "❌ 错误: IO 线程未运行"
    echo "IO 错误: $IO_ERROR"
    exit 1
fi

if [ "$SQL_RUNNING" != "Yes" ]; then
    echo "❌ 错误: SQL 线程未运行"
    echo "SQL 错误: $SQL_ERROR"
    exit 1
fi

if [ "$DELAY" != "NULL" ] && [ "$DELAY" -gt 60 ]; then
    echo "⚠️  警告: 复制延迟超过 60 秒"
fi

echo "✅ 主从复制正常"

性能监控 SQL※

-- 查看 binlog 磁盘占用
SELECT 
    CONCAT(ROUND(SUM(file_size)/1024/1024/1024, 2), ' GB') AS total_size
FROM information_schema.innodb_tablespaces;

-- 查看最近的 binlog 事件速率
SHOW GLOBAL STATUS LIKE 'Binlog_cache%';

-- 查看从库并行复制状态
SELECT * FROM performance_schema.replication_applier_status_by_worker;

-- 查看复制延迟详情
SELECT 
    CHANNEL_NAME,
    COUNT_TRANSACTIONS_IN_QUEUE AS pending_transactions,
    COUNT_TRANSACTIONS_RETRIES AS retries
FROM performance_schema.replication_connection_status;

3.3 定时任务配置※

# 编辑 crontab
crontab -e

# 每 5 分钟检查主从状态
*/5 * * * * /path/to/monitor_replication.sh >> /var/log/mysql/repl_monitor.log 2>&1

# 每天凌晨 2 点清理 7 天前的 binlog
0 2 * * * mysql -uroot -p'password' -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);"

# 每小时检查 binlog 磁盘占用
0 * * * * df -h | grep mysql | awk '{if($5+0 > 80) print "MySQL 磁盘使用率: "$5}' | mail -s "磁盘告警" admin@example.com

四、同步机制※

4.1 复制原理※

graph TD
    subgraph Master["主库 (Master)"]
        A[1. 执行 SQL] --> B[2. 写 binlog]
    end
    
    subgraph Slave["从库 (Slave)"]
        C[3. IO Thread] --> D[4. 写 relay-log]
        D --> E[5. SQL Thread]
        E --> F[6. 执行 SQL]
    end
    
    B -->|复制 binlog 事件| C
    
    style Master fill:#e1f5ff
    style Slave fill:#fff4e1

4.2 三种复制模式※

异步复制（Asynchronous Replication）※

• 特点: 主库执行完事务后立即返回，不等待从库确认
• 优点: 性能最好，主库不受从库影响
• 缺点: 主库宕机可能丢失未同步的数据
• 适用场景: 对性能要求高，可容忍少量数据丢失

# 默认即为异步复制，无需特殊配置

半同步复制（Semi-Synchronous Replication）※

• 特点: 主库等待至少一个从库确认接收到 binlog 后才返回
• 优点: 数据安全性高，几乎不丢数据
• 缺点: 性能略有下降，依赖网络质量
• 适用场景: 金融、电商等对数据一致性要求高的场景

-- 主库安装插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

-- 从库安装插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

-- 主库启用半同步
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000;  -- 超时时间(ms)

-- 从库启用半同步
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- 重启从库 IO 线程使生效
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

-- 查看半同步状态
SHOW STATUS LIKE 'Rpl_semi_sync%';

组复制（Group Replication）※

• 特点: 多主模式，基于 Paxos 协议的强一致性复制
• 优点: 高可用，自动故障转移，强一致性
• 缺点: 配置复杂，性能开销较大
• 适用场景: 需要多主写入和自动故障转移

4.3 GTID vs 传统复制※

GTID 格式※

# GTID = source_id:transaction_id
3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5

# 表示：
# - 源服务器 UUID: 3E11FA47-71CA-11E1-9E33-C80AA9429562
# - 事务序列号: 1 到 5

4.4 并行复制优化※

-- 从库启用并行复制
STOP SLAVE;

-- 设置并行类型
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';  -- 基于逻辑时钟（推荐）
-- 或
-- SET GLOBAL slave_parallel_type = 'DATABASE';    -- 基于数据库

-- 设置并行工作线程数（根据 CPU 核心数）
SET GLOBAL slave_parallel_workers = 4;

-- 保持提交顺序
SET GLOBAL slave_preserve_commit_order = ON;

START SLAVE;

-- 查看并行复制状态
SELECT * FROM performance_schema.replication_applier_status_by_worker;

五、主从切换※

5.1 计划内切换（主库升级维护）※

步骤 1：准备阶段※

# 1. 通知业务方，准备切换时间窗口
# 2. 确认从库同步正常
# 3. 备份当前主库数据
mysqldump -uroot -p --all-databases --master-data=2 > /backup/pre_switch_backup.sql

步骤 2：主库设置只读※

-- 在当前主库执行
SET GLOBAL read_only = 1;
SET GLOBAL super_read_only = 1;

-- 等待所有事务完成
SHOW PROCESSLIST;

-- 刷新日志
FLUSH LOGS;

步骤 3：确认从库同步完成※

-- 在从库检查
SHOW SLAVE STATUS\G

-- 确认以下字段：
-- Seconds_Behind_Master: 0
-- Retrieved_Gtid_Set 和 Executed_Gtid_Set 一致

步骤 4：提升从库为新主库※

-- 在目标从库执行
STOP SLAVE;
RESET SLAVE ALL;

-- 关闭只读
SET GLOBAL read_only = 0;
SET GLOBAL super_read_only = 0;

-- 记录新主库状态
SHOW MASTER STATUS;

步骤 5：将原主库配置为从库※

-- 在原主库执行
CHANGE MASTER TO
  MASTER_HOST='192.168.1.101',  -- 新主库 IP
  MASTER_USER='repl',
  MASTER_PASSWORD='Repl@Pass2024!',
  MASTER_AUTO_POSITION=1;

START SLAVE;

-- 检查状态
SHOW SLAVE STATUS\G

步骤 6：切换应用连接※

# 方案一：修改应用配置，重启应用
vim /app/config/database.yml
# 修改 host: 192.168.1.101

# 方案二：使用 VIP/域名（推荐）
# 切换 VIP 到新主库
ip addr del 192.168.1.200/24 dev eth0  # 在旧主库
ip addr add 192.168.1.200/24 dev eth0  # 在新主库

# 方案三：使用负载均衡器
# 在 HAProxy/LVS 上切换后端

5.2 故障切换（主库宕机）※

紧急切换步骤※

-- 1. 在最新的从库上停止复制
STOP SLAVE;

-- 2. 提升为主库
RESET SLAVE ALL;
SET GLOBAL read_only = 0;
SET GLOBAL super_read_only = 0;

-- 3. 记录新主库 GTID 位置
SHOW MASTER STATUS;

-- 4. 其他从库指向新主库
-- (在其他从库执行)
STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST='192.168.1.101',  -- 新主库
  MASTER_USER='repl',
  MASTER_PASSWORD='Repl@Pass2024!',
  MASTER_AUTO_POSITION=1;
START SLAVE;

自动故障切换工具※

# MHA (Master High Availability)
# 安装 MHA
yum install -y mha4mysql-manager mha4mysql-node

# 配置 MHA
vim /etc/mha/app1.cnf

# 启动 MHA Manager
nohup masterha_manager --conf=/etc/mha/app1.cnf > /var/log/mha/app1.log 2>&1 &

# Orchestrator（推荐）
# Web 界面管理，自动故障检测和切换
docker run -d \
  --name orchestrator \
  -p 3000:3000 \
  openarkcode/orchestrator:latest

5.3 级联复制※

graph TD
    Master["Master
主库"] --> Slave1["Slave 1
中继从库"]
    Slave1 --> Slave2["Slave 2
二级从库"]
    
    style Master fill:#99ccff
    style Slave1 fill:#99ff99
    style Slave2 fill:#ffcc99

-- 中继从库配置
[mysqld]
server-id = 2
log_bin = mysql-bin           -- 启用 binlog
log_slave_updates = 1         -- 从主库复制的数据也写入 binlog
binlog_format = ROW

-- 二级从库指向中继从库
CHANGE MASTER TO
  MASTER_HOST='中继从库IP',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_AUTO_POSITION=1;

六、故障处理※

6.1 IO 线程问题※

问题 1：无法连接主库※

错误信息：
Last_IO_Error: error connecting to master 'repl@192.168.1.100:3306'

排查步骤：

# 1. 检查网络连通性
ping 192.168.1.100
telnet 192.168.1.100 3306

# 2. 检查防火墙
sudo firewall-cmd --list-all
sudo iptables -L -n | grep 3306

# 3. 检查主库监听
# (主库)
netstat -tlnp | grep 3306
# 确保监听 0.0.0.0:3306 而非 127.0.0.1:3306

# 4. 修改主库绑定地址
vim /etc/my.cnf
# bind-address = 0.0.0.0  # 或注释掉

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

问题 2：复制用户权限不足※

错误信息：
Last_IO_Error: error executing query 'show slave status': Access denied

解决方法：

-- 在主库重新授权
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;

-- 从库重新配置
STOP SLAVE;
START SLAVE;

问题 3：binlog 不存在※

错误信息：
Last_IO_Error: Got fatal error 1236 from master: 'Could not find first log file name in binary log index file'

解决方法：

-- 方案一：使用 GTID 重新连接
STOP SLAVE;
CHANGE MASTER TO MASTER_AUTO_POSITION=1;
START SLAVE;

-- 方案二：重新初始化从库
-- 1. 主库全量备份
-- 2. 从库恢复备份
-- 3. 重新配置主从关系

6.2 SQL 线程问题※

问题 1：主键冲突（错误 1062）※

Last_SQL_Error: Error 'Duplicate entry '1' for key 'PRIMARY'' on query

解决方法：

-- 方案一：跳过错误（可能导致数据不一致）
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

-- 方案二：修复数据（推荐）
-- 1. 在从库查看冲突记录
SELECT * FROM table_name WHERE id = 1;

-- 2. 手动删除冲突数据
DELETE FROM table_name WHERE id = 1;

-- 3. 重启复制
START SLAVE;

-- 方案三：GTID 跳过
STOP SLAVE;
SET GTID_NEXT='uuid:transaction_id';  -- 从 SHOW SLAVE STATUS 获取
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';
START SLAVE;

问题 2：表不存在（错误 1146）※

Last_SQL_Error: Error 'Table 'mydb.test_table' doesn't exist' on query

解决方法：

-- 方案一：从主库同步表结构
-- (主库)
mysqldump -uroot -p --no-data mydb test_table > /tmp/table_structure.sql

-- (从库)
mysql -uroot -p mydb < /tmp/table_structure.sql

-- 重启复制
START SLAVE;

-- 方案二：跳过该语句
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

问题 3：复制延迟过大※

Seconds_Behind_Master: 3600  -- 延迟 1 小时

优化方案：

-- 1. 启用并行复制
STOP SLAVE;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 8;
SET GLOBAL slave_preserve_commit_order = ON;
START SLAVE;

-- 2. 优化从库硬件
-- - 使用 SSD
-- - 增加内存（innodb_buffer_pool_size）
-- - 优化网络带宽

-- 3. 主库优化
-- - 减少大事务
-- - 批量操作拆分
-- - 避免全表更新

-- 4. 检查慢查询
-- (从库)
SELECT * FROM information_schema.processlist 
WHERE command = 'Query' AND time > 10;

6.3 数据一致性检查※

使用 pt-table-checksum※

# 安装 Percona Toolkit
yum install -y percona-toolkit

# 在主库执行（会自动检查所有从库）
pt-table-checksum \
  --host=192.168.1.100 \
  --user=root \
  --password=yourpassword \
  --databases=mydb \
  --no-check-binlog-format

# 输出示例：
# TS            ERRORS  DIFFS  ROWS  CHUNKS  SKIPPED  TIME  TABLE
# 02-04T10:30:00     0      0  1000      10        0  0.5  mydb.users
# 02-04T10:30:01     0      5  2000      20        0  1.2  mydb.orders  ← 发现不一致

使用 pt-table-sync 修复※

# 修复不一致的表
pt-table-sync \
  --execute \
  --sync-to-master \
  h=192.168.1.101 \  # 从库地址
  D=mydb,t=orders

# 或批量修复
pt-table-sync \
  --execute \
  --sync-to-master \
  h=192.168.1.101

6.4 紧急恢复场景※

场景 1：从库完全损坏※

# 1. 从主库重新备份
mysqldump -uroot -p --all-databases --master-data=2 > /backup/rebuild.sql

# 2. 传输到从库
scp /backup/rebuild.sql slave:/tmp/

# 3. 从库恢复
mysql -uroot -p < /tmp/rebuild.sql

# 4. 重新配置主从
CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_AUTO_POSITION=1;
START SLAVE;

场景 2：主库 binlog 被误删※

# 1. 立即停止从库复制（防止同步错误）
mysql -uroot -p -e "STOP SLAVE;"

# 2. 主库重新全量备份
# 3. 所有从库重新初始化

七、常见问题※

Q1: 从库只读模式下如何临时写入？※

-- 方法一：使用 super 权限用户
-- super 用户可以在 read_only 模式下写入

-- 方法二：临时关闭只读
SET GLOBAL read_only = 0;
-- 执行写入操作
SET GLOBAL read_only = 1;

-- 方法三：使用 SQL_LOG_BIN 防止写入 binlog
SET SESSION SQL_LOG_BIN = 0;
-- 执行写入（不会复制到其他从库）
SET SESSION SQL_LOG_BIN = 1;

Q2: 如何不停机添加新从库？※

# 方法一：从现有从库克隆
# 1. 在现有从库停止复制
mysql -e "STOP SLAVE;"

# 2. 备份数据和位置信息
mysqldump --all-databases --master-data=2 > /backup/new_slave.sql

# 3. 恢复到新从库
mysql -h 新从库 < /backup/new_slave.sql

# 4. 在新从库配置主从
# 5. 原从库继续运行
mysql -e "START SLAVE;"

# 方法二：使用 XtraBackup 热备份（推荐）
# 在现有从库执行，无需停止复制
xtrabackup --backup --slave-info --target-dir=/backup/new_slave

Q3: binlog 占用磁盘过大如何清理？※

-- 1. 查看当前 binlog
SHOW BINARY LOGS;

-- 2. 确认所有从库已同步
-- (在所有从库执行)
SHOW SLAVE STATUS\G

-- 3. 清理指定之前的日志
PURGE BINARY LOGS TO 'mysql-bin.000050';

-- 4. 清理指定日期之前
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

-- 5. 自动清理配置
SET GLOBAL expire_logs_days = 7;  -- MySQL 5.x
SET GLOBAL binlog_expire_logs_seconds = 604800;  -- MySQL 8.0+

Q4: 主从延迟多少算正常？※

Q5: 能否跨版本配置主从？※

规则：

• ✅ 支持：主库版本 ≤ 从库版本（推荐）
• ⚠️ 谨慎：主库版本 > 从库版本（可能不兼容）
• ❌ 不建议：跨大版本（如 5.7 → 8.0 需测试）

# 示例：从 MySQL 5.7 升级到 8.0
# 1. 先升级从库到 8.0
# 2. 测试从库运行稳定
# 3. 主从切换，原从库（8.0）变主库
# 4. 升级原主库到 8.0，作为从库

Q6: 如何监控主从同步状态？※

-- Prometheus + Exporter 方案
-- 1. 安装 mysqld_exporter
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.14.0/mysqld_exporter-0.14.0.linux-amd64.tar.gz

-- 2. 配置监控用户
CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'password';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';

-- 3. 启动 exporter
./mysqld_exporter --config.my-cnf=/etc/.my.cnf

-- 4. Grafana 导入 Dashboard
-- 推荐：MySQL Overview (ID: 7362)

Q7: 从库可以创建索引吗？※

-- ✅ 可以，从库可以独立创建索引优化查询
-- 不会影响主库，也不会影响复制

-- 在从库创建索引
SET SESSION SQL_LOG_BIN = 0;  -- 不记录到 binlog
CREATE INDEX idx_user_email ON users(email);
SET SESSION SQL_LOG_BIN = 1;

-- ⚠️ 注意：主从切换后，索引不会同步到新从库

八、最佳实践※

8.1 架构设计※

推荐架构：一主多从※

graph TD
    VIP["VIP: 192.168.1.200"]
    VIP --> Master["Master
主库
192.168.1.100"]
    
    Master --> Slave1["Slave 1
读负载
192.168.1.101"]
    Master --> Slave2["Slave 2
备份
192.168.1.102"]
    Master --> Slave3["Slave 3
分析
192.168.1.103"]
    Master --> Slave4["Slave 4
容灾
192.168.1.104"]
    
    style VIP fill:#ff9999
    style Master fill:#99ccff
    style Slave1 fill:#99ff99
    style Slave2 fill:#ffcc99
    style Slave3 fill:#cc99ff
    style Slave4 fill:#ffff99

从库分工※

• Slave 1: 承载读请求，分担主库压力
• Slave 2: 专用备份，定时全量/增量备份
• Slave 3: 数据分析，OLAP 查询
• Slave 4: 异地容灾，跨机房部署

8.2 配置建议※

8.3 安全建议※

-- 1. 复制用户使用强密码
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'C0mpl3x!P@ssw0rd#2024';

-- 2. 限制复制用户 IP 范围
-- 使用 'repl'@'192.168.1.%' 而非 'repl'@'%'

-- 3. 启用 SSL 加密复制（跨机房）
CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_SSL=1,
  MASTER_SSL_CA='/etc/mysql/ca.pem',
  MASTER_SSL_CERT='/etc/mysql/client-cert.pem',
  MASTER_SSL_KEY='/etc/mysql/client-key.pem',
  MASTER_AUTO_POSITION=1;

-- 4. 定期轮换复制用户密码
ALTER USER 'repl'@'192.168.1.%' IDENTIFIED BY 'NewPassword';
-- 然后在所有从库更新密码

8.4 备份策略※

# 全量备份（每天凌晨）
0 2 * * * mysqldump -uroot -p --all-databases --master-data=2 | gzip > /backup/full_$(date +\%Y\%m\%d).sql.gz

# 增量备份（每小时）
0 * * * * mysqlbinlog /var/lib/mysql/mysql-bin.* > /backup/incremental_$(date +\%Y\%m\%d_\%H).binlog

# 备份保留策略
# - 全量备份：保留 30 天
# - 增量备份：保留 7 天
# - 异地备份：每周同步到远程存储

# 清理旧备份
0 3 * * * find /backup -name "full_*.sql.gz" -mtime +30 -delete
0 3 * * * find /backup -name "incremental_*.binlog" -mtime +7 -delete

8.5 性能优化清单※

• ✅ 主库使用 SSD 存储
• ✅ innodb_buffer_pool_size 设为内存的 60-70%
• ✅ 启用半同步复制（金融场景）
• ✅ 从库启用并行复制
• ✅ 监控主从延迟，设置告警阈值
• ✅ 定期清理 binlog，避免磁盘满
• ✅ 使用连接池减少连接开销
• ✅ 避免大事务，拆分为小事务
• ✅ 读写分离，读请求分配到从库
• ✅ 定期检查数据一致性

8.6 日常巡检脚本※

#!/bin/bash
# daily_check.sh - MySQL 主从每日巡检

echo "========== MySQL 主从巡检报告 =========="
echo "时间: $(date '+%Y-%m-%d %H:%M:%S')"
echo ""

# 1. 检查 MySQL 服务状态
echo "1. MySQL 服务状态:"
systemctl is-active mysqld && echo "  ✅ MySQL 运行中" || echo "  ❌ MySQL 未运行"

# 2. 检查主从复制状态
echo ""
echo "2. 主从复制状态:"
SLAVE_STATUS=$(mysql -uroot -p'password' -e "SHOW SLAVE STATUS\G" 2>/dev/null)
IO_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_IO_Running:" | awk '{print $2}')
SQL_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_SQL_Running:" | awk '{print $2}')
DELAY=$(echo "$SLAVE_STATUS" | grep "Seconds_Behind_Master:" | awk '{print $2}')

echo "  IO 线程: $IO_RUNNING"
echo "  SQL 线程: $SQL_RUNNING"
echo "  延迟: $DELAY 秒"

# 3. 检查磁盘空间
echo ""
echo "3. 磁盘使用率:"
df -h | grep -E '/var/lib/mysql|/backup'

# 4. 检查 binlog 数量和大小
echo ""
echo "4. Binlog 统计:"
mysql -uroot -p'password' -e "SHOW BINARY LOGS;" | wc -l
du -sh /var/lib/mysql/mysql-bin.*

# 5. 检查慢查询
echo ""
echo "5. 慢查询统计（最近 24 小时）:"
mysql -uroot -p'password' -e "SELECT COUNT(*) FROM mysql.slow_log WHERE start_time > DATE_SUB(NOW(), INTERVAL 1 DAY);"

# 6. 生成报告
if [ "$IO_RUNNING" = "Yes" ] && [ "$SQL_RUNNING" = "Yes" ]; then
    echo ""
    echo "========== ✅ 巡检通过 =========="
    exit 0
else
    echo ""
    echo "========== ❌ 巡检失败，请处理 =========="
    exit 1
fi

附录※

A. 快速命令参考※

-- 主库常用命令
SHOW MASTER STATUS;
SHOW BINARY LOGS;
FLUSH LOGS;
PURGE BINARY LOGS TO 'mysql-bin.000100';

-- 从库常用命令
SHOW SLAVE STATUS\G
START SLAVE;
STOP SLAVE;
RESET SLAVE ALL;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

-- 监控命令
SHOW PROCESSLIST;
SHOW ENGINE INNODB STATUS\G
SHOW GLOBAL STATUS LIKE 'Rpl%';

B. 配置文件模板※

完整配置文件已在第二章节提供，可根据实际环境调整参数。

C. 相关文档※

• MySQL 官方文档 - Replication
• GTID 复制文档
• Percona Toolkit 文档
• Orchestrator 官方文档

文档版本: v2.0
最后更新: 2026年2月4日
适用版本: MySQL 5.7 / 8.0

💡 提示: 本指南持续更新中，如有问题或建议，欢迎反馈。