IT博客汇
  • 首页
  • 精华
  • 技术
  • 设计
  • 资讯
  • 扯淡
  • 权利声明
    • 登录 注册

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

    C1G发表于 2014-11-07 06:59:59
    love 0

    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/
    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
    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
    25. frontend mysql
    26. bind 192.168.0.107:3306
    27. maxconn 3000
    28. default_backend mysql_slave
    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
    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. #
    15. MYSQL_HOST="localhost"
    16. MYSQL_PORT="3306"
    17. MYSQL_USERNAME="mysqlcheck"
    18. MYSQL_PASSWORD="paSSword"
    19. MYSQL_PATH="/opt/mysql/bin/"
    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" ];
    33. then
    34. # mysql is fine, return http 200
    35. /bin/echo -e "HTTP/1.1 200 OK\r\n"
    37. else
    38. # mysql is down, return http 503
    39. /bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"
    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. #
    15. MYSQL_HOST="localhost"
    16. MYSQL_PORT="3307"
    17. MYSQL_USERNAME="mysqlcheck"
    18. MYSQL_PASSWORD="paSSword"
    19. MYSQL_PATH="/opt/mysql/bin/"
    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"
    37. else
    38. # mysql is down, return http 503
    39. /bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"
    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';
    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 ;
    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
    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
    3. net.ipv4.tcp_tw_reuse = 1
    4. net.ipv4.tcp_tw_recycle = 1
    6. net.ipv4.tcp_fin_timeout = 15
    7. net.ipv4.tcp_max_tw_buckets = 35000

    sysctl -p

    使用nginx反向代理haprox后台

    1. #省略
    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

    Related Posts

    • MariaDB/MySQL 概率性任意密码(身份认证)登录漏洞 建议升级到5.5.24以上(CVE-2012-2122) ( 2012-06-12)
    • Oracle发布了本季安全补丁,包含了mysql在内的高危漏洞补丁 ( 2012-04-19)
    • mysql5.1.26rc升级至Percona mysql5.5.17 ( 2011-12-16)


沪ICP备19023445号-2号
友情链接