【保姆级教程】MySQL主从复制最全配置指南，含监控脚本和故障处理

MySQL 主从复制（Replication）是构建高可用、高性能数据库架构的基石。下面我将为您全面详细地解析 MySQL 主从复制。

一、主从复制是什么？

主从复制 是指数据从一个 MySQL 数据库（主库）复制到一个或多个 MySQL 数据库（从库）的过程。

核心价值：

• 数据备份：从库作为主库的实时备份
• 读写分离：主库处理写操作，从库处理读操作
• 负载均衡：将读请求分散到多个从库
• 高可用：主库故障时，从库可升级为主库
• 数据分析：在从库执行报表查询，不影响主库性能

二、主从复制原理架构

复制流程概览：

主库 (Master)
    ↓ 二进制日志 (Binlog)
    ↓ 
从库 I/O 线程 (Slave I/O Thread)
    ↓ 中继日志 (Relay Log)
    ↓
从库 SQL 线程 (Slave SQL Thread)
    ↓
从库数据文件

核心组件详解：

1. 二进制日志 (Binary Log)

• 记录所有对数据库的数据修改操作
• 三种格式：STATEMENT、ROW、MIXED
• 主从复制的数据来源

1. 复制线程

• 主库 Binlog Dump 线程：读取 Binlog 发送给从库
• 从库 I/O 线程：连接主库，接收 Binlog 数据，写入中继日志
• 从库 SQL 线程：读取中继日志，重放 SQL 语句

1. 中继日志 (Relay Log)

• 从库的临时存储，格式与 Binlog 相同
• SQL 线程从中读取并执行

三、主从复制配置实战

环境准备：

• 主库 IP: 192.168.1.10
• 从库 IP: 192.168.1.11
• 复制用户: repl
• 版本: MySQL 8.0+

步骤 1：主库配置

1.1 修改主库配置文件

/etc/mysql/my.cnf
[mysqld]
 服务器唯一ID
server-id = 1

 开启二进制日志
log_bin = /var/log/mysql/mysql-bin.log

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

 需要复制的数据库（可选）
 binlog_do_db = mydb

 忽略复制的数据库（可选）
 binlog_ignore_db = mysql

 自动清理过期binlog
expire_logs_days = 7
max_binlog_size = 100M

 从库也需要记录binlog（用于级联复制）
log_slave_updates = 1

1.2 重启主库服务

sudo systemctl restart mysql

1.3 创建复制用户

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

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

-- 刷新权限
FLUSH PRIVILEGES;

1.4 查看主库状态

SHOW MASTER STATUS;

记录输出结果：

++-+--++-+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
++-+--++-+
| mysql-bin.000001 |      154 |              |                  |                   |
++-+--++-+

1.5 备份主库数据（可选）

使用 mysqldump 备份
mysqldump -u root -p --all-databases --master-data > master_backup.sql

步骤 2：从库配置

2.1 修改从库配置文件

/etc/mysql/my.cnf
[mysqld]
 服务器唯一ID（必须与主库不同）
server-id = 2

 开启中继日志
relay_log = /var/log/mysql/mysql-relay-bin

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

 超级用户仍可写（可选）
 super_read_only = 1

 记录binlog（用于级联复制）
log_bin = /var/log/mysql/mysql-bin.log

2.2 重启从库服务

sudo systemctl restart mysql

2.3 导入主库数据（如果做了备份）

mysql -u root -p < master_backup.sql

2.4 配置复制链路

-- 停止从库复制
STOP SLAVE;

-- 配置主库连接信息
CHANGE MASTER TO
MASTER_HOST = '192.168.1.10',
MASTER_USER = 'repl',
MASTER_PASSWORD = 'ReplPassword123!',
MASTER_LOG_FILE = 'mysql-bin.000001',  -- 主库的File
MASTER_LOG_POS = 154;                  -- 主库的Position

-- 启动从库复制
START SLAVE;

步骤 3：验证复制状态

3.1 检查从库复制状态

SHOW SLAVE STATUS\G

关键指标检查：

-- 查看简洁状态
SELECT 
    Slave_IO_State,
    Master_Host,
    Master_Log_File,
    Read_Master_Log_Pos,
    Relay_Log_File,
    Relay_Log_Pos,
    Slave_IO_Running,
    Slave_SQL_Running,
    Seconds_Behind_Master
FROM performance_schema.replication_applier_status_by_worker;

正常状态应该是：

• ​​Slave_IO_Running: Yes​​
• ​​Slave_SQL_Running: Yes​​
• ​​Seconds_Behind_Master: 0​​（或很小的数字）

