Install and Configure HAProxy for MySQL Galera Cluster

In this post, I’m going to cover on how to add load balancer for the MariaDB Galera Cluster. The major steps are:

  1. Ensure all Galera nodes are running as a single cluster (all nodes are primary and synced).
  2. Install HAproxy (you can install it on separate node or on the application server).
  3. Configure clustercheck script. This script performs health check on each backend server.
  4. Configure HAproxy statistic page.
  5. Point the application to load balancer.

Our architecture looks like below:


Configure clustercheck script

* Steps described in this section should be performed on all DB nodes unless specified otherwise.

1. Firstly, we need to configure the backend health check reporting. We will use Percona’s clustercheck script available here. Get the script and put it under /usr/local/bin by running following commands:

$ git clone
$ cp percona-clustercheck/clustercheck /usr/local/bin

2. The clustercheck script performs regular check on the Galera node by monitoring several MySQL variables/status. It yields a simple HTML output with corresponding HTTP return code (either 503 – Service Unavailable or 200 – OK). To make things easier for HAproxy to trigger the script and get the latest status of the backend, we have to make it listens to a port. We can use xinetd to turn the script into a service daemon and make it listen to a custom port, in this case, I’m going to use 9200.

Create a new file called /etc/xinet.d/mysqlchk, and add following lines:

# default: on
# description: mysqlchk
service mysqlchk
  disable = no
  flags = REUSE
  socket_type = stream
  port = 9200
  wait = no
  user = nobody
  server = /usr/local/bin/clustercheck
  log_on_failure += USERID
  only_from =
  per_source = UNLIMITED

3. Then, we need to add the mysqlchk service into xinetd:

$ echo 'mysqlchk      9200/tcp    # MySQL check' >> /etc/services

4. By default, the script will use a MySQL user called “clustercheckuser” with password “clustercheckpassword!”. We need to ensure this MySQL user is exist with the corresponding password before the script would be able to perform health checks. Run following DDL statements on one of the DB node (Galera should replicate the statement to the other nodes):

mysql> GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword!';

You can change this value inside the clustercheck script in line 32,33. In this post, I’m going to use the default username and password.

5. Verify if the script returns a correct value:

$ /usr/local/bin/clustercheck > /dev/null
$ echo $?

If the DB node is in synced, you should get 0. Otherwise 1 should be the output. The backend health check is configured.

Install HAproxy

* Steps described in this section should be performed on HAproxy or application server.

1. The easy way is using package manager (yum/apt). However, it’s highly recommended to use the latest version available on HAproxy site. Either way, I’ll show the the installation steps here.

a) If you choose to install HAproxy via package manager:

$ yum install haproxy # Redhat/CentOS
$ sudo apt-get install haproxy # Debian/Ubuntu

b) Via source from HAproxy download site:

$ yum install php-curl gcc make # Redhat/CentOS
$ apt-get install php5-curl gcc make # Debian/Ubuntu
$ wget
$ tar xvzfz
$ cd
$ make TARGET=linux26
$ cp -f haproxy /usr/sbin/haproxy

Installing from source (option b) comes with no init script. So you have to create it manually or simply provision the process via command line (which is non-standard way). I’m not going to cover this unconventional way in this post.


Configure HAproxy

Now we have HAproxy installed. We need to configure it to listen on port 3307 for MySQL service and perform back-end health checks. On /etc/haproxy/haproxy.cfg, ensure following lines exist:

        pidfile /var/run/
        user haproxy
        group haproxy
        stats socket /var/run/haproxy.socket user haproxy group haproxy mode 600 level admin
        maxconn 8192
        spread-checks 3
        mode    tcp
        option  dontlognull
        option tcp-smart-accept
        option tcp-smart-connect
        retries 3
        option redispatch
        maxconn 8192
        timeout check   3500ms
        timeout queue   3500ms
        timeout connect 3500ms
        timeout client  10800s
        timeout server  10800s
        group admin users admin
        user admin insecure-password admin
        user stats insecure-password yourpassword
listen admin_page
        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
