MySQL主从复制与高可用架构实战指南（2026版）

MySQL作为全球最流行的开源关系型数据库，在高并发场景下的高可用架构设计至关重要。本文从基础主从复制到Group Replication集群，提供一套完整的MySQL高可用方案。

---

一、架构选型：不同场景的最佳方案

方案	延迟	强一致性	自动切换	适合场景
异步主从	秒级	否	否	读写分离、备份
半同步复制	毫秒级	基本	需配合	电商、交易系统
Group Replication	毫秒级	是	是	金融级、强一致性
InnoDB Cluster	毫秒级	是	是	生产首选，自动运维
ProxySQL + 多主	毫秒级	取决于后端	是	读写分离最佳

二、基础主从复制部署（MySQL 8.4+）

2.1 主库配置

# /etc/my.cnf.d/master.cnf
[mysqld]
server_id = 1
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 1G
binlog_do_db = myapp       # 只复制指定库
# binlog_ignore_db = mysql # 忽略mysql库

# 推荐使用ROW格式（MySQL 8.0+默认），比STATEMENT更安全
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

systemctl restart mysqld

2.2 创建复制用户

mysql -u root -p

CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'Repl@2026!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;

# 查看主库状态
SHOW MASTER STATUS\G
# 记录 File 和 Position，从库需要

2.3 从库配置

# /etc/my.cnf.d/slave.cnf
[mysqld]
server_id = 2
log_bin = mysql-bin
binlog_format = ROW
read_only = 1          # 从库只读
relay_log = relay-bin
log_slave_updates = ON # 级联复制时需要
gtid_mode = ON
enforce_gtid_consistency = ON

# 半同步从库（对应主库半同步配置）
plugin_load = "rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_slave_enabled = 1

systemctl restart mysqld

2.4 配置复制链路

# 在从库执行
CHANGE MASTER TO
    MASTER_HOST='192.168.1.10',
    MASTER_USER='repl',
    MASTER_PASSWORD='Repl@2026!',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=123456789,
    MASTER_CONNECT_RETRY=10,
    MASTER_RETRY_COUNT=60;

# 使用GTID方式（推荐）
CHANGE MASTER TO
    MASTER_HOST='192.168.1.10',
    MASTER_USER='repl',
    MASTER_PASSWORD='Repl@2026!',
    MASTER_AUTO_POSITION=1;

START SLAVE;

# 检查复制状态
SHOW SLAVE STATUS\G
# 重点关注：
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
# Seconds_Behind_Master: 0

三、MHA + ProxySQL 高可用方案

3.1 ProxySQL部署

# 安装ProxySQL 2.x
cat > /etc/yum.repos.d/proxysql.repo << EOF
[proxysql]
name=ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/centos/9
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/centos/9/repodata/repomd.xml.key
EOF

yum install proxysql -y
systemctl enable proxysql
systemctl start proxysql

3.2 配置ProxySQL读写分离

mysql -u admin -padmin -h 127.0.0.1 -P 6032

# 添加后端MySQL节点
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections) VALUES
(0, '192.168.1.10', 3306, 100, 1000),   # 写节点 (hostgroup 0)
(1, '192.168.1.11', 3306, 100, 2000),   # 读节点 (hostgroup 1)
(1, '192.168.1.12', 3306, 100, 2000);   # 读节点 (hostgroup 1)

# 配置读写分离规则
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES
(1, 1, '^SELECT.*FOR UPDATE', 0, 1),     # SELECT FOR UPDATE 走写库
(2, 1, '^SELECT', 1, 1),                  # 普通SELECT走读库
(3, 1, '^INSERT|^UPDATE|^DELETE', 0, 1); # 写操作走写库

# 配置监控用户
INSERT INTO mysql_users (username, password, default_hostgroup) VALUES
('myapp', 'App@2026!', 0);

# 应用配置
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

3.3 MHA自动故障切换

# 安装MHA
yum install -y mha4mysql-manager mha4mysql-node

# /etc/mha/myapp.cnf
[server default]
manager_workdir=/var/log/mha/myapp
manager_log=/var/log/mha/myapp/manager.log
user=root
password=Root@2026!
ssh_user=root
repl_user=repl
repl_password=Repl@2026!
ping_interval=3
secondary_check_script=masterha_secondary_check -s 192.168.1.11 -s 192.168.1.12
master_binlog_dir=/var/lib/mysql

[server1]
hostname=192.168.1.10
candidate_master=1

[server2]
hostname=192.168.1.11
candidate_master=1

[server3]
hostname=192.168.1.12
no_master=1  # 不参与主选举，只做Slave

# 检测SSH免密
masterha_check_ssh --conf=/etc/mha/myapp.cnf

# 检测复制状态
masterha_check_repl --conf=/etc/mha/myapp.cnf

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

# 手动故障切换测试
masterha_master_switch --conf=/etc/mha/myapp.cnf --master_state=dead

四、MySQL InnoDB Cluster（MySQL Shell方案）

4.1 安装MySQL Shell

yum install mysql-shell -y

# 创建集群
mysqlsh root@192.168.1.10:3306

# 检查实例配置
dba.checkInstanceConfiguration('root@192.168.1.10:3306')

# 配置实例（生产环境）
dba.configureInstance('root@192.168.1.10:3306', {password: 'Root@2026!'})

# 创建集群
var cluster = dba.createCluster('mycluster', {multiMaster: false})
# multiMaster: true 为多主模式，false为单主模式

