使用HAProxy给MySQL slave群进行负载均衡和状态监控
来源:互联网 发布:对网络的看法1000字 编辑:程序博客网 时间:2024/06/03 11:17
一.安装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
- 使用HAProxy给MySQL slave群进行负载均衡和状态监控
- haproxy给mysql做负载均衡
- Haproxy+多台MySQL从服务器(Slave) 实现负载均衡
- Haproxy+多台MySQL从服务器(Slave) 实现负载均衡
- HAProxy MySQL负载均衡
- 利用Haproxy给MySQL Cluster做负载均衡
- 在Linux 下 用HAProxy给MySQL做负载均衡
- 使用haproxy给mail server做负载均衡
- Mysql的Haproxy反向代理和负载均衡
- Mysql的Haproxy反向代理和负载均衡
- Haproxy搭建MySQL主备和Tomcat负载均衡集群
- haproxy实现mysql从库负载均衡
- 【小镇的技术天梯】lanmp服务器集群搭建(5)haproxy给mysql节点负载均衡
- 负载均衡工具haproxy安装,配置,使用
- 负载均衡工具haproxy安装,配置,使用
- 负载均衡工具haproxy安装,配置,使用
- 负载均衡工具haproxy安装,配置,使用
- 负载均衡工具haproxy安装,配置,使用
- wmsys.wm_concat
- hdu1009 FatMouse' Trade(贪心)
- Thinkphp部署模式下缓存问题
- Gson数据的解析。
- java学习体系及java基础知识
- 使用HAProxy给MySQL slave群进行负载均衡和状态监控
- 实例说明optimize table在优化MySQL时很重要
- Service1
- eclipse 错误提示API level 11 (current min is 8)android.app.Activity#getActionBar解决办法
- Effective C++ 43条 处理模板化基类内的名称
- 鸡啄米vc++2010系列32(标签控件Tab Control 下)
- 值得我们了解的Web调试利器fiddler
- 摇一摇 周边 与 ibeacon
- 查询数据库的表名称和列名称