MySQL 高可用架構方案二：Keepalived + LVS + 讀寫分離 + 負載均衡完整實作

MySQL 高可用架構方案二：Keepalived + LVS + 讀寫分離 + 負載均衡完整實作

基於 CentOS + MySQL 5.6 半同步複制，結合 **Keepalived (VRRP) + LVS (IPVS)** 實現**讀寫 VIP 分離、主備自動切換、讀請求負載均衡**的生產級高可用架構。

架構拓撲

核心設計邏輯

• 正常：Write VIP → Master (A)；Read VIP → LVS (WRR 權重) 分發至 A(權重1) + B(權重3)。
• Master 故障：Keepalived 偵測 → B 升主 (執行 remove_slave.sh 停止複制、重置 Binlog) → Write VIP + Read VIP 全漂移至 B，服務不中斷。
• Slave 故障：Read VIP 從 LVS 踢除 B，Write VIP 不變。

軟體安裝 (雙節點同步執行)

# 關聯內核模組
ln -s /usr/src/kernels/$(uname -r) /usr/src/linux

# 安裝 IPVS (LVS 用戶空間工具)
yum install -y ipvsadm

# 安裝 Keepalived
wget https://www.keepalived.org/software/keepalived-2.2.4.tar.gz
tar zxvf keepalived-2.2.4.tar.gz
cd keepalived-2.2.4
./configure --prefix=/usr/local/keepalived
make && make install

# 建立軟連結
ln -s /usr/local/keepalived/etc/keepalived /etc/
ln -s /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/
ln -s /usr/local/keepalived/sbin/keepalived /sbin/

MySQL 主從配置 (半同步)

Master (171) /etc/my.cnf：

server-id=1
log-bin=mysql-bin
binlog-do-db=ppl
binlog-ignore-db=mysql
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000

建立複制帳號：GRANT REPLICATION SLAVE ON *.* TO 'repdb01'@'%' IDENTIFIED BY '123456';

Slave (172) /etc/my.cnf：

server-id=2
master-host=192.168.150.171
master-user=repdb01
master-password=123456
master-port=3306
replicate-do-db=ppl
replicate-ignore-db=mysql
slave-skip-errors=1062
read-only=1
skip-slave-start

啟動複制：CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=106; START SLAVE;

Keepalived + LVS 配置核心片段

Master (171) /etc/keepalived/keepalived.conf：

global_defs { router_id MYSQL-HA }

vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 90
    priority 100          # Master 優先級高
    advert_int 1
    nopreempt             # 非搶佔模式，避免震盪
    authentication { auth_type PASS auth_pass abcd1234 }
    virtual_ipaddress {
        192.168.150.173 label eth0:1   # Write VIP
        192.168.150.174 label eth0:2   # Read VIP
    }
    notify_master "/usr/local/mysql/bin/remove_slave.sh"
}

# Write VIP -> 只指向 Master (本機)
virtual_server 192.168.150.173 3306 {
    delay_loop 2
    lb_algo wrr
    lb_kind DR
    persistence_timeout 60
    protocol TCP
    real_server 192.168.150.171 3306 {
        weight 3
        notify_down "/usr/local/mysql/bin/mysql.sh"
        TCP_CHECK { connect_port 3306 connect_timeout 10 nb_get_retry 3 delay_before_retry 3 }
    }
}

# Read VIP -> Master(權重1) + Slave(權重3)
virtual_server 192.168.150.174 3306 {
    delay_loop 2
    lb_algo wrr
    lb_kind DR
    persistence_timeout 60
    protocol TCP
    real_server 192.168.150.171 3306 {
        weight 1
        notify_down "/usr/local/mysql/bin/mysql.sh"
        TCP_CHECK { connect_port 3306 connect_timeout 10 nb_get_retry 3 delay_before_retry 3 }
    }
    real_server 192.168.150.172 3306 {
        weight 3
        TCP_CHECK { connect_port 3306 connect_timeout 10 nb_get_retry 3 delay_before_retry 3 }
    }
}

Slave (172) 差異：priority 99、virtual_server 173 只指向本機 (172)、virtual_server 174 只指向本機 (權重3)。

關鍵腳本

/usr/local/mysql/bin/remove_slave.sh (升主執行)：

#!/bin/bash
mysql -uroot -p123456 -e "SET GLOBAL read_only=OFF; RESET MASTER; STOP SLAVE; CHANGE MASTER TO MASTER_HOST='localhost';"
sed -i 's/^read-only/#read-only/' /etc/my.cnf
chmod 755 $0

/usr/local/mysql/bin/mysql.sh (節點下線觸發)：

#!/bin/bash
/etc/init.d/keepalived stop

Real Server VIP 綁定腳本 /usr/local/keepalived/bin/lvs-rs.sh (兩節點均需執行)：

#!/bin/bash
VIP=192.168.150.174
case $1 in
start)
    ifconfig lo:0 $VIP netmask 255.255.255.255 broadcast $VIP
    route add -host $VIP dev lo:0
    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 Start 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 Stoped"
    ;;
esac
chmod 755 $0
echo "/usr/local/keepalived/bin/lvs-rs.sh start" >> /etc/rc.local

驗證與測試

# 檢查 LVS 規則
ipvsadm -Ln
# 正常應顯示 Write VIP 指向 171，Read VIP 指向 171+172

# 模擬 Master 掛掉 (關機或 systemctl stop mysqld + keepalived)
# 觀察 VIP 漂移、Slave 升主、應用無感知切換

生產環境補強建議

• 使用 **GTID + 半同步** 替代傳統 Binlog 位置複制，簡化故障轉移座標計算。
• 引入 **Orchestrator / MHA / ProxySQL** 自動化拓撲管理、讀寫分離、查詢路由。
• 監控指標：Seconds_Behind_Master、Slave_IO/SQL_Running、VIP 狀態、LVS 連線數、QPS/延遲。
• 跨機房部署需三節點以上 (或引入仲裁節點) 防 Split-Brain。