使用HAProxy给MySQL slave群进行负载均衡和状态监控

一.安装haproxy haproxy机器 http://haproxy.1wt.deu 需翻墙 tar zxvf haproxy-1.4.25.tar.gzcd haproxy-1.4.25make TARGET=linux26make installmkdir -p /usr/local/haproxy/chown nobody:nobody /usr/local/haproxy/mkdir /etc/haproxy/cp examples/haprox

一.安装haproxy

haproxy机器

http://haproxy.1wt.deu

需翻墙

tar zxvf haproxy-1.4.25.tar.gz
cd haproxy-1.4.25
make TARGET=linux26
make install
mkdir -p /usr/local/haproxy/
chown nobody:nobody /usr/local/haproxy/
mkdir /etc/haproxy/
cp examples/haproxy.cfg /etc/haproxy/
cp examples/haproxy.init /etc/init.d/haproxy
chown root:root /etc/init.d/haproxy 
chmod 700 /etc/init.d/haproxy

修改haproxy启动脚本

/usr/sbin/$BASENAME
改成
/usr/local/sbin/$BASENAME

sed -i -r 's|/usr/sbin|/usr/local/sbin|' /etc/init.d/haproxy

编辑配置文件
vi /etc/haproxy/haproxy.cfg

global
	#log 127.0.0.1	local0
	log 127.0.0.1	local3 info
	#log loghost	local0 info
	maxconn 4096
	chroot /usr/local/haproxy
	uid nobody
	gid nobody
	daemon
	debug
	#quiet
defaults
	log	global
	mode	tcp
	#option	httplog
	option	dontlognull
	retries	3
	option redispatch
	maxconn	2000
	contimeout	5000
	clitimeout	50000
	srvtimeout	50000
frontend mysql
	bind 192.168.0.107:3306
	maxconn 3000
	default_backend mysql_slave
backend	mysql_slave  
	#cookie	SERVERID rewrite
	mode tcp
	balance	roundrobin 
	#balance	source 
	#balance	leastconn 
	contimeout 10s
	timeout check 2s
	option httpchk OPTIONS * HTTP/1.1\r\nHost:\ www
	server	mysql_192_168_0_104_3306 192.168.0.104:3306 weight 1 check port 9300 inter 5s rise 2 fall 3
	server	mysql_192_168_0_104_3307 192.168.0.104:3307 weight 1 check port 9301 inter 5s rise 2 fall 3
	#server	mysql_192_168_0_106_3306 192.168.0.106:3306 weight 1 check port 9300 inter 5s rise 2 fall 3
listen  admin_status
	mode  http
	bind 192.168.0.107:8000
	option httplog
	log global
	stats enable
	stats refresh 30s
	stats hide-version
	stats realm Haproxy\ Statistics
	stats uri  /admin-status 
	stats auth  admin:123456 
	stats admin if TRUE

打开监控的iptables

iptables -A INPUT -p tcp -m tcp -s 192.168.0.0/24 --dport 8000 -j ACCEPT

添加自启动并启动haproxy服务

chkconfig –add haproxy 
chkconfig haproxy on
service haproxy start

被监控机上

我这里是单机双实例,所以有2个脚本,单机只需一个脚本和一个服务端口就行
编辑mysql检测3306脚本
vi /opt/shell/mysqlchk_status_3306.sh

