haproxy mysql loadbalancer olarak kullanmak

########lb01-/etc//haproxy/haproxy.cfg--####################
global
    pidfile /var/run/haproxy.pid
    daemon
    user haproxy
    group haproxy
    stats socket /var/run/haproxy.socket    user haproxy    group haproxy    mode 600 level admin
    node haproxy_172.16.33.06
    description haproxy    server

        #* Performance Tuning
    maxconn 8192
    spread-checks 3
    quiet
defaults
        #log    global
    mode tcp
    option  dontlognull
    option tcp-smart-accept
    option tcp-smart-connect
        #option dontlog-normal
    retries 3
    option redispatch
    maxconn 8192
    timeout check 3500ms
    timeout queue 3500ms
    timeout connect 3500ms
    timeout client 10800s
    timeout server 10800s

    userlist STATSUSERS
    group admin users admin
    user admin insecure-password admin-parolasi-degistir
    user    stats insecure-password genel-parola-degistir

listen admin_page
    bind *:9600
    mode http
    stats enable
    stats refresh 60s
    stats uri /
    acl AuthOkay_ReadOnly http_auth(STATSUSERS)
    acl AuthOkay_Admin http_auth_group(STATSUSERS) admin
    stats http-request auth realm admin_page unless AuthOkay_ReadOnly
        #stats admin if AuthOkay_Admin

listen haproxy_172_16_33_06._3306
    bind *:3306
    mode tcp
    timeout client 10800s
    timeout server 10800s
    balance leastconn
    option httpchk
    option allbackups
    default-server port 9200 inter 2s downinter 5s rise 3 fall 2 slowstart 60s    maxconn 500 maxqueue 256 weight 100
    server 172.16.33.11 172.16.33.11:3306 check
    server 172.16.33.12 172.16.33.12:3306 check
    server 172.16.33.13 172.16.33.13:3306 check port 9200  inter 2s downinter 5s rise 3 fall 2 slowstart 60s    maxconn 100 maxqueue 256 weight 100 backup

########lb01-/etc//haproxy/haproxy.cfg--####################





#########mysql server---adding-/etc/services----##############
mysqlchk        9200/tcp
mysqlchk        9200/tcp





#########mysql server---adding-/etc/services----##############

#########mysql server---adding- xinetd services----############

/etc/xinetd.d/mysqlchk
# default: on
# description: mysqlchk
service mysqlchk
{
        flags           = REUSE
        socket_type     = stream
        port            = 9200
        wait            = no
        user            = root
        server          = /usr/local/sbin/mysqlchk
        log_on_failure  += USERID
        log_on_success  =
        disable         = no
        #only_from       = 0.0.0.0/0
        only_from       = 0.0.0.0/0
        per_source      = UNLIMITED
}
#########mysql server---adding- xinetd services----##############



######################mysqlchk######################
#####!/bin/bash
#
# 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 500 Internal Server Error\r" (else)
#
# The purpose of this script is make haproxy capable of monitoring mysql properly
#

MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USERNAME='smysqlchk'
MYSQL_PASSWORD='smy$qlchk*!'
MYSQL_OPTS="-N -q -A --connect-timeout=10"
TMP_FILE="/dev/shm/mysqlchk.$$.out"
ERR_FILE="/dev/shm/mysqlchk.$$.err"
FORCE_FAIL="/dev/shm/proxyoff"
MYSQL_BIN='/usr/bin/mysql'
CHECK_QUERY="show global status where variable_name='wsrep_local_state'"
CHECK_QUERY2="show global variables where variable_name='wsrep_sst_method'"
CHECK_QUERY3="show global variables where variable_name='read_only'"
preflight_check()
{
    for I in "$TMP_FILE" "$ERR_FILE"; do
        if [ -f "$I" ]; then
            if [ ! -w $I ]; then
                echo -e "HTTP/1.1 503 Service Unavailable\r\n"
                echo -e "Content-Type: Content-Type: text/plain\r\n"
                echo -e "\r\n"
                echo -e "Cannot write to $I\r\n"
                echo -e "\r\n"
                exit 1
            fi
        fi
    done
}
return_ok()
{
    echo -e "HTTP/1.1 200 OK\r\n"
    echo -e "Content-Type: text/html\r\n"
    echo -e "Content-Length: 43\r\n"
    echo -e "\r\n"
    echo -e "<html><body>MySQL is running.</body></html>\r\n"
    echo -e "\r\n"
  #  rm $ERR_FILE $TMP_FILE
    exit 0
}
return_fail()
{
    echo -e "HTTP/1.1 503 Service Unavailable\r\n"
    echo -e "Content-Type: text/html\r\n"
    echo -e "Content-Length: 42\r\n"
    echo -e "\r\n"
    echo -e "<html><body>MySQL is *down*.</body></html>\r\n"
#    sed -e 's/\n$/\r\n/' $ERR_FILE
    echo -e "\r\n"
 #   rm $ERR_FILE $TMP_FILE
    exit 1
}
preflight_check
if [ -f "$FORCE_FAIL" ]; then
        echo "$FORCE_FAIL found" > $ERR_FILE
        return_fail;
fi
status=$($MYSQL_BIN $MYSQL_OPTS --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD -e "$CHECK_QUERY;" 2>/dev/null | awk '{print $2;}')
if [ $? -ne 0 ]; then
        return_fail;
fi

if [ $status -eq 2 ] || [ $status -eq 4 ] ; then

    readonly=$($MYSQL_BIN $MYSQL_OPTS --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD -e "$CHECK_QUERY3;" 2>/dev/null | awk '{print $2;}')
    if [ $? -ne 0 ]; then
        return_fail;
    fi   
    if [ "$readonly" = "YES" -o "$readonly" = "ON" ]; then
        return_fail;
    fi

    if [ $status -eq 2 ]; then
        method=$($MYSQL_BIN $MYSQL_OPTS --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD -e "$CHECK_QUERY2;" 2>/dev/null | awk '{print $2;}')
        if [ $? -ne 0 ]; then
            return_fail;
        fi
        if [ -z "$method" ] || [ "$method" = "rsync" ] || [ "$method" = "mysqldump" ]; then
            return_fail;
        fi
    fi
    return_ok;
fi

return_fail;

######################mysqlchk######################