Keepalived+HAProxy實(shí)現(xiàn)MySQL高可用負(fù)載均衡的配置
更新時(shí)間：2016年02月28日 16:54:38 作者：樂晨
這篇文章主要介紹了keepalived+haproxy實(shí)現(xiàn)MySQL高可用負(fù)載均衡的配置方法,通過這兩個(gè)軟件可以有效地使MySQL脫離故障及進(jìn)行健康檢測,需要的朋友可以參考下
Keepalived
由于在生產(chǎn)環(huán)境使用了mysqlcluster,需要實(shí)現(xiàn)高可用負(fù)載均衡,這里提供了keepalived+haproxy來實(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)來決定服務(wù)器工作正常與否。如web server的服務(wù)端口一般是80，如果Keepalived檢測到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)就是這里指定的，但這里指定的不算，還是得要通過競選通過優(yōu)先級(jí)來確定，里如果這里設(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ù)以萬計(jì)的并發(fā)連接.同時(shí)可以保護(hù)服務(wù)器不暴露到網(wǎng)絡(luò)上,通過端口映射.它還自帶監(jiān)控服務(wù)器狀態(tài)的頁面.
安裝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 #心跳檢測的文件
server db1 10.1.6.203:3306 weight 1 check port 9222 inter 12000 rise 3 fall 3 #服務(wù)器定義,check inter 12000是檢測心跳頻率 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)站健康檢測URL，用來檢測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;
測試
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
測試:
通過vip10.1.6.173 3366訪問cluster數(shù)據(jù)庫(注意賬戶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ù)庫掛掉.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 的存儲(chǔ)引擎,myisam和innodb的區(qū)別
這篇文章主要介紹了Mysql 的存儲(chǔ)引擎,myisam和innodb的區(qū)別,需要的朋友可以參考下2014-12-12
關(guān)于Mysql5.7及8.0版本索引失效情況匯總
這篇文章主要介紹了關(guān)于Mysql5.7及8.0版本索引失效情況匯總，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-08-08
這篇文章主要介紹了MySQL 兩種恢復(fù)數(shù)據(jù)的方法，幫助恢復(fù)線上數(shù)據(jù)，保證數(shù)據(jù)完整，感興趣的朋友可以了解下2020-10-10
這篇文章主要介紹了關(guān)于msyql事務(wù)隔離的相關(guān)資料，文中講解非常細(xì)致，幫助大家更好的理解和學(xué)習(xí)mysql，感興趣的朋友可以了解下2020-07-07
mysql插入中文數(shù)據(jù)變成問號(hào)的解決方案
這篇文章主要介紹了mysql插入中文數(shù)據(jù)變成問號(hào)的解決方案，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-04-04
在mysql中，通常使用limit做分頁，而且經(jīng)常會(huì)跟order by 連用。在order by 上加索引有時(shí)候是很有幫助的，不然系統(tǒng)會(huì)做很多的filesort2013-02-02
這篇文章主要給大家介紹了關(guān)于MySQL實(shí)例crash的相關(guān)資料，文中通過示例代碼的非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用mysql具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2018-12-12
mysql判斷字段是否存在的方法有很多，如使用desc命令、show columns 命令、describe 命令等等，感興趣的朋友可以參考下2014-01-01
淺談innodb的索引頁結(jié)構(gòu),插入緩沖,自適應(yīng)哈希索引
下面小編就為大家?guī)硪黄獪\談innodb的索引頁結(jié)構(gòu),插入緩沖,自適應(yīng)哈希索引。小編覺得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過來看看吧2017-03-03
全面解析MySQL?Explain如何優(yōu)化SQL查詢性能
在?MySQL?中，EXPLAIN?關(guān)鍵字可以幫助我們分析查詢執(zhí)行計(jì)劃，從而優(yōu)化查詢性能，所以本文就來和大家詳細(xì)講講Explain是如何優(yōu)化SQL查詢性能的2023-05-05