MySQL负载均衡
1. 添加监控MySQL状态的端口# vi /etc/services
mysqlcheck 6033 / tcp # MySQL status check
2. 使用xinetd守护进程运行MySQL状态检测# cat /etc/xinetd.d/mysqlchk
service mysqlcheck
{
disable = no
flags = REUSE
socket_type = stream
port = 6033
wait
= no
user = root
server = /
usr/
local/
haproxy/
sbin/
mysqlchk_status.sh
log_on_failure += USERID
}3. 状态检测脚本# vi /usr/local/haproxy/sbin/mysqlchk_status.sh
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | #/bin/bash
MYSQL_HOST
="localhost"
MYSQL_PORT
="3306"
MYSQL_USERNAME
="root"
MYSQL_PASSWORD
="123456"
ERROR_MSG
=/
usr/
bin/
mysql --host
=$MYSQL_HOST
--port
=$MYSQL_PORT
--user
=$MYSQL_USERNAME
--password
=$MYSQL_PASSWORD
-e
"show databases;"
if
[
"$ERROR_MSG
"
!
= ""
]
then
# mysql is fine, return http 200
/
bin/
echo
-e
"HTTP/1.1 200 OK\r
\n
"
/
bin/
echo
-e
"Content-Type: Content-Type: text/plain\r
\n
"
/
bin/
echo
-e
"\r
\n
"
/
bin/
echo
-e
"MySQL is running.\r
\n
"
/
bin/
echo
-e
"\r
\n
"
else
# mysql is fine, return http 503
/
bin/
echo
-e
"HTTP/1.1 503 Service Unavailable\r
\n
"
/
bin/
echo
-e
"Content-Type: Content-Type: text/plain\r
\n
"
/
bin/
echo
-e
"\r
\n
"
/
bin/
echo
-e
"MySQL is *down*.\r
\n
"
/
bin/
echo
-e
"\r
\n
"
fi |
# chown haproxy.haproxy /usr/local/haproxy/sbin/mysqlchk_status.sh
4. HAproxy日志
#touch/var/log/haproxy.log
#chownhaproxy.haproxy/var/log/haproxy.log
编辑/etc/syslog.conf文件，添加如下语句local0.*
/
var/
log/
haproxy.log5. HAProxy配置文件[root@localhost htdocs]# cat /usr/local/haproxy/conf/haproxy.conf
global
maxconn 4096
daemon
pidfile /
usr/
local/
haproxy/
run/
haproxy.pid
#debug
#quiet
user haproxy
group haproxy
defaults
log global
mode http
option httplog
option dontlognull
log 127.0.0.1 local0
retries 3
option redispatch
maxconn 2000
contimeout 5000
clitimeout 50000
srvtimeout 50000
listen admin_stats 192.168.0.1:80
mode http
stats uri /
dbs
stats realm Global\ statistics
stats auth test
:123456
listen proxy-mysql 0.0.0.0:23306
mode tcp
balance roundrobin
option httpchk OPTIONS *
HTTP/
1.1
\r\nHost:\ www
server db01 192.168.0.1:3306
weight 1
check port 6033
inter 1s rise 2
fall 2
server db02 192.168.0.2:3306
weight 1
check port 6033
inter 1s rise 2
fall 2
option tcpka6. HAProxy启动脚本# cat /etc/init.d/haproxy
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | #! /bin/sh
set
-e
PATH
=/
sbin:/
bin:/
usr/
sbin:/
usr/
bin:/
usr/
local/
haproxy/
sbin
PROGDIR
=/
usr/
local/
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 |