MySQL 高可用架構：Keepalived + VIP 實現主從自動故障轉移實戰

MySQL 高可用架構：Keepalived + VIP 實現主從自動故障轉移實戰

Keepalived 基於 VRRP 協議提供虛擬 IP (VIP) 漂移，配合腳本檢測 MySQL/Haproxy 健康狀態，構建輕量級主從高可用方案。本文記錄 CentOS 環境下從編譯安裝到配置生效的完整流程。

一、依賴包安裝與編譯部署

# 1. 安裝編譯依賴
yum -y install kernel-devel openssl-devel popt-devel

# 2. 下載編譯安裝 (示例 1.2.9，建議用新版 2.x)
wget https://www.keepalived.org/software/keepalived-1.2.9.tar.gz
tar -xf keepalived-1.2.9.tar.gz
cd keepalived-1.2.9
./configure --prefix=/usr/local/keepalived
make && make install

# 3. 部署運行文件
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
chmod +x /etc/init.d/keepalived
mkdir -p /etc/keepalived
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

二、核心配置文件解析 (/etc/keepalived/keepalived.conf)

global_defs {
    notification_email { admin@company.com }
    notification_email_from keepalived@company.com
    smtp_server smtp.126.com
    smtp_connect_timeout 30
    router_id LVS_MASTER_01      # 全局唯一標識
}

# 健康檢查腳本 (檢測 Haproxy/MySQL 進程，異常則調整優先級)
vrrp_script chk_mysql {
    script "/etc/keepalived/check_mysql.sh"
    interval 5
    weight -20          # 檢查失敗則優先級 -20，觸發主備切換
    fall 3              # 連續 3 次失敗生效
    rise 2              # 連續 2 次成功恢復
}

vrrp_instance VI_MYSQL {
    state MASTER        # 備機改為 BACKUP
    interface eth0      # 綁定網卡
    virtual_router_id 51 # 主備必須一致
    priority 100        # 主機優先級高，備機設 90
    advert_int 1        # VRRP 心跳間隔 1 秒
    authentication {
        auth_type PASS
        auth_pass strong_password_123  # 主備一致
    }
    track_script {
        chk_mysql
    }
    virtual_ipaddress {
        192.168.78.220/24 dev eth0  # VIP，主備漂移
    }
}

三、健康檢查腳本示例 (/etc/keepalived/check_mysql.sh)

#!/bin/bash
# 檢測 MySQL 進程 + 端口 + 簡單查詢
MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3306"
MYSQL_USER="health_check"
MYSQL_PASS="check_pass"

if ! nc -z $MYSQL_HOST $MYSQL_PORT &>/dev/null; then
    exit 1
fi

if ! mysql -h$MYSQL_HOST -P$MYSQL_PORT -u$MYSQL_USER -p$MYSQL_PASS -e "SELECT 1;" &>/dev/null; then
    exit 1
fi
exit 0

chmod +x /etc/keepalived/check_mysql.sh
# 需在 MySQL 創建健康檢查用戶
# GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'health_check'@'127.0.0.1' IDENTIFIED BY 'check_pass';

四、啟動驗證與常見坑

# 1. 啟動 (線上勿重啟 network)
systemctl start keepalived   # 或 service keepalived start

# 2. 驗證 VIP 掛載
ip addr show eth0
# 主機應看到 192.168.78.220/24 scope global eth0

# 3. 模擬故障測試
systemctl stop mysqld   # 主庫停庫
# 觀察備機日誌 /var/log/messages，應見 VIP 漂移、狀態切換為 MASTER

五、生產環境關鍵優化建議

1. 雙主架構下勿用 Keepalived 做 VIP：易發生「腦裂」(雙主同時持有 VIP)。推薦：Keepalived 只管 VIP，業務讀寫分離走中間件 (ProxySQL/MaxScale/OneProxy)，或用 MHA/Orchestrator 做主從切換 + VIP 漂移。
2. 健康檢查要「深」：僅檢測進程/端口不夠，必須執行 SELECT 1 或 SHOW SLAVE STATUS 驗證實際可用性。
3. 防火牆放行 VRRP 多播：iptables -I INPUT -i eth0 -d 224.0.0.18 -p vrrp -j ACCEPT。
4. 網絡抖動防護：調大 advert_int (如 3-5 秒)、增加 fall/rise 閾值，避免頻繁切換。
5. 監控告警：對接 Prometheus/Zabbix 監聽 vrrp_state_transitions_total、VIP 所在節點變化。

六、Keepalived 2.x 新特性提示

• 支持 BFD (雙向轉發檢測) 亞秒級故障感知。
• track_interface 直接監聽網卡鏈路狀態，無需腳本。
• 配置語法更簡潔，建議新項目直接部署 2.0+ 版本 (EPEL 源可直接 yum install keepalived)。