3.2 测试数据同步

-- 在主库创建测试数据
CREATE DATABASE IF NOT EXISTS test_repl;
USE test_repl;
CREATE TABLE test_table (id INT, name VARCHAR(100));
INSERT INTO test_table VALUES (1, 'test data');

-- 在从库检查数据是否同步
SELECT * FROM test_repl.test_table;

四、复制模式详解

1. 基于语句的复制 (SBR)

binlog_format = STATEMENT

优点： 日志量小，节省带宽 缺点： 不确定性函数可能导致主从不一致

1. 基于行的复制 (RBR) - 推荐

binlog_format = ROW

优点： 数据一致性最好，最安全 缺点： 日志量较大

1. 混合模式复制 (MBR)

binlog_format = MIXED

优点： 兼顾性能和一致性 缺点： 仍有小概率不一致

五、高级复制架构

1. 一主多从架构

主库
    /  |  \
从库1 从库2 从库3

应用场景： 读写分离、负载均衡

1. 级联复制架构

主库 → 从库1(中继) → 从库2
               ↘ 从库3

优点： 减轻主库压力 配置： 在中继从库设置 ​​log_slave_updates = 1​​

1. 双主复制架构

主库A ↔ 主库B

注意： 需要处理自增主键冲突，配置自动故障切换

六、监控与维护

1. 关键监控指标

-- 查看复制延迟
SHOW SLAVE STATUS\G
-- 关注: Seconds_Behind_Master

-- 查看复制线程状态
SHOW PROCESSLIST;

-- 查看二进制日志状态
SHOW BINARY LOGS;
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';

1. 自动化监控脚本

!/bin/bash
 check_replication.sh

SLAVE_STATUS=$(mysql -u root -p -e "SHOW SLAVE STATUS\G")

IO_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_IO_Running:" | awk '{print $2}')
SQL_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_SQL_Running:" | awk '{print $2}')
SECONDS_BEHIND=$(echo "$SLAVE_STATUS" | grep "Seconds_Behind_Master:" | awk '{print $2}')

if [ "$IO_RUNNING" != "Yes" ] || [ "$SQL_RUNNING" != "Yes" ]; then
    echo "CRITICAL: Replication is broken!" | mail -s "MySQL Replication Alert" admin@company.com
fi

if [ "$SECONDS_BEHIND" -gt 60 ]; then
    echo "WARNING: Replication delay $SECONDS_BEHIND seconds" | mail -s "MySQL Replication Delay" admin@company.com
fi

1. 常见问题处理

复制中断修复

-- 查看错误信息
SHOW SLAVE STATUS\G

-- 跳过指定错误（谨慎使用）
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

-- 重新配置复制
STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO ...;
START SLAVE;

主从数据不一致修复

使用 pt-table-checksum 检查一致性
pt-table-checksum --replicate=test.checksums h=192.168.1.10

 使用 pt-table-sync 修复不一致
pt-table-sync --execute h=192.168.1.10 h=192.168.1.11

七、GTID 复制（推荐）

全局事务标识 (GTID) 优势：

• 自动位置跟踪
• 故障切换更简单
• 避免位置错误

GTID 配置：

主从库都配置
gtid_mode = ON
enforce_gtid_consistency = ON

GTID 复制配置：

-- 从库配置（无需指定文件和位置）
CHANGE MASTER TO
MASTER_HOST = '192.168.1.10',
MASTER_USER = 'repl',
MASTER_PASSWORD = 'ReplPassword123!',
MASTER_AUTO_POSITION = 1;

八、最佳实践总结

1. 配置检查清单

• ✅ 主从 server-id 不同
• ✅ 网络连通性正常
• ✅ 复制用户权限正确
• ✅ 数据初始状态一致
• ✅ 时区设置相同

1. 安全建议

• 专用复制用户，最小权限原则
• SSL 加密复制连接
• 定期监控复制状态
• 从库设置 ​​read_only = 1​​

1. 性能优化

• 使用 ROW 格式的二进制日志
• 适当增大 ​​binlog_cache_size​​
• 监控并及时清理二进制日志
• 考虑使用多线程复制

多线程复制配置
slave_parallel_workers = 4
slave_parallel_type = LOGICAL_CLOCK

通过以上完整的配置和优化，您可以构建稳定、高效的 MySQL 主从复制环境，为业务系统提供可靠的数据保障。 另外搭配便捷的80kmMYSQL备份工具，可定时备份、异地备份，MYSQL导出导入。可本地连接LINUX里的MYSQL，简单便捷。可以大大地提高工作效率喔。