High Availability: MySQL Cluster with Galera + HAProxy

In this post, I am going to show you my implementation on how to achieve high availability MySQL setup with load balancing using HAProxy, Galera cluster, garbd and virtual IP using keepalived.

Actually, the process is similar with my previous post as here, with some added steps to configure HAProxy and garbd. The architecture can be describe as image below:

 

Variable that I used:

OS: CentOS 6.0 64bit
MySQL server1: 192.168.0.171
MySQL server2: 192.168.0.172
HAProxy server1: 192.168.0.151
HAProxy server2: 192.168.0.152
Virtual IP to be shared among HAProxy: 192.168.0.170
MySQL root password: [email protected]#
Cluster root username: clusteroot
Cluster root password: [email protected]#
Galera SST user: sst
Galera SST password: sstpass123

Server hostname is important in cluster. Following information has been setup in all servers /etc/hosts file. All configurations shown below are assuming that firewall is turn OFF and SELINUX is DISABLED:

192.168.0.151 haproxy1.cluster.local haproxy1
192.168.0.152 haproxy2.cluster.local haproxy2
192.168.0.171 galera1.cluster.local galera1
192.168.0.172 galera2.cluster.local galera2
127.0.0.1     localhost.localdomain localhost
::1           localhost6 localhost6.localdomain

MySQL Cluster with Galera

1. Following steps are similar with my previous post. But in this tutorial, I am going to rewrite it as refer to this case with latest version of Galera and MySQL. I have no MySQL server installed in this server at the moment. Download the latest Galera library, MySQL with wsrep, MySQL client and MySQL shared from MySQL download page:

$ mkdir -p /usr/local/src/galera
$ cd /usr/local/src/galera
$ wget https://launchpad.net/galera/2.x/23.2.0/+download/galera-23.2.0-1.rhel5.x86_64.rpm
$ wget https://launchpad.net/codership-mysql/5.5/5.5.20-23.4/+download/MySQL-server-5.5.20_wsrep_23.4-1.rhel5.x86_64.rpm
$ wget http://dev.mysql.com/get/Downloads/MySQL-5.5/MySQL-client-5.5.20-1.el6.x86_64.rpm/from/http://ftp.jaist.ac.jp/pub/mysql/
$ wget http://dev.mysql.com/get/Downloads/MySQL-5.5/MySQL-shared-5.5.20-1.el6.x86_64.rpm/from/http://ftp.jaist.ac.jp/pub/mysql/

2. Remove unwanted library and install all the packages in following sequence:

$ rpm -e --nodeps mysql-libs
$ rpm -Uhv galera-23.2.0-1.rhel5.x86_64.rpm
$ rpm -Uhv MySQL-client-5.5.20-1.el6.x86_64.rpm
$ rpm -Uhv MySQL-shared-5.5.20-1.el6.x86_64.rpm
$ rpm -Uhv MySQL-server-5.5.20_wsrep_23.4-1.rhel5.x86_64.rpm

3. Start the MySQL service and make sure it start on boot:

$ chkconfig mysql on
$ service mysql start

4. Setup the MySQL root password:

$ /usr/bin/mysqladmin -u root password '[email protected]#'

5. Setup the MySQL client for root. Create new text file /root/.my.cnf using text editor and add following line:

[client]
user=root
passowrd='[email protected]#'

6. Change the permission to make sure it is not viewable by others:

$ chmod 600 /root/.my.cnf

7. Login to the MySQL server by executing command “mysql” and execute following line. We will also need to create another root user called clusteroot with password and haproxy without password (for HAProxy monitoring) as stated on variables above:

mysql> DELETE FROM mysql.user WHERE user='';
mysql> GRANT USAGE ON *.* TO [email protected]'%' IDENTIFIED BY '[email protected]#';
mysql> UPDATE mysql.user SET Password=PASSWORD('[email protected]#') WHERE User='root';
mysql> GRANT USAGE ON *.* to [email protected]'%' IDENTIFIED BY 'sstpass123';
mysql> GRANT ALL PRIVILEGES on *.* to [email protected]'%';
mysql> GRANT USAGE on *.* to [email protected]'%' IDENTIFIED BY '[email protected]#';
mysql> GRANT ALL PRIVILEGES on *.* to [email protected]'%';
mysql> INSERT INTO mysql.user (host,user) values ('%','haproxy');
mysql> FLUSH PRIVILEGES;
mysql> quit

