Skip to content


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

blog_haproxy

一.安装haproxy

haproxy机器
http://haproxy.1wt.deu
需翻墙

  1. tar zxvf haproxy-1.4.25.tar.gz
  2. cd haproxy-1.4.25
  3. make TARGET=linux26
  4. make install
  5. mkdir -p /usr/local/haproxy/
  6. chown nobody:nobody /usr/local/haproxy/
  7. mkdir /etc/haproxy/
  8. cp examples/haproxy.cfg /etc/haproxy/
  9.  
  10. cp examples/haproxy.init /etc/init.d/haproxy
  11. chown root:root /etc/init.d/haproxy
  12. chmod 700 /etc/init.d/haproxy

修改haproxy启动脚本

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

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

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

  1. global
  2. #log 127.0.0.1 local0
  3. log 127.0.0.1 local3 info
  4. #log loghost local0 info
  5. maxconn 4096
  6. chroot /usr/local/haproxy
  7. uid nobody
  8. gid nobody
  9. daemon
  10. debug
  11. #quiet
  12.  
  13. defaults
  14. log global
  15. mode tcp
  16. #option httplog
  17. option dontlognull
  18. retries 3
  19. option redispatch
  20. maxconn 2000
  21. contimeout 5000
  22. clitimeout 50000
  23. srvtimeout 50000
  24.  
  25. frontend mysql
  26. bind 192.168.0.107:3306
  27. maxconn 3000
  28. default_backend mysql_slave
  29.  
  30. backend mysql_slave 
  31. #cookie SERVERID rewrite
  32. mode tcp
  33. balance roundrobin
  34. #balance source
  35. #balance leastconn
  36. contimeout 10s
  37. timeout check 2s
  38. option httpchk OPTIONS * HTTP/1.1\r\nHost:\ www
  39. server mysql_192_168_0_104_3306 192.168.0.104:3306 weight 1 check port 9300 inter 5s rise 2 fall 3
  40. server mysql_192_168_0_104_3307 192.168.0.104:3307 weight 1 check port 9301 inter 5s rise 2 fall 3
  41. #server mysql_192_168_0_106_3306 192.168.0.106:3306 weight 1 check port 9300 inter 5s rise 2 fall 3
  42.  
  43. listen  admin_status
  44. mode  http
  45. bind 192.168.0.107:8000
  46. option httplog
  47. log global
  48. stats enable
  49. stats refresh 30s
  50. stats hide-version
  51. stats realm Haproxy\ Statistics
  52. stats uri  /admin-status
  53. stats auth  admin:123456
  54. stats admin if TRUE

打开监控的iptables

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

添加自启动并启动haproxy服务

  1. chkconfig –add haproxy
  2. chkconfig haproxy on
  3. service haproxy start

