您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 主主互備 + Keepalived 實現雙活高可用架構完整實戰

編輯：MySQL 綜合教學

MySQL 主主互備 + Keepalived 實現雙活高可用架構完整實戰

主主互備 (Master-Master) 結合 Keepalived VIP 漂移，構建無單點的 MySQL 雙活架構。本文記錄 CentOS 6.5 環境下的完整部署流程：主主同步配置、Keepalived 雙機熱備、健康檢查腳本、故障切換驗證。

一、環境規劃

角色	IP	Server ID	備註
Master (Primary)	192.168.1.210	1	正常對外服務
Slave (Secondary)	192.168.1.211	2	備用，故障時接管
VIP	192.168.1.208	-	對外統一入口

二、主主同步配置

1. Master (210) /etc/my.cnf

[mysqld]
basedir=/usr/local/mysql
datadir=/var/lib/mysql
port=3306
socket=/var/lib/mysql/mysql.sock
server_id=1
log-bin=mysql-bin
relay-log=mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

2. Slave (211) /etc/my.cnf

[mysqld]
basedir=/usr/local/mysql
datadir=/var/lib/mysql
port=3306
socket=/var/lib/mysql/mysql.sock
server_id=2
log-bin=mysql-bin
relay-log=mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

3. 重啟並記錄 Binlog 位點

service mysqld restart
mysql -uroot -ppasswd -e 'SHOW MASTER STATUS;'
# 兩邊均記錄 File=mysql-bin.000001, Position=414

4. 建立同步帳號並啟動互備

# 兩台均執行
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.%' IDENTIFIED BY 'replication';
FLUSH PRIVILEGES;

# 210 指向 211
CHANGE MASTER TO
    MASTER_HOST='192.168.1.211',
    MASTER_USER='replication',
    MASTER_PASSWORD='replication',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=414;
START SLAVE;

# 211 指向 210
CHANGE MASTER TO
    MASTER_HOST='192.168.1.210',
    MASTER_USER='replication',
    MASTER_PASSWORD='replication',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=414;
START SLAVE;

5. 驗證同步狀態

SHOW SLAVE STATUS\G;
# 確認 Slave_IO_Running=Yes, Slave_SQL_Running=Yes

若報錯可執行 STOP SLAVE; RESET SLAVE; 重來。

6. 功能驗證

rpm -ivh https://mirrors.opencas.cn/epel/6/i386/epel-release-6-8.noarch.rpm
yum -y install keepalived

2. Master (210) /etc/keepalived/keepalived.conf

global_defs {
    notification_email { admin@example.com }
    notification_email_from keepalived@localhost
    smtp_server 127.0.0.1
    smtp_connect_timeout 30
    router_id LVS_MASTER
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 80
    priority 100
    advert_int 2
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.208/24 dev eth0
    }
    track_script { chk_mysql }
}

virtual_server 192.168.1.208 3306 {
    delay_loop 2
    lb_algo rr
    lb_kind DR
    persistence_timeout 50
    protocol TCP
    real_server 192.168.1.210 3306 {
        weight 3
        notify_down /usr/local/keepalived/bin/mysql_check.sh
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }
}

3. Slave (211) 差異配置

priority 90
real_server 192.168.1.211 3306
notify_down /usr/local/keepalived/bin/mysql_check.sh

4. MySQL 健康檢查腳本

# /usr/local/keepalived/bin/mysql_check.sh
#!/bin/bash
# 檢查 MySQL 存活，失敗則停止 keepalived 觸發 VIP 漂移
if ! mysqladmin -uroot -ppasswd ping >/dev/null 2>&1; then
    pkill keepalived
fi

四、授權 VIP 訪問

GRANT ALL ON *.* TO 'root'@'192.168.1.208' IDENTIFIED BY 'vip_pass';
FLUSH PRIVILEGES;

五、故障切換驗證

正常狀態：客戶端連 VIP 192.168.1.208 → 解析至 Master (210)
SHOW VARIABLES LIKE 'hostname'; -- 顯示 master
故障注入：service mysqld stop 於 Master (210)
自動切換：Keepalived 偵測 3306 端口失效 → VIP 漂移至 Slave (211)
驗證：客戶端重連 VIP → 解析至 Slave (211)
SHOW VARIABLES LIKE 'hostname'; -- 顯示 slave

六、關鍵避坑指南

auto_increment_offset/step：主主雙寫必須配置 auto_increment_offset=1/2、auto_increment_increment=2 避免主鍵衝突。
雙寫風險：同時寫入兩節點極易導致數據不一致。生產建議「單寫單備」，VIP 僅指向 Active 節點，Keepalived 故障切換後由應用層或中間件路由寫入。
nopreempt：防止主節點恢復後搶佔 VIP 造成頻繁切換 (Flapping)。
同步延遲監控：Seconds_Behind_Master 持續 > 0 需告警，防止切換時數據丟失。
防火牆：開放 3306、VRRP 多播 (224.0.0.18) 端口。