8. Create the configuration files and directory, copy the example configuration and create mysql exclusion configuration file:

$ mkdir -p /etc/mysql/conf.d/
$ cp /usr/share/mysql/wsrep.cnf /etc/mysql/conf.d/
$ touch /etc/my.cnf
$ echo '!includedir /etc/mysql/conf.d/' >> /etc/my.cnf

9. Configure MySQL wsrep with Galera library. Open /etc/mysql/conf.d/wsrep.cnf using text editor and find and edit following line:

For galera1.cluster.local:

wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://"
wsrep_sst_auth=sst:sstpass123

For galera2.cluster.local:

wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.0.171:4567"
wsrep_sst_auth=sst:sstpass123

10. Restart services in both servers:

$ service mysql restart

11. Check whether Galera replication is running fine:

$ mysql -e "show status like 'wsrep%'"

If the cluster is working, you should see following value in both servers:

wsrep_ready = ON

HAProxy servers

1. In both haproxy1 and haproxy2, we will start with installing virtual IP to make sure the HAProxy IP is always available. Lets download and install keepalived from here. OpenSSL header and popt library are required, so we will install it first using yum:

$ yum install -y openssl openssl-devel popt*
$ cd /usr/local/src
$ wget http://www.keepalived.org/software/keepalived-1.2.2.tar.gz
$ tar -xzf keepalived-1.2.2.tar.gz
$ cd keepalived-*
$ ./configure
$ make
$ make install

2. Since we have virtual IP which shared between these 2 servers, we need to tell kernel that we have a non-local IP to be bind to HAProxy service later. Add following line into /etc/sysctl.conf:

net.ipv4.ip_nonlocal_bind = 1

Run following command to apply the changes:

$ sysctl -p

3. By default, keepalived configuration file will be setup under /usr/local/etc/keepalived/keepalived.conf. We will make things easier by symlink it into /etc directory. We will also need to clear the configuration example inside it:

$ ln -s /usr/local/etc/keepalived/keepalived.conf /etc/keepalived.conf
$ cat /dev/null > /etc/keepalived.conf

4. This step is different in both servers for keepalived configuration.

For haproxy1, add following line into /etc/keepalived.conf:

vrrp_script chk_haproxy {
        script "killall -0 haproxy" # verify the pid is exist or not
        interval 2                      # check every 2 seconds
        weight 2                        # add 2 points of prio if OK
}
 
vrrp_instance VI_1 {
        interface eth0			# interface to monitor
        state MASTER
        virtual_router_id 51		# Assign one ID for this route
        priority 101                    # 101 on master, 100 on backup
        virtual_ipaddress {
            192.168.0.170		# the virtual IP
        }
        track_script {
            chk_haproxy
        }
}

For haproxy2, add following line into /etc/keepalived.conf:

vrrp_script chk_haproxy {
        script "killall -0 haproxy"     # verify the pid is exist or not
        interval 2                      # check every 2 seconds
        weight 2                        # add 2 points of prio if OK
}
 
vrrp_instance VI_1 {
        interface eth0			# interface to monitor
        state MASTER
        virtual_router_id 51		# Assign one ID for this route
        priority 100                    # 101 on master, 100 on backup
        virtual_ipaddress {
            192.168.0.170		# the virtual IP
        }
        track_script {
            chk_haproxy
        }
}

5. Download and install HAProxy. Get the source from http://haproxy.1wt.eu/#down .We also need to install some required library using yum:

$ yum install pcre* -y
$ cd /usr/local/src
$ wget http://haproxy.1wt.eu/download/1.4/src/haproxy-1.4.19.tar.gz
$ tar -xzf haproxy-1.4.19.tar.gz
$ cd haproxy-*
$ make TARGET=linux26 ARCH=x86_64 USE_PCRE=1
$ make install