被监控机上

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

  1. #!/bin/bash
  2. #
  3. # /usr/local/bin/mysqlchk_status.sh
  4. #
  5. # This script checks if a mysql server is healthy running on localhost. It will
  6. # return:
  7. #
  8. # "HTTP/1.x 200 OK\r" (if mysql is running smoothly)
  9. #
  10. # – OR –
  11. #
  12. # "HTTP/1.x 503 Internal Server Error\r" (else)
  13. #
  14.  
  15. MYSQL_HOST="localhost"
  16. MYSQL_PORT="3306"
  17. MYSQL_USERNAME="mysqlcheck"
  18. MYSQL_PASSWORD="paSSword"
  19. MYSQL_PATH="/opt/mysql/bin/"
  20.  
  21. #
  22. # We perform a simple query that should return a few results
  23. #${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
  24. ${MYSQL_PATH}mysql -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USERNAME} -p${MYSQL_PASSWORD} -e "show full processlist;" >/tmp/processlist${MYSQL_PORT}.txt
  25. ${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
  26. iostat=`grep "Slave_IO_Running" /tmp/rep${MYSQL_PORT}.txt  |awk '{print $2}'`           
  27. sqlstat=`grep "Slave_SQL_Running" /tmp/rep${MYSQL_PORT}.txt |awk '{print $2}'`           
  28. result=$(cat /tmp/processlist${MYSQL_PORT}.txt|wc -l)
  29. echo iostat:$iostat and sqlstat:$sqlstat
  30. # if slave_IO_Running and Slave_sql_Running ok,then return 200 code
  31. if [ "$result" -gt "3" ] && [ "$iostat" = "Yes" ] && [ "$sqlstat" = "Yes" ];
  32.  
  33. then
  34.         # mysql is fine, return http 200
  35.         /bin/echo -e "HTTP/1.1 200 OK\r\n"
  36.         
  37. else
  38.         # mysql is down, return http 503
  39.         /bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"
  40.         
  41. fi

vi /opt/shell/mysqlchk_status_3307.sh

  1. #!/bin/bash
  2. #
  3. # /usr/local/bin/mysqlchk_status.sh
  4. #
  5. # This script checks if a mysql server is healthy running on localhost. It will
  6. # return:
  7. #
  8. # "HTTP/1.x 200 OK\r" (if mysql is running smoothly)
  9. #
  10. # – OR –
  11. #
  12. # "HTTP/1.x 503 Internal Server Error\r" (else)
  13. #
  14.  
  15. MYSQL_HOST="localhost"
  16. MYSQL_PORT="3307"
  17. MYSQL_USERNAME="mysqlcheck"
  18. MYSQL_PASSWORD="paSSword"
  19. MYSQL_PATH="/opt/mysql/bin/"
  20.  
  21. #
  22. # We perform a simple query that should return a few results
  23. #${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
  24. ${MYSQL_PATH}mysql -S/data/mysql/mysql.sock -u${MYSQL_USERNAME} -p${MYSQL_PASSWORD} -e "show full processlist;" >/tmp/processlist${MYSQL_PORT}.txt
  25. ${MYSQL_PATH}mysql -S/data/mysql/mysql.sock -u${MYSQL_USERNAME} -p${MYSQL_PASSWORD} -e "show slave status\G;" >/tmp/rep${MYSQL_PORT}.txt
  26. iostat=`grep "Slave_IO_Running" /tmp/rep${MYSQL_PORT}.txt  |awk '{print $2}'`           
  27. sqlstat=`grep "Slave_SQL_Running" /tmp/rep${MYSQL_PORT}.txt |awk '{print $2}'`           
  28. result=$(cat /tmp/processlist${MYSQL_PORT}.txt|wc -l)
  29. #echo iostat:$iostat and sqlstat:$sqlstat
  30. echo $result
  31. # if slave_IO_Running and Slave_sql_Running ok,then return 200 code
  32. if [ "$result" -gt "3" ] && [ "$iostat" = "Yes" ] && [ "$sqlstat" = "Yes" ];
  33. then
  34.         # mysql is fine, return http 200
  35.         /bin/echo -e "HTTP/1.1 200 OK\r\n"
  36.         
  37. else
  38.         # mysql is down, return http 503
  39.         /bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"
  40.         
  41. fi

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

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

  1. CREATE USER 'mysqlcheck'@'localhost' IDENTIFIED BY 'PaSSword';
  2.  
  3. 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 ;
  4.  
  5. flush privileges;

测试脚本
./mysqlchk_status_3306.sh

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

  1. service mysqlchk_status3306
  2. {
  3.         flags           = REUSE
  4.         socket_type     = stream
  5.         bind            = 192.168.0.104
  6.         port            = 9300
  7.         wait            = no
  8.         user            = nobody
  9.         server          = /opt/shell/mysqlchk_status_3306.sh
  10.         log_type        = FILE /dev/null
  11.         log_on_failure  += USERID
  12.         disable         = no
  13.         only_from       = 192.168.0.0/24
  14. }
  15. service mysqlchk_status3307
  16. {
  17.         flags           = REUSE
  18.         socket_type     = stream
  19.         bind            = 192.168.0.104
  20.         port            = 9301
  21.         wait            = no
  22.         user            = nobody
  23.         server          = /opt/shell/mysqlchk_status_3307.sh
  24.         log_type        = FILE /dev/null
  25.         log_on_failure  += USERID
  26.         disable         = no
  27.         only_from       = 192.168.0.0/24
  28. }

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

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

  1. mysqlchk_status3306    9300/tcp #haproxy mysql check
  2. mysqlchk_status3307    9301/tcp #haproxy mysql check

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

打开iptables

  1. iptables -A INPUT -p tcp -m tcp -s 192.168.0.0/24 --dport 9300 -j ACCEPT
  2. 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

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

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

在监控机运行测试
telnet 192.168.0.104 9300

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

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

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

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

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

  1. Oct 23 14:37:00 lova xinetd[11057]: START: mysqlchk_status3306 pid=11464 from=192.168.0.22
  2. Oct 23 14:37:00 lova xinetd[11057]: EXIT: mysqlchk_status3306 status=0 pid=11464 duration=0(sec)
  3. Oct 23 14:37:05 lova xinetd[11057]: START: mysqlchk_status3306 pid=11494 from=192.168.0.22
  4. 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

  1. net.ipv4.ip_local_port_range = 1025 65000
  2.  
  3. net.ipv4.tcp_tw_reuse = 1
  4. net.ipv4.tcp_tw_recycle = 1
  5.  
  6. net.ipv4.tcp_fin_timeout = 15
  7. net.ipv4.tcp_max_tw_buckets = 35000

sysctl -p

使用nginx反向代理haprox后台

  1. #省略
  2.  
  3. listen  admin_status
  4.     mode  http
  5.     bind 192.168.0.107:8000
  6.     option httplog
  7.     log global
  8.     stats enable
  9.     stats refresh 30s
  10.     stats hide-version
  11.     stats realm Haproxy\ Statistics
  12.     #stats uri  /admin-status
  13.     stats uri  /haproxy/
  14.     #stats auth  admin:123456
  15.     #stats admin if TRUE

nginx.conf

  1. #省略
  2.              location ~* ^/haproxy/
  3.              {
  4.   proxy_pass http://192.168.0.107:8000;
  5.   proxy_set_header Host $host;
  6.   proxy_set_header X-Real-IP $remote_addr;
  7.   proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
  8.   #proxy_set_header X-Forwarded-For $remote_addr;
  9.   proxy_redirect off;
  10.              }
  11. #省略

参考:
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

Posted in haproxy/Atlas, 技术.

Tagged with , , , .


No Responses (yet)

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.



Some HTML is OK

or, reply to this post via trackback.