How To Run MariaDB High Availability Cluster using Galera

When a single MariaDB/MySQL server is no longer able to serve the traffic, it’s time to upgrade to a more reliable and easy to upgrade database server. For scaling MariaDB we can use Galera which is already included in mariadb-server package in Debian 11.
Galera doesn’t provide a way to balance the connection from the client to the database server itself, so we’ll use HAProxy for this purpose. HAProxy is built with sophisticated and customizable health checks methods, allowing a number of services to be load-balanced.

Server list

192.168.88.31 mariadb-cluster-01
192.168.88.32 mariadb-cluster-02
192.168.88.33 mariadb-cluster-03
192.168.88.35 mariadb-haproxy

Set the server on the same spec, because it’ll use the weaker one as the reference for all the clusters.

1. Initial Setup

The first thing to do after installing the os, is to update all the packages on the system

sudo apt update && sudo apt upgrade -y

do this on regular basis, but take a careful look when you’re updating the MariaDB because it can cause some downtime.

Setting Server Hostname

Set a proper name for each server, it’ll really helpful when doing some work on the server, because it can recognize by its hostname.

# server haproxy
sudo hostnamectl set-hostname --static mariadb-haproxy
# server mariadb 1
sudo hostnamectl set-hostname --static mariadb-cluster-01
# server mariadb 2
sudo hostnamectl set-hostname --static mariadb-cluster-02
# server mariadb 3
sudo hostnamectl set-hostname --static mariadb-cluster-03

then run this command on each server (MySQL and HAProxy)

echo '192.168.88.31 mariadb-haproxy
192.168.88.32 mariadb-cluster-01
192.168.88.33 mariadb-cluster-02
192.168.88.35 mariadb-cluster-03' | sudo tee -a /etc/hosts

so we can access each server using the hostname instead of the IP address. We won’t use the hostname to access the server itself, but to keep it in sync on all server, just in case we need to add more servers in the future.

2. Install MariaDB Server

On all of mariadb server (3 Server), install mariadb-server packages, which already included galera cluster.

sudo apt install mariadb-server -y

by default Debian will run the services after installation, so we’ll need stop the service and run it again after the configuration completed.

sudo systemctl stop mariadb

Server mariadb-cluster-01

For the Galera configuration, we need to create new file /etc/mysql/mariadb.conf.d/50-galera.cnf, copy following code

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=192.168.88.31
 
# Galera Provider
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
 
# Cluster IP
wsrep_cluster_name="mariadb-cluster"
wsrep_cluster_address="gcomm://192.168.88.31,192.168.88.32,192.168.88.33"
wsrep_sst_method=rsync
wsrep_node_address="192.168.88.31"
wsrep_node_name="mariadb-cluster-01"

because we set the binding in Galera, then we need to disable the bind-address on default location

sudo sed -i 's/bind-address/#bind-address/g' /etc/mysql/mariadb.conf.d/50-server.cnf

start the cluster from this server

sudo galera_new_cluster

Check the mariadb status

sudo systemctl status mariadb

get the cluster size

sudo mysql  -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

galera cluster size
it only show single server, because we only run 1 server so far. It’ll increase every time we run more server.

Server mariadb-cluster-02

For the Galera configuration, we need to create new file /etc/mysql/mariadb.conf.d/50-galera.cnf, copy following code

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=192.168.88.32
 
# Galera Provider
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
 
# Cluster IP
wsrep_cluster_name="mariadb-cluster"
wsrep_cluster_address="gcomm://192.168.88.31,192.168.88.32,192.168.88.33"
wsrep_sst_method=rsync
wsrep_node_address="192.168.88.32"
wsrep_node_name="mariadb-cluster-02"

because we set the binding in Galera, then we need to disable the bind-address on default location

sudo sed -i 's/bind-address/#bind-address/g' /etc/mysql/mariadb.conf.d/50-server.cnf

start MariaDB server

sudo systemctl start mariadb

get the cluster size

sudo mysql  -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
# output
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+

Server mariadb-cluster-03

For the Galera configuration, we need to create new file /etc/mysql/mariadb.conf.d/50-galera.cnf, copy following code

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=192.168.88.33
 
# Galera Provider
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
 
# Cluster IP
wsrep_cluster_name="mariadb-cluster"
wsrep_cluster_address="gcomm://192.168.88.31,192.168.88.32,192.168.88.33"
wsrep_sst_method=rsync
wsrep_node_address="192.168.88.33"
wsrep_node_name="mariadb-cluster-03"

because we set the binding in Galera, then we need to disable the bind-address on default location

sudo sed -i 's/bind-address/#bind-address/g' /etc/mysql/mariadb.conf.d/50-server.cnf

start MariaDB server

sudo systemctl start mariadb

get the cluster size

sudo mysql  -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
# output
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

Perfect, all servers running and syncing.

Query Testing

After the initial install is done, now time to test the cluster by creating a dummy database and some data samples. Run this query from any MariaDB server, let’s say we’ll run from mariadb-cluster-03
Login as MariaDB root

sudo mysql

then create a database and table

CREATE DATABASE sampledb;
CREATE TABLE sampledb.msg ( `id` INT NOT NULL AUTO_INCREMENT , `message` TEXT NOT NULL , PRIMARY KEY (`id`));
INSERT INTO sampledb.msg (`id`, `message`) VALUES (1, 'Lorem ipsum');
INSERT INTO sampledb.msg (`id`, `message`) VALUES (2, 'I run on db cluster');
INSERT INTO sampledb.msg (`id`, `message`) VALUES (3, 'Who am I');

create dummy database galera cluster

to make sure all the row already sync on cluster from any cluster run

sudo mysql -e "SELECT * from sampledb.msg"

select data from any cluster

Setup HAProxy Load Balancer

If you’re aware we don’t set any access to our Galera cluster from outside, to make it more secure we’ll only allow access from the HAProxy IP Address. Because we don’t have any load balancer yet, we’ll use these HAProxy as the load balancer.

Install HAProxy from Debian 11 repository

sudo apt install haproxy -y

From any MariaDB server, create user for HAProxy, this user will query by HAProxy periodically to make sure it’s up

CREATE USER 'user_haproxy'@HAPROXY-IP
# example
CREATE USER 'user_haproxy'@192.168.88.35

Create config file /etc/haproxy/haproxy.cfg. In this config we’ll load balance all the three servers equal, that’s why I mention on the beginning we need to make sure all the server specs are equal.

# stats dashboard
listen stats
    bind :8080
    mode http
    stats enable
    stats hide-version
    stats realm Haproxy\ Statistics
    stats uri /
    stats auth admin:password
 
listen galera_cluster
    bind 192.168.88.35:3306
    balance source
    mode tcp
    option mysql-check user user_haproxy
    option tcplog
    server mariadb-cluster-01 mariadb-cluster-01:3306  check weight 1
    server mariadb-cluster-02 mariadb-cluster-02:3306  check weight 1
    server mariadb-cluster-03 mariadb-cluster-03:3306  check weight 1

Restart HAProxy service

sudo systemctl restart haproxy

Then login to HAProxy statistic, port 8080. Using username admin and password password, changes it to more secure password and username.

mysql haproxy running okay

After everything running, we need to create the [email protected] instead of [email protected]. This setup been deployed to handle million traffic for WordPress

Leave a Reply

Your email address will not be published.