資訊專欄INFORMATION COLUMN
方案背景
涉及項(xiàng)目：XXXXXX銀行項(xiàng)目
涉及功能：自動(dòng)化運(yùn)維平臺(tái)
優(yōu)化項(xiàng)：mysql數(shù)據(jù)庫(kù)高可用架構(gòu)
規(guī)劃vs目標(biāo)
主機(jī)：192.168.31.113、192.168.31.114
操作系統(tǒng)：CentOS7.6
中間件及其版本：mysql5.7、keepalived-2.1.2
操作用戶：root、shsnc
2.2 實(shí)現(xiàn)目標(biāo)
實(shí)施過(guò)程
1）192.168.31.113 mysql配置
修改mysql配置文件添加如下配置：
vim my.cnf
server-id=113
log-bin=mysql-bin
enforce-gtid-consistency=true
gtid-mode=on重啟mysql。
連接mysql并配置mysql雙主：
mysql -h192.168.31.113 -uroot -P3306 -pshsnc!@#
stop slave;
change master to master_host=135.10.110.114,master_user=root,master_password=shsnc!@#,master_port=3306,master_auto_position=1;
start slave;2）192.168.31.114 mysql配置
修改mysql配置文件添加如下配置：
vim my.cnf
server-id=114
log-bin=mysql-bin
enforce-gtid-consistency=true
gtid-mode=on重啟Mysql。
連接mysql并配置mysql雙主：
mysql -h192.168.31.114 -uroot -P3306 -pshsnc!@#
stop slave;
change master to master_host=135.10.110.113,master_user=root,master_password=shsnc!@#,master_port=3306,master_auto_position=1;
start slave;3）驗(yàn)證mysql雙主
配置完成后在分別連接兩臺(tái)mysql，查看雙主狀態(tài)：
show slave statusG;Slave_IO_Running: Yes
Slave_SQL_Running: Yes
確認(rèn)雙方mysql的slave狀態(tài)均為雙yes，主主搭建成功。
主機(jī)：192.168.31.113、192.168.31.114
操作用戶：root
--安裝keepalived：
安裝編譯依賴包
yum install -y libnl;
yum install -y libnfnetlink-devel zlib zlib-devel gcc gcc-c++ openssl openssl-devel openssh;
yum install -y bc;解壓安裝包
tar zxvf keepalived-2.1.2.tar.gz編譯安裝
cd keepalived-2.1.2
mkdir /home/shsnc/keepalived
./configure --prefix=/home/shsnc/keepalived
make
makeinstall復(fù)制配置文件
復(fù)制keepalived配置文件目錄到etc下，用于systemctl的keepalived服務(wù)啟動(dòng)。
cp -r /home/shsnc/keepalived/etc/keepalived /etc/keepalived啟動(dòng)keepalived服務(wù)
systemctl啟動(dòng)keepalived服務(wù)
systemctl start keepalived1）編寫(xiě)check腳本
作用：檢查mysql是否可用，不可用則停止本機(jī)keepalived，使虛擬ip轉(zhuǎn)移至可用mysql。
vim /etc/keepalived/script/check_mysql.sh
#!/bin/bash
##檢測(cè)mysql實(shí)例端口是否通
RETVAL=$?
# failover
check_port=$(ss -nlpt | grep "mysqld" | awk -F[: ]+ /3306/{print $6})
if [ ! -n "${check_port}" ]
then
/bin/systemctl stop keepalived
else
#exit $RETVAL
echo mysql is alive
fi
cpuidle=$(vmstat 1 3 |tail -1 |awk $0~/[[:digit:]]+/{print $(NF-2)})
cpu_used=$(awk -v x=$cpuidle BEGIN{printf "%.2f
",100-x})
failpoint=93.0
if [ $(echo "${cpu_used} >= $failpoint" | bc ) -eq 1 ]
then
/bin/systemctl stop keepalived
else
echo "CPU utilization does not exceed 93"
exit $RETVAL
fi
exit 02）編寫(xiě)notify腳本
作用：設(shè)置切換后的mysql主庫(kù)可讀寫(xiě)，從庫(kù)只讀，確保數(shù)據(jù)寫(xiě)入的唯一入口。
vim /etc/keepalived/script/keepalived_notify.sh
#!/bin/bash
. ~/.bash_profile
#mysql user
DB_USER="root"
#mysql user password
DB_PASSWORD=!QAZ3wsx@gzyd
MYSQL_SOCK=""
#mysql_bin
MYSQL_BIN="/data/mysql/mysql_5737/bin/mysql"
#mysql client command
MYSQL_CMD="${MYSQL_BIN} -u${DB_USER} -p${DB_PASSWORD}"
#query the killed seesions id sql
MYSQL_SQL="select concat(kill ,id,;) from information_schema.processlist where user not in (system user,repl,replic,backup,bkpuser,bomcjk,root,myrobot)"
#define function: get mysql service information
function get_mysql_infor()
{
for sock in `ps -ef | grep mysqld | grep --socket= | awk -F--socket= {print $2} | awk {print $1}`
do
MYSQL_SOCK="${MYSQL_SOCK}$(echo $sock)"
done
}
#define function: mysql kill sessions
function kill_sessions()
{
#receive a mysql socket file parameter
#my_sock=$(get_mysql_infor)
unset MYSQL_SOCK
get_mysql_infor
ln -sv ${MYSQL_SOCK} /tmp/mysql.sock
${MYSQL_CMD} -S ${MYSQL_SOCK} -Ne "${MYSQL_SQL}" 2>/dev/null | ${MYSQL_CMD} > /dev/null 2>&1
}
#define function: set mysql read_only mode
function set_readonly()
{
#receive mysql socket file && read_only sign parameter
#my_sock=$(get_mysql_infor)
unset MYSQL_SOCK
get_mysql_infor
my_sign=$1
#begin to set mysql read_only mode
#${MYSQL_CMD} -S ${MYSQL_SOCK} -Ne "set global read_only=${my_sign}" 2>/dev/null
${MYSQL_CMD} -S ${MYSQL_SOCK} -Ne "SET GLOBAL sync_binlog=1;SET GLOBAL innodb_flush_log_at_trx_commit=1;SET GLOBAL read_only=${my_sign};SET GLOBAL super_read_only=${my_sign};" 2>/dev/null
}
#define function: keepalived state changed to master
function Keepalived_changed_to_master()
{
#my_sock=$(get_mysql_infor)
unset MYSQL_SOCK
get_mysql_infor
Seconds_Behind_Master=$(${MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Seconds_Behind_Master | awk -F": " {print $2})
Slave_IO_Running=$(${MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Slave_IO_Running | awk -F": " {print $2})
Slave_SQL_Running=$(${MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Slave_SQL_Running | awk -F": " {print $2})
Master_Log_File=$(${MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Master_Log_File | awk -F": " {print $2})
Relay_Master_Log_File=$({MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Relay_Master_Log_File | awk -F": " {print $2})
Read_Master_Log_Pos=$({MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Read_Master_Log_Pos | awk -F": " {print $2})
Exec_Master_Log_Pos=$({MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Exec_Master_Log_Pos | awk -F": " {print $2})
echo "正常雙YES的情況下切換"
if [ "${Slave_IO_Running}" = "Yes" -a "${Slave_SQL_Running}" = "Yes" ]
then
if [ $Seconds_Behind_Master -eq 0 ]
then
set_readonly 0
exit 0
fi
fi
#if [ ${Slave_IO_Running} = Connecting -a ${Slave_SQL_Running} = Yes -a ${Seconds_Behind_Master} == NULL ];
if [ "${Slave_IO_Running}" = "Connecting" -a "${Slave_SQL_Running}" = "Yes" ]
then
if [ "${Master_Log_File}" = "${Relay_Master_Log_File}" -a "${Read_Master_Log_Pos}" = "${Exec_Master_Log_Pos}" ]
then
set_readonly 0
exit 0
fi
fi
}
#define function: keepalived state changed to backup
function Keepalived_changed_to_backup()
{
#set mysql read_only mode
set_readonly 1
#kill mysql sessions
kill_sessions
}
#start this shell
case $1 in
master)
Keepalived_changed_to_master
;;
backup)
Keepalived_changed_to_backup
;;
*)
;;
esac
exit 01）配置192.168.31.113的keepalived
vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
shsnc@shsnc.com
}
notification_email_from smtp.163.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id mysql-ha
}
vrrp_script check_mysql {
script "/etc/keepalived/script/check_mysql.sh"
interval 5
fall 3
rise 2
#timeout 60
}
vrrp_instance VI_3306 {
state BACKUP
interface ens192
virtual_router_id 188
priority 100
nopreempt
advert_int 1
authentication {
auth_type PASS
auth_pass 110120
}
virtual_ipaddress {
114.168.1.188/24 dev ens192
}
track_script {
check_mysql
}
notify_master "/etc/keepalived/script/keepalived_notify.sh master"
notify_backup "/etc/keepalived/script/keepalived_notify.sh backup"
}2）配置192.168.31.114的keepalived
vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
shsnc@shsnc.com
}
notification_email_from smtp.163.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id mysql-ha
}
vrrp_script check_mysql {
script "/etc/keepalived/script/check_mysql.sh"
interval 5
fall 3
rise 2
#timeout 60
}
vrrp_instance VI_3306 {
state BACKUP
interface ens192
virtual_router_id 188
priority 90
#nopreempt
advert_int 1
authentication {
auth_type PASS
auth_pass 110120
}
virtual_ipaddress {
114.168.1.188/24 dev ens192
}
track_script {
check_mysql
}
notify_master "/etc/keepalived/script/keepalived_notify.sh master"
notify_backup "/etc/keepalived/script/keepalived_notify.sh backup"
}重啟192.168.31.113、192.168.31.114的keepalived服務(wù)。
systemctl stop keepalived;
systemctl start keepalived;
測(cè)試
4.1 mysql故障自動(dòng)切換測(cè)試
1）測(cè)試方法
模擬mysql故障，查看vip是否自動(dòng)切換至可用mysql，查看mysql讀寫(xiě)權(quán)限是否自動(dòng)切換。模擬故障數(shù)據(jù)庫(kù)修復(fù)后啟動(dòng)，查看mysql雙主同步是否正常，數(shù)據(jù)是否同步。
2）測(cè)試過(guò)程
停用192.168.31.114的mysql服務(wù)，此時(shí)vip已漂移至192.168.31.113（說(shuō)明keepalived的check腳本執(zhí)行成功）。
查看mysql讀寫(xiě)權(quán)限，此時(shí)192.168.31.113的mysql數(shù)據(jù)庫(kù)為可讀可寫(xiě)，192.168.31.114的mysql數(shù)據(jù)庫(kù)為只讀（說(shuō)明keepalived的notify腳本執(zhí)行成功）。
通過(guò)虛擬ip連接數(shù)據(jù)庫(kù)成功，說(shuō)明自動(dòng)切換可用mysql成功。
故障自動(dòng)切換恢復(fù)后，再次啟動(dòng)192.168.31.114的mysql數(shù)據(jù)庫(kù)，可以看到兩臺(tái)數(shù)據(jù)庫(kù)數(shù)據(jù)一致，數(shù)據(jù)已經(jīng)同步，可繼續(xù)提供高可用的mysql雙主架構(gòu)。
4.2 Keepalived腦裂測(cè)試
測(cè)試方法：keepalived配置不同VRRP組播通訊時(shí)間進(jìn)行測(cè)試，通過(guò)禁止192.168.31.114上iptables的vrrp協(xié)議訪問(wèn)，觸發(fā)keepalived腦裂，再分別查看腦裂觸發(fā)時(shí)間。
配置策略
配置組播時(shí)間為1秒
執(zhí)行命令
iptables禁止vrrp協(xié)議訪問(wèn)并記錄時(shí)間。
查看日志
查看keepalived日志，得到腦裂觸發(fā)時(shí)間。
測(cè)試結(jié)論
當(dāng)advert_int配置為1的時(shí)候，腦裂觸發(fā)時(shí)間2~3s。
配置策略
配置組播時(shí)間為3秒
執(zhí)行命令
iptables禁止vrrp協(xié)議訪問(wèn)并記錄時(shí)間。
查看日志
查看keepalived日志，得到腦裂觸發(fā)時(shí)間。
測(cè)試結(jié)論
當(dāng)advert_int配置為3的時(shí)候，腦裂觸發(fā)時(shí)間2~3s。
配置策略
配置組播時(shí)間5秒。
執(zhí)行命令
iptables禁止vrrp協(xié)議訪問(wèn)并記錄時(shí)間。
查看日志
查看keepalived日志，得到腦裂觸發(fā)時(shí)間。
測(cè)試結(jié)論
當(dāng)advert_int配置為5的時(shí)候，腦裂觸發(fā)時(shí)間10~12s。
配置策略
配置組播時(shí)間1秒，配置vrrp_garp_master_refresh為10秒。
執(zhí)行命令
iptables禁止vrrp協(xié)議訪問(wèn)并記錄時(shí)間。
查看日志分：
測(cè)試結(jié)論
當(dāng)vrrp_garp_master_refresh設(shè)置為10s的時(shí)候，腦裂恢復(fù)之后主節(jié)點(diǎn)每間隔10S發(fā)一次包到網(wǎng)關(guān)。
最終測(cè)試結(jié)果
通過(guò)以上測(cè)試驗(yàn)證，Keepalived + MySQL雙主熱備方案可實(shí)現(xiàn)mysql數(shù)據(jù)庫(kù)雙主實(shí)時(shí)備份，m故障后秒級(jí)自動(dòng)恢復(fù)可用，且數(shù)據(jù)不丟失，提供了數(shù)據(jù)完整、功能高可用的故障處理能力，方案可行。
文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請(qǐng)注明本文地址：http://systransis.cn/yun/129112.html
摘要：歸根到底，高可用性就意味著更少的宕機(jī)時(shí)間。首先，可以盡量避免應(yīng)用宕機(jī)來(lái)減少宕機(jī)時(shí)間。降低平均失效時(shí)間我們對(duì)系統(tǒng)變更缺少管理是所有導(dǎo)致宕機(jī)事件中最普遍的原因。 我們之前了解了復(fù)制、擴(kuò)展性，接下來(lái)就讓我們來(lái)了解可用性。歸根到底，高可用性就意味著 更少的宕機(jī)時(shí)間。 老規(guī)矩，討論一個(gè)名詞，首先要給它下個(gè)定義，那么什么是可用性？ 1 什么是可用性 我們常見(jiàn)的可用性通常以百分比表示，這本身就有其隱...
閱讀 1356·2023-01-11 13:20
閱讀 1707·2023-01-11 13:20
閱讀 1215·2023-01-11 13:20
閱讀 1906·2023-01-11 13:20
閱讀 4165·2023-01-11 13:20
閱讀 2757·2023-01-11 13:20
閱讀 1402·2023-01-11 13:20
閱讀 3671·2023-01-11 13:20
