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 updatesudo apt-get install software-properties-common -ysudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8sudo add-apt-repository "deb [arch=amd64,arm64,ppc64el] http://mariadb.mirror.liquidtelecom.com/repo/10.4/ubuntu $(lsb_release -cs) main"sudo apt updatesudo 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 nodesRemove 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!