# 添加节点
cluster.addInstance('root@192.168.1.11:3306')
cluster.addInstance('root@192.168.1.12:3306')

# 查看集群状态
cluster.status()
cluster.describe()

4.2 MySQL Router配置

# 安装Router
yum install mysql-router -y

# /etc/mysqlrouter/mysqlrouter.conf
[DEFAULT]
logging_folder=/var/log/mysqlrouter
runtime_folder=/var/run/mysqlrouter

[logger]
level = INFO

[routing:read_write]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://mycluster/default?role=PRIMARY
routing_strategy=first-available
protocol=classic

[routing:read_only]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://mycluster/default?role=SECONDARY
routing_strategy=round-robin
protocol=classic

[metadata_cache:mycluster]
bootstrap_server_addresses=mysql://192.168.1.10:3306,mysql://192.168.1.11:3306,mysql://192.168.1.12:3306
user=mysql_router
metadata_cluster=mycluster
ttl=5

systemctl enable mysqlrouter
systemctl start mysqlrouter

# 应用连接
# 写连接: mysql -u myapp -p -h 127.0.0.1 -P 6446
# 读连接: mysql -u myapp -p -h 127.0.0.1 -P 6447

五、日常运维与监控

5.1 复制状态监控

#!/bin/bash
# check_replication.sh
MYSQL_USER="root"
MYSQL_PASS="Root@2026!"

mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW SLAVE STATUS\G" | grep -E "Slave_(IO|SQL)_Running|Seconds_Behind_Master"

# 如果复制中断则触发告警
IO_STATUS=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW SLAVE STATUS\G" | grep "Slave_IO_Running:" | awk '{print $2}')
SQL_STATUS=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW SLAVE STATUS\G" | grep "Slave_SQL_Running:" | awk '{print $2}')

if [ "$IO_STATUS" != "Yes" ] || [ "$SQL_STATUS" != "Yes" ]; then
    echo "CRITICAL: 复制中断！"
    # 发送告警
    # curl -X POST https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=KEY -d '{"msgtype":"text","text":{"content":"MySQL复制中断！"}}'
fi

5.2 主从延迟优化

# 并行复制（MySQL 8.0+）
# 在从库设置
# 基于数据库并行
slave_parallel_workers = 8
slave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = 1

# 调整主库binlog同步策略
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

# 调整从库
relay_log_recovery = 1
skip_slave_start = 0

5.3 备份策略

# 从库不阻塞业务的逻辑备份
mysqldump -u root -p \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --all-databases \
    --flush-logs \
    --master-data=2 \
    --delete-master-logs \
    | gzip > /backup/mysql/full_$(date +%Y%m%d_%H%M%S).sql.gz

# 定期purge旧的binlog
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

# 使用XtraBackup物理备份（推荐，适合大库）
xtrabackup --backup --target-dir=/backup/mysql/inc/$(date +%Y%m%d) \
    --user=root --password=Root@2026! \
    --slave-info \
    --safe-slave-backup

xtrabackup --prepare --target-dir=/backup/mysql/inc/$(date +%Y%m%d)

六、常见故障处理

6.1 复制中断：1062错误（主键冲突）

# 跳过单个错误
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

# 跳过所有1062错误（需谨慎）
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

6.2 主库宕机恢复

# 1. 在最新从库执行
STOP SLAVE;
RESET SLAVE ALL;

# 2. 提升为主库
SET GLOBAL read_only = 0;

# 3. 查看GTID
SHOW MASTER STATUS\G

# 4. 其他从库重新指向新主库
CHANGE MASTER TO
    MASTER_HOST='192.168.1.11',
    MASTER_USER='repl',
    MASTER_PASSWORD='Repl@2026!',
    MASTER_AUTO_POSITION=1;
START SLAVE;

6.3 延迟过大处理

# 查看延迟原因
SHOW SLAVE STATUS\G
# 检查：Seconds_Behind_Master

# 常见原因：
# 1. 从库硬件性能不足 → 升级从库配置
# 2. 大事务 → 拆分大事务为小批次
# 3. 从库有频繁查询 → 增加更多从库分担

# 快速追赶：临时关闭从库查询
SET GLOBAL read_only = 1;  # 确保从库只读
STOP SLAVE;

# 使用并行复制追赶
SET GLOBAL slave_parallel_workers = 16;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';

START SLAVE;

七、安全建议

# 1. 复制链路的SSL加密
CHANGE MASTER TO
    MASTER_SSL=1,
    MASTER_SSL_CA='/etc/mysql/ssl/ca.pem',
    MASTER_SSL_CERT='/etc/mysql/ssl/client-cert.pem',
    MASTER_SSL_KEY='/etc/mysql/ssl/client-key.pem';

# 2. 限制复制用户权限
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%' REQUIRE SSL;

# 3. 数据库审计日志
# 在my.cnf启用
audit_log=ON
audit_log_format=JSON
audit_log_file=/var/log/mysql/audit.log
audit_log_policy=ALL

# 4. 最小权限原则
# 应用账号只给DML权限
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'myapp'@'%';

---

MySQL高可用没有银弹，需要根据业务对一致性、延迟、成本的要求选择合适方案。建议生产环境使用 InnoDB Cluster + MySQL Router 组合，兼顾易用性和可靠性。

🔗 相关文章：ELK日志分析部署 | Nginx安全配置 | Zabbix监控部署