Highly Available MySQL with Galera and HAProxy

Recently, I had a requirement where I wanted a MySQL Cluster which would support automatic fail overs. So one of my colleagues in the project did a good analysis on available solutions and out of those we chose Galera for it’s simplicity.

Here is the complete guide on setting up an HA MySQL cluster with Galera. The environment we used was ubuntu based.

Install MariaDB on all nodes

We wanted a 3 node cluster and each of the 3 nodes need to be configured with mariadb before proceeding. Enter the following commands line by line and this would install mariadb on all of your 3 nodes.

sudo apt update
sudo apt-get install software-properties-common -y
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
sudo add-apt-repository "deb [arch=amd64,arm64,ppc64el] http://mariadb.mirror.liquidtelecom.com/repo/10.4/ubuntu $(lsb_release -cs) main"
sudo apt update
sudo apt -y install mariadb-server mariadb-client

After the installation, on each node please run the following.

sudo mysql_secure_installation

Here they would ask a few questions and these should be the answers.

Switch to unix_socket authentication [Y/n] n
Change the root password? [Y/n] y
# create a unique password and enter the SAME password on all 3 nodes
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] n
Remove test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] y

Make sure you add the same password on all 3 nodes when setting up mariadb in the above.

Setup GaleraDB on the first node

Once Mariadb is up on all the nodes, pick one of the nodes to setup GaleraDB first. I picked the node galera-01 in my setup.

You have to first edit the mariadb conf like so.

sudo vim /etc/mysql/mariadb.cnf

And uncomment the following lines.

character-set-server = utf8
character_set_server = utf8

Save the file and create a new file to store GaleraDB configs like so.

vim /etc/mysql/mariadb.conf.d/galera.cnf

And insert the following.

[mysqld] 
bind-address=0.0.0.0 
default_storage_engine=InnoDB 
binlog_format=row 
innodb_autoinc_lock_mode=2  
# Galera cluster configuration 
wsrep_on=ON 
wsrep_provider=/usr/lib/galera/libgalera_smm.so wsrep_cluster_address="gcomm://<node-1-ip>,<node-2-ip>,<node-3-ip>" 
wsrep_cluster_name="mariadb-galera-cluster" 
wsrep_sst_method=rsync  
# Cluster node configuration 
wsrep_node_address="<node-1-ip>" 
wsrep_node_name="galera-db-01"

On the above file, please put the valid ip addresses of each node.

Setting up the other 2 nodes

On the other 2 nodes, the setup is fairly simple. All you have to do is create the GaleraDB configuration like so.

On Node 2,

vim /etc/mysql/mariadb.conf.d/galera.cnf
[mysqld] 
bind-address=0.0.0.0 
default_storage_engine=InnoDB 
binlog_format=row 
innodb_autoinc_lock_mode=2
# Galera cluster configuration 
wsrep_on=ON 
wsrep_provider=/usr/lib/galera/libgalera_smm.so wsrep_cluster_address="gcomm://<node-1-ip>,<node-2-ip>,<node-3-ip>"
wsrep_cluster_name="mariadb-galera-cluster" 
wsrep_sst_method=rsync
# Cluster node configuration 
wsrep_node_address="<node-2-ip>" 
wsrep_node_name="galera-db-02"

On Node 3,

vim /etc/mysql/mariadb.conf.d/galera.cnf
[mysqld] 
bind-address=0.0.0.0 
default_storage_engine=InnoDB 
binlog_format=row 
innodb_autoinc_lock_mode=2
# Galera cluster configuration 
wsrep_on=ON 
wsrep_provider=/usr/lib/galera/libgalera_smm.so wsrep_cluster_address="gcomm://<node-1-ip>,<node-2-ip>,<node-3-ip>"
wsrep_cluster_name="mariadb-galera-cluster" 
wsrep_sst_method=rsync
# Cluster node configuration 
wsrep_node_address="<node-3-ip>" 
wsrep_node_name="galera-db-03"

Notice that only thing you have to change is wsrep_node_address and wsrep_node_name to match the name and IP of the node you are in.

Save the config files.

Initialize GaleraDB

On all 3 nodes, stop mariadb service using the following command.

systemctl stop mariadb

Then on Node 1, run the following command.

galera_new_cluster

Check if Galera has started by running the following command.

mysql -u root -p -e "show status like 'wsrep_%'"

You may get an output like the following.

Check the cluster size using this command.

mysql -u root -p -e "show status like 'wsrep_cluster_size'"

and you may get an output like this.

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+

On the other 2 nodes, you can start mariadb service like so.

systemctl start mariadb

Now if you re run the cluster size command, you will see the cluster size has increased, according to the number of nodes you have. I got 3 in my case.

mysql -u root -p -e "show status like 'wsrep_cluster_size'"
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

Testing the Setup

In one of the nodes, login to mysql using the following command.

mysql -u root -p

and create a test database called test_galera using the following command.

create database test_galera;

And login to mysql of another node using the same command I gave above and see if you can see the newly created database by running the command below.

show databases;

You should be able to see the test_galera db created and synced on all of your nodes.

Setup High Availability with HAProxy

If you check the above test scenario and try to create DBs on any of the nodes, you would see that the data would automatically sync between the nodes. Which means Availability is achieved. Now to achieve high availability we will use HaProxy as the loadbalancer.
Set up another VM for this and install HAProxy as follows.

sudo apt-get update
sudo apt-get install haproxy

Edit HA Proxy Config as follows.

sudo vim /etc/haproxy/haproxy.cfg

Add the following lines.

# MySQL Cluster FE configuration
frontend mysql_cluster_frontend
    bind *:3306
    mode tcp
    option tcplog
    default_backend galera_cluster_backend

# MySQL Cluster BE configuration
backend galera_cluster_backend
    mode tcp
    option tcpka
    balance leastconn
    server mysql-01 <node-1-ip>:3306  check weight 1
    server mysql-02 <node-2-ip>:3306  check weight 1
    server mysql-03 <node-3-ip>:3306  check weight 1

Make sure to update the ip addresses of each node in the config above.

systemctl restart haproxy

That’s it. Connect to Loadbalancer VM IP address : 3306 and you should now have a Highly Available MySQL Cluster with Galera.

I hope this post helped you. Will be back in another similar blog post in the future.

Cheers!