建置MySQL HA雙主架構
本文發佈於770天前,文中的資訊可能已有所發展或是改變,請謹慎使用!
環境準備
在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
查詢、記錄二進位日誌檔positionshow master status;
File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
---|---|---|---|
mysql-bin.000001 | 618 | mysql |
備份資料庫後解鎖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