简体中文（ZH-CN）English（EN）繁体中文（ZH-TW）日本語（JA）한국어（KO）Melayu（MS）Français（FR）Deutsch（DE）
Home
Article
Q&A
Course
Topic
Download
Game
Dictionary
Recent Updates

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

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

WBOY
WBOYOriginal
2016-06-07 16:42:081576browse

一.安装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

blog_haproxy

一.安装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

 


Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：如何设置mysql允许被远程访问Next article：golang go-sql-drive mysql连接池的实现

Related articles

See more