DB-유

Setting Up a MariaDB Galera Cluster: A Three-Node Guide

· mariadb, galera, high-availability, cluster

After one too many midnight failovers on a master-slave pair, I moved our small internal databases to Galera. Every node holds the same data, you can write to any of them, and a committed transaction exists on every node before the client gets its acknowledgement. The Galera library and wsrep provider ship inside the regular MariaDB packages now, so setup is mostly configuration.

I'm building a three-node cluster on CentOS 7 with MariaDB 10.3. Three is the minimum sensible size. With two nodes a network split leaves neither side able to prove it holds the majority, and the whole cluster stops accepting writes.

My layout is db1 10.20.0.11, db2 10.20.0.12, db3 10.20.0.13 on a low-latency private network, with time synced by chronyd and the MariaDB 10.3 repo in /etc/yum.repos.d/MariaDB.repo on each node. Put SELinux in permissive mode during setup, or adjust the mysql_port_t labels for the extra ports. I usually go permissive first and tighten later.

1. Install on every node

sudo yum install -y MariaDB-server MariaDB-client galera rsync
sudo systemctl enable mariadb

Do not start the service yet. Galera needs its configuration in place before the first start, and the first node must be started differently from the others.

2. Firewall

Galera uses four ports: 3306 for clients, 4567 for group communication, 4568 for incremental state transfer and 4444 for full state snapshot transfer.

sudo firewall-cmd --permanent --new-zone=galera
sudo firewall-cmd --permanent --zone=galera --add-source=10.20.0.0/24
sudo firewall-cmd --permanent --zone=galera --add-port={3306,4567,4568,4444}/tcp
sudo firewall-cmd --permanent --zone=galera --add-port=4567/udp
sudo firewall-cmd --reload

3. Cluster configuration

Create /etc/my.cnf.d/galera.cnf on each node. Only wsrep_node_address and wsrep_node_name differ.

[mysqld]
binlog_format            = ROW
default_storage_engine   = InnoDB
innodb_autoinc_lock_mode = 2
innodb_flush_log_at_trx_commit = 0
bind-address             = 0.0.0.0

wsrep_on                 = ON
wsrep_provider           = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name       = "prod-cluster"
wsrep_cluster_address    = "gcomm://10.20.0.11,10.20.0.12,10.20.0.13"
wsrep_node_address       = "10.20.0.11"
wsrep_node_name          = "db1"
wsrep_sst_method         = rsync

binlog_format=ROW and innodb_autoinc_lock_mode=2 are required by Galera. innodb_flush_log_at_trx_commit=0 is safe here because durability is provided by the other nodes rather than the local disk; if you lose all three at once, you have bigger problems than the last second of transactions.

I stick with the rsync SST method for small clusters because it's the simplest, even though it blocks the donor for the whole transfer. On a large data set, switch to mariabackup later and set wsrep_sst_auth.

4. Bootstrap the first node

The first node has nobody to join, so it must be told to form a new cluster:

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

You should see wsrep_cluster_size 1. Run mysql_secure_installation on this node now; the changes will replicate to the others as they join.

Never run galera_new_cluster on a node when a cluster already exists. I did it once out of habit. It creates a second, independent cluster, and you end up with two sets of data that will never merge.

5. Join the others

On db2 and then db3, a plain start is enough. The node reads wsrep_cluster_address, contacts the existing members and requests a state transfer:

sudo systemctl start mariadb
sudo journalctl -u mariadb -f

Watch for WSREP: Member 1.0 (db2) synced with group in the log. Wait for each node to reach Synced before starting the next; two nodes doing SST from a single donor at once is slow and unnecessary.

Is the cluster healthy?

On any node:

SHOW STATUS LIKE 'wsrep_%';

The values that matter:

  • wsrep_cluster_size is 3
  • wsrep_cluster_status is Primary
  • wsrep_local_state_comment is Synced
  • wsrep_ready is ON
  • wsrep_connected is ON

Then prove replication in both directions. On db1:

CREATE DATABASE galera_test;
CREATE TABLE galera_test.t (id INT AUTO_INCREMENT PRIMARY KEY, node VARCHAR(10));
INSERT INTO galera_test.t (node) VALUES ('db1');

On db3:

INSERT INTO galera_test.t (node) VALUES ('db3');
SELECT * FROM galera_test.t;

Note the id values jump by 3. That is wsrep_auto_increment_control giving each node its own offset so simultaneous inserts never collide.

Finally, reboot one node and watch it rejoin. With a small change set it should use IST (incremental) rather than a full SST; the log says WSREP: Receiving IST.

Trouble I ran into

[ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view: 110 (Connection timed out) on a joining node. Port 4567 is blocked or wsrep_cluster_address has a wrong IP. Test with nc -zv 10.20.0.11 4567 from the joiner.

WSREP: It may not be safe to bootstrap the cluster from this node. It was not the last one to leave the cluster after all nodes were shut down. Look at /var/lib/mysql/grastate.dat on each node; the one with the highest seqno (or safe_to_bootstrap: 1) is the node to run galera_new_cluster on. On the others, start normally afterwards.

ERROR 1047 (08S01): WSREP has not yet prepared node for application use. The node is not Synced, either still joining or in a non-primary partition after a split. Check wsrep_cluster_status; if every node says non-Primary, force one to primary with SET GLOBAL wsrep_provider_options='pc.bootstrap=YES'; on the node with the latest data.

Tables without a primary key replicate but delete operations on them become extremely slow, and Galera cannot guarantee row ordering. Set wsrep_certify_nonPK=OFF on a test system to find them, or query information_schema.tables for tables missing a PRIMARY index and add one.

ERROR 1213 (40001): Deadlock found when trying to get lock on commit with no local deadlock. That is a certification failure: two nodes changed the same row concurrently and the later one lost. The application should retry the transaction; the error code is the same one it should already handle.

Next I'd look at a load balancer or ProxySQL in front, and sending writes for a given table mostly to one node to cut certification conflicts.