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)
Related Posts:
- LowEndBoxTV: Free Power Toys for Your Linux Server! - October 4, 2024
- Happy Halloween from MangoMail!Get 3 Months FREE Email Hosting on New Signups! - October 3, 2024
- LowEndBoxTV: Stop Losing Your WordPress Data! Backup Your WordPress Easily for FREE! - October 2, 2024
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?
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.
Thanks for the reply! It was useful :)
You have provided such a information in this informative way , nice.