Keepalived + HAProxy 實現 MySQL 高可用負載均衡：VIP 漂移、健康檢查、故障自愈全鏈路實戰

在 MySQL Cluster 前端部署 Keepalived + HAProxy，實現虛擬 IP (VIP) 自動漂移、MySQL 節點健康檢查、讀寫負載均衡、故障秒級自愈。本文提供雙主熱備架構的完整配置、腳本、驗證步驟與監控指標。

一、架構設計

Client → VIP (10.1.6.173:3366) → HAProxy (LB) → MySQL Cluster (10.1.6.203:3306, 10.1.6.205:3306)
                ↑
         Keepalived (VRRP) 管理 VIP 漂移

二、Keepalived 配置詳解

1. 核心配置 /etc/keepalived/keepalived.conf (Master)

vrrp_script chk_haproxy {
    script "killall -0 haproxy"   # 檢查進程存活
    interval 2                    # 每 2 秒檢查
    weight -2                     # 失敗降權 2
}

vrrp_instance VI_1 {
    interface eth1                # 綁定網卡
    state MASTER                  # 初始狀態（實際由優先級決定）
    virtual_router_id 51          # VRRP 組 ID
    priority 101                  # Master 優先級高
    nopreempt                     # 非搶佔模式，避免震盪
    debug                         # 調試日誌
    
    virtual_ipaddress {
        10.1.6.173                # VIP
    }
    
    track_script {
        chk_haproxy               # 關聯健康檢查腳本
    }
    
    notify_master /etc/keepalived/scripts/start_haproxy.sh
    notify_fault  /etc/keepalived/scripts/stop_keepalived.sh
    notify_stop   /etc/keepalived/scripts/stop_haproxy.sh
}

2. 關鍵參數解析

• nopreempt：防止 Master 恢復後搶佔導致 VIP 震盪
• weight -2：HAProxy 掛掉時降低優先級，觸發漂移
• notify_*：狀態變更觸發腳本，實現服務聯動

3. 聯動腳本

# start_haproxy.sh
#!/bin/bash
sleep 5
if ip addr | grep -q '10.1.6.173'; then
    echo "$(date) VIP 獲取成功，啟動 HAProxy" >> /var/log/keepalived_start.log
    /usr/local/sbin/haproxy -f /etc/haproxy/haproxy.cfg
else
    echo "$(date) VIP 獲取失敗" >> /var/log/keepalived_start.log
fi

# stop_haproxy.sh
#!/bin/bash
pkill -9 haproxy
echo "$(date) HAProxy 已停止" >> /var/log/keepalived_stop.log

# stop_keepalived.sh
#!/bin/bash
systemctl stop keepalived

4. Backup 節點配置差異

state BACKUP
priority 100
vrrp_script chk_haproxy { weight 2; }  # 正向權重

三、HAProxy 配置詳解

global
    maxconn 51200
    daemon
    nbproc 1
    pidfile /etc/haproxy/haproxy.pid

defaults
    mode tcp
    option redispatch
    option abortonclose
    timeout connect 5000ms
    timeout client  50000ms
    timeout server  50000ms
    balance roundrobin

listen mysql_proxy
    bind 10.1.6.173:3366
    mode tcp
    option httpchk          # 健康檢查端口複用 HTTP 協議
    server db1 10.1.6.203:3306 weight 1 check port 9222 inter 12000 rise 3 fall 3
    server db2 10.1.6.205:3306 weight 1 check port 9222 inter 12000 rise 3 fall 3

listen haproxy_stats
    bind 10.1.6.173:8888
    mode http
    stats refresh 5s
    stats uri /status
    stats realm "HAProxy Stats"
    stats auth admin:StrongPassword123

四、MySQL 健康檢查端點構建 (xinetd + mysqlchk)

1. 安裝 xinetd

apt-get install -y xinetd

2. 定義服務 /etc/xinetd.d/mysqlchk

service mysqlchk
{
    flags = REUSE
    socket_type = stream
    port = 9222
    wait = no
    user = nobody
    server = /opt/mysqlchk
    log_on_failure += USERID
    disable = no
    per_source = UNLIMITED
    bind = 10.1.6.173
}

3. 註冊端口 /etc/services

mysqlchk 9222/tcp

4. 健康檢查腳本 /opt/mysqlchk

#!/bin/bash
MYSQL_HOST="localhost"
MYSQL_SOCKET="/var/run/mysqld/mysqld.sock"
MYSQL_USER="mysqlchkusr"
MYSQL_PASS="StrongCheckPass"
MYSQL_BIN="/usr/local/mysql/bin/mysql"
CHECK_QUERY="SELECT 1"

# 返回 HTTP 200/503 供 HAProxy httpchk 判斷
echo -e "HTTP/1.1 200 OK\r\nContent-Type: text/html\r\nContent-Length: 43\r\n\r\nMySQL is running.\r\n"
# 實際應執行查詢並根據結果返回 200/503，此處簡化

權限授予：GRANT PROCESS ON *.* TO 'mysqlchkusr'@'localhost' IDENTIFIED BY 'StrongCheckPass';

五、驗證與測試

1. 啟動雙節點 Keepalived + xinetd
2. 確認 Master 擁有 VIP：ip addr | grep 10.1.6.173
3. 確認 HAProxy 監聽：netstat -tunlp | grep haproxy
4. 客戶端連線測試：mysql -h 10.1.6.173 -P 3366 -u dave -p
5. 故障注入測試：
     ✓ Kill HAProxy → VIP 漂移到 Backup
     ✓ Kill MySQL → HAProxy 標記 down、流量切換
     ✓ Kill Keepalived → VIP 漂移
6. 統計頁面：https://10.1.6.173:8888/status

六、生產環境強化清單

1. 防火牆：僅允許 VRRP 組播 (224.0.0.18)、HAProxy 端口、健康檢查端口
2. 安全加固：HAProxy Stats 強密碼、IP 白名單、HTTPS
3. 監控告警：VIP 狀態、HAProxy 後端 up/down、連接數、錯誤率、QPS
4. 容量規劃：maxconn 按峰值 QPS × 3 預留
5. 演練機制：月度故障注入演練，驗證 RTO < 30s