MySQL从库集群方案之HAProxy篇_MySQL

#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 
 } 
}

#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

/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 
# 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 ___FCKpd___2 ]; then
 BASENAME=`find ___FCKpd___2 -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 ___FCKpd___2quot;Usage: $BASENAME {start|stop|restart|reload|condrestart|status|check}"
 exit 1
esac
 
exit $?

chkconfig –add HAProxy 
chkconfig HAProxy on
service HAProxy start

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 
 
}

service xinetd restart

#!/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