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