MariaDB Galera Cluster with Highly available Galera Load Balancer (GLB)

What is galera cluster?

MariaDB Galera Cluster is a synchronous active-active multi-master cluster for MariaDB that is available on Linux-based operating systems. Galera Cluster supports only the XtraDB and InnoDB storage engines. It uses the Galera library for replication with automatic node joining, failed nodes drop from the cluster and for reading & writing to the cluster nodes with smaller client latencies.

Galera cluster needs at least 3 database nodes for best performance. One node is used as donar and other two nodes used as backup/fail-over nodes. Highly available GLB offer health check and load balancing between these backend nodes. Here I’ll deploy this GLB.

How to configure Galera Cluster:

Architecture of a Galera Load Balancer

Take 3 EC2 instance (t2.micro) on AWS. Tune your systems to get best performance.

Install MariaDB on each node:

sudo apt update && apt upgrade -y
sudo apt-get install software-properties-common -y
sudo apt-key adv — recv-keys — keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db

sudo vim /etc/apt/sources.list

deb [arch=amd64,i386] http://ftp.ubuntu-tw.org/mirror/mariadb/repo/10.1/debian jessie main
deb-src http://ftp.ubuntu-tw.org/mirror/mariadb/repo/10.1/debian jessie main

Now update the system and install MariaDB

sudo apt update
sudo apt-get install mariadb-server -y

Create a user ‘test’ with your desired password and give privilege to access.

Disable bind address for localhost;

sudo nano /etc/mysql/my.cnf

#bind-address = 127.0.0.1

Stop mysql service and check “on each node”

sudo service mysql stop
sudo service mysql status

For 1st node (Donar Node):

Configure Galeria Culster on ‘/etc/mysql/conf.d/galera.cnf’

[mysqld]
# MySQL settings
binlog_format=row
default-storage-engine=InnoDB
innodb_autoinc_lock_mode=2
# Disable Query Cache
query_cache_size=0
query_cache_type=0
# Allow server to accept connections on all interfaces.
bind-address=0.0.0.0
[galera]
# Galera Mandatory settings
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_sst_method=rsync
# SST Donor
wsrep_sst_donor=node-1_ip
# Disable slave foreign key check
wsrep_slave_FK_checks = OFF
# Cluster Name
wsrep_cluster_name=”GG-MariaDB-Galeria-Cluster”
# Galera Node Configuration
wsrep_node_address=”this_node_ip”
wsrep_node_name=”this_node_name”
# Galeria Optional setting
wsrep_slave_threads=1
innodb_flush_log_at_trx_commit=0

Start First Cluster Node “Donar” by —

sudo galera_new_cluster

Check that the cluster initialized by

mysql -u test -p
‘SELECT VARIABLE_VALUE as “cluster size” FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME=”wsrep_cluster_size”’

In the same process configure the other 2 nodes without the ‘Donar section’.

Then only start your database by

sudo service mysql start

Check that the cluster has 3 nodes now by:

mysql -u test -p
‘SELECT VARIABLE_VALUE as “cluster size” FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME=”wsrep_cluster_size”’

Configuraation of Galera Load Balancer (GLB):

# Go to root user

apt-get update; apt -y upgrade
apt-get install -y git build-essential dh-autoreconf gcc libtool
git clone https://github.com/codership/glb
cd glb/
./bootstrap.sh
./configure
make
make install

# Galera Load Balancer is now installed on your system. You can test it by

src/glbd

Service Installation:

cp files/glbd.sh /etc/init.d/glb
cp files/glbd.cfg /etc/default/glbd
Configure glbd on ‘/etc/default/glbd’
LISTEN_ADDR=”3306"
CONTROL_ADDR=”glb_ip:4444"
CONTROL_FIFO=”/var/run/glbd.fifo”
THREADS=”3"
MAX_CONN=256
DEFAULT_TARGETS=”node-1_ip:3306:1 node-2_ip:3306:1 node-3_ip:3306:1"
OTHER_OPTIONS=” — round-robin”

Start the Galera Load Balancer

systemctl daemon-reload
service glb restart
service glb getinfo

QUERYING THE GALERA LOAD BALANCER

echo getinfo | nc -q 1 glb_ip 4444
echo getstats | nc -q 1 glb_ip 4444

Install mysql-client

apt-get install mariadb-client -y

Now test that galera load balancer is working well

mysql -utest -p -h glb_ip -P3306
SELECT @@wsrep_node_name;

On another window

echo getinfo | nc -q 1 glb_ip 4444
echo getstats | nc -q 1 glb_ip 4444

Do it again to check load balancing working well.

To remove or add a node to GLB, the following commands should be used:

echo node_to_remove_ip:3306:-1 | nc -s 1 glb_ip 4444