MySQL從庫集群方案之HAProxy篇
原創(chuàng)作者：崔曉輝
HAProxy 反向代理服務(wù)器支持雙機(jī)熱備支持虛擬主機(jī)，其配置簡(jiǎn)單，擁有非常不錯(cuò)的服務(wù)器健康檢查功能。當(dāng)其代理的后端服務(wù)器出現(xiàn)故障，HAProxy會(huì)自動(dòng)將該服務(wù)器摘除，故障恢復(fù)后再自動(dòng)將該服務(wù)器加入。本文介紹如何通過兩臺(tái)HAProxy服務(wù)器實(shí)現(xiàn)MySQL熱備的集群方案。
【51CTO獨(dú)家特稿】HAProxy反向代理服務(wù)器支持雙機(jī)熱備支持虛擬主機(jī)，其配置簡(jiǎn)單，擁有非常不錯(cuò)的服務(wù)器健康檢查功能。當(dāng)其代理的后端服務(wù)器出現(xiàn)故障，HAProxy會(huì)自動(dòng)將該服務(wù)器摘除，故障恢復(fù)后再自動(dòng)將該服務(wù)器加入。
這里有兩臺(tái)HAProxy機(jī)器，分別安裝keepalived，組成熱備形式。作用：當(dāng)一臺(tái)有問題，另一臺(tái)可以在1秒內(nèi)接管。
xinetd服務(wù)的作用是檢測(cè)端口，本文中使用8890端口。HAProxy用http協(xié)議檢測(cè)這個(gè)端口是否正常。
MySQL同步狀態(tài)腳本，是放在從庫本地，由xinetd服務(wù)來激活腳本，正常就會(huì)輸出200狀態(tài)碼給HAProxy，證明從庫正常；否則，就剔除。（這里就可以加上短信報(bào)警了）
系統(tǒng)架構(gòu)圖
使用軟件
- HAProxy 1.4.16
- Keepalived 1.1.20
- Xinetd 2.3.14
- MySQL 同步狀態(tài)腳本 0.2
一、系統(tǒng)約定
系統(tǒng)環(huán)境
- OS:CentOS 5.6 x86_64
- MASTER:192.168.1.65
- BACKUP:192.168.1.66
- VIP:192.168.1.67
- serivce Port:3306
工作流程
準(zhǔn)備工作：應(yīng)用配置好slave的VIP 192.168.1.67 端口3306
(1)應(yīng)用服務(wù)器
(2)連接HAProxy的vip 192.168.1.67:3306，根據(jù)算法，分配到一臺(tái)slave。
(3)檢測(cè)slave的8890端口是否返回http 200狀態(tài)碼。
(4)返回200 狀態(tài)碼，HAProxy 返回正常，繼續(xù)服務(wù)。
(5)返回503，剔除該slave，并將mysql請(qǐng)求轉(zhuǎn)發(fā)到另外一臺(tái)slave。
(6)有問題的slave，發(fā)送短信報(bào)警，相關(guān)人員檢查。
二、Keepalived 1.1.20的安裝于配置
#cd /var/tmp/
#wget http://www.keepalived.org/software/keepalived-1.1.20.tar.gz
#tar zxvf keepalived-1.1.20.tar.gz
#cd keepalived-1.1.20
#./configure –prefix=/usr
#make && make install
#cp /usr/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/
#cp /usr/etc/sysconfig/keepalived /etc/sysconfig/
#mkdir /etc/keepalived
vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
coralzd@gmail.com
}
notification_email_from coralzd@gmail.com
smtp_server 192.168.1.1
smtp_connect_timeout 30
router_id LVS_DEVEL
}
vrrp_script chk_HAProxy {
script "killall -0 HAProxy"
interval 2
weight 2
}
vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 50
priority 150
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
track_interface {
eth0
}
virtual_ipaddress {
192.168.1.67
}
track_script {
chk_HAProxy
}
}
三、HAProxy 1.4.16的安裝與配置
#cd /var/tmp/
#wget http://HAProxy.1wt.eu/download/1.4/src/HAProxy-1.4.16.tar.gz
#tar -zxvf HAProxy-1.4.16.tar.gz
#cd HAProxy-1.4.16
#make install
#mkdir -p /usr/local/HAProxy/etc
#mkdir -p /usr/local/HAProxy/sbin
#cp examples/HAProxy.cfg /usr/local/HAProxy/etc
#ln -s /usr/local/sbin/HAProxy /usr/local/HAProxy/sbin/HAProxy
#mkdir /usr/share/HAProxy
/etc/HAProxy/HAProxy.cfg
global
log 127.0.0.1 local1 notice
maxconn 4096
chroot /usr/share/HAProxy
uid 99
gid 99
daemon
#debug
#quiet
defaults
log global
mode http
#option httplog
option dontlognull
retries 3
option redispatch
maxconn 2000
contimeout 5000
clitimeout 50000
srvtimeout 50000
listen DZW_MYSQL_SLAVE 192.168.1.67:3306
#cookie SERVERID rewrite
mode tcp
maxconn 200
balance roundrobin
option httpchk OPTIONS * HTTP/1.1\r\nHost:\ www
server mysql_192_168_1_23 192.168.1.23:3306 check port 8890 inter 5s rise 2 fall 3
server mysql_192_168_1_24 192.168.1.24:3306 check port 8890 inter 5s rise 2 fall 3
srvtimeout 20000
listen admin_status
mode http
bind 192.168.1.65:8899
option httplog
log global
stats enable
stats refresh 10s
stats hide-version
stats realm Haproxy\ Statistics
stats uri /admin-status
stats auth admin:123456
stats admin if TRUE
HAProxy 啟動(dòng)腳本
/etc/init.d/HAProxy
#!/bin/sh
#
# chkconfig: - 85 15
# description: HA-Proxy is a TCP/HTTP reverse proxy which is particularly suited \
# for high availability environments.
# processname: HAProxy
# config: /etc/HAProxy/HAProxy.cfg
# pidfile: /var/run/HAProxy.pid
# Script Author: Simon Matter <simon.matter@invoca.ch>
# Version: 2004060600
# Source function library.
if [ -f /etc/init.d/functions ]; then
. /etc/init.d/functions
elif [ -f /etc/rc.d/init.d/functions ] ; then
. /etc/rc.d/init.d/functions
else
exit 0
fi
# Source networking configuration.
. /etc/sysconfig/network
# Check that networking is up.
[ ${NETWORKING} = "no" ] && exit 0
# This is our service name
BASENAME=HAProxy
if [ -L $0 ]; then
BASENAME=`find $0 -name $BASENAME -printf %l`
BASENAME=`basename $BASENAME`
fi
[ -f /etc/$BASENAME/$BASENAME.cfg ] || exit 1
RETVAL=0
start() {
/usr/sbin/$BASENAME -c -q -f /etc/$BASENAME/$BASENAME.cfg
if [ $? -ne 0 ]; then
echo "Errors found in configuration file, check it with '$BASENAME check'."
return 1
fi
echo -n "Starting $BASENAME: "
daemon /usr/sbin/$BASENAME -D -f /etc/$BASENAME/$BASENAME.cfg -p /var/run/$BASENAME.pid
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && touch /var/lock/subsys/$BASENAME
return $RETVAL
}
stop() {
echo -n "Shutting down $BASENAME: "
killproc $BASENAME -USR1
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/$BASENAME
[ $RETVAL -eq 0 ] && rm -f /var/run/$BASENAME.pid
return $RETVAL
}
restart() {
/usr/sbin/$BASENAME -c -q -f /etc/$BASENAME/$BASENAME.cfg
if [ $? -ne 0 ]; then
echo "Errors found in configuration file, check it with '$BASENAME check'."
return 1
fi
stop
start
}
reload() {
/usr/sbin/$BASENAME -c -q -f /etc/$BASENAME/$BASENAME.cfg
if [ $? -ne 0 ]; then
echo "Errors found in configuration file, check it with '$BASENAME check'."
return 1
fi
/usr/sbin/$BASENAME -D -f /etc/$BASENAME/$BASENAME.cfg -p /var/run/$BASENAME.pid -sf $(cat /var/run/$BASENAME.pid)
}
check() {
/usr/sbin/$BASENAME -c -q -V -f /etc/$BASENAME/$BASENAME.cfg
}
rhstatus() {
status $BASENAME
}
condrestart() {
[ -e /var/lock/subsys/$BASENAME ] && restart || :
}
# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
restart
;;
reload)
reload
;;
condrestart)
condrestart
;;
status)
rhstatus
;;
check)
check
;;
*)
echo $"Usage: $BASENAME {start|stop|restart|reload|condrestart|status|check}"
exit 1
esac
exit $?
chkconfig –add HAProxy
chkconfig HAProxy on
service HAProxy start
四、xinetd安裝和配置
yum install -y xinetd
vim /etc/xinetd.d/mysql_status.sh
service mysqlrep_status
{
flags = REUSE
socket_type = stream
port = 8890
wait = no
user = nobody
server = /usr/local/bin/mysqlrep_status.sh
log_on_failure += USERID
disable = no
}
重啟xinetd
service xinetd restart
MySQL同步檢測(cè)腳本（腳本檢測(cè)同步sql和IO進(jìn)程是否都為真，以及select是否達(dá)到20個(gè)進(jìn)程以上）
#!/bin/bash
#
# /usr/local/bin/mysqlchk_status.sh
#
# 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 503 Internal Server Error\r" (else)
#
MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USERNAME="repdb63"
MYSQL_PASSWORD="mylqs9eyex7s"
#
# We perform a simple query that should return a few results
#/usr/local/mysql/bin/mysql -hlocalhost –urepdb63 –pmylqs9eyex7s -e "show slave status\G;" > /tmp/rep.txt
mysql -urepdb63 -pmylqs9eyex7s -e "show full processlist;" >/tmp/processlist.txt
mysql -urepdb63 -pmylqs9eyex7s -e "show slave status\G;" >/tmp/rep.txt
iostat=`grep "Slave_IO_Running" /tmp/rep.txt |awk '{print $2}'`
sqlstat=`grep "Slave_SQL_Running" /tmp/rep.txt |awk '{print $2}'`
result=$(cat /tmp/processlist.txt|wc -l)
#echo iostat:$iostat and sqlstat:$sqlstat
# if slave_IO_Running and Slave_sql_Running ok,then return 200 code
if [ "$result" -lt "20" ] && [ "$iostat" = "Yes" ] && [ "$sqlstat" = "Yes" ];
then
# mysql is fine, return http 200
/bin/echo -e "HTTP/1.1 200 OK\r\n"
else
# mysql is down, return http 503
/bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"
fi
注意：在mysql slave另行建立一個(gè)具有process和slave_client權(quán)限的賬號(hào)。
作者簡(jiǎn)介：崔曉輝，網(wǎng)名coralzd，大眾網(wǎng)系統(tǒng)管理員，精通網(wǎng)站系統(tǒng)架構(gòu)、Unix技術(shù)。gtalk：coralzd@gmail.com
【51CTO.com獨(dú)家特稿，轉(zhuǎn)載請(qǐng)注明原文作者和出處。】
【編輯推薦】
責(zé)任編輯：yangsai
來源：
51CTO.com