listen  mysql_3307
        bind *:3307
        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 64 maxqueue 128 weight 100
        server db1 check
        server db2 check
        server db3 check

Now enable the service on boot and fire it up:

RHEL/CentOS 6:

$ chkconfig haproxy on # RHEL6
$ service haproxy start # RHEL6

Ubuntu 14.04 and lower, Debian 7 and lower:

$ update-rc.d haproxy defaults
$ sudo service haproxy start

RHEL/CentOS 7, Debian 8, Ubuntu 15.04:

$ systemctl enable haproxy
$ systemctl start haproxy

Verify if HAproxy is listening to the correct ports:

$ sudo netstat -tulpn | grep haproxy
tcp        0      0  *               LISTEN      370/haproxy
tcp        0      0  *               LISTEN      370/haproxy

3307 is the MySQL load-balanced port, while 9600 is the HAproxy statistic page. You can login to check the status by going to http://haproxy_ip_address:9600/ and login with username ‘admin’ and password ‘yourpassword’ as configured inside haproxy.cfg. You should see something like below:

Screen Shot 2015-09-17 at 3.00.03 PM

Now, you can redirect the application or MySQL client to HAproxy host on port 3307, for a load balanaced  MySQL connections with auto failover.

Replacing OpenJDK with Oracle JDK on CentOS 6

I encountered some issues with an application running on Java. I thought it would be fine if running on standard OpenJDK release available at CentOS repository since it is really easy to install with yum, but it wasn’t. The application does required JDK from Oracle release (which is lame).

So here what you should do when you want to replace OpenJDK with Java SE JDK:

1. Check the current OpenJDK version. I am going to retain the same version if possible:

