Galera is MariaDB’s multi-master replication technology which makes it a snap to setup highly-available multi-node MariaDB clusters.  In this tutorial we’ll walk through setting up a cluster and also look at some replication events.

Cluster Design

For Galera, you want to use odd-number clustered sizes: 3 nodes, 5 nodes, etc.  It’s possible to run a 1-node Galera cluster but then you’re not replicating, so that’d be pointless.  The nodes can be anywhere in the world, but keep in mind that Galera is synchronous replication.  This means the more nodes and the further they are from each other, the more latency.

Note that we’re only referring to write latency.  If you have three nodes that are placed far from each other but there are only a few time-insensitive writes throughout the day and the bulk of interactions are read (e.g., a blog or news site) then this system can still work well.

In this design, I’m using the following cluster and anonymized IPs:

• db1.lowend.party on 1.1.1.1
• db2.lowend.party on 2.2.2.2
• db3.lowend.party on 3.3.3.3

Installing MariaDB

I put each node’s DNS entries on each host’s /etc/hosts.  This is not strictly necessary because we’ll use IPs for cluster communications but it doesn’t hurt.

In /etc/hosts on each node:

1.1.1.1 db1.lowend.party
2.2.2.2 db2.lowend.party
3.3.3.3 db3.lowend.party

Now install MariaDB and also rsync on each node:

apt-get -y install mariadb-server rsync

Run the MariaDB installation security script on each node:

mysql_secure_installation

Now shut down MariaDB on all nodes:

systemctl stop mariadb

Configuring for Galera

On each node, we’d going to create a galera.cnf file that has MariaDB parameters.  There’s a set of parameters that are common to all nodes and a section that is unique to each node.  On db1, create the following in /etc/mysql/conf.d/galera.cnf

# common to all
[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://1.1.1.1,2.2.2.2,3.3.3.3"
wsrep_sst_method=rsync

# unique to this node
wsrep_node_address="1.1.1.1"
wsrep_node_name="db1"

Do the same on db2, changing the last two lines to:

# unique to this node
wsrep_node_address="2.2.2.2"
wsrep_node_name="db2"

And repeat on db3, changing the last two lines to:

# unique to this node
wsrep_node_address="3.3.3.3"
wsrep_node_name="db3"

Starting Up the Galera Cluster

On db1, run the new cluster command:

 galera_new_cluster

This is a convenience wrapper around the –wsrep-new-cluster flag to MariaDB.  After it executes, you’ll see MariaDB has started up in cluster initialization mode:

root@db1:~# ps -ef | grep -i mysq
mysql    15439     1  2 20:29 ?        00:00:00 /usr/sbin/mysqld --wsrep-new-cluster

Check to see how many nodes are in your cluster.  Connect to mysql and query the wsreap_cluster_size variable:

# mysql
MariaDB [(none)]> show status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+
1 row in set (0.001 sec)

Now on db2:

systemctl start mariadb.service 

And then back on db1:

MariaDB [(none)]> show status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+
1 row in set (0.001 sec)

Then on db3:

systemctl start mariadb.service 

And once again on db1:

MariaDB [(none)]> show status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.001 sec)

Testing Galera

Let’s create a database and put some data in it.  In MariaDB, execute these commands:

create database gtest;
use gtest;
create table buffy ( name varchar(30), team_role varchar(100) );
insert into buffy (name, team_role) values ( 'Buffy', 'slayer');
insert into buffy (name, team_role) values ( 'Giles', 'research');
insert into buffy (name, team_role) values ( 'Zander', 'comic relief');
insert into buffy (name, team_role) values ( 'Cordelia', 'eye candy');

Now go over to db3 and see if the data has replicated:

root@db3:/etc/mysql# mysql gtest
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 38
Server version: 10.3.22-MariaDB-0+deb10u1 Debian 10
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [gtest]> select * from buffy;
+----------+--------------+
| name     | team_role    |
+----------+--------------+
| Buffy    | slayer       |
| Giles    | research     |
| Zander   | comic relief |
| Cordelia | eye candy    |
+----------+--------------+
4 rows in set (0.001 sec)

Let’s shut down db2’s MariaDB.  On db2:

systemctl stop mariadb

And now on db1, execute these commands on db1:

use gtest;
insert into buffy (name, team_role) values ( 'Faith', 'slayer');
insert into buffy (name, team_role) values ( 'Wesley', 'whiner');
insert into buffy (name, team_role) values ( 'Oz', 'werewolf');

Now start MariaDB again on db2:

systemctl start mariadb

Check out /var/log/mysql/error.log and you’ll see messages like this:

2020-07-21 21:03:02 0 [Note] WSREP: Shifting JOINER -> JOINED (TO: 15)
2020-07-21 21:03:02 0 [Note] WSREP: Member 0.0 (db2) synced with group.
2020-07-21 21:03:02 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 15)
2020-07-21 21:03:02 1 [Note] WSREP: Synchronized with group, ready for connections

This shows db2 has successfully synchronized (and you can query the table to see this).  You can also compare the ‘wsrep_last_committed’ status variable and they should be the same across the cluster.  Again on db2:

MariaDB [(none)]> show status like 'wsrep_last_committed';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| wsrep_last_committed | 15    |
+----------------------+-------+
1 row in set (0.001 sec)

Of course, we don’t have to write only on db1.  Let’s insert some data on db3:

use gtest;
insert into buffy (name, team_role) values ( 'Willow', 'research');
insert into buffy (name, team_role) values ( 'Anya', 'bad news');

And then on db1:

MariaDB [(none)]> use gtest
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [gtest]> select * from buffy;
+----------+--------------+
| name     | team_role    |
+----------+--------------+
| Buffy    | slayer       |
| Giles    | research     |
| Zander   | comic relief |
| Cordelia | eye candy    |
| Faith    | slayer       |
| Wesley   | whiner       |
| Oz       | werewolf     |
| Willow   | research     |
| Anya     | bad news     |
+----------+--------------+
9 rows in set (0.000 sec)

• Author
• Recent Posts

raindog308

Dread Lord of LowEnd Content at LowEndBox

I'm raindog308, techno polymath and long-time LowEndTalk community Administrator. My technical interests include all things Unix, perl, python, golang, shell scripting, vintage operating systems such as MVS, and relational database systems such as Oracle, PostgreSQL, and MySQL.

I also enjoy using LEBs! I have an empire of little guys for VPNs, larger guys for apps, and big guys for databases and storage. When I'm not in front a screen I'm into German Shepherd dogs, quality knives, target shooting, theology, tabletop roleplaying games, and forest hiking.

I enjoy writing technical articles here on LowEndBox to help people get more out of their systems. You can find me on LowEndTalk @raindog308

Latest posts by raindog308 (see all)

• Crunchbits Discontinuing Popular Annual Plans – The Community Mourns! - November 20, 2024
• RackNerd’s Black Friday 2024: Bigger, Better, and Now in Dublin! - November 19, 2024
• It’s the Season of Giving and CharityHost Has Deals for You! - November 18, 2024