【数据库知识】Mysql进阶-高可用MHA（Master High Availability）方案

集群部署模式下的高可用方案

以下是 MySQL 8.x 高可用集群的部署方案及原理详解，以 InnoDB Cluster（基于MySQL Group Replication和MySQL Router）为核心实现：

一、高可用架构原理

1. 核心组件

2. 故障切换流程

二、详细部署步骤 (3节点集群)

1. 环境准备

# 所有节点执行
systemctl stop firewalld
setenforce 0
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
yum install -y mysql-server mysql-shell mysql-router

2. 节点配置（以 node1 为例）

# /etc/my.cnf (所有节点)
[mysqld]
# 通用配置
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# Group Replication 配置
server_id = 1  # 每个节点唯一
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_checksum = NONE
log_bin = mysql-bin
log_slave_updates = ON
binlog_format = ROW
master_info_repository = TABLE
relay_log_info_repository = TABLE
transaction_write_set_extraction = XXHASH64

# Group Replication 核心参数
plugin_load_add = 'group_replication.so'
group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"  # UUID
group_replication_start_on_boot = OFF
group_replication_local_address = "node1:33061"  # 内部通信端口
group_replication_group_seeds = "node1:33061,node2:33061,node3:33061"

3. 初始化集群

# 在第一个节点(node1)执行
mysqlsh
\connect root@node1
dba.configureInstance('root@node1', {password: 'StrongPass123!'})
dba.createCluster('myCluster')
\sql SET PERSIST group_replication_bootstrap_group=ON;
\js cluster = dba.getCluster()
cluster.addInstance('root@node2:3306', {password: 'StrongPass123!'})
cluster.addInstance('root@node3:3306', {password: 'StrongPass123!'})

4. 部署MySQL Router

# 任一节点执行
mysqlrouter --bootstrap root@node1:3306 --directory /opt/mysqlrouter --user=mysql
systemctl start mysqlrouter

三、关键配置说明

1. Group Replication 参数

2. MySQL Router 配置

# /opt/mysqlrouter/mysqlrouter.conf
[DEFAULT]
logging_folder = /var/log/mysqlrouter
runtime_folder = /var/run/mysqlrouter
data_folder = /opt/mysqlrouter/data

[routing:rw]
bind_address = 0.0.0.0
bind_port = 6446
destinations = metadata_cache://myCluster/default?role=PRIMARY

[routing:ro]
bind_address = 0.0.0.0
bind_port = 6447
destinations = metadata_cache://myCluster/default?role=SECONDARY

四、故障切换机制详解

1. 故障检测

• 心跳机制：节点间每1秒发送心跳包

• 超时判定：连续5次未收到响应视为节点故障（默认5秒）

2. 切换流程

1. 故障确认：剩余节点通过多数派投票确认主节点失效

2. 新主选举：基于以下优先级自动选举：
   • 节点权重（group_replication_member_weight）

   • 数据最新的节点

   • 启动顺序

3. 路由切换：MySQL Router 2秒内更新路由表

3. 网络分区处理

• 脑裂防护：采用 group_replication_unreachable_majority_timeout（默认3小时）

• 恢复策略：

# 被隔离节点恢复后执行
STOP GROUP_REPLICATION;
START GROUP_REPLICATION;

五、验证与监控

1. 集群状态检查

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

-- 检查同步延迟
SHOW STATUS LIKE 'group_replication%delay';

2. 故障模拟测试

# 在Primary节点执行
systemctl stop mysqld

# 观察自动切换
mysqlsh -e "dba.getCluster().status()"

3. 监控指标
   | 指标 | 健康标准 |
   |-------------------------------|----------------------------|
   | group_replication_primary | 显示有效IP地址 |
   | group_replication_delay | 延迟 < 100ms |
   | group_replication_members | 存活节点数 ≥ (N/2 + 1) |

六、生产环境注意事项

1. 网络要求：
   • 节点间RTT < 2ms

   • 使用10Gbps以上网络

   • 禁用UDP协议的ECN功能（避免数据包乱序）

2. 安全配置：

   -- 创建专用账户
   CREATE USER 'cluster_admin'@'%' IDENTIFIED BY 'SecurePass!';
   GRANT ALL PRIVILEGES ON *.* TO 'cluster_admin'@'%' WITH GRANT OPTION;
   
   -- 启用SSL加密
   SET GLOBAL group_replication_ssl_mode = "REQUIRED";
   

3. 备份策略：

   # 物理备份建议
   mysqlbackup --user=root --password=xxx --backup-dir=/backup --compress \
   --include-databases="mydb" --ssl-mode=REQUIRED
   

