Databases

How To Configure MariaDB Galera Cluster on CentOS 7

How To Configure MariaDB Galera Cluster on CentOS 7

Galera is a multi-master cluster for MariaDB which replicates data using synchronous replication. Galera allows any nodes in the cluster to act as master and write to any node at a time. The active-active configuration of Galera cluster provides more load balancing and fault tolerance since there is no failover.

 

In this tutorial, we are going to see how to install and configure a Galera cluster comprised of three nodes with MariaDB and CentOS 7. Three nodes are the minimum requirement to achieve full redundancy. If two nodes used to form a Galera cluster and one node was to shutdown ungracefully, then the second node would not work as well.

Step 1 - Installing MariaDB

For the Galera Cluster, we need three nodes with MariaDB and CentOS 7. Galera is included by default in MariaDB 10.x. So if you have nodes setup with CentOS 7 and MariaDB then skip this step and proceed to step 2.

First, we are going to add the MariaDB repository to each of the three nodes. Open the repo file with any editor:

$ nano /etc/yum.repos.d/MariaDB10.1.repo

Insert the following repository information and save the file:

# MariaDB 10.1 CentOS repository list
# https://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = https://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Install the packages using yum as following:

$ yum install MariaDB-server MariaDB-client MariaDB-common -y

After packages are installed, we are going to secure MariaDB using the following command. The command will prompt to set the root password to access MariaDB, remove anonymous user and test database.

$ mysql_secure_installation

Galera can use rsync to perform the replication between cluster node. Also, we are going to verify using lsof to ensure that MariaDB binds to correct port. So we need to install both rsync and lsof packages:

$ yum install rsync lsof -y

Enable MariaDB service to auto restart during a reboot

$ systemctl enable mariadb

Step 2 - Galera Master Node Configuration

We are going to ensure at least one node is fully operational with Galera before joining additional nodes. We need to add the following content to MariaDB configuration file in /etc/my.cnf.d/server.cnf under [galera] section:

binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.10.0.11,10.10.0.12,10.10.0.13"

## Galera Cluster Configuration
wsrep_cluster_name="galeracluster1"

## Galera Synchronization Configuration
wsrep_sst_method=rsync

## Galera Node Configuration
wsrep_node_address="10.10.0.11"
wsrep_node_name="galera1"

The same content needs to be copied to all nodes to be joined to the cluster except the node_address and node_name.

We need to add a log location for MariaDB or else all logs will get added to syslog. Add the following log location under the [mysqld] section:

log_error=/var/log/mariadb.log

Save the configuration file and exit. Since the log file does not exist, we will create it as follows:

$ touch /var/log/mariadb.log

Give the error log the appropriate permissions to be able to log:

$ chown mysql:mysql /var/log/mariadb.log

Step 3 - Create Galera Cluster and Verify Ports

We are now ready to create the cluster. Keep in mind that do not run the cluster creation command on more than one node.

$ galera_new_cluster

Galera replication traffic takes place on port 4567. To verify Galera has bound to the correct ports, we will use lsof as following:

$ lsof -i:4567
 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
 mysqld 2563 mysql 11u IPv4 30210 0t0 TCP *:tram (LISTEN)

MariaDB listens for client connections on port 3306. We will use lsof to verify the port is listening:

$ lsof -i:3306
 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
 mysqld 2563 mysql 26u IPv4 30210 0t0 TCP *:mysql (LISTEN)

Step 4 - Adding Firewall Rules

To ensure traffic is not blocked we will open some ports using firewalld as following:

$ firewall-cmd --zone=public --add-service=mysql --permanent
$ 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

Restart firewall to activate newly added rules:

$ firewall-cmd --reload

Step 5 - Verify Galera Master Node

At this point, the first master Galera node should be functioning. We can verify through the MariaDB shell using cluster size command. The value of cluster size should be more than 1.

$ mysql -uroot -p

MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_cluster_size';
   +--------------------+-------+
   | Variable_name | Value |
   +--------------------+-------+
   | wsrep_cluster_size | 1 |
   +--------------------+-------+

As we can see from the cluster size, there is only one member node in the cluster which is the primary master node we just configured.

Step 6 - Adding Additional Nodes to Galera Cluster

Follow Step 2 to add the content of [galera] section on both of the nodes. Only change the value for node address and node name respectively:

wsrep_node_address="10.10.0.12"
wsrep_node_name="galera2"
wsrep_node_address="10.10.0.13"
wsrep_node_name="galera3"

The wsrep_node_address is the IP address of the node being configured and the wsrep_node_name is the name of that node. After adding each servers configuration files, we can start or restart MariaDB:

$ systemctl start mariadb

As we join each node to the cluster, the wsrep_cluster_size should increase. After adding all the three nodes, we can check the status again from MariaDB shell of any node:

MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_cluster_size';
   +--------------------+-------+
   | Variable_name | Value |
   +--------------------+-------+
   | wsrep_cluster_size | 3 |
   +--------------------+-------+

We can also view the full configuration of Galera by typing the following:

MariaDB [(none)]> show status like 'wsrep%';

Step 7 - Testing Replication On The Galera Cluster

We can test the replication between cluster nodes are functioning as expected by creating a test database on one node and viewing the database list on another node. If replication working properly, the test database should be on all the nodes. We are going to create a test database named ‘galera_db’ on node 1.

$ mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.1.25-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

MariaDB [(none)]>

MariaDB [(none)]> create database galera_db;

Query OK, 1 row affected (0.01 sec)

We are now going to see the database list from node 3 to ensure the test database is present:

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| galera_db |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

If the database does not show up in the list, then replication is not working. Check the status of the MariaDB service and logs to pinpoint the issue.

Latest HOW-TOs