java -version
java version "1.6.0_30"
OpenJDK Runtime Environment (IcedTea6 1.13.3) (rhel-
OpenJDK 64-Bit Server VM (build 23.25-b01, mixed mode)

Check the current RPM package name:

$ rpm -qa | grep openjdk

2. Download Java SE Development Kit 6 update 30, which is equivalent to OpenJDK 1.6.0_30. There is no way (AFAIK) that we can download it directly using curl or wget. You need to have an account with Oracle. Sign up using your web browser and you will get a temporary link similar to below. Copy it from  your web browser and use wget to download it directly to the box:

$ wget -O jdk-6u30-linux-x64-rpm.bin

**You should do this quick. The link will be expired within minutes.

3. Remove the installed OpenJDK package:

$ yum remove -y java-1.6.0-openjdk

4. Install Oracle JDK:

$ chmod 755 jdk-6u30-linux-x64-rpm.bin
$ ./jdk-6u30-linux-x64-rpm.bin

5. Verify the new Java version:

java -version
java version "1.6.0_30"
Java(TM) SE Runtime Environment (build 1.6.0_30-b12)
Java HotSpot(TM) 64-Bit Server VM (build 20.5-b03, mixed mode)

Now my application works like a charm! The new path to JRE now is /usr/java/jdk1.6.0_30/jre/

Linux: Follow and Download using wget

Your Linux box is incomplete if you do not have wget installed. It is a simple CLI based application used to download files from network.  It support HTTP, HTTPS and FTP protocols as well as retrieval through HTTP proxies.



Download latest version of PHPmyAdmin from SourceForge.


You can just simply use the latest link of SourceForge software and wget will automatically follow it. You also need to specify the ‘content-disposition’ flag. This option is useful for some file-downloading CGI programs that use “Content-Disposition” header to describe what the name of a downloaded file should be:

$ wget --content-disposition

Download TokuMX  with cookies and redirection from Tokutek download site.


To download from this page, I was required to have cookies from download.php=tokumx-1.0-3-linux-x86_64.tgz page and use the same cookies to download from download.php?df=1. You can navigate to this page if you want to try that with a web browser,

$ wget --keep-session-cookies \
--save-cookies cookies.txt \ && \
wget --load-cookies cookies.txt \
--content-disposition && \
rm -f download.php* cookies.txt


Download Certain Files from Web Directory


You can download certain files (*.gz) under same directory using wget with -r flag. You need to specify –no-directories and –no-host-directories to output the files in the current directory while –level is the depth of the directory it will go:

$ wget -r --accept="*.gz" \
--no-directories \
--no-host-directories \
--level 1 \


I will keep updating this page to share my collection of wget commands.


Linux: Using lsyncd – Live Syncing (Mirror) Daemon

I have a situation where there is one critical website under our company server is critically need to be sync to our backup server. Sync means whatever changes happen in master server, it will replicated to slave server. Yes, you can rsync. But, I do not want to schedule the task as cron to sync. There is one tool which is better than that and very suitable to be applied in this situation called lsyncd.

Lsyncd watches a local directory trees event monitor interface (inotify or fsevents). It aggregates and combines events for a few seconds and then spawns one (or more) process(es) to synchronize the changes. By default this is rsync. Lsyncd is thus a light-weight live mirror solution that is comparatively easy to install not requiring new filesystems or blockdevices and does not hamper local filesystem performance.

OS: RHEL 5.3 (Tikanga) 64bit
Source IP:
Source directory: /home/webmedia/public_html
Destination IP:
Destination directory: /backup/webmedia/public_html

Destination server

In destination server, we just need to make sure that the destination directory is exist. If not, create the destination folder:

$ mkdir -p /backup/webmedia/public_html

Source server

1. It is good to have embedded host value that point to the IP address. Open /etc/hosts and add destination host value:   backup-server.local

2. We need to allow passwordless authentication to the destination server. We will using root user to execute the sync process. Run following command as root:

$ ssh-keygen -t dsa

Please enter for all prompts. Once done, run following command to copy the public key to destination server:

$ cat ~/.ssh/ | ssh root@backup-server.local “cat >> ~/.ssh/authorized_keys”

Or we can use another command which do the same thing called ssh-copy-id:

$ ssh-copy-id -i ~/.ssh/id_dsa root@backup-server.local

Once done, try to access the destination server with following command. Make sure you can access the server without any password prompted:

$ ssh root@backup-server.local

3. Lsyncd required LUA to be installed. For latest RedHat distribution (RHEL 6) you can use yum to install:

$ yum install -y lua*

In my case, I am using the RHEL 5, and the packages are not available at yum. So I will need to find another place for RPM which is here:

Download these 3 packages and install:

$ mkdir -p /usr/local/src/lua
$ cd /usr/local/src/lua
$ wget
$ wget
$ wget
$ rpm -Uhv *.rpm

4. Download and install lsyncd from this website:

$ cd /usr/local/src
$ wget
$ tar -xzf  lsyncd-2.0.6.tar.gz
$ cd lsyncd-*
$ ./configure
$ make 
$ make install

5. Once done, start the daemon in the source server by using this command:

$ lsyncd -rsyncssh /home/webmedia/public_html root@backup-server.local /backup/webmedia/public_html

Done. The file should be in sync now. You can check the process using ‘ps‘ command and monitor the progress by constantly viewing the destination directory. In some cases, the sync terminate immediately with following error at /var/log/message:

lsyncd: Error, Consider increasing /proc/sys/fs/inotify/max_user_watches

Depending on the directory that you are watching, you might need to increase the max_user_watches to higher value using following command:

$ echo 65500 > /proc/sys/fs/inotify/max_user_watches

Once the synchronization complete, you can make some test by add more files in the source server, after 5 seconds you will see that the files has been synced to the destination directory. To make this daemon run automatically after boot, you can put the lsyncd command into /etc/rc.local.

Linux: Run Command in Many Servers Simultaneously

Server administrators usually have many servers to manage. There will be some time when we need to run the same command in every server we have. In my case, I need to update MySQL version on all 6 database servers we have to the latest version via yum. It just need to run simple command but I need to repeat the process 6 times for each server. Furthermore, we will keep doing this in the future over and over again.

To achieve this, I am going to use Webmin cluster. So all servers must have been installed with Webmin and we need to integrate all of them and joining the Webmin cluster. In this tutorial, I will only use 2 servers as example of setup.

Variables as below:

OS: CentOS 6.2 64bit
Webmin (master)/MySQL server #1:
Webmin (node1)/MySQL server #2:
Webmin username: root
Webmin password: Gn&Pe42#e

1. Download and install Webmin for all servers. You need to repeat step number 1 to 3 on all servers:

$ cd /usr/local/src
$ wget
$ rpm -Uhv  webmin-1.580-1.noarch.rpm

2. Configure firewall to open port 10000 for Webmin communication. Add following line into /etc/sysconfig/iptables before any REJECT line (-j REJECT):

-A INPUT -p tcp -m tcp --dport 10000 -j ACCEPT

Save the file and restart firewall:

$ service iptables restart

3. Start Webmin:

$ service webmin start

4. In master node, access the Webmin via and login as root. Go to Webmin > Webmin Server Index > and click “Register a new server“. Enter the server information as screenshot below:

And click “Save“.

5. The server should now register in the Webmin but not joining the cluster yet. Go to Webmin > Cluster > Cluster Webmin Servers > and click Add Server for “this server” and ““.

6. If the joining is completed, you should see all servers in the managed list like below:

7. Now we can start to run any command on all servers. What I need to do now is go to Webmin > Cluster > Cluster Shell Commands > and enter the command that I need to run and which server you want it to run. I will choose “<All hosts>” and click “Run Command Now”  as screenshot below:

The output will then appeared from both of the servers. In my real case, I need to repeat step 4,5,6 to add another 4 servers and finally just execute the command as step 7. This simple setup is just a great way for your server administration in the future. Cheers!

Upgrade DELL Open Manage Server Administrator (OMSA)

Some of our servers are running on Dell which include Open Manage Server Administrator (OMSA) to manage the physical server  remotely. OMSA can be access via port 1311 in HTTPS by using web browser. Most of the time, I only use OMSA to manage and monitor our physical disk which run on RAID.

The version that I currently use is 6.3.0, which is the old one (because I have not update anything on OMSA since first usage). Following steps show on how to update Dell OMSA with the easiest way. Before upgrade, lets take a look on how OMSA’s current look:

Variable as below:

OS: Red Hat Enterprise Linux Server release 5.6 (Tikanga)
Server IP:

1. We will use yum to update current OMSA installation. Lets add the Dell Open Manage repository into it:

$ cd /usr/local/src
$ wget -q -O - | bash

2. The safest way is to stop and remove the old version:

/opt/dell/srvadmin/sbin/ stop
$ yum remove -y srvadmin*

3. Lets install new OMSA:

$ yum install -y srvadmin-all

4. Log out from SSH and relogin back to reset OMSA path.

5. Start OMSA service to load new update:

$ /opt/dell/srvadmin/sbin/ start

And check whether it runs on the correct port:

$ netstat -tulpn | grep 1311
tcp     0       0*       LISTEN       29960/dsm_om_connsv

Done! Now lets see how our OMSA new look:

It seems Dell has hired great web developer for this new OMSA interface (version 6.5.0). As for me, it is not really matter as long as all required functionalities are still there. Cheers!

Solaris: Setting Up Package Manager and Installer

Oracle Solaris 11 is one of the  most popular UNIX-based operating system which widely download to be used by enterprise user, after FreeBSD. If you have FreeBSD basic knowledge, you should able to play around with Solaris as well.

If FreeBSD we have ports, the package manager and application installer, in Solaris, we can use pkgutil. It is rather simple to use and similar to apt-get and yum on how to use it. We will use OpenCSW, which aims to produce an easy to use open source software distribution installable on top of Solaris and Solaris-based systems, developed by a transparent and democratic community.

My Oracle Solaris version:

$ cat /etc/release
Oracle Solaris 11 11/11 X86
Copyright (c) 1983, 2011, Oracle and/or its affiliates. All rights reserved.
Assembled 18 October 2011

1. Run following command as root to install the pkgutil application:

$ pkgadd -d

2. We need to set some environment for CSW. Open ~/.profile using text editor:

$ nano ~/.profile

And append :/opt/csw/bin at the end of PATH line and add another line for XFILESEARCHPATH so it will be shown like below:

export PATH=/usr/bin:/usr/sbin:/opt/csw/bin
export XFILESEARCHPATH=/opt/csw/lib/X11/%T/%N%C:/usr/openwin/lib/X11/%T/%N%C

To check whether it is correctly inserted, use following command:

$ echo $PATH

3. Now we should able to use pkgutil. Lets update our catalog:

$ pkgutil -U

Note: If you unable to execute the command with error similar to ‘command not found’, you need to relogin your session or type su – command

4. It will download the list from this mirror: . To find available application in the catalog, we can use following command:

$ pkgutil -a mysql
libmysqlclient15   CSWlibmysqlclient15 5.0.92,REV=2011.10.05     1.4 MB
libmysqlclient_r15 CSWlibmysqlclient-r15 5.0.92,REV=2011.10.05   1.4 MB
mysql4             CSWmysql4 4.1.22,REV=2008.01.20              13.0 MB
mysql4bench        CSWmysql4bench 4.1.22,REV=2006.11.28        487.4 KB
mysql4client       CSWmysql4client 4.1.22,REV=2007.12.01         2.4 MB
mysql4devel        CSWmysql4devel 4.1.22,REV=2008.01.20          2.1 MB
mysql4rt           CSWmysql4rt 4.1.22,REV=2008.01.20             3.6 MB
mysql4test         CSWmysql4test 4.1.22,REV=2006.11.28           1.3 MB
mysql5             CSWmysql5 5.0.92,REV=2011.10.05              11.0 MB
mysql5client       CSWmysql5client 5.0.92,REV=2011.10.05         3.0 MB
mysql5devel_stub   CSWmysql5devel 5.0.92,REV=2011.10.05          7.9 KB
mysql5rt_stub      CSWmysql5rt 5.0.92,REV=2011.10.05             7.9 KB
mysql_dev          CSWmysql-dev 5.0.92,REV=2011.10.05          115.8 KB
php4_mysql         CSWphp4mysql 4.4.9,REV=2009.05.28            17.3 KB
php5_mysql         CSWphp5-mysql 5.3.8,REV=2011.09.04           22.6 KB
php5_mysqli        CSWphp5-mysqli 5.3.8,REV=2011.09.04          49.8 KB
php5_pdomysql      CSWphp5-pdomysql 5.3.8,REV=2011.09.04        17.3 KB
php5mysql_stub     CSWphp5mysql 5.3.8,REV=2011.09.04             2.5 KB
php5mysqli_stub    CSWphp5mysqli 5.3.8,REV=2011.09.04            2.5 KB
php5pdomysql_stub  CSWphp5pdomysql 5.3.8,REV=2011.09.04          2.5 KB
pm_dbdmysql        CSWpmdbdmysql 4.013,REV=2010.02.11          169.8 KB
py_mysql           CSWpy-mysql 1.2.3,REV=2011.03.13             41.0 KB
pymysql            CSWpymysql 1.2.3,REV=2011.03.13               1.8 KB
rb18_mysql_2_8_1   CSWrb18-mysql-2-8-1 2.8.1,REV=2011.08.14    217.0 KB

5. Now lets install application using pkgutil:

$ pkgutil -i -y mysql5

To update application:

$ pkgutil -u mysql5

To remove application:

$ pkgutil -r mysql5

To update all applications:

$ pkgutil -u

From here you may start to manage your applications and let your Solaris working!

Ubuntu: Install Windows Application using Winetricks

Ubuntu is surely good for end-user operating system but sometimes we still need to have Windows application more. Using Wine, we can run Windows applications in UNIX/LINUX environment. If you are using Ubuntu, installing Wine is just simple if you are using Ubuntu Software Center. Just search for ‘Wine’ and then install.

In order to run Windows application correctly, we need to install some of the Windows-only requirement like .NET Framework, windows-based fonts and some of Windows libraries (dll) file. We can achieve this by using Winetricks, an extension built for Wine to help installing Windows application in a better way (other than manually download and execute).

Variable that I used is:

OS: Ubuntu 10.04 (Lucid)
User: mikey
Wine location: /home/mikey/.wine or ~/.wine

1. Download and install Winetricks:

$ cd ~/Downloads
$ wget
$ chmod +x winetricks
$ sudo cp winetricks /usr/local/bin

2. We can browse around on categories that Winetricks could do for us:

$ winetricks apps list
$ winetricks benchmarks list
$ winetricks dlls list
$ winetricks fonts list
$ winetricks games list
$ winetricks settings list

3. In this case, I will start by installing .NET Framework 2.0:

$ winetricks dotnet20

4. To install many applications, we can run following command:

$ winetricks corefonts vcrun6 mfc40 vb6run msxml6

5. To remove all applications which has been installed by Winetricks and re-initialize Wine:

$ rm -Rf ~/.wine
$ wineboot


ELS: Great Server Administration Tool

ELS stands for Easy Linux Security. ELS was created by the Server Monkeys Founder, Richard Gannon. ELS takes many of the tasks performed by server administrators and puts it into an easy to use program. It is released under the GNU/GPL so it is free to use.

If you want to know more about this project, please go to this website, . To install this tool, just execute following command as root:

wget; chmod +x; sh

Once installed, you should able to perform following command and output below should appear:
Continue reading “ELS: Great Server Administration Tool” »

Windows: Find And Replace Text Command Line Utility

FART or Find And Replace Text command line utility is a Windows improved version of the well-known ‘grep‘ command, with advanced features such as: case-adaption of the replace string; find (and replace) in filenames or auto CVS edit. You can download from Sourceforge and integrate it into your Windows environment.

Why We Need This?

For me, Windows Server 2008 R2 search tool is not really good, where its hard for me to find/replace some text into all files in certain directory recursively. Since I have experience in managing Linux box (using command line), it is really convenience to have something command-line based to do this for you.

Lets say something wrong happen to your website, which being injected, similar to this post, but happen in Windows server, then this is what you need.

Here is my environment:

OS: Windows 2008 R2 64bit
Directory: C:\user1\public_html
Infected files: All .html files under this directory
Infected code: “<script src= ></script>”

1. Download the application at Sourceforge,

2. Extract the zip files somewhere and you will see there is one file inside called fart.exe. Move or copy it to C:\Windows\system32.

Continue reading “Windows: Find And Replace Text Command Line Utility” »

FreeBSD: Update Ports

FreeBSD has a lot of variety applications available via port. For me, this is the best thing so far in FreeBSD, plus simplicity, configurable and stability. Portsnap is a tool to let us get the distributed FreeBSD ports tree, in a simple way, we update the ports and get the latest stable version from the main tree.

To update ports, run following command:

portsnap fetch update

Once done, we need to extract them so it will update what available under /usr/ports:

portsnap extract

Attention: It might takes long time depending on your connection speed and hardware specs. You can grab a cup of coffee and watch Youtube to wait for this process to complete.

Linux: Remove Specific String in Text Files

Webmaster usually can get headache when their website’s static page like HTML, JS and CSS being injected with some kind of malicious code. You will see some iframe tag or source tag inside your HTML coding and some of it has caused your website being classified by Google and Firefox as ‘harmful’.

We called this as XSS attack (cross site-scripting; X means cross) which enable attackers to inject client-side script into the web pages viewed by other users. Usually it caused by permission of your web files is globally writable. You can find out more about this attack at Wikipedia, since here I just showing you some way to find and remove the injected scripts.

I am using following variables:

Infected user: user1
User’s web directory: /home/user1/public_html

1. Usually, you will received a report regards to your website has been listed as harmful or ‘Reported Attack Site’ as below:

2. Click the ‘Why was this site blocked?’ and then you will be redirected to Google Safe Browsing page. This website will tell you what malicious software has been hosted, or being injected into your code. Lets say in this case, the values is

Continue reading “Linux: Remove Specific String in Text Files” »