6. Create HAProxy configuration file and paste following configuration. This configuration will tell HAProxy to be a reverse-proxy for the virtual IP on port 3306. It then forward the request to backend servers. MySQL checking need to be done via haproxy user:

$ mkdir -p /etc/haproxy
$ touch /etc/haproxy/haproxy.cfg

Add following line into /etc/haproxy/haproxy.cfg:

defaults
        log global
        mode http
        retries 3
        option redispatch
        maxconn 4096
        contimeout 50000
        clitimeout 50000
        srvtimeout 50000
 
listen mysql_proxy 0.0.0.0:3306
        mode tcp
        balance roundrobin
        option tcpka
        option httpchk
        option mysql-check user haproxy
        server galera1 192.168.0.171:3306 weight 1
        server galera2 192.168.0.172:3306 weight 1

7. Since we only have 2 database servers, it means we have 2 members in the cluster. Even though it is working but still not a good idea to have database failover because it can cause “split brain”.  Split brain mode refers to a state in which each database server does not know the high availability (HA) role of its redundant peer, and cannot determine which server currently has the primary HA role. So we will use both HAProxy servers to be the 3rd and 4th member. We called them arbitrator. Galera has provided the binary called garbd to overcome this problem. Download and install Galera library:

$ cd /usr/local/src
$ wget https://launchpad.net/galera/2.x/23.2.0/+download/galera-23.2.0-1.rhel5.x86_64.rpm
$ rpm -Uhv galera-23.2.0-1.rhel5.x86_64.rpm

8.  Run following command to start garbd as daemon to join my_wsrep_cluster group:

$ garbd -a gcomm://192.168.0.171:4567 -g my_wsrep_cluster -d

9. Now lets start keepalived and HAProxy and do some testing whether the IP failover, database failover and load balancing are working. Run following command in both servers:

$ keepalived -f /etc/keepalived.conf
$ haproxy -D -f /etc/haproxy/haproxy.cfg

Ping IP 192.168.0.170 from another host. Now in haproxy1, stop the network:

$ service network stop

You will notice that the IP will be down for 2 seconds and then it will up again. It means that haproxy2 has taking over IP 192.168.0.170 from haproxy1. If you start back the network in haproxy1, you will noticed the same thing happen because haproxy1 will taking over back the IP from haproxy2, as what we configure in /etc/keepalived.conf. You can also try to kill haproxy process and you will see the virtual IP will be take over again by haproxy2.

In other hand, you can try to stop mysql process in galera2 and create a new database inside galera1. After a while, start back the mysql process in galera2 and you should see that galera2 will synchronize to galera1 as reference node to update data.

If everything working as expected, add following line into /etc/rc.local so the service started automatically after boot:

/usr/local/sbin/haproxy -D -f /etc/haproxy/haproxy.cfg
/usr/local/sbin/keepalived -f /etc/keepalived.conf
/usr/bin/garbd -a gcomm://192.168.0.171:4567 -g my_wsrep_cluster -d

Now your MySQL is running in high availability mode. The MySQL client just need to access 192.168.0.170 as the MySQL database server host.

*Notes: All steps above need to be done on all respective servers except if specified

High Availability: MySQL Cluster with Galera + MySQL Proxy

In this tutorial, I am going to show you on how to achieve higher MySQL uptime with some help from MySQL Proxy, Galera cluster and virtual IP using keepalived.

Actually, the process is similar with my previous post as here, with some added steps to configure MySQL proxy and virtual IP. The architecture can be describe as image below:

Variable that I used:

OS: CentOS 6.0 64bit
MySQL server1: 192.168.0.171
MySQL server2: 192.168.0.172
MySQL proxy server1: 192.168.0.151
MySQL proxy server2: 192.168.0.152
Virtual IP to be shared among MySQL proxies: 192.168.0.170
MySQL root password: [email protected]#
Cluster root username: clusteroot
Cluster root password: [email protected]#
Galera SST user: sst
Galera SST password: sstpass123

Server hostname is important in cluster. Following information has been setup in all servers /etc/hosts file. All configurations shown below are assuming that firewall is turn OFF:

