架構拓撲
┌─────────────┐ ┌─────────────┐
│ 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 + 監控告警 + 人工介入確認