環境準備

在MySQL的Master-Master複製基礎上加入keepalived

節點資訊

虛擬IP：10.0.0.150
真實IP：10.0.3.140 10.0.3.141

基本軟體

keepalived,基於VRRP協定來實現的服務高可用方案

apt install mariadb-server keepalived

初始化mysql設置root密碼

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
flush privileges;
exit;

資料庫默認綁定127.0.0.1，需修改
/etc/mysql/mariadb.conf.d/50-server.cnf中bind-address加# 注釋或改為0.0.0.0

節點配置

[mysqld]
server-id              = 10
log_bin                = log/mysql-bin
expire_logs_days        = 10
max_binlog_size        = 512M
max_binlog_cache_size    = 64m
binlog_cache_size        = 32m
binlog-ignore-db       = mysql
binlog_format           =  mixed
sync_binlog               = 100
log_slave_updates       = 1
relay-log-recovery
relay_log = log/relay-bin
relay_log_index = log/relay-bin.index
#master_info_repository=TABLE
#relay-log-info-repository=TABLE

#innodb_flush_logs_at_trx_commit=0

主節點設置帳號

CREATE USER 'sync_sql'@'10.0.3.141' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'sync_sql'@'10.0.3.141';
FLUSH PRIVILEGES;

鎖定主節點表FLUSH TABLES WITH READ LOCK
查詢、記錄二進位日誌檔position
show master status;

備份資料庫後解鎖UNLOCK TABLES

從節點

配置複製資訊

CHANGE MASTER TO MASTER_HOST='10.0.3.140',
MASTER_PORT=3306,
MASTER_USER='sync_sql',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=618;

啟動複製進程
START slave;
查看同步狀態
SHOW slave status \G
紅框兩項為Yes則說明正常

keepalived配置

創建配置檔keepalived.conf

global_defs {
   router_id MYSQL_HA  #當前節點名
}
vrrp_script restart_mysql {
   script "/etc/keepalived/restart_mysql.sh"  #重啟 mysql 服務
   interval 2
   weight 2
}
vrrp_instance VI_1 {
    state BACKUP         #兩臺配置節點均為BACKUP
    interface ens192
    virtual_router_id 51    # VRRP 組名
    priority 100    # 優先順序 (主伺服器優先順序高)
    advert_int 1    # 心跳間隔 (秒)
    nopreempt            #不搶佔，高優先順序上設置
    authentication {    #設置驗證資訊
        auth_type PASS
        auth_pass 123456
    }
    track_script {
        restart_mysql  # 重啟 mysql 狀態腳本
    }
    virtual_ipaddress {
        10.0.0.150 # 虛擬IP
    }
}
virtual_server 10.0.0.150 3306 {   #linux虛擬伺服器(LVS)配置 
    delay_loop 2     #每個2秒檢查一次real_server狀態
    lb_algo wrr      #LVS調度演算法,rr|wrr|lc|wlc|lblc|sh|dh
    lb_kind DR      #LVS集群模式 ,NAT|DR|TUN
    persistence_timeout 60    #會話保持時間
    protocol TCP    #使用的協議是TCP還是UDP
    real_server 10.0.3.140 3306 {
        weight 3   #權重
        TCP_CHECK {
            connect_timeout 10   #連接超時時間
            nb_get_retry 3      #重連次數
            delay_before_retry 3 #重連間隔時間
            connect_port 3306    #健康檢查端口
        }
    }    
}

mysql狀態檢測腳本

#!/bin/bash
START_MYSQL="systemctl restart mariadb.service"
STOP_MYSQL="systemctl stop mariadb.service"
LOG_FILE="/var/log/keepalived/mysql-check.log"
HAPS=`ps -C mariadbd --no-header |wc -l`

if [ $HAPS -eq 0 ];then
    date "+%Y-%m-%d %H:%M:%S" >> $LOG_FILE
    echo $START_MYSQL >> $LOG_FILE
    $START_MYSQL >> $LOG_FILE 2>&1
    sleep 3
    if [ `ps -C mysqld --no-header |wc -l` -eq 0 ];then
        date "+%Y-%m-%d %H:%M:%S" >> $LOG_FILE
        echo "Start MySQL failed, kill all keepalived" >> $LOG_FILE
        killall keepalived
    fi
fi