192.168.0.151 myproxy1.cluster.local myproxy1
192.168.0.152 myproxy2.cluster.local myproxy2
192.168.0.171 galera1.cluster.local galera1
192.168.0.172 galera2.cluster.local galera2
127.0.0.1     localhost.localdomain localhost
::1           localhost6 localhost6.localdomain

MySQL Cluster with Galera

1. Following steps are similar with my previous post. But in this tutorial, I am going to rewrite it as refer to this case with latest version of Galera and MySQL. I have no MySQL server installed in this server at the moment. Download the latest Galera library, MySQL with wsrep, MySQL client and MySQL shared from MySQL download page:

$ mkdir -p /usr/local/src/galera
$ cd /usr/local/src/galera
$ wget https://launchpad.net/galera/2.x/23.2.0/+download/galera-23.2.0-1.rhel5.x86_64.rpm
$ wget https://launchpad.net/codership-mysql/5.5/5.5.20-23.4/+download/MySQL-server-5.5.20_wsrep_23.4-1.rhel5.x86_64.rpm
$ wget http://dev.mysql.com/get/Downloads/MySQL-5.5/MySQL-client-5.5.20-1.el6.x86_64.rpm/from/http://ftp.jaist.ac.jp/pub/mysql/
$ wget http://dev.mysql.com/get/Downloads/MySQL-5.5/MySQL-shared-5.5.20-1.el6.x86_64.rpm/from/http://ftp.jaist.ac.jp/pub/mysql/

2. Remove unwanted library and install all the packages in following sequence:

$ yum remove mysql-libs -y
$ rpm -Uhv galera-23.2.0-1.rhel5.x86_64.rpm
$ rpm -Uhv MySQL-client-5.5.20-1.el6.x86_64.rpm
$ rpm -Uhv MySQL-shared-5.5.20-1.el6.x86_64.rpm
$ rpm -Uhv MySQL-server-5.5.20_wsrep_23.4-1.rhel5.x86_64.rpm

3. Start the MySQL service and make sure it start on boot:

$ chkconfig mysql on
$ service mysql start

4. Setup the MySQL root password:

$ /usr/bin/mysqladmin -u root password '[email protected]#'

5. Setup the MySQL client for root. Create new text file /root/.my.cnf using text editor and add following line:

[client]
user=root
passowrd='[email protected]#'

6. Change the permission to make sure it is not viewable by others:

$ chmod 600 /root/.my.cnf

7. Login to the MySQL server by executing command “mysql” and execute following line. We will also need to create another root user called clusteroot with password as stated on variables above:

mysql> DELETE FROM mysql.user WHERE user='';
mysql> GRANT USAGE ON *.* TO [email protected]'%' IDENTIFIED BY '[email protected]#';
mysql> UPDATE mysql.user SET Password=PASSWORD('[email protected]#') WHERE User='root';
mysql> GRANT USAGE ON *.* to [email protected]'%' IDENTIFIED BY 'sstpass123';
mysql> GRANT ALL PRIVILEGES on *.* to [email protected]'%';
mysql> GRANT USAGE on *.* to [email protected]'%' IDENTIFIED BY '[email protected]#';
mysql> GRANT ALL PRIVILEGES on *.* to [email protected]'%' ;
mysql> quit

8. Create the configuration files and directory, copy the example configuration and create mysql exclusion configuration file:

$ mkdir -p /etc/mysql/conf.d/
$ cp /usr/share/mysql/wsrep.cnf /etc/mysql/conf.d/
$ touch /etc/my.cnf
$ echo '!includedir /etc/mysql/conf.d/' >> /etc/my.cnf

9. Configure MySQL wsrep with Galera library. Open /etc/mysql/conf.d/wsrep.cnf using text editor and find and edit following line:

For galera1.cluster.local:

wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://"
wsrep_sst_auth=sst:sstpass123

For galera2.cluster.local:

wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.0.171:4567"
wsrep_sst_auth=sst:sstpass123

10. Restart services in both servers:

$ service mysql restart

11. Check whether Galera replication is running fine:

$ mysql -e "show status like 'wsrep%'"

If the cluster is working, you should see following value in both servers:

