MySQL主从复制配置实战指南

2025年3月15日 · 阅读约需33分钟

前言

MySQL主从复制是实现数据库高可用、读写分离、数据备份的核心技术。本文将从原理到实战，详细讲解MySQL主从复制的配置、监控、故障处理和优化，帮助你构建稳定可靠的数据库架构。

本文基于MySQL 5.7/8.0版本，适用于CentOS 7/8、Ubuntu 18.04/20.04等主流发行版。

一、主从复制原理

1.1 复制原理概述

MySQL主从复制基于二进制日志（binlog）实现，包含三个核心线程：

Master（主库）：将数据变更记录到二进制日志（Binlog）
IO线程（从库）：连接主库，请求并拉取Binlog，写入中继日志（Relay Log）
SQL线程（从库）：读取中继日志，重放SQL，实现数据同步

复制流程：

Master → Binlog → IO线程 → Relay Log → SQL线程 → Slave

1.2 复制模式

1. 异步复制（默认）

主库写入Binlog后立即返回成功
不等待从库确认
性能最高，但存在数据丢失风险

2. 半同步复制

主库等待至少一个从库收到Binlog并写入中继日志
数据安全性高，性能略有下降
MySQL 5.7+支持

3. 组复制（MGR）

MySQL 8.0+支持
基于Paxos协议
强一致性，支持多主写入

1.3 复制格式

1. STATEMENT（基于语句）

记录SQL语句
日志量小
非确定性函数（如UUID()）可能导致数据不一致

2. ROW（基于行，推荐）

记录每行数据的变化
数据一致性高
日志量较大

3. MIXED（混合模式）

自动选择STATEMENT或ROW
一般场景推荐

二、环境准备

2.1 服务器规划

角色	IP地址	操作系统	MySQL版本
Master	192.168.1.10	CentOS 7	5.7
Slave1	192.168.1.11	CentOS 7	5.7
Slave2	192.168.1.12	CentOS 7	5.7

2.2 安装MySQL 5.7

# 配置Yum源
yum install -y https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

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

# 启动并设置开机自启
systemctl start mysqld
systemctl enable mysqld

# 获取初始密码
grep 'temporary password' /var/log/mysqld.log

# 登录并修改密码
mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Root@123456';

2.3 防火墙配置

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

# 关闭SELinux（或配置策略）
setenforce 0
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config

三、主库配置

3.1 修改配置文件

# /etc/my.cnf
[mysqld]
# 基础配置
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0

# 主库唯一ID（必须唯一）
server-id=1

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

# binlog格式（推荐ROW）
binlog_format=ROW

# 二进制日志保留天数
expire_logs_days=7

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

# 忽略的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

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

# 半同步复制配置（可选）
# plugin-load=rpl_semi_sync_master=semisync_master.so
# rpl_semi_sync_master_enabled=1
# rpl_semi_sync_master_timeout=1000

3.2 重启MySQL生效

systemctl restart mysqld

3.3 创建复制用户

-- 登录MySQL
mysql -uroot -p

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

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

-- 刷新权限
FLUSH PRIVILEGES;

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

输出示例：

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

记录下File和Position的值，配置从库时需要使用

3.4 数据备份（已有数据场景）

如果主库已有数据，需要先备份并导入从库：

# 锁表（防止数据写入）
mysql -uroot -p -e "FLUSH TABLES WITH READ LOCK;"

# 备份数据库（新窗口执行）
mysqldump -uroot -p --all-databases --single-transaction --master-data=2 > all_db.sql

# 解锁表
mysql -uroot -p -e "UNLOCK TABLES;"

四、从库配置

4.1 修改配置文件

# /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0

# 从库唯一ID（必须唯一，不能与主库相同）
server-id=2

# 开启中继日志
relay-log=relay-bin

# 只读模式（普通用户）
read_only=1

# GTID模式（与主库一致）
gtid_mode=ON
enforce_gtid_consistency=ON