通过以上方案，MySQL集群可在30秒内完成自动故障转移，RPO=0（零数据丢失），RTO<60秒，满足金融级高可用要求。

主从模式下的高可用方案

以下是MySQL主从模式下实现高可用性的完整方案及原理详解，包含多种主流实现方式：

一、高可用架构核心原理

关键机制：

1. 故障检测：通过心跳检测（2-5秒）判断主库状态
2. 自动切换：VIP漂移或连接重定向到新主
3. 数据一致性：确保切换后新主数据完整性
4. 脑裂防护：使用多数派确认机制

二、主流高可用方案

方案1：MHA（Master High Availability）

部署步骤：

1. 环境准备（所有节点）：

# 安装Perl模块
yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager

# 下载MHA
wget https://github.com/yoshinorim/mha4mysql-manager/archive/refs/tags/v0.58.tar.gz
tar zxvf v0.58.tar.gz && cd mha4mysql-manager-0.58
perl Makefile.PL && make && make install

2. 配置SSH互信：

ssh-keygen -t rsa
ssh-copy-id -i ~/.ssh/id_rsa.pub root@master
ssh-copy-id -i ~/.ssh/id_rsa.pub root@slave1

3. 配置文件：

# /etc/mha/app1.cnf
[server default]
manager_workdir=/var/log/mha/app1
manager_log=/var/log/mha/app1/manager.log
ssh_user=root
repl_user=repl
repl_password=Repl@1234

[server1]
hostname=master
candidate_master=1

[server2]
hostname=slave1
no_master=1

4. 启动管理节点：

masterha_manager --conf=/etc/mha/app1.cnf

切换过程：

1. 检测主库不可达（连续3次失败）
2. 确认从库数据一致性
3. 提升最优先从库为新主
4. 修改其他从库指向新主
5. 通知应用VIP切换

方案2：Keepalived + VIP

配置示例：

1. Master节点keepalived.conf：

vrrp_script chk_mysql {
    script "/etc/keepalived/check_mysql.sh"
    interval 2
    weight 2
}

vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.200/24
    }
    track_script {
        chk_mysql
    }
}

2. 健康检查脚本：

#!/bin/bash
# /etc/keepalived/check_mysql.sh
if ! mysql -uroot -p$password -e "SELECT 1" >/dev/null 2>&1; then
    systemctl stop keepalived
    exit 1
fi
exit 0

切换特点：
• VIP漂移时间：3-5秒

• 需配合半同步复制使用

• 需要人工介入修复原主

方案3：ProxySQL中间件

架构实现：

关键配置：

-- 添加后端节点
INSERT INTO mysql_servers(hostgroup_id, hostname, port) 
VALUES 
(10, 'master', 3306),
(20, 'slave1', 3306),
(20, 'slave2', 3306);

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

-- 设置健康检查
UPDATE global_variables SET variable_value='2000' 
WHERE variable_name='mysql-monitor_slave_lag_when_null';

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

自动切换流程：

1. 每1秒执行SELECT @@read_only检查
2. 标记连续失败节点为OFFLINE_SOFT
3. 自动将流量路由到新主
4. 通过Galera或Group Replication同步配置

三、数据一致性保障

1. 半同步复制配置

# master配置
[mysqld]
plugin_load = "rpl_semi_sync_master=semisync_master.so"
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 1000  # 1秒超时

# slave配置
[mysqld]
plugin_load = "rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_slave_enabled = 1

2. GTID模式启用

-- 所有节点执行
SET GLOBAL gtid_mode = ON;
SET GLOBAL enforce_gtid_consistency = ON;

-- my.cnf配置
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON

四、异常处理机制

1. 脑裂防护措施

# 切换前状态检查脚本
CURRENT_MASTER=$(mysql -h vip -P 3306 -uroot -p$pass -e "SELECT @@server_id" -sN)
if [ "$CURRENT_MASTER" != "$EXPECTED_MASTER" ]; then
    echo "Split brain detected!" >&2
    exit 1
fi

2. 数据补偿机制

-- 使用pt-table-checksum校验数据一致性
pt-table-checksum --replicate=test.checksums u=root,p=password,h=vip

-- 使用pt-table-sync修复差异
pt-table-sync --execute --sync-to-master h=slave1,D=test,t=users

五、监控指标建议

六、方案对比选型

推荐组合：
金融场景：MHA + 半同步复制 + ProxySQL
互联网场景：Orchestrator + 异步GTID + 读写中间件

通过以上方案，MySQL主从架构可实现99.95%以上的可用性，RTO（恢复时间目标）控制在30秒以内，RPO（数据恢复点目标）趋近于零。

关联知识

【分布式技术】分布式共识算法Paxos