一.安装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.gzcd haproxy-1.4.25make target=linux26make installmkdir -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/haproxychown 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 #quietdefaults log global mode tcp #option httplog option dontlognull retries 3 option redispatch maxconn 2000 contimeout 5000 clitimeout 50000 srvtimeout 50000frontend mysql bind 192.168.0.107:3306 maxconn 3000 default_backend mysql_slavebackend 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 3listen 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 onservice 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=localhostmysql_port=3306mysql_username=mysqlcheckmysql_password=passwordmysql_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}.txtiostat=`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=localhostmysql_port=3307mysql_username=mysqlcheckmysql_password=passwordmysql_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}.txtiostat=`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 checkmysqlchk_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 acceptiptables -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 deniedhttp/1.1 200 okconnection closed by foreign host.
之前用root运行过所以报错,在被监控机删除临时文件
rm -f /tmp/processlist3306.txt /tmp/processlist3307.txtrm -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.22oct 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.22oct 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 65000net.ipv4.tcp_tw_reuse = 1net.ipv4.tcp_tw_recycle = 1net.ipv4.tcp_fin_timeout = 15net.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