萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

Linux 高可用架構實戰:MySQL 多主一從 + Keepalived 跨機房集群部署全紀錄

架構拓撲

┌─────────────┐     ┌─────────────┐
│ Master01    │     │ Master02    │  ← Keepalived VIP 漂移 (172.16.51.100)
│ 172.16.0.51 │     │ 172.16.0.60 │
│ MariaDB 10.1│     │ MariaDB 10.1│
└──────┬──────┘     └──────┬──────┘
       │                   │
       └─────────┬─────────┘
                 ▼
        ┌─────────────────┐
        │  Slave01/02     │  ← 只讀負載、備份、災備
        │ 172.16.0.63/69  │
        └─────────────────┘

基礎環境初始化(四節點統一執行)

# 1. Hosts 解析
cat >> /etc/hosts <<'EOF'
172.16.0.51 mysql-master01
172.16.0.60 mysql-master02
172.16.0.63 mysql-slave01
172.16.0.69 mysql-slave02
EOF

# 2. 時間同步
echo "*/5 * * * * /usr/sbin/ntpdate pool.ntp.org >/dev/null 2>&1" >> /var/spool/cron/root
ntpdate pool.ntp.org

# 3. SSH 互信(expect 自動化)
ssh-keygen -t rsa -f ~/.ssh/id_rsa -P ''
# 分發公鑰腳本 auto_auth.sh(見正文),批量執行
echo mysql-{master0{1,2},slave0{1,2}} | sed 's/ /\n/g' | xargs -i bash auto_auth.sh {} lookback

MariaDB 安裝(主庫優先,從庫依序)

wget -q https://www.dwhd.org/wp-content/uploads/2016/01/install_mysql.sh && bash install_mysql.sh
# 腳本支持 MySQL 5.5/5.6/5.7、MariaDB 5.5/10.0/10.1、Galera、Percona
# 安裝完成輸出:端口、數據目錄、root 密碼、socket 路徑

Keepalived 編譯安裝(雙主節點)

wget https://www.keepalived.org/software/keepalived-1.2.19.tar.gz
tar xf keepalived-1.2.19.tar.gz && cd keepalived-1.2.19
./configure --prefix=/usr/local/keepalived --sysconfdir=/etc \
  --with-kernel-dir=/usr/src/kernels/$(uname -r) --enable-sha1
make -j $(nproc) && make install

# 環境與服務註冊
echo "export PATH=/usr/local/keepalived/sbin:\$PATH" > /etc/profile.d/keepalived.sh
source /etc/profile.d/keepalived.sh
ln -sv /usr/local/keepalived/sbin/keepalived /usr/sbin/
chkconfig keepalived on

Keepalived 核心配置(Master01 / Master02 差異化)

通用段

global_defs {
    notification_email { admin@dwhd.org }
    notification_email_from mysql-keepalived@ds.com
    smtp_server 127.0.0.1
    smtp_connect_timeout 30
    router_id MySQL-HA
}

vrrp_script check_mysql {
    script "/etc/keepalived/scripts/mysql_check.sh"
    interval 2
}

vrrp_sync_group VG1 { group { VI_1 } }

Master01 (Priority 100, nopreempt)

vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 88
    priority 100
    advert_int 1
    nopreempt
    authentication { auth_type PASS; auth_pass 1111 }
    track_script { check_mysql }
    virtual_ipaddress { 172.16.51.100 label eth0:0 }
    notify_master /etc/keepalived/scripts/master.sh
    notify_backup /etc/keepalived/scripts/backup.sh
    notify_stop   /etc/keepalived/scripts/stop.sh
}

Master02 (Priority 90)

vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 88
    priority 90
    advert_int 1
    authentication { auth_type PASS; auth_pass 1111 }
    track_script { check_mysql }
    virtual_ipaddress { 172.16.51.100 label eth0:0 }
    notify_master /etc/keepalived/scripts/master.sh
    notify_backup /etc/keepalived/scripts/backup.sh
    notify_stop   /etc/keepalived/scripts/stop.sh
}

關鍵腳本實現(/etc/keepalived/scripts/)

