資訊專欄INFORMATION COLUMN
方案背景
涉及項目：XXXXXX銀行項目
涉及功能：自動化運維平臺
優化項：mysql數據庫高可用架構
規劃vs目標
主機：192.168.31.113、192.168.31.114
操作系統：CentOS7.6
中間件及其版本：mysql5.7、keepalived-2.1.2
操作用戶：root、shsnc
2.2 實現目標
實施過程
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）驗證mysql雙主
配置完成后在分別連接兩臺mysql，查看雙主狀態：
show slave statusG;Slave_IO_Running: Yes
Slave_SQL_Running: Yes
確認雙方mysql的slave狀態均為雙yes，主主搭建成功。
主機：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復制配置文件
復制keepalived配置文件目錄到etc下，用于systemctl的keepalived服務啟動。
cp -r /home/shsnc/keepalived/etc/keepalived /etc/keepalived啟動keepalived服務
systemctl啟動keepalived服務
systemctl start keepalived1）編寫check腳本
作用：檢查mysql是否可用，不可用則停止本機keepalived，使虛擬ip轉移至可用mysql。
vim /etc/keepalived/script/check_mysql.sh
#!/bin/bash
##檢測mysql實例端口是否通
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）編寫notify腳本
作用：設置切換后的mysql主庫可讀寫，從庫只讀，確保數據寫入的唯一入口。
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服務。
systemctl stop keepalived;
systemctl start keepalived;
測試
4.1 mysql故障自動切換測試
1）測試方法
模擬mysql故障，查看vip是否自動切換至可用mysql，查看mysql讀寫權限是否自動切換。模擬故障數據庫修復后啟動，查看mysql雙主同步是否正常，數據是否同步。
2）測試過程
停用192.168.31.114的mysql服務，此時vip已漂移至192.168.31.113（說明keepalived的check腳本執行成功）。
查看mysql讀寫權限，此時192.168.31.113的mysql數據庫為可讀可寫，192.168.31.114的mysql數據庫為只讀（說明keepalived的notify腳本執行成功）。
通過虛擬ip連接數據庫成功，說明自動切換可用mysql成功。
故障自動切換恢復后，再次啟動192.168.31.114的mysql數據庫，可以看到兩臺數據庫數據一致，數據已經同步，可繼續提供高可用的mysql雙主架構。
4.2 Keepalived腦裂測試
測試方法：keepalived配置不同VRRP組播通訊時間進行測試，通過禁止192.168.31.114上iptables的vrrp協議訪問，觸發keepalived腦裂，再分別查看腦裂觸發時間。
配置策略
配置組播時間為1秒
執行命令
iptables禁止vrrp協議訪問并記錄時間。
查看日志
查看keepalived日志，得到腦裂觸發時間。
測試結論
當advert_int配置為1的時候，腦裂觸發時間2~3s。
配置策略
配置組播時間為3秒
執行命令
iptables禁止vrrp協議訪問并記錄時間。
查看日志
查看keepalived日志，得到腦裂觸發時間。
測試結論
當advert_int配置為3的時候，腦裂觸發時間2~3s。
配置策略
配置組播時間5秒。
執行命令
iptables禁止vrrp協議訪問并記錄時間。
查看日志
查看keepalived日志，得到腦裂觸發時間。
測試結論
當advert_int配置為5的時候，腦裂觸發時間10~12s。
配置策略
配置組播時間1秒，配置vrrp_garp_master_refresh為10秒。
執行命令
iptables禁止vrrp協議訪問并記錄時間。
查看日志分：
測試結論
當vrrp_garp_master_refresh設置為10s的時候，腦裂恢復之后主節點每間隔10S發一次包到網關。
最終測試結果
通過以上測試驗證，Keepalived + MySQL雙主熱備方案可實現mysql數據庫雙主實時備份，m故障后秒級自動恢復可用，且數據不丟失，提供了數據完整、功能高可用的故障處理能力，方案可行。
文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。
轉載請注明本文地址：http://m.specialneedsforspecialkids.com/yun/129112.html
摘要：歸根到底，高可用性就意味著更少的宕機時間。首先，可以盡量避免應用宕機來減少宕機時間。降低平均失效時間我們對系統變更缺少管理是所有導致宕機事件中最普遍的原因。 我們之前了解了復制、擴展性，接下來就讓我們來了解可用性。歸根到底，高可用性就意味著 更少的宕機時間。 老規矩，討論一個名詞，首先要給它下個定義，那么什么是可用性？ 1 什么是可用性 我們常見的可用性通常以百分比表示，這本身就有其隱...
閱讀 1353·2023-01-11 13:20
閱讀 1699·2023-01-11 13:20
閱讀 1211·2023-01-11 13:20
閱讀 1901·2023-01-11 13:20
閱讀 4161·2023-01-11 13:20
閱讀 2747·2023-01-11 13:20
閱讀 1397·2023-01-11 13:20
閱讀 3664·2023-01-11 13:20