wsrep_ready = ON

MySQL proxy servers

1. In both myproxy1 and myproxy2, we will start with installing virtual IP to make sure the MySQL proxy IP is always available. Lets download and install keepalived from here. OpenSSL header and popt library are required, so we will install it first using yum:

$ yum install -y openssl openssl-devel popt*
$ cd /usr/local/src
$ wget http://www.keepalived.org/software/keepalived-1.2.2.tar.gz
$ tar -xzf keepalived-1.2.2.tar.gz
$ cd keepalived-*
$ ./configure
$ make
$ make install

2. Since we have virtual IP which shared between these 2 servers, we need to tell kernel that we have a non-local IP to be bind to mysql proxy service later. Add following line into /etc/sysctl.conf:

net.ipv4.ip_nonlocal_bind = 1

Run following command to apply the changes:

$ sysctl -p

3. By default, keepalived configuration file will be setup under /usr/local/etc/keepalived/keepalived.conf. We will make things easier by symlink it into /etc directory. We will also clear the configuration example inside it:

$ ln -s /usr/local/etc/keepalived/keepalived.conf /etc/keepalived.conf
$ cat /dev/null > /etc/keepalived.conf

4. Download MySQL proxy at http://dev.mysql.com/downloads/mysql-proxy/ .We will setup MySQL proxy under /usr/local directory:

$ cd /usr/local
$ wget http://mysql.oss.eznetsols.org/Downloads/MySQL-Proxy/mysql-proxy-0.8.2-linux-rhel5-x86-64bit.tar.gz
$ tar -xzf mysql-proxy-0.8.2-linux-rhel5-x86-64bit.tar.gz
$ mv mysql-proxy-0.8.2-linux-rhel5-x86-64bit mysql-proxy
$ rm -Rf mysql-proxy-0.8.2-linux-rhel5-x86-64bit.tar.gz

5. This step is different in both servers for keepalived configuration.

For myproxy1, add following line into /etc/keepalived.conf:

vrrp_script chk_mysqlproxy {
        script "killall -0 mysql-proxy" # verify the pid is exist or not
        interval 2                      # check every 2 seconds
        weight 2                        # add 2 points of prio if OK
}
 
vrrp_instance VI_1 {
        interface eth0			# interface to monitor
        state MASTER
        virtual_router_id 51		# Assign one ID for this route
        priority 101                    # 101 on master, 100 on backup
        virtual_ipaddress {
            192.168.0.170		# the virtual IP
        }
        track_script {
            chk_mysqlproxy
        }
}

 

For myproxy2, add following line into /etc/keepalived.conf:

vrrp_script chk_mysqlproxy {
        script "killall -0 mysql-proxy" # verify the pid is exist or not
        interval 2                      # check every 2 seconds
        weight 2                        # add 2 points of prio if OK
}
 
vrrp_instance VI_1 {
        interface eth0			# interface to monitor
        state MASTER
        virtual_router_id 51		# Assign one ID for this route
        priority 100                    # 101 on master, 100 on backup
        virtual_ipaddress {
            192.168.0.170		# the virtual IP
        }
        track_script {
            chk_mysqlproxy
        }
}

6. Run following command to start MySQL proxy in both servers:

$ /usr/local/mysql-proxy/bin/mysql-proxy -D -P 0.0.0.0:3306 -b 192.168.0.171:3306 -b 192.168.0.172:3306

7. Now lets start keepalived and do some testing whether the IP failover is working. Run following command in both servers:

$ keepalived -f /etc/keepalived.conf

Ping IP 192.168.0.170 from another host. Now in myproxy1, stop the network:

$ service network stop

You will notice that the IP will be down for 2 seconds and then it will up again. It means that myproxy2 has taking over IP 192.168.0.170 from myproxy1. If you start back the network in myproxy1, you will noticed the same thing happen because myproxy1 will taking over back the IP from myproxy2, as what we configure in /etc/keepalived.conf. You can also try to kill mysql-proxy process and you will see the virtual IP will be take over again by myproxy2.

Now your MySQL is running in high availability mode. The MySQL client just need to access 192.168.0.170 as the MySQL database server host.