mysql_check.sh:健康檢查 → 故障轉移觸發器

#!/bin/bash
username=root
password=ZWE4MDM3NTgwNjc2
bindir="/usr/local/mariadb/bin"

mysql_check() { ${bindir}/mysql -u${username} -p${password} -e "select version();"; }
mysql_pid_check() { ps aux | pgrep mysqld; }

while :; do
    [ -n "$(mysql_check)" ] && [ -n "$(mysql_pid_check)" ] && exit 0
    [ -n "$(mysql_check)" ] && exit 0 || {
        [[ "$i" -gt "4" ]] && break || { let i++ && sleep 0.3 && continue; }
    }
done
service keepalived stop  # 連續 5 次失敗 → 停止 keepalived → VIP 漂移

master.sh:升主操作(停從、調優寫入性能、授權應用賬號)

#!/bin/bash
# 等待主從同步追平
while :; do
    Master_Log_File=$(${bindir}/mysql ... -e "show slave status\G" | awk '/Master_Log_File/{print $2}')
    Relay_Master_Log_File=$(${bindir}/mysql ... | awk '/Relay_Master_Log_File/{print $2}')
    Read_Master_Log_Pos=$(${bindir}/mysql ... | awk '/Read_Master_Log_Pos/{print $2}')
    Exec_Master_Log_Pos=$(${bindir}/mysql ... | awk '/Exec_Master_Log_Pos/{print $2}')
    [ "$Master_Log_File" = "$Relay_Master_Log_File" ] && [ "$Read_Master_Log_Pos" -eq "$Exec_Master_Log_Pos" ] && { echo OK; break; }
    sleep 10; [[ "$i" -gt "60" ]] && break; let i+=10
done

# 切主配置
${bindir}/mysql -u${username} -p${password} -e "
STOP SLAVE;
SET GLOBAL innodb_support_xa=0;
SET GLOBAL sync_binlog=0;
SET GLOBAL innodb_flush_log_at_trx_commit=0;
FLUSH LOGS;
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;" > /tmp/master_status_$(date +%F-%T).txt

backup.sh:降備操作(恢復安全配置、關閉事件調度器)

${bindir}/mysql -u${username} -p${password} -e "
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
SET GLOBAL event_scheduler=0;
SET GLOBAL innodb_support_xa=0;
SET GLOBAL sync_binlog=0;
SET GLOBAL innodb_flush_log_at_trx_commit=0;"

stop.sh:VRRP 停止時持久化 Binlog 位點

# 確保 Binlog 位點穩定後再停止
while :; do
    M_File1=$(${bindir}/mysql ... -e "show master status\G" | awk '/File/{print $2}')
    M_Pos1=$(${bindir}/mysql ... | awk '/Position/{print $2}')
    sleep 1
    M_File2=$(${bindir}/mysql ... | awk '/File/{print $2}')
    M_Pos2=$(${bindir}/mysql ... | awk '/Position/{print $2}')
    [ "$M_File1" = "$M_File2" ] && [ "$M_Pos1" -eq "$M_Pos2" ] && { echo ok; break; }
    sleep 10; [[ "$i" -gt "60" ]] && break; let i+=10
done

權限與啟動

chmod +x /etc/keepalived/scripts/*.sh
# 雙主同步腳本
scp -r /etc/keepalived/scripts root@mysql-master02:/etc/keepalived/
ssh root@mysql-master02 "chmod +x /etc/keepalived/scripts/*.sh"

# 啟動
systemctl start keepalived
# 驗證 VIP
ip addr show eth0 | grep 172.16.51.100

跨機房部署關鍵點

  • 網絡延遲 < 2ms:同城雙活前提,否則建議單主異地從
  • Keepalived 組播/單播:跨 VLAN 需配置 unicast_peer,確保 VRRP 報文通達
  • 數據一致性:半同步複製 rpl_semi_sync_master_wait_for_slave_count=1,防數據丟失
  • 分裂腦防範:nopreempt + 監控告警 + 人工介入確認
copyright © 萬盛學電腦網 all rights reserved