Blog Emka

Tutorial Linux, Pemrograman dan Teknologi

Instalasi dan Konfigurasi HAProxy sebagai Load Balancer MySQL

by · February 24, 2012

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.

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

2. Konfigurasi HAProxy

Contoh konfigurasi HAProxy:

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

Uji Coba Konfigurasi HAProxy

1. Pastikan dulu syntax konfigurasi HAProxy yang kita buat sudah benar.

./haproxy -f ./haproxy.cfg -c

2. Jalankan HAProxy
Silakan jalankan HAProxy untuk mencoba syntax yang kita tulis, silakan matikan lagi untuk konfigurasi selanjutnya.

./haproxy3 -f ./haproxy.cfg -p /var/run/haproxy.pid

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:

#!/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

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

# /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
}

c. Kemudian pastikan bahwa skrip kita telah terdapat pada daftar services sistem (/etc/services).

[email protected] 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

d. Siapkan user pengecek kondisi server (sesuai nama user dalam skrip-skrip diatas, misalnya mysqlchkuser).

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 [email protected]%                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| 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>

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.

#!/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

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

# /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
}

c. Kemudian pastikan bahwa skrip kita telah terdapat pada daftar services sistem (/etc/services).

[email protected] ~# cat /etc/services | grep 9200
#wap-wsp		9200/tcp			# WAP connectionless session service
wap-wsp		9200/udp			# WAP connectionless session service
mysqlchk	9200/tcp

d. Siapkan user pengecek kondisi server (sesuai nama user dalam skrip-skrip diatas, misalnya mysqlchkuser).

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 [email protected]%                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| 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>

e. Berikan permission 777 untuk folder /tmp/mysqlchk.* (atau sesuaikan menurut skrip anda)

# chmod 777 /tmp/mysqlchk.*

Uji Coba HAProxy

Silakan uji coba HAProxy dengan menjalankannya berikut file konfigurasi yang telah kita buat.

# /usr/local/sbin/haproxy -f /usr/local/etc/haproxy.cfg -p /var/run/haproxy.pid

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)

Tags:

Wagiman Wiryosukiro

Petani Sistem Informasi, tukang las plugin & themes Wordpress. Co-Founder SistemInformasi.biz. Saat ini aktif sebagai Developer & kontributor di OpenMandriva Linux.

You may also like...

2 Responses

  1. Dheny Muhammad Ismail says:
    December 15, 2013 at 4:47 pm

    sudah pernah di test belum ini? server virtual atau fisik? 🙂

    Reply
  1. DevOps Expert | Haproxy - The Reliable, High Performance TCP/HTTP Load Balancer - QODR Bee
    April 3, 2019

    […] Instalasi dan Konfigurasi HAProxy sebagai Load Balancer MySQL […]

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: