MHA

一、HAProxy 安装步骤

1. 安装 HAProxy（Linux 系统）

Ubuntu/Debian

sudo apt update
sudo apt install -y haproxy
sudo systemctl enable haproxy

CentOS/RHEL

sudo yum install -y haproxy
sudo systemctl enable haproxy

验证安装

haproxy -v
# 应输出类似：HAProxy version 2.4.0

二、MySQL 负载均衡配置

1. 基础配置（/etc/haproxy/haproxy.cfg）

global
    log /dev/log local0
    maxconn 4096
    user haproxy
    group haproxy
    daemon

defaults
    log global
    mode tcp
    timeout connect 5000ms
    timeout client 50000ms
    timeout server 50000ms

listen mysql-cluster
    bind *:3306
    mode tcp
    balance roundrobin
    option mysql-check user haproxy_check
    server mysql-master 192.168.1.160:3306 check
    server mysql-slave1 192.168.1.20:3306 check
    server mysql-slave2 192.168.1.29:3306 check

2. 关键配置解析

三、MySQL 健康检查配置

1. 在MySQL主从服务器上创建监控用户

-- 在主库执行
CREATE USER 'haproxy_check'@'%' IDENTIFIED BY 'Check@123';
FLUSH PRIVILEGES;

2. 增强健康检查配置

listen mysql-cluster
    ...
    option mysql-check user haproxy_check
    check inter 2000 rise 2 fall 3
    # 每2秒检查一次，成功2次标记为UP，失败3次标记为DOWN

四、读写分离配置（可选）

1. 识别读/写请求

frontend mysql-front
    bind *:3306
    mode tcp
    default_backend mysql-write

  backend mysql-write
    mode tcp
    server mysql-master 192.168.1.160:3306 check

  backend mysql-read
    mode tcp
    balance roundrobin
    server mysql-slave1 192.168.1.20:3306 check
    server mysql-slave2 192.168.1.29:3306 check

五、启动与验证

1. 启动HAProxy

sudo systemctl restart haproxy
sudo systemctl status haproxy

2. 验证负载均衡

# 查看HAProxy统计页面（需先配置）
curl http://localhost:8404/stats

# 测试MySQL连接
mysql -u appuser -p -h haproxy-server-ip

六、高级配置

1. 启用统计页面

listen stats
    bind *:8404
    mode http
    stats enable
    stats uri /stats
    stats auth admin:Admin@123

2. 日志配置

# 修改rsyslog配置
echo "local0.* /var/log/haproxy.log" | sudo tee -a /etc/rsyslog.d/haproxy.conf
sudo systemctl restart rsyslog

七、生产环境建议

1. 安全加固

   bind *:3306 ssl crt /etc/haproxy/mysql.pem

2. 连接池优化

   default_backend mysql-cluster
   timeout client 1h
   timeout server 1h

3. 故障转移

   server mysql-slave1 192.168.1.20:3306 check backup

八、与Keepalived集成（高可用）

1. 安装Keepalived

sudo apt install -y keepalived  # Ubuntu
sudo yum install -y keepalived  # CentOS

2. 配置VIP漂移

vrrp_script chk_haproxy {
    script "killall -0 haproxy"
    interval 2
}

vrrp_instance VI_1 {
    virtual_router_id 51
    advert_int 1
    virtual_ipaddress {
        192.168.1.100/24
    }
    track_script {
        chk_haproxy
    }
}

总结

✅ 基础负载均衡：轮询分发MySQL请求
✅ 健康检查：自动剔除故障节点
✅ 读写分离：通过前端规则实现（可选）
✅ 高可用：结合Keepalived实现VIP漂移

最终架构：
应用层 → HAProxy (192.168.1.100:3306) → {Master:160, Slave1:20, Slave2:29}