Instalasi dan Konfigurasi HAProxy sebagai Load Balancer MySQL

1. Instalasi HAProxy

Unduh sourcecode haproxy terbaru dari http://haproxy.1wt.eu, ekstraks dan compile source codenya. Silakan lakukan dengan aplikasi installer paket (yum, apt-get, urpmi dll) jika kesulitan.

[sourcecode language=”bash”]
wget -c http://haproxy.1wt.eu/download/1.4/src/haproxy-1.4.19.tar.gz
tar -xvf haproxy-1.4.15.tar.gz
cd haproxy-1.4.15
make install
[/sourcecode]

2. Konfigurasi HAProxy

Contoh konfigurasi HAProxy:

[sourcecode language=”bash”]
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
[/sourcecode]

Uji Coba Konfigurasi HAProxy

1. Pastikan dulu syntax konfigurasi HAProxy yang kita buat sudah benar.
[sourcecode]./haproxy -f ./haproxy.cfg -c[/sourcecode]

2. Jalankan HAProxy
Silakan jalankan HAProxy untuk mencoba syntax yang kita tulis, silakan matikan lagi untuk konfigurasi selanjutnya.
[sourcecode]./haproxy3 -f ./haproxy.cfg -p /var/run/haproxy.pid[/sourcecode]

Konfigurasi Server Master Replikasi MySQL

Implementasikan konfigurasi ini pada server master 1.
a. Kita harus membuat service mysql di xinetd (sebuah pengganti inetd), dimana diwakili oleh sebuah skrip yang akan mengecek keadaan server mysql.

Berikut skripnya:
[sourcecode language=”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
[/sourcecode]

b. Selain itu kita juga harus membuat skrip tersebut sebagai services. Berikut skripnya:

[sourcecode language=”bash”]
# /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
}
[/sourcecode]

c. Kemudian pastikan bahwa skrip kita telah terdapat pada daftar services sistem (/etc/services).
[sourcecode]
root@srv14 src# cat /etc/services | grep 9200
#wap-wsp 9200/tcp # WAP connectionless session service
wap-wsp 9200/udp # WAP connectionless session service
mysqlchk 9200/tcp # mysqlchk
[/sourcecode]

d. Siapkan user pengecek kondisi server (sesuai nama user dalam skrip-skrip diatas, misalnya mysqlchkuser).
[sourcecode language=”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>
[/sourcecode]

Implementasikan pada Server master 2
a. Kita harus membuat service mysql di xinetd (sebuah pengganti inetd), dimana diwakili oleh sebuah skrip yang akan mengecek keadaan server mysql.
[sourcecode language=”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"

b. Selain itu kita juga harus membuat skrip tersebut sebagai services. Berikut skripnya:
[sourcecode language=”bash”]
# /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
}
[/sourcecode]

c. Kemudian pastikan bahwa skrip kita telah terdapat pada daftar services sistem (/etc/services).
[sourcecode]
root@srv15 ~# cat /etc/services | grep 9200
#wap-wsp 9200/tcp # WAP connectionless session service
wap-wsp 9200/udp # WAP connectionless session service
mysqlchk 9200/tcp
[/sourcecode]

mysql>
[/sourcecode]

e. Berikan permission 777 untuk folder /tmp/mysqlchk.* (atau sesuaikan menurut skrip anda)
[sourcecode]# chmod 777 /tmp/mysqlchk.*[/sourcecode]

Uji Coba HAProxy

Silakan uji coba HAProxy dengan menjalankannya berikut file konfigurasi yang telah kita buat.
[sourcecode]
# /usr/local/sbin/haproxy -f /usr/local/etc/haproxy.cfg -p /var/run/haproxy.pid
[/sourcecode]

Nah, untuk memonitor service di kedua server, silakan gunakan webbrowser untuk melihatnya di http://ip-address-balancer-anda:31337/.

Diramu dari berbagai artikel HEBAT di :
1. LinuxAdminZone.com (http://linuxadminzone.com/how-to-install-setup-and-config-haproxy-loadbalancer-for-content-switching/)
2. ALinux.web.id (http://alinux.web.id/2011/08/17/load-balancing-mysql-replication-master-to-master-with-haproxy.html)
3. Dan Cryer (http://www.dancryer.com/2010/01/load-balancing-mysql-with-ha-proxy)