Keepalived+HAProxy實(shí)現(xiàn)MySQL高可用負(fù)載均衡的配置
Keepalived
由于在生產(chǎn)環(huán)境使用了mysqlcluster,需要實(shí)現(xiàn)高可用負(fù)載均衡,這里提供了keepalived+haproxy來(lái)實(shí)現(xiàn).
keepalived主要功能是實(shí)現(xiàn)真實(shí)機(jī)器的故障隔離及負(fù)載均衡器間的失敗切換.可在第3,4,5層交換.它通過VRRPv2(Virtual Router Redundancy Protocol) stack實(shí)現(xiàn)的.
Layer3:Keepalived會(huì)定期向服務(wù)器群中的服務(wù)器.發(fā)送一個(gè)ICMP的數(shù)據(jù)包（既我們平時(shí)用的Ping程序）,如果發(fā)現(xiàn)某臺(tái)服務(wù)的IP地址沒有激活，Keepalived便報(bào)告這臺(tái)服務(wù)器失效，并將它從服務(wù)器群中剔除，這種情況的典型例子是某臺(tái)服務(wù)器被非法關(guān)機(jī)。Layer3的方式是以服務(wù)器的IP地址是否有效作為服務(wù)器工作正常與否的標(biāo)準(zhǔn)。
Layer4:主要以TCP端口的狀態(tài)來(lái)決定服務(wù)器工作正常與否。如web server的服務(wù)端口一般是80，如果Keepalived檢測(cè)到80端口沒有啟動(dòng)，則Keepalived將把這臺(tái)服務(wù)器從服務(wù)器群中剔除。
Layer5:在網(wǎng)絡(luò)上占用的帶寬也要大一些。Keepalived將根據(jù)用戶的設(shè)定檢查服務(wù)器程序的運(yùn)行是否正常，如果與用戶的設(shè)定不相符，則Keepalived將把服務(wù)器從服務(wù)器群中剔除。
Software Design
keepalived啟動(dòng)后會(huì)有單個(gè)進(jìn)程
8352 ? Ss 0:00 /usr/sbin/keepalived 8353 ? S 0:00 \_ /usr/sbin/keepalived 8356 ? S 0:01 \_ /usr/sbin/keepalived
父進(jìn)程:內(nèi)存管理,子進(jìn)程管理等等
子進(jìn)程:VRRP子進(jìn)程
子進(jìn)程:Healthchecking 子進(jìn)程
實(shí)例
2臺(tái)mysqlcluster 10.1.6.203 master 10.1.6.205 backup
vip 10.1.6.173
目的訪問10.1.6.173 3366端口 分別輪詢通過haproxy轉(zhuǎn)發(fā)到10.1.6.203 3306 和10.1.6.205 3306
mysqlcluster搭建參照之前博客,這里在2臺(tái)機(jī)上安裝keepalived
root@10.1.6.203:~# apt-get install keepalived
root@10.1.6.203:~# cat /etc/keepalived/keepalived.conf
vrrp_script chk_haproxy {
script "killall -0 haproxy" # verify the pid existance
interval 2 # check every 2 seconds
weight -2 # add 2 points of prio if OK
}
vrrp_instance VI_1 {
interface eth1 # interface to monitor
state MASTER
virtual_router_id 51 # Assign one ID for this route
priority 101 # 101 on master, 100 on backup
nopreempt
debug
virtual_ipaddress {
10.1.6.173
}
track_script { #注意大括號(hào)空格
chk_haproxy
}
notify_master /etc/keepalived/scripts/start_haproxy.sh #表示當(dāng)切換到master狀態(tài)時(shí),要執(zhí)行的腳本
notify_fault /etc/keepalived/scripts/stop_keepalived.sh #故障時(shí)執(zhí)行的腳本
notify_stop /etc/keepalived/scripts/stop_haproxy.sh #keepalived停止運(yùn)行前運(yùn)行notify_stop指定的腳本 }
VRRPD配置包括三個(gè)類:
- VRRP同步組(synchroization group)
- VRRP實(shí)例(VRRP Instance)
- VRRP腳本
這里使用了 VRRP實(shí)例, VRRP腳本
注意配置選項(xiàng):
stat:指定instance(Initial)的初始狀態(tài)，就是說在配置好后，這臺(tái)服務(wù)器的初始狀態(tài)就是這里指定的，但這里指定的不算，還是得要通過競(jìng)選通過優(yōu)先級(jí)來(lái)確定，里如果這里設(shè)置為master，但如若他的優(yōu)先級(jí)不及另外一臺(tái)，那么這臺(tái)在發(fā)送通告時(shí)，會(huì)發(fā)送自己的優(yōu)先級(jí)，另外一臺(tái)發(fā)現(xiàn)優(yōu)先級(jí)不如自己的高，那么他會(huì)就回?fù)屨紴閙aster
- interface：實(shí)例綁定的網(wǎng)卡，因?yàn)樵谂渲锰摂MIP的時(shí)候必須是在已有的網(wǎng)卡上添加的
- priority 101：設(shè)置本節(jié)點(diǎn)的優(yōu)先級(jí)，優(yōu)先級(jí)高的為master
- debug：debug級(jí)別
- nopreempt:設(shè)置為不搶占
vrrp_script chk_haproxy {
script "killall -0 haproxy" # verify the pid existance
interval 2 # check every 2 seconds 腳本執(zhí)行間隔
weight -2 # add 2 points of prio if OK 腳本結(jié)果導(dǎo)致的優(yōu)先級(jí)變更：2表示優(yōu)先級(jí)+2；-2則表示優(yōu)先級(jí)-2
}
然后在實(shí)例(vrrp_instance)里面引用，有點(diǎn)類似腳本里面的函數(shù)引用一樣：先定義，后引用函數(shù)名
track_script {
chk_haproxy
}
注意：VRRP腳本(vrrp_script)和VRRP實(shí)例(vrrp_instance)屬于同一個(gè)級(jí)別
root@10.1.6.203:scripts# cat start_haproxy.sh
#!/bin/bash
sleep 5
get=`ip addr |grep 10.1.6.173 |wc -l`
echo $get >> /etc/keepalived/scripts/start_ha.log
if [ $get -eq 1 ]
then
echo "`date +%c` success to get vip" >> /etc/keepalived/scripts/start_ha.log
/usr/local/sbin/haproxy -f /etc/haproxy/haproxy.cfg
else
echo "`date +%c` can not get vip" >> /etc/keepalived/scripts/start_ha.log
fi
root@10.1.6.203:scripts# cat stop_keepalived.sh
#!/bin/bash
pid=`pidof keepalived`
if [ $pid == "" ]
then
echo "`date +%c` no keepalived process id" >> /etc/keepalived/scripts/stop_keep.log
else
echo "`date +%c` will stop keepalived " >> /etc/keepalived/scripts/stop_keep.log
/etc/init.d/keepalived stop
fi
/etc/init.d/keepalived stop
root@10.1.6.203:scripts# cat stop_haproxy.sh
#!/bin/bash
pid=`pidof haproxy`
echo "`date +%c` stop haproxy" >> /etc/keepalived/scripts/stop_ha.log
kill -9 $pid
同理配置10.1.6.205
root@10.1.6.205:~# cat /etc/keepalived/keepalived.conf
vrrp_script chk_haproxy {
script "killall -0 haproxy" # verify the pid existance
interval 2 # check every 2 seconds
weight 2 # add 2 points of prio if OK
}
vrrp_instance VI_1 {
interface eth1 # interface to monitor
state BACKUP
virtual_router_id 51 # Assign one ID for this route
priority 100 # 101 on master, 100 on backup
virtual_ipaddress {
10.1.6.173
}
track_script {
chk_haproxy
}
notify_master /etc/keepalived/scripts/start_haproxy.sh
notify_fault /etc/keepalived/scripts/stop_keepalived.sh
notify_stop /etc/keepalived/scripts/stop_haproxy.sh
}
HAProxy
下面再介紹下haproxy
HAProxy是一款基于TCP(第四層)和HTTP(第七層)應(yīng)用的代理軟件,它也可作為負(fù)載均衡器.可以支持?jǐn)?shù)以萬(wàn)計(jì)的并發(fā)連接.同時(shí)可以保護(hù)服務(wù)器不暴露到網(wǎng)絡(luò)上,通過端口映射.它還自帶監(jiān)控服務(wù)器狀態(tài)的頁(yè)面.
安裝haproxy
wget -O/tmp/haproxy-1.4.22.tar.gz http://haproxy.1wt.eu/download/1.4/src/haproxy-1.4.22.tar.gz tar xvfz /tmp/haproxy-1.4.22.tar.gz -C /tmp/ cd /tmp/haproxy-1.4.22 make TARGET=linux26 make install
haproxy需要對(duì)每一個(gè)mysqlcluster服務(wù)器進(jìn)行健康檢查
1.在2臺(tái)主機(jī)分別配置haproxy.cfg
root@10.1.6.203:scripts# cat /etc/haproxy/haproxy.cfg
global
maxconn 51200 #默認(rèn)最大連接數(shù)
#uid 99
#gid 99
daemon #以后臺(tái)形式運(yùn)行haproxy
#quiet
nbproc 1 #進(jìn)程數(shù)量(可以設(shè)置多個(gè)進(jìn)程提高性能)
pidfile /etc/haproxy/haproxy.pid #haproxy的pid存放路徑,啟動(dòng)進(jìn)程的用戶必須有權(quán)限訪問此文件
defaults
mode tcp #所處理的類別 (#7層 http;4層tcp )
option redispatch #serverId對(duì)應(yīng)的服務(wù)器掛掉后,強(qiáng)制定向到其他健康的服務(wù)器
option abortonclose #當(dāng)服務(wù)器負(fù)載很高的時(shí)候，自動(dòng)結(jié)束掉當(dāng)前隊(duì)列處理比較久的連接
timeout connect 5000s #連接超時(shí)
timeout client 50000s #客戶端超時(shí)
timeout server 50000s #服務(wù)器超時(shí)
log 127.0.0.1 local0 #錯(cuò)誤日志記錄
balance roundrobin #默認(rèn)的負(fù)載均衡的方式,輪詢方式
listen proxy
bind 10.1.6.173:3366 #監(jiān)聽端口
mode tcp #http的7層模式
option httpchk #心跳檢測(cè)的文件
server db1 10.1.6.203:3306 weight 1 check port 9222 inter 12000 rise 3 fall 3 #服務(wù)器定義,check inter 12000是檢測(cè)心跳頻率 rise 3是3次正確認(rèn)為服務(wù)器可用， fall 3是3次失敗認(rèn)為服務(wù)器不可用，weight代表權(quán)重
server db2 10.1.6.205:3306 weight 1 check port 9222 inter 12000 rise 3 fall 3
listen haproxy_stats
mode http
bind 10.1.6.173:8888
option httplog
stats refresh 5s
stats uri /status #網(wǎng)站健康檢測(cè)URL，用來(lái)檢測(cè)HAProxy管理的網(wǎng)站是否可以用，正常返回200，不正常返回503
stats realm Haproxy Manager
stats auth admin:p@a1SZs24 #賬號(hào)密碼
root@10.1.6.205:~$ cat /etc/haproxy/haproxy.cfg
global
maxconn 51200
#uid 99
#gid 99
daemon
#quiet
nbproc 1
pidfile /etc/haproxy/haproxy.pid
defaults
mode tcp
option redispatch
option abortonclose
timeout connect 5000s
timeout client 50000s
timeout server 50000s
log 127.0.0.1 local0
balance roundrobin
listen proxy
bind 10.1.6.173:3366
mode tcp
option httpchk
server db1 10.1.6.203:3306 weight 1 check port 9222 inter 12000 rise 3 fall 3
server db2 10.1.6.205:3306 weight 1 check port 9222 inter 12000 rise 3 fall 3
listen haproxy_stats
mode http
bind 10.1.6.173:8888
option httplog
stats refresh 5s
stats uri /status
stats realm Haproxy Manager
stats auth admin:p@a1SZs24
2.安裝xinetd
root@10.1.6.203:~# apt-get install xinetd
3.在每個(gè)節(jié)點(diǎn)添加xinetd服務(wù)腳本和mysqlchk端口號(hào)
root@10.1.6.203:~# vim /etc/xinetd.d/mysqlchk
# default: on
# description: mysqlchk
service mysqlchk #需要在servive定義
{
flags = REUSE
socket_type = stream
port = 9222
wait = no
user = nobody
server = /opt/mysqlchk
log_on_failure += USERID
disable = no
per_source = UNLIMITED
bind = 10.1.6.173
}
root@10.1.6.203:~# vim /etc/services
mysqlchk 9222/tcp # mysqlchk
4.編寫mysqlchk監(jiān)控服務(wù)腳本
root@10.1.6.203:~# ls -l /opt/mysqlchk
-rwxr--r-- 1 nobody root 1994 2013-09-17 11:27 /opt/mysqlchk
root@10.1.6.203:~# cat /opt/mysqlchk
#!/bin/bash
#
# This script checks if a mysql server is healthy running on localhost. It will
# return:
# "HTTP/1.x 200 OK\r" (if mysql is running smoothly)
# - OR -
# "HTTP/1.x 500 Internal Server Error\r" (else)
#
# The purpose of this script is make haproxy capable of monitoring mysql properly
#
MYSQL_HOST="localhost"
MYSQL_SOCKET="/var/run/mysqld/mysqld.sock"
MYSQL_USERNAME="mysqlchkusr" #該賬戶密碼需要在mysql里添加
MYSQL_PASSWORD="secret"
MYSQL_OPTS="-N -q -A"
TMP_FILE="/dev/shm/mysqlchk.$$.out"
ERR_FILE="/dev/shm/mysqlchk.$$.err"
FORCE_FAIL="/dev/shm/proxyoff"
MYSQL_BIN="/opt/mysqlcluster/mysql-cluster-gpl-7.2.6-linux2.6-x86_64/bin/mysql"
CHECK_QUERY="select 1"
preflight_check()
{
for I in "$TMP_FILE" "$ERR_FILE"; do
if [ -f "$I" ]; then
if [ ! -w $I ]; then
echo -e "HTTP/1.1 503 Service Unavailable\r\n"
echo -e "Content-Type: Content-Type: text/plain\r\n"
echo -e "\r\n"
echo -e "Cannot write to $I\r\n"
echo -e "\r\n"
exit 1
fi
fi
done
}
return_ok()
{
echo -e "HTTP/1.1 200 OK\r\n"
echo -e "Content-Type: text/html\r\n"
echo -e "Content-Length: 43\r\n"
echo -e "\r\n"
echo -e "<html><body>MySQL is running.</body></html>\r\n"
echo -e "\r\n"
rm $ERR_FILE $TMP_FILE
exit 0
}
return_fail()
{
echo -e "HTTP/1.1 503 Service Unavailable\r\n"
echo -e "Content-Type: text/html\r\n"
echo -e "Content-Length: 42\r\n"
echo -e "\r\n"
echo -e "<html><body>MySQL is *down*.</body></html>\r\n"
sed -e 's/\n$/\r\n/' $ERR_FILE
echo -e "\r\n"
rm $ERR_FILE $TMP_FILE
exit 1
}
preflight_check
if [ -f "$FORCE_FAIL" ]; then
echo "$FORCE_FAIL found" > $ERR_FILE
return_fail;
fi
$MYSQL_BIN $MYSQL_OPTS --host=$MYSQL_HOST --socket=$MYSQL_SOCKET --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD -e "$CHECK_QUERY" > $TMP_FILE 2> $ERR_FILE
if [ $? -ne 0 ]; then
return_fail;
fi
return_ok;
測(cè)試
2個(gè)節(jié)點(diǎn)開啟keepalived(主節(jié)點(diǎn)會(huì)獲得vip,自動(dòng)拉起haproxy),xinetd
root@10.1.6.203:~# ip add 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo 2: eth0: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast state DOWN qlen 1000 link/ether 00:26:b9:36:0f:81 brd ff:ff:ff:ff:ff:ff inet 211.151.105.186/26 brd 211.151.105.191 scope global eth0 3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 00:26:b9:36:0f:83 brd ff:ff:ff:ff:ff:ff inet 10.1.6.203/24 brd 10.1.6.255 scope global eth1 inet 10.1.6.173/32 scope global eth1 4: eth2: <BROADCAST,MULTICAST> mtu 1500 qdisc noop state DOWN qlen 1000 link/ether 00:26:b9:36:0f:85 brd ff:ff:ff:ff:ff:ff 5: eth3: <BROADCAST,MULTICAST> mtu 1500 qdisc noop state DOWN qlen 1000 link/ether 00:26:b9:36:0f:87 brd ff:ff:ff:ff:ff:ff root@10.1.6.203:~# netstat -tunlp | grep ha tcp 0 0 10.1.6.173:3366 0.0.0.0:* LISTEN 1042/haproxy tcp 0 0 10.1.6.203:8888 0.0.0.0:* LISTEN 1042/haproxy udp 0 0 0.0.0.0:56562 0.0.0.0:* 1042/haproxy root@10.1.6.203:~# netstat -tunlp | grep xine tcp 0 0 10.1.6.203:9222 0.0.0.0:* LISTEN 30897/xinetd root@10.1.6.203:~# ps -ef | grep haproxy root 1042 1 0 Sep17 ? 00:00:00 /usr/local/sbin/haproxy -f /etc/haproxy/haproxy.cfg
測(cè)試:
通過vip10.1.6.173 3366訪問cluster數(shù)據(jù)庫(kù)(注意賬戶dave權(quán)限需要加3個(gè)ip10.1.6.203,10.1.6.205,10.1.6.173)
root@10.1.6.203:mgm# mysql -udave -p -h 10.1.6.173 -P 3366 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1344316 Server version: 5.5.22-ndb-7.2.6-gpl-log MySQL Cluster Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | dave | | test | +--------------------+ 3 rows in set (0.01 sec) mysql>
手動(dòng)分別使keepalive,haproxy,數(shù)據(jù)庫(kù)掛掉.vip10.1.6.173會(huì)自動(dòng)漂到10.1.6.205從上，并不影響vip的訪問
通過vip,haproxy查看各節(jié)點(diǎn)狀態(tài)
http://10.1.6.173:8888/status
相關(guān)文章
MySQL串行化隔離級(jí)別(間隙鎖實(shí)現(xiàn))
本文主要介紹了MySQL串行化隔離級(jí)別(間隙鎖實(shí)現(xiàn))，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2022-06-06
這篇文章主要介紹了MySQL 索引優(yōu)化案例，文章圍繞主題展開詳細(xì)的內(nèi)容介紹，具有一定的參考價(jià)值，感興趣的小伙伴可以參考一下，希望對(duì)你的學(xué)習(xí)有所幫助2022-08-08
這篇文章主要給大家介紹了關(guān)于Docker中如何修改mysql8默認(rèn)加密方式的相關(guān)資料,文中大概介紹了docker啟動(dòng)命令中添加額外參數(shù)、mysql啟動(dòng)后使用sql修改以及啟動(dòng)前掛載修改好的配置文件等方法,需要的朋友可以參考下2023-06-06
mysql “ Every derived table must have its own alias”出現(xiàn)錯(cuò)誤解決辦法
這篇文章主要介紹了mysql “ Every derived table must have its own alias”出現(xiàn)錯(cuò)誤解決辦法的相關(guān)資料,需要的朋友可以參考下2017-01-01
SQL中日期與字符串互相轉(zhuǎn)換操作實(shí)例
我們經(jīng)常出于某種目的需要使用各種各樣的日期格式,當(dāng)然我們可以使用字符串操作來(lái)構(gòu)造各種日期格式,下面這篇文章主要給大家介紹了關(guān)于SQL中日期與字符串互相轉(zhuǎn)換操作的相關(guān)資料,需要的朋友可以參考下2022-10-10
從MySQL的源碼剖析Innodb buffer的命中率計(jì)算
這篇文章主要介紹了從MySQL的源碼剖析Innodb buffer的命中率計(jì)算,作者結(jié)合C語(yǔ)言寫的算法來(lái)分析innodb buffer hit Ratios,需要的朋友可以參考下2015-05-05
探索MySQL?8中utf8mb4釋放多語(yǔ)言數(shù)據(jù)的強(qiáng)大潛力
這篇文章主要為大家介紹了探索MySQL?8中utf8mb4釋放多語(yǔ)言數(shù)據(jù)的強(qiáng)大潛力,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2023-12-12
安全快速修改Mysql數(shù)據(jù)庫(kù)名的5種方法
mysql中如何重命名數(shù)據(jù)庫(kù)？這篇文章主要介紹了安全快速修改Mysql數(shù)據(jù)庫(kù)名的5種方法,需要的朋友可以參考下2014-04-04
在本篇文章中小編給大家分享的是一篇關(guān)于workerman寫mysql連接池的實(shí)例代碼內(nèi)容，有需要的朋友們可以參考下。2020-01-01
Mysql占用過高CPU時(shí)的優(yōu)化手段(必看)
下面小編就為大家?guī)?lái)一篇Mysql占用過高CPU時(shí)的優(yōu)化手段(必看)。小編覺得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過來(lái)看看吧2017-03-03