MySQL-HAProxy

0.readme

readme

create-user.sql

mysql> create user 'mysqlchkuser'@'localhost' identified by 'mysql321';
mysql> GRANT SELECT on mysql.* TO 'mysqlchkusr'@'localhost'  identified by 'mysql321' with grant option;
mysql> flush privileges;

mysql> show grants for mysqlchkusr;
+------------------------------------------------------------------------------------------------------------+
| Grants for mysqlchkusr@%                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'mysqlchkusr'@'%' IDENTIFIED BY PASSWORD '*4ADE5E38BA4BB05808B2EBDF16E4175E9EA590D5' |
| GRANT SELECT ON `mysql`.* TO 'mysqlchkusr'@'%' WITH GRANT OPTION                                           |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> 

haproxy.cfg

#[root@master mysql]# cat /usr/local/etc/haproxy.cfg 
global
log 127.0.0.1 local0
maxconn 4096
user haproxy
group haproxy
daemon

defaults
log global
mode tcp
option tcplog
option dontlognull
retries 3
option redispatch
maxconn 2000
contimeout 4000
clitimeout 50000
srvtimeout 30000
stats enable
stats scope .

frontend mysql_cluster
bind 111.68.112.42:3306
default_backend mysql_cluster

backend mysql_cluster
mode tcp
balance roundrobin
stats enable
option tcpka
option httpchk
server db01 111.68.112.43:3306 weight 1 check port 9200 inter 5s rise 2 fall 2
server db02 111.68.112.44:3306 weight 1 check port 9200 inter 5s rise 2 fall 2

listen stats 111.68.112.42:31337
mode http
option httpclose
balance roundrobin
stats uri /
stats realm Haproxy\ Statistics
#stats auth user:pass
#[root@master mysql]# 

haproxy.m1-mysql.cnf

##[root@srv15 ~]# cat /etc/my.cnf
[mysqld]
port=3306
datadir=/home/mysql
socket=/usr/local/mysql/tmp/mysql.sock
user=mysql
old_passwords=0
#symbolic-links=0
query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

log = 1
long_query_time = 1
slow_query_log	= 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
general_log	= 1
general_log_file =	/var/log/mysqld.log

server_id           = 2
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1

bind-address = 0.0.0.0
local-infile = 0
##[root@srv15 ~]# 

haproxy.m2-mysql.cnf

##[root@srv14 ~]# cat /etc/my.cnf
[mysqld]
port=3306
datadir=/home/mysql/
socket=/usr/local/mysql/tmp/mysql.sock
user=mysql
old_passwords=0
#symbolic-links=0
#query_cache_size = 268435456
query_cache_size = 256M
query_cache_type=1
query_cache_limit = 1048576

log = 1
long_query_time = 1
slow_query_log	= 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
general_log	= 1
general_log_file =	/var/log/mysqld.log

server_id           = 1
log_bin             = /var/log/mysql/mysql-bin.log 
log_bin_index       = /var/log/mysql/mysql-bin.log.index 
relay_log           = /var/log/mysql/mysql-relay-bin 
relay_log_index     = /var/log/mysql/mysql-relay-bin.index 
expire_logs_days    = 10 
max_binlog_size     = 100M 
log_slave_updates   = 1

bind-address = 0.0.0.0
local-infile = 0

opt.mysqlchk.bash

#!/bin/bash
# /opt/mysqlchk 
# 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
#
# Author: Unai Rodriguez
#
# It is recommended that a low-privileged-mysql user is created to be used by
# this script. Something like this:
#
# mysql> GRANT SELECT on mysql.* TO 'mysqlchkusr'@'localhost' \
#     -> IDENTIFIED BY '257retfg2uysg218' WITH GRANT OPTION;
# mysql> flush privileges;

MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USERNAME="mysqlchkusr"
MYSQL_PASSWORD="mysql321"

TMP_FILE="/tmp/mysqlchk.out"
ERR_FILE="/tmp/mysqlchk.err"

#
# We perform a simple query that should return a few results :-p
#
/usr/sbin/mysql --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME \
	--password=$MYSQL_PASSWORD -e"show databases;" > $TMP_FILE 2> $ERR_FILE

#
# Check the output. If it is not empty then everything is fine and we return
# something. Else, we just do not return anything.
#
if [ "$(/bin/cat $TMP_FILE)" != "" ]
then
	# mysql is fine, return http 200
	/bin/echo -e "HTTP/1.1 200 OK\r\n"
	/bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
	/bin/echo -e "\r\n"
	/bin/echo -e "MySQL is running.\r\n"
	/bin/echo -e "\r\n"
else
	# mysql is fine, return http 503
	/bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"
	/bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
	/bin/echo -e "\r\n"
	/bin/echo -e "MySQL is *down*.\r\n"
	/bin/echo -e "\r\n"
fi

xinetd.mysqlchk.sh

# /etc/xinetd.d/mysqlchk
# default: on
# description: mysqlchk
service mysqlchk
{
        flags           = REUSE
        socket_type     = stream
        port            = 9200
        wait            = no
        user            = nobody
        server          = /opt/mysqlchk
        log_on_failure  += USERID
        disable         = no
        only_from       = 0.0.0.0/0 # recommended to put the IPs that need
                                    # to connect exclusively (security purposes)
        per_source      = UNLIMITED # Recently added (May 20, 2010)
                                    # Prevents the system from complaining
                                    # about having too many connections open from
                                    # the same IP. More info:
                                    # http://www.linuxfocus.org/English/November2000/article175.shtml
}