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" # # 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@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] 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] 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)