# 开启中继日志自动恢复
relay_log_recovery=1

# 半同步复制（可选）
# plugin-load=rpl_semi_sync_slave=semisync_slave.so
# rpl_semi_sync_slave_enabled=1

4.2 重启MySQL

systemctl restart mysqld

4.3 导入备份数据（如有）

# 将备份文件复制到从库
scp all_db.sql root@192.168.1.11:/root/

# 在从库导入
mysql -uroot -p < all_db.sql

4.4 配置主从连接

方式一：传统方式（基于binlog位置）

-- 登录从库MySQL
mysql -uroot -p

-- 停止复制（如果已配置）
STOP SLAVE;

-- 配置主库连接
CHANGE MASTER TO
  MASTER_HOST='192.168.1.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='Repl@123456',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;

-- 启动复制
START SLAVE;

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

方式二：GTID方式（推荐）

STOP SLAVE;

CHANGE MASTER TO
  MASTER_HOST='192.168.1.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='Repl@123456',
  MASTER_AUTO_POSITION=1;

START SLAVE;

SHOW SLAVE STATUS\G

4.5 验证复制状态

执行 SHOW SLAVE STATUS\G，重点关注以下两个参数：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

两个都为Yes表示复制正常运行！

五、多从库配置

配置第二个从库（192.168.1.12）：

# /etc/my.cnf
[mysqld]
server-id=3  # 注意ID唯一
relay-log=relay-bin
read_only=1
gtid_mode=ON
enforce_gtid_consistency=ON

-- 从库2执行
STOP SLAVE;

CHANGE MASTER TO
  MASTER_HOST='192.168.1.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='Repl@123456',
  MASTER_AUTO_POSITION=1;

START SLAVE;

SHOW SLAVE STATUS\G

六、主从复制监控

6.1 常用监控命令

-- 主库状态
SHOW MASTER STATUS;
SHOW BINARY LOGS;
SHOW MASTER LOGS;

-- 从库状态
SHOW SLAVE STATUS\G

-- 查看复制进程
SHOW PROCESSLIST;

-- 查看GTID执行情况
SHOW GLOBAL VARIABLES LIKE 'gtid%';
SHOW GLOBAL STATUS LIKE 'gtid%';

6.2 关键监控指标

指标	说明	正常值
Slave_IO_Running	IO线程状态	Yes
Slave_SQL_Running	SQL线程状态	Yes
Seconds_Behind_Master	延迟秒数	0（接近0）
Last_IO_Error	IO线程错误	空
Last_SQL_Error	SQL线程错误	空
Relay_Log_Space	中继日志大小	稳定

6.3 监控脚本

#!/bin/bash
# mysql_replication_check.sh

MYSQL_USER="root"
MYSQL_PASS="Root@123456"
SLAVE_STATUS=$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} -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}')
BEHIND_MASTER=$(echo "${SLAVE_STATUS}" | grep "Seconds_Behind_Master:" | awk '{print $2}')

if [ "${IO_RUNNING}" != "Yes" ] || [ "${SQL_RUNNING}" != "Yes" ]; then
    echo "ERROR: MySQL replication is broken!"
    echo "Slave_IO_Running: ${IO_RUNNING}"
    echo "Slave_SQL_Running: ${SQL_RUNNING}"
    exit 1
fi

if [ "${BEHIND_MASTER}" != "0" ] && [ "${BEHIND_MASTER}" != "NULL" ]; then
    if [ ${BEHIND_MASTER} -gt 300 ]; then
        echo "WARNING: MySQL replication lag is ${BEHIND_MASTER} seconds"
        exit 2
    fi
fi

echo "OK: MySQL replication is running normally, lag: ${BEHIND_MASTER}s"
exit 0

七、常见故障处理

7.1 主键冲突（1062错误）

错误信息：

Last_SQL_Error: Error 'Duplicate entry 'xxx' for key 'PRIMARY''

解决方案：

-- 方案1：跳过该事务（谨慎使用）
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;

-- 方案2：GTID模式跳过指定事务
STOP SLAVE;
SET GTID_NEXT='aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:123';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';
START SLAVE;

7.2 主库Binlog丢失

错误信息：

Got fatal error 1236 from master when reading data from binary log

解决方案：

重新做主从
使用GTID自动定位

7.3 从库中继日志损坏

错误信息：

Relay log read failure: Could not parse relay log event entry

解决方案：

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

7.4 复制延迟过大

可能原因：

主库写入压力大
从库性能不足
大事务执行
网络延迟

优化方案：

# 从库配置优化
[mysqld]
# 并行复制（MySQL 5.7+）
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=4

# 关闭从库binlog（不需要级联复制时）
log_bin=0

八、读写分离实现

8.1 应用层实现

// Java示例：动态切换数据源
public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DynamicDataSourceContextHolder.getDataSourceType();
    }
}

// 使用：写操作走主库，读操作走从库
@DataSource("master")
public void insert(User user) { ... }

@DataSource("slave")
public User selectById(Long id) { ... }

8.2 中间件实现（ProxySQL）

# 安装ProxySQL
yum install -y proxysql

# 配置监控用户和读写分离
# 主库：写组
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (10, '192.168.1.10', 3306);

# 从库：读组
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, '192.168.1.11', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, '192.168.1.12', 3306);

# 读写分离规则
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup) 
VALUES (1, 1, '^SELECT.*FOR UPDATE$', 10),
       (2, 1, '^SELECT', 20);

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;

九、主从切换

9.1 手动切换流程

停止应用写入

确认从库追平主库

-- 从库执行，直到Seconds_Behind_Master=0
SHOW SLAVE STATUS\G

提升从库为新主库

STOP SLAVE;
RESET MASTER;
-- 配置可写
SET GLOBAL read_only=0;

其他从库指向新主库

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='新主库IP', ...;
START SLAVE;

应用切换到新主库

9.2 MGR高可用集群

MySQL Group Replication提供自动故障转移：

# my.cnf配置
plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.1.10:33061"
group_replication_group_seeds= "192.168.1.10:33061,192.168.1.11:33061,192.168.1.12:33061"
group_replication_bootstrap_group=off

十、性能优化

10.1 主库优化

[mysqld]
# 同步提交（性能与安全平衡）
sync_binlog=1
innodb_flush_log_at_trx_commit=1

# Binlog缓存
binlog_cache_size=1M
max_binlog_cache_size=8M

# 组提交
binlog_group_commit_sync_delay=100
binlog_group_commit_sync_no_delay_count=10

10.2 从库优化

[mysqld]
# 并行复制
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=8

# 中继日志优化
relay_log_info_repository=TABLE
master_info_repository=TABLE

# 从库不刷binlog（无级联复制时）
log_slave_updates=0

10.3 网络优化

# 增大TCP缓冲区
echo 'net.core.wmem_max=16777216' >> /etc/sysctl.conf
echo 'net.core.rmem_max=16777216' >> /etc/sysctl.conf
sysctl -p

总结

MySQL主从复制是构建高可用数据库架构的基础。本文涵盖了：

复制原理：三种复制模式、三种日志格式
环境搭建：MySQL安装、主从配置、GTID模式
监控运维：状态监控、延迟告警、自动化脚本
故障处理：常见错误及解决方案
进阶应用：读写分离、主从切换、MGR集群
性能优化：主从库配置、网络调优

通过合理配置主从复制，可以实现数据备份、读写分离、故障转移，显著提升数据库的可用性和扩展性。

参考资料：

MySQL官方文档：https://dev.mysql.com/doc/refman/5.7/en/replication.html
MySQL高可用最佳实践：https://www.percona.com/blog/category/mysql-high-availability/
ProxySQL官方文档：https://proxysql.com/documentation/