LowEndBox - Cheap VPS, Hosting and Dedicated Server Deals

Painless MariaDB Replication with Galera Clusters

Tags: , , , , , Date/Time: September 22, 2021 @ 12:00 am, by raindog308

GaleraGalera 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)

 

I'm Andrew, techno polymath and long-time LowEndTalk community Moderator. My technical interests include all things Unix, perl, python, shell scripting, and relational database systems. I enjoy writing technical articles here on LowEndBox to help people get more out of their VPSes.

3 Comments

  1. This is interesting! What’s the advantage of Galera over the standard MySQL replication? Is it mainly that it supports multi-master mode? Also why does the cluster size have to be odd?

    September 30, 2021 @ 1:13 pm | Reply
    • Not sure about the first question, but the reason you want an odd number of nodes is for quorum. For example, you have two nodes: A & B, and there is a network outage, and during the network outage a client updates record X with new data on node A, and at the same time a different client is connected to node B, and it also writes new data to record X. When the network outage is fixed and nodes A and B are able to communicate again, both will attempt to update each other with the new [differing] data on record X, but the data won’t agree, so they will be stuck in an “argument”. When you have a third server, node C, when the network is fixed, he will be the mediator and decide which node’s data is “right” concerning record X, and which version of the new data gets written to all the nodes in the cluster. This works as long as your node count is odd, because having an odd node count means you can never have an equal number of nodes arguing over who is right.
      Of course my example is probably a bit over-simplified, and the DB experts may have clarification correction to make, but you get the idea.

      October 1, 2021 @ 4:59 pm | Reply

Leave a Reply

Some notes on commenting on LowEndBox:

  • Do not use LowEndBox for support issues. Go to your hosting provider and issue a ticket there. Coming here saying "my VPS is down, what do I do?!" will only have your comments removed.
  • Akismet is used for spam detection. Some comments may be held temporarily for manual approval.
  • Use <pre>...</pre> to quote the output from your terminal/console, or consider using a pastebin service.

Your email address will not be published. Required fields are marked *