MariaDB + Galera Cluster

Highly available & distributed MySQL database with MariaDB & Galera Cluster. Supports auto failover.

1. Gather your hosts

In order to have a more flexible configuration, and not have to remember IP addresses, edit /etc/hosts and insert the following lines:

Copy

100.0.1.10 db1
100.0.1.11 db2
100.0.1.12 db3

Modify each IP address & each hostname as you please.

Info

It is (obviously) possible to add IPv6 addresses:

Copy

fe80::1 db1
fe80::2 db2
fe80::2 db3

2. Install MariaDB & Galera Cluster

Debian:

Copy

sudo apt install mariadb-server galera-4

Centos/Fedora/RHEL:

Copy

sudo dnf install mariadb-server mariadb-server-galera

Gentoo:

Copy

sudo emerge dev-db/mariadb sys-cluster/galera

3. Configure the first node

Info

Edit /etc/mysql/mariadb.cnf.d/50-server.cnf & make sure the following line is commented:

Copy

# bind-address = 0.0.0.0

Create the user that will be used for replication: (modify all bold parameters)

Copy

mysql -u root -p

Copy

CREATE USER 'replication' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'replication';
FLUSH PRIVILEGES;

Edit /etc/mysql/mariadb.cnf.d/60-galera.cnf: (modify all bold parameters)

Copy

# Debian: [galera]
# RHEL: [mysqld]

[galera]
binlog_format            = row
default-storage-engine   = innodb
innodb_autoinc_lock_mode = 2

# Galera Provider
wsrep_on = ON
wsrep_provider = /usr/lib/galera/libgalera_smm.so

# Cluster
wsrep_cluster_name    = "MariaDB Galera Cluster"
wsrep_cluster_address = "gcomm://db1,db2,db3"
wsrep_sst_method      = rsync
wsrep_sst_auth        = replication:passwd

# This node's config
bind-address                = db1
wsrep_node_name             = "db1"
wsrep_node_address          = "db1"
wsrep_node_receive_address  = "db1"
wsrep_node_incoming_address = "db1"

Create the cluster:

Copy

systemctl stop mariadb
galera_new_cluster

4. Configuring the other nodes

Now that the cluster has been configured on the first node, you need to copy the configuration file to all other nodes, making sure to modify the last 5 parameters, which uniquely identify the node.

Example: configuration on a second node.

Copy

# Debian: [galera]
# RHEL: [mysqld]

[galera]
binlog_format            = row
default-storage-engine   = innodb
innodb_autoinc_lock_mode = 2

# Galera Provider
wsrep_on = ON
wsrep_provider = /usr/lib/galera/libgalera_smm.so

# Cluster
wsrep_cluster_name    = "MariaDB Galera Cluster"
wsrep_cluster_address = "gcomm://db1,db2,db3"
wsrep_sst_method      = rsync
wsrep_sst_auth        = replication:passwd

# This node's config
bind-address                = db2
wsrep_node_name             = "db2"
wsrep_node_address          = "db2"
wsrep_node_receive_address  = "db2"
wsrep_node_incoming_address = "db2"

After configuring each node, you can start MariaDB:

Copy

systemctl start mariadb

5. Configure the firewall

Make sure to open the following ports:

3306 (TCP): MySQL Client Connections & State Snapshot Transfer (mysqldump)
4567 (TCP/UDP): Galera Cluster Replication
4568 (TCP): Incremental State Transfer
4444 (TCP): State Snapshot Transfers

CentOS/Fedora/RHEL (firewalld):

Copy

firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --zone=public --add-port=4567/tcp --permanent
firewall-cmd --zone=public --add-port=4567/udp --permanent
firewall-cmd --zone=public --add-port=4568/tcp --permanent
firewall-cmd --zone=public --add-port=4444/tcp --permanent
firewall-cmd --reload

Debian (ufw):

Copy

ufw allow 3306,4567,4568,4444/tcp
ufw allow 4567/udp

Warning

This is only an example config. It is highly recommended you modify it so that only your nodes can communicate on these ports.

6. Configuration test

Verify that your cluster has the right number of nodes with the following command:

Copy

mysql -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

In this case, since we configured 3 nodes, the command will return the following:

Copy

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

To be 100% certain that replication works, let's create a DB and a test table on a node:

Copy

mysql -u root -p

Copy

CREATE DATABASE testdb;
CREATE TABLE testdb.msg (
    'id' INT NOT NULL AUTO_INCREMENT,
    'message' VARCHAR(255) NOT NULL,
    PRIMARY KEY ('id')
);
INSERT INTO testdb.msg ('id', 'message') VALUES (1, 'AAA');
INSERT INTO testdb.msg ('id', 'message') VALUES (2, 'BBB');
INSERT INTO testdb.msg ('id', 'message') VALUES (3, 'CCC');

Execute this command on any other node:

Copy

mysql -e "SELECT * FROM testdb.msg"

7. Load balancer with nginx

It's possible to optionally use nginx as a cluster load balancer. Edit /etc/nginx/nginx.conf and add the following config:

Copy

stream {
	upstream cluster {
		zone tcp_servers 64k;
		server db1:3306;
		server db2:3306;
		server db3:3306;
	}

	# NOTE: change this port if you already have
	# mariadb/mysql running on this host
	server {
		# required: nginx >=1.25.5
		# https://nginx.org/en/docs/stream/ngx_stream_core_module.html#server_name
		# server_name db.internal.example.com;
		listen 3306;
		proxy_pass cluster;
		proxy_connect_timeout 3s;
	}
}

← Back to the main page