MySQL高可用架构方案

MySQL 是业务系统中最常见的数据库，但用好它并不容易。索引设计、SQL 优化、事务处理都是日常开发中需要关注的点。本文从实际场景出发，讲常见问题和解决思路。

一、高可用目标

#

1.1 衡量指标

指标	说明	优秀标准
可用性	系统可用时间占比	99.99%（全年停机<1小时）
RTO	恢复时间目标	< 30秒
RPO	恢复点目标	0（不丢数据）

#

1.2 可用性等级

等级	可用性	年停机时间
2个9	99%	87.6小时
3个9	99.9%	8.76小时
4个9	99.99%	52.56分钟
5个9	99.999%	5.26分钟

二、主从复制架构

#

2.1 一主一从

┌─────────┐      复制      ┌─────────┐
│  Master │  ──────────>  │  Slave  │
│  (读写)  │               │  (只读)  │
└─────────┘               └─────────┘

特点：

• 手动切换
• 主库故障时，提升从库为主库
• 适合读多写少，对RTO要求不高

#

2.2 一主多从

         ┌──────────> ┌─────────┐
┌────────┐            │ Slave 1 │
│ Master │ ──────────>├─────────┤
└────────┘            │ Slave 2 │
         └──────────> │ Slave 3 │
                      └─────────┘

特点：

• 读扩展能力强
• 可专门分离出备份从库、报表从库
• 延迟问题需要关注

#

2.3 级联复制

Master ──> Slave1 ──> Slave2
  │
  └────> Slave3

适用：

• 从库数量很多时，减轻主库压力
• 跨地域复制

三、高可用方案对比

#

3.1 方案总览

方案	自动切换	数据一致性	复杂度	适用规模
主从+手动	否	异步可能丢	低	小型
MHA	是	异步可能丢	中	中型
MGR	是	强一致	中	中型
Orchestrator	是	异步可能丢	中	大型
MySQL Cluster	是	强一致	高	大型
云数据库	是	配置决定	低	各种规模

四、MHA（Master High Availability）

#

4.1 架构

┌─────────┐
│ Manager │  ← 监控和故障切换
└────┬────┘
     │
     ├──> Master（写）
     ├──> Slave1（读）
     ├──> Slave2（读）
     └──> Slave3（读/候选主库）

#

4.2 故障切换流程

1. Manager检测到Master不可达
2. 尝试SSH到Master，确认是否真的故障（避免网络抖动）
3. 选择最新的Slave作为新Master
4. 从其他Slave应用差异的binlog
5. 提升新Master
6. 其他Slave重新指向新Master
7. 发送告警

#

4.3 MHA配置

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

# Manager配置
cat /etc/mha/app1.cnf
[server default]
user=mha
password=password
ssh_user=root
repl_user=repl
repl_password=password
ping_interval=1
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change

[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

#

4.4 VIP漂移

使用Keepalived或脚本实现VIP自动漂移：

#!/bin/bash
# master_ip_failover

vip="192.168.1.100"
new_master="$1"

# 在新主库添加VIP
ssh $new_master "ip addr add $vip/24 dev eth0"

# 在旧主库移除VIP（如果可达）
ssh -o ConnectTimeout=3 $old_master "ip addr del $vip/24 dev eth0" 2>/dev/null

五、MGR（MySQL Group Replication）

#

5.1 架构

     ┌──────────┐
     │  Primary  │ ← 单主模式（默认）
     │  (读写)   │
     └────┬─────┘
          │  Paxos协议
    ┌─────┼─────┐
    ▼     ▼     ▼
┌─────┐┌─────┐┌─────┐
│Secondary│Secondary│Secondary│
│ (只读)  │ (只读)  │ (只读)  │
└─────┘└─────┘└─────┘

#

5.2 特点

• 基于Paxos协议，多数派确认
• 自动故障检测和切换
• 数据强一致性（不会丢数据）
• 支持单主和多主模式

#

5.3 MGR配置

# 每个节点配置
[mysqld]
server_id = 1
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_format = ROW
log_slave_updates = ON

# MGR插件
plugin_load_add = group_replication.so
transaction_write_set_extraction = XXHASH64
group_replication_group_name = "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
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

# 单主模式
group_replication_single_primary_mode = ON
group_replication_enforce_update_everywhere_checks = OFF

#

5.4 启动MGR

-- 第一个节点（引导组）
SET GLOBAL group_replication_bootstrap_group = ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group = OFF;

-- 其他节点加入
START GROUP_REPLICATION;

-- 查看组成员
SELECT * FROM performance_schema.replication_group_members;

-- 查看主节点
SELECT * FROM performance_schema.global_status 
WHERE variable_name = 'group_replication_primary_member';

#

5.5 MGR限制

• 仅支持InnoDB表
• 表必须有主键
• 不支持外键的级联操作
• 最大节点数：9个
• 网络要求高（低延迟）

六、Orchestrator

#

6.1 特点

• GitHub开源的MySQL高可用工具
• 可视化拓扑管理
• 支持自动和手动故障转移
• 支持预检查，降低误切换

#

6.2 架构

┌─────────────┐
│ Orchestrator │ ← Web UI + API
│   (RAFT)    │
└──────┬──────┘
       │
  ┌────┴────┐
  ▼         ▼
Master    Slave

#

6.3 核心功能

• 自动发现：自动发现MySQL拓扑
• 可视化：Web界面展示复制拓扑
• 故障转移：支持自动和手动
• 钩子：支持自定义脚本（切换后执行）

七、ProxySQL读写分离

#

7.1 架构

App ──> ProxySQL ──> Master（写）
            │
            ├──> Slave1（读）
            ├──> Slave2（读）
            └──> Slave3（读）

#

7.2 配置

-- 添加后端服务器
INSERT INTO mysql_servers(hostgroup_id, hostname, port) 
VALUES 
(10, 'master', 3306),  -- 写组
(20, 'slave1', 3306),  -- 读组
(20, 'slave2', 3306),
(20, 'slave3', 3306);

-- 配置读写规则
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, 
    destination_hostgroup, apply) 
