MySQL利用HaProxy进行负载均衡

1.安装多台数据库

2.编译haproxy

  wget http://www.haproxy.org/download/1.6/src/haproxy-1.6.4.tar.gz
  tar xzvf haproxy-1.6.4.tar.gz
  cd haproxy-1.6.4
  make TARGET=linux2628 PREFIX=/opt/haproxy
  make install PREFIX=/opt/haproxy

3.增加配置并且启动

global
    maxconn 51200 #默认最大连接数 
    #uid 99
    #gid 99
    daemon    #以后台形式运行haproxy
    #quiet
    nbproc 1   #进程数量(可以设置多个进程提高性能) 
    pidfile run/haproxy.pid #haproxy的pid存放路径,启动进程的用户必须有权限访问此文件 
  
defaults
    mode tcp      #所处理的类别 (#7层 http;4层tcp ) 
    option redispatch  #serverId对应的服务器挂掉后,强制定向到其他健康的服务器 
    option abortonclose #当服务器负载很高的时候，自动结束掉当前队列处理比较久的连接 
    timeout connect 5000s  #连接超时
    timeout client 50000s  #客户端超时
    timeout server 50000s  #服务器超时
    log 127.0.0.1 local0   #错误日志记录
    balance roundrobin     #默认的负载均衡的方式,轮询方式 
  
listen proxy
    bind 10.1.6.173:3366  #监听端口 
    mode tcp        #http的7层模式
    #option httpchk    #心跳检测的文件
    option mysql-check user root   #mysql健康检查  root为mysql登录用户名
    balance round robin            #调度算法
    server db1 10.1.6.203:3306 weight 1 check port 9222 inter 12000 rise 3 fall 3   
    #服务器定义,check inter 12000是检测心跳频率 rise 3是3次正确认为服务器可用， fall 3是3次失败认为服务器不可用，weight代表权重 
    server db2 10.1.6.205:3306 weight 1 check port 9222 inter 12000 rise 3 fall 3
  
listen haproxy_stats
    mode http
    bind 10.1.6.173:8888
    option httplog
    stats refresh 5s  
    stats uri /status #网站健康检测URL，用来检测HAProxy管理的网站是否可以用，正常返回200，不正常返回503 
    stats realm Haproxy Manager
    stats auth admin:p@123456 #账号密码

4.增加mysql-check

    #!/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_PATH="/opt/mysql/bin/"
    MYSQL_PORT="3306"
    MYSQL_USERNAME=$1
    MYSQL_PASSWORD=$2
     
    #
    # 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