haproxy+keepalived mysql_HAProxy+keepalived MySQL负载均衡

环境：vip 192.168.0.233

mysql1  192.168.0.227：3306

mysql2  192.168.0.230：3306

1. 监控脚本编写(mysqlrep_status.sh)

#!/bin/bash

#

# /opt/phpdba/keepalived/etc/keepalived/mysqlrep_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="127.0.0.1"

MYSQL_PORT="3306"

MYSQL_USERNAME="root"

MYSQL_PASSWORD="123456"

MYSQL_MAXTHREADNUM="30"

# We perform a simple query that should return a few results

/opt/phpdba/mysql/bin/mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USERNAME -p$MYSQL_PASSWORD -e "show full processlist;" >/tmp/processlist.txt

/opt/phpdba/mysql/bin/mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USERNAME -p$MYSQL_PASSWORD -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 processlist num:$result iostat:$iostat and sqlstat:$sqlstat

# if slave_IO_Running and Slave_sql_Running ok,then return 200 code

if [ "$result" -lt "$MYSQL_MAXTHREADNUM" ] && [ "$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

2、系统监听服务

a、修改/etc/service文件，

mysqlcheck      8890/tcp                # Mysql Check

mysqlcheck      8890/udp                # Mysql Check

b、在/etc/xinetd.d/目录下添加mysqlchk

service mysqlcheck

{

flags           = REUSE

socket_type     = stream

port            = 8890

wait            = no

user            = root

server          = /opt/phpdba/keepalived/etc/keepalived/mysqlrep_status.sh

log_on_failure  += USERID

disable         = no

}

或者 /etc/xinetd.conf 添加

mysqlcheck stream tcp nowait root /opt/phpdba/keepalived/etc/keepalived/mysqlrep_status.sh mysqlcheck

c、重启xinetd (如无此服务，yum -y install xinetd)

/etc/init.d/xinetd restart   or service xinetd restart

d、telnet 测试

telnet 192.168.0.233 8890

Trying 192.168.0.233…

Connected to 192.168.0.233.

Escape character is ‘^]’.

HTTP/1.1 200 OK

Connection closed by foreign host.

3、haproxy配置

global

log 127.0.0.1 local3 notice

maxconn 20480

uid mysql

gid mysql

daemon

nbproc 1

pidfile /opt/phpdba/haproxy/logs/haproxy.pid

defaults

log global

mode http

#option httplog

option dontlognull

retries 3

option redispatch

maxconn 20480

contimeout 5000

clitimeout 50000

srvtimeout 50000

listen MYSQL_SLAVE 0.0.0.0:3333

#cookie SERVERID rewrite

mode tcp

maxconn 20480

balance roundrobin

option httpchk OPTIONS * HTTP/1.1\r\nHost:\ www

server mysql_227 192.168.0.227:3306 check port 8890 inter 5s rise 2 fall 3

server mysql_230 192.168.0.230:3306 check port 8890 inter 5s rise 2 fall 3

srvtimeout 20000

listen admin_status

mode http

bind 0.0.0.0: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

4、keepalived.conf

! Configuration File for keepalived

vrrp_script chk_http_port {

script "/opt/phpdba/keepalived/etc/keepalived/check_haproxy.sh"

interval 2

weight 2

global_defs {

router_id LVS_db_phpdba

}

vrrp_instance VI_1 {

state MASTER

interface em1

virtual_router_id 51

priority 150

advert_int 1

authentication {

auth_type PASS

auth_pass phpdba

}

track_script {

chk_http_port

}

virtual_ipaddress {

192.168.0.233

}

}

}

5、haproxy进程监控(check_haproxy.sh)

#!/bin/bash

A=`ps -C haproxy --no-header |wc -l`

if [ $A -eq 0 ];then

/opt/phpdba/haproxy/sbin/haproxy -f /opt/phpdba/haproxy/conf/haproxy.cfg

sleep 3

if [ `ps -C haproxy --no-header |wc -l` -eq 0 ];then

/etc/init.d/keepalived stop

fi

fi

6、haproxy 启动脚本

cat /etc/init.d/haproxyd

#! /bin/sh

set -e

PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/haproxy/sbin

PROGDIR=/opt/phpdba/haproxy

PROGNAME=haproxy

DAEMON=$PROGDIR/sbin/$PROGNAME

CONFIG=$PROGDIR/conf/$PROGNAME.conf

PIDFILE=$PROGDIR/run/$PROGNAME.pid

DESC="HAProxy daemon"

SCRIPTNAME=/etc/init.d/$PROGNAME

# Gracefully exit if the package has been removed.

test -x $DAEMON || exit 0

start()

{

echo -n "Starting $DESC: $PROGNAME"

$DAEMON -f $CONFIG

echo "."

}

stop()

{

echo -n "Stopping $DESC: $PROGNAME"

haproxy_pid=cat $PIDFILE

kill $haproxy_pid

echo "."

}

restart()

{

echo -n "Restarting $DESC: $PROGNAME"

$DAEMON -f $CONFIG -p $PIDFILE -sf $(cat $PIDFILE)

echo "."

}

case "$1" in

start)

start

;;

stop)

stop

;;

restart)

restart

;;

*)

echo "Usage: $SCRIPTNAME {start|stop|restart}" >&2

exit 1

;;

esac

exit 0