#!/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="mysqlcheck"
MYSQL_PASSWORD="paSSword"
MYSQL_PATH="/opt/mysql/bin/"
# 
# We perform a simple query that should return a few results 
#${MYSQL_PATH}mysql -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USERNAME} -p${MYSQL_PASSWORD} -e "show slave status\G;" >/tmp/rep${MYSQL_PORT}.txt
${MYSQL_PATH}mysql -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USERNAME} -p${MYSQL_PASSWORD} -e "show full processlist;" >/tmp/processlist${MYSQL_PORT}.txt
${MYSQL_PATH}mysql -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USERNAME} -p${MYSQL_PASSWORD} -e "show slave status\G;" >/tmp/rep${MYSQL_PORT}.txt
iostat=`grep "Slave_IO_Running" /tmp/rep${MYSQL_PORT}.txt  |awk '{print $2}'`            
sqlstat=`grep "Slave_SQL_Running" /tmp/rep${MYSQL_PORT}.txt |awk '{print $2}'`           
result=$(cat /tmp/processlist${MYSQL_PORT}.txt|wc -l)
echo iostat:$iostat and sqlstat:$sqlstat 
# if slave_IO_Running and Slave_sql_Running ok,then return 200 code 
if [ "$result" -gt "3" ] && [ "$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

vi /opt/shell/mysqlchk_status_3307.sh

#!/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="3307"
MYSQL_USERNAME="mysqlcheck"
MYSQL_PASSWORD="paSSword"
MYSQL_PATH="/opt/mysql/bin/"
# 
# We perform a simple query that should return a few results 
#${MYSQL_PATH}mysql -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USERNAME} -p${MYSQL_PASSWORD} -e "show slave status\G;" >/tmp/rep${MYSQL_PORT}.txt
${MYSQL_PATH}mysql -S/data/mysql/mysql.sock -u${MYSQL_USERNAME} -p${MYSQL_PASSWORD} -e "show full processlist;" >/tmp/processlist${MYSQL_PORT}.txt
${MYSQL_PATH}mysql -S/data/mysql/mysql.sock -u${MYSQL_USERNAME} -p${MYSQL_PASSWORD} -e "show slave status\G;" >/tmp/rep${MYSQL_PORT}.txt
iostat=`grep "Slave_IO_Running" /tmp/rep${MYSQL_PORT}.txt  |awk '{print $2}'`            
sqlstat=`grep "Slave_SQL_Running" /tmp/rep${MYSQL_PORT}.txt |awk '{print $2}'`           
result=$(cat /tmp/processlist${MYSQL_PORT}.txt|wc -l)
#echo iostat:$iostat and sqlstat:$sqlstat 
echo $result
# if slave_IO_Running and Slave_sql_Running ok,then return 200 code 
if [ "$result" -gt "3" ] && [ "$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

chmod 775 /opt/shell/mysqlchk_status_3306.sh
chmod 775 /opt/shell/mysqlchk_status_3307.sh

在mysql slave另行建立一个具有process和slave_client权限的账号。

CREATE USER 'mysqlcheck'@'localhost' IDENTIFIED BY 'PaSSword';
GRANT PROCESS , REPLICATION CLIENT ON * . * TO 'mysqlcheck'@'localhost' IDENTIFIED BY 'PaSSword' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
flush privileges;

测试脚本
./mysqlchk_status_3306.sh

添加服务
绑定内网ip,运行于930端口,只开放给192.168.0内网
yum install -y xinetd
vim /etc/xinetd.d/mysql_status

service mysqlchk_status3306
{ 
        flags           = REUSE 
        socket_type     = stream 
        bind            = 192.168.0.104
        port            = 9300
        wait            = no 
        user            = nobody 
        server          = /opt/shell/mysqlchk_status_3306.sh
        log_type        = FILE /dev/null
        log_on_failure  += USERID 
        disable         = no 
        only_from       = 192.168.0.0/24 
}
service mysqlchk_status3307
{ 
        flags           = REUSE 
        socket_type     = stream 
        bind            = 192.168.0.104
        port            = 9301
        wait            = no 
        user            = nobody 
        server          = /opt/shell/mysqlchk_status_3307.sh
        log_type        = FILE /dev/null
        log_on_failure  += USERID 
        disable         = no 
        only_from       = 192.168.0.0/24 
}

bind和only_from的ip地址要有haproxy能请求的权限,使用drbd用0.0.0.0
user要用server脚本的执行权限
port端口要在/etc/service 中声明

chattr -i /etc/services
vi /etc/services

mysqlchk_status3306    9300/tcp			#haproxy mysql check
mysqlchk_status3307    9301/tcp			#haproxy mysql check

services中的mysqlchk_status3306 要和xinetd.d中service名对应

打开iptables

iptables -A INPUT -p tcp -m tcp -s 192.168.0.0/24 --dport 9300 -j ACCEPT
iptables -A INPUT -p tcp -m tcp -s 192.168.0.0/24 --dport 9301 -j ACCEPT

/etc/init.d/iptables save

添加自启动及启动服务
chkconfig xinetd –level 345 on
/etc/init.d/xinetd start

查看是否运行
netstat -lntp

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name   
tcp        0      0 0.0.0.0:9300                0.0.0.0:*                   LISTEN      4863/xinetd         
tcp        0      0 0.0.0.0:9301                0.0.0.0:*                   LISTEN      4863/xinetd

如果没有的话注意检测下bind地址及服务端口

在监控机运行测试
telnet 192.168.0.104 9300

Trying 192.168.0.104...
Connected to 192.168.0.104 (192.168.0.104).
Escape character is '^]'.
/opt/shell/mysqlchk_status_3306.sh: line 24: /tmp/processlist3306.txt: Permission denied
/opt/shell/mysqlchk_status_3306.sh: line 25: /tmp/rep3306.txt: Permission denied
HTTP/1.1 200 OK
Connection closed by foreign host.

之前用root运行过所以报错,在被监控机删除临时文件

rm -f /tmp/processlist3306.txt /tmp/processlist3307.txt
rm -f /tmp/rep3306.txt /tmp/rep3307.txt

没有输出则需检查mysqlchk_status_3306.sh脚本执行权限

启动后/var/log/messages 中会有很多日志

Oct 23 14:37:00 lova xinetd[11057]: START: mysqlchk_status3306 pid=11464 from=192.168.0.22
Oct 23 14:37:00 lova xinetd[11057]: EXIT: mysqlchk_status3306 status=0 pid=11464 duration=0(sec)
Oct 23 14:37:05 lova xinetd[11057]: START: mysqlchk_status3306 pid=11494 from=192.168.0.22
Oct 23 14:37:05 lova xinetd[11057]: EXIT: mysqlchk_status3306 status=0 pid=11494 duration=0(sec)

在haproxy配置中将日志输出到黑洞
log_type = FILE /dev/null

查看监控

直接访问localhost是503

http://localhost/

503 Service Unavailable

No server is available to handle this request.

加上admin-status

http://localhost/admin-status

应用时需在slave mysql上的mysql添加通过haproxy的用户权限

haproxy的命令
/etc/init.d/haproxy
Usage: haproxy {start|stop|restart|reload|condrestart|status|check}

附
优化time_wait,防止端口耗尽
vi /etc/sysctl.conf

net.ipv4.ip_local_port_range = 1025 65000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 15
net.ipv4.tcp_max_tw_buckets = 35000

sysctl -p

使用nginx反向代理haprox后台

#省略
listen  admin_status
    mode  http
    bind 192.168.0.107:8000
    option httplog
    log global
    stats enable
    stats refresh 30s
    stats hide-version
    stats realm Haproxy\ Statistics
    #stats uri  /admin-status
    stats uri  /haproxy/
    #stats auth  admin:123456
    #stats admin if TRUE

nginx.conf

#省略
             location ~* ^/haproxy/
             {
		  proxy_pass		http://192.168.0.107:8000;
		  proxy_set_header	Host		$host;
		  proxy_set_header	X-Real-IP	$remote_addr;
		  proxy_set_header	X-Forwarded-For	$proxy_add_x_forwarded_for;
		  #proxy_set_header	X-Forwarded-For	$remote_addr;
		  proxy_redirect	off;
             }
#省略

参考:

http://linux.die.net/man/5/xinetd.conf

http://adslroot.blogspot.com/2013/12/haproxy-mysql.html

http://sssslide.com/www.slideshare.net/Severalnines/haproxy-mysql-slides