Keepalived + LVS 實作 MySQL 從庫負載均衡與高可用架構

Keepalived + LVS 實作 MySQL 從庫負載均衡與高可用架構

單一從庫無法承載高併發讀請求，且缺乏自動故障切換。本文記錄生產環境驗證過的 Keepalived + LVS (DR 模式) 方案，實現 MySQL 從庫讀流量負載分發與節點健康檢查自動摘除，配合 VIP 透明化應用連線。

一、架構拓撲與角色定義

核心原理 (DR 模式)：LVS 僅修改 MAC 地址轉發封包，Real Server 直接回應客戶端，回包不經 LVS，極大減輕 LVS 網卡壓力，適合高讀流量場景。

二、環境準備 (所有節點)

# 關閉 SELinux 與防火牆 (生產建議改用 iptables 精細放行)
setenforce 0
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
systemctl stop firewalld && systemctl disable firewalld

# 開啟核心轉發
echo "net.ipv4.ip_forward = 1" >> /etc/sysctl.conf
sysctl -p

三、LVS Master 部署 (192.168.1.180)

1. 安裝套件

yum install -y ipvsadm gcc openssl-devel popt-devel
# Keepalived 建議編譯安裝獲取最新版
wget https://www.keepalived.org/software/keepalived-2.2.8.tar.gz
tar xzf keepalived-*.tar.gz && cd keepalived-*
./configure --with-kernel-dir=/usr/src/kernels/$(uname -r)
make && make install

2. 核心配置 /etc/keepalived/keepalived.conf

! Configuration File for keepalived
global_defs {
    router_id LVS_MASTER_01
}

vrrp_instance VI_MYSQL {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass S3cur3P@ss
    }
    virtual_ipaddress {
        192.168.20.253/24 dev eth0 label eth0:VIP
    }
}

virtual_server 192.168.20.253 3306 {
    delay_loop 6
    lb_algo wlc          # 加權最少連線，適合長連線資料庫
    lb_kind DR           # Direct Routing 模式
    protocol TCP
    persistence_timeout 0 # 無持久連線，讀請求無狀態

    real_server 192.168.20.182 3306 {
        weight 1
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }
    real_server 192.168.20.183 3306 {
        weight 1
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }
}

3. 啟動與驗證

systemctl enable keepalived && systemctl start keepalived
ipvsadm -Ln
# 輸出應顯示 VIP:3306 -> 兩個 Real Server，狀態為 Route

四、Real Server (從庫) 部署 (192.168.20.182/183)

1. VIP 綁定腳本 /etc/rc.d/init.d/realserver

#!/bin/bash
# chkconfig: - 80 90
# description: LVS Real Server VIP binding
VIP=192.168.20.253
. /etc/rc.d/init.d/functions

case "$1" in
start)
    ifconfig lo:0 $VIP netmask 255.255.255.255 broadcast $VIP
    route add -host $VIP dev lo:0
    # ARP 抑制關鍵參數：防止 Real Server 搶答 ARP 請求
    echo 1 > /proc/sys/net/ipv4/conf/lo/arp_ignore
    echo 2 > /proc/sys/net/ipv4/conf/lo/arp_announce
    echo 1 > /proc/sys/net/ipv4/conf/all/arp_ignore
    echo 2 > /proc/sys/net/ipv4/conf/all/arp_announce
    sysctl -p >/dev/null 2>&1
    echo "RealServer VIP $VIP bound OK"
    ;;
stop)
    ifconfig lo:0 down
    route del $VIP >/dev/null 2>&1
    echo 0 > /proc/sys/net/ipv4/conf/lo/arp_ignore
    echo 0 > /proc/sys/net/ipv4/conf/lo/arp_announce
    echo 0 > /proc/sys/net/ipv4/conf/all/arp_ignore
    echo 0 > /proc/sys/net/ipv4/conf/all/arp_announce
    echo "RealServer VIP released"
    ;;
*)
    echo "Usage: $0 {start|stop}"
    exit 1
esac

2. 核心參數持久化 /etc/sysctl.conf

net.ipv4.ip_forward = 1
# LVS DR 模式 ARP 抑制
net.ipv4.conf.lo.arp_ignore = 1
net.ipv4.conf.lo.arp_announce = 2
net.ipv4.conf.all.arp_ignore = 1
net.ipv4.conf.all.arp_announce = 2

chmod +x /etc/rc.d/init.d/realserver
chkconfig realserver on
service realserver start

五、功能驗證與故障演練

1. 正常分發：測試機 mysql -h 192.168.20.253 -u user -p 連線成功，多次連線觀察分發至兩台從庫 (可查 SHOW VARIABLES LIKE 'server_id')。
2. 節點下線自動摘除：Slave1 systemctl stop mysqld，ipvsadm -Ln 顯示該節點狀態變 Down，流量全轉 Slave2。
3. 節點恢復自動加入：Slave1 重啟 MySQL，健康檢查通過後自動恢復 Route 狀態。
4. LVS Master 故障：部署雙機 Keepalived (VRRP priority 不同) 實現 VIP 漂移，此處略。

六、常見坑點與避坑指南

七、擴展建議

• ProxySQL 做中間層：支援讀寫分離、查詢路由、熔斷、緩存，功能更豐富，適合微服務架構。
• MHA/Orchestrator：主庫自動故障切換，配合從庫 LVS 形成全鏈路高可用。
• 監控告警：Prometheus + mysqld_exporter + node_exporter 監控 Seconds_Behind_Master、Slave_IO_Running、LVS 連線數。