VALUES 
(1, 1, '^SELECT.*FOR UPDATE', 10, 1),
(2, 1, '^SELECT', 20, 1),
(3, 1, '.*', 10, 1);

-- 加载配置
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

八、云数据库高可用

#

8.1 阿里云RDS

版本	架构	RTO
基础版	单节点	数分钟
高可用版	主从 + 自动切换	30秒内
集群版	一主多从	30秒内
三节点版	MGR-like	30秒内

#

8.2 腾讯云CDB

• 自动故障切换
• 支持跨可用区部署
• 只读实例自动扩展

九、故障切换实践

#

9.1 切换前检查

-- 1. 检查复制延迟
SHOW SLAVE STATUS \G;
-- Seconds_Behind_Master = 0

-- 2. 检查数据一致性
pt-table-checksum --host=master --host=slave

-- 3. 检查从库状态
SHOW PROCESSLIST;
-- 确保没有长时间查询

#

9.2 切换步骤

# 1. 暂停应用写入（或切到只读）

# 2. 确认主从数据一致

# 3. 停止从库复制
STOP SLAVE;

# 4. 重置从库（变为独立库）
RESET SLAVE ALL;

# 5. 开启新主库读写
SET GLOBAL read_only = OFF;

# 6. 应用指向新主库

# 7. 旧主库修复后作为从库加入
CHANGE MASTER TO ...
START SLAVE;

#

9.3 切换后验证

-- 验证新主库
SHOW MASTER STATUS;
SHOW PROCESSLIST;

-- 验证从库连接
SHOW SLAVE STATUS \G;
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0

十、高可用设计建议

#

10.1 架构选型

场景	推荐方案
创业公司/小团队	云数据库
中小规模	MHA + Keepalived
中等规模	MGR（单主）
大规模	Orchestrator + ProxySQL
金融级	MGR + 多副本

#

10.2 监控告警

-- 复制延迟监控
SELECT 
    MAX(Seconds_Behind_Master) AS max_delay
FROM information_schema.processlist
WHERE command = 'Binlog Dump';

-- 告警：delay > 10秒

#

10.3 灾备设计

同城双中心：
  中心A（主）      中心B（备）
  ┌──────┐        ┌──────┐
  │Master│───────>│Slave │
  └──────┘  同步   └──────┘

异地灾备：
  中心A ──> 中心B（同城）──> 中心C（异地）
  同步/半同步    异步复制

十一、总结

方案	自动切换	一致性	复杂度	推荐
主从手动	否	异步	低	不推荐生产
MHA	是	异步	中	传统方案
MGR	是	强一致	中	推荐
Orchestrator	是	异步	中	大规模推荐
云数据库	是	可选	低	快速上线

高可用设计的核心原则：

1. 自动故障检测和切换
2. 数据一致性优先
3. 完善的监控和告警
4. 定期的故障演练
5. 文档化操作流程

核心要点

1. 索引设计的原则：最左前缀原则、避免索引列参与计算、选择合适的索引类型

2. SQL 优化的方法：使用 EXPLAIN 分析执行计划、避免 SELECT *、优化 JOIN

3. 事务隔离级别的选择：根据业务需求选择合适的级别

4. 常见的锁问题：行锁、表锁、死锁的处理

总结

MySQL 优化是一个持续的过程，需要结合业务特点和数据量来调整。在实际项目中，定期分析慢查询日志、优化索引、调整配置参数，都能提升数据库性能。