Galera Cluster MariaDB Configuration On CentOS 7

Galera cluster is a true multi-master MySQL cluster using synchronous replication.   It allows for any of the nodes to be used as a master or all of them as well as providing automatic node joining and node removal. The multi-master configuration is very different from the typical master-slave configuration done with MySQL  servers and can provide much more load balancing and fault tolerance.  This guide will help you setup a basic Galera Cluster with MariaDB and CentOS 7. We will configure 3 nodes as that is the minimum required for redundancy, if you were to configure a two-node cluster, if one was to leave the cluster ungracefully the second node would cease to function as well.

Galera And MariaDB Installation

First you are going to want to install MariaDB, version 10.1, on each of the three nodes.

Add the repository to each of the 3 nodes

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

Insert the following repository information and save the file

# MariaDB 10.1 CentOS repository list - created 2017-08-10 00:39 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Install the packages from yum, Galera is included when these are installed

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

Once yum has completed installing finish the installation by running

mysql_secure_installation

We are going to use rsync to perform the replication so install that as well, we will also use lsof to make sure the server is bound to the correct ports:

yum install -y rsync lsof

Make sure each of the MariaDB instances starts on reboot

systemctl enable mariadb

Galera Master Node Configuration

After installing MariaDB on the master node edit the server.cnf file

nano /etc/my.cnf.d/server.cnf

You will want to insert the following under the [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://192.168.1.100,192.168.1.101,192.168.1.102"

## Galera Cluster Configuration
wsrep_cluster_name="cluster1"
## Galera Synchronization Configuration
wsrep_sst_method=rsync
## Galera Node Configuration
wsrep_node_address="192.168.1.100"
wsrep_node_name="centos7-vm1"

wsrep_on=ON – Setting this to ON enables replication. In MariaDB 10.1, replication is turned off as a default, so this needs to be explicitly stated.
wsrep_cluster_address  – This is where we specify each of the IP addresses for the nodes delineated by a comma. The primary node is always the first IP address, this this case its 192.168.1.100
wsrep_cluster_name – Is the name of the cluster, you can name this anything you want
wsrep_node_address – Is the IP address of the node you are configuring
wsrep_node_name – This is the name of the node you are currently configuring, it can be named anything you want, it just needs to be unique.

Under the [mysqld] section add a log location (if you don’t, it will log to the main syslog)

log_error=/var/log/mariadb.log

Once you have finished editing and saved server.cnf, go ahead and create the error log

touch /var/log/mariadb.log

Give the error log the appropriate permissions:

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

You can now start the new master node by typing the following

galera_new_cluster

After you have started it, make sure it has bound to the correct ports using lsof

Port 4567 is for replication traffic:

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

Port 3306 is for MySQL client connections:

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

You will want to add the the ports to the firewall, in this case we are using firewalld

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

Then reload the firewall:

firewall-cmd --reload

Make sure the cluster is running my connecting to MariaDB on the primary node

mysql -uroot -p

Then check the cluster size

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

It should say 1 at this point because only the primary node is connected.

Adding Additional Nodes To Galera

After installing MariaDB on the addtional nodes, you will want to copy the [galera] section of /etc/my.cnf.d/server.cnf that We created earlier and insert it into the server.cnf on each of the additional nodes. The only lines that will each on each of the additional nodes will be the the following:

wsrep_node_address="192.168.1.100"
wsrep_node_name="centos7-vm1"

The wsrep_node_address will be the IP address of the node you are configuring and the wsrep_node_name will be the name of that node.

After you have finished each of the servers configuration files, you can start them normally

systemctl start mariadb

As each node connects to the cluster you should see the wsrep_cluster_size increase:

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

You will also see nodes join in the log:

WSREP: Member 1.0 (centos7-vm2) synced with group.

The logs will also indicate when a node as left the group:

WSREP: forgetting 96a5eca6 (tcp://192.168.1.101:4567)
WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 0, memb_num = 2

You can view the full configuration of Galera by typing the following:

MariaDB [(none)]> show status like 'wsrep%';
 +------------------------------+--------------------------------------------------------+
 | Variable_name | Value |
 +------------------------------+--------------------------------------------------------+
 | wsrep_apply_oooe | 0.000000 |
 | wsrep_apply_oool | 0.000000 |
 | wsrep_apply_window | 1.000000 |
 | wsrep_causal_reads | 0 |
 | wsrep_cert_deps_distance | 1.000000 |
 | wsrep_cert_index_size | 2 |
 | wsrep_cert_interval | 0.000000 |
 | wsrep_cluster_conf_id | 3 |
 | wsrep_cluster_size | 3 |
 | wsrep_cluster_state_uuid | 6173c852-7ca0-11e7-8d8e-0e2551d18de1 |
 | wsrep_cluster_status | Primary |
 | wsrep_commit_oooe | 0.000000 |
 | wsrep_commit_oool | 0.000000 |
 | wsrep_commit_window | 1.000000 |
 | wsrep_connected | ON |
 | wsrep_desync_count | 0 |
 | wsrep_evs_delayed | |
 | wsrep_evs_evict_list | |
 | wsrep_evs_repl_latency | 0/0/0/0/0 |
 | wsrep_evs_state | OPERATIONAL |
 | wsrep_flow_control_paused | 0.000000 |
 | wsrep_flow_control_paused_ns | 0 |
 | wsrep_flow_control_recv | 0 |
 | wsrep_flow_control_sent | 0 |
 | wsrep_gcomm_uuid | 87a5891a-7ca0-11e7-a3bb-fe31f8409645 |
 | wsrep_incoming_addresses | 192.168.1.101:3306,192.168.1.7:3306,192.168.1.100:3306 |
 | wsrep_last_committed | 2 |
 | wsrep_local_bf_aborts | 0 |
 | wsrep_local_cached_downto | 1 |
 | wsrep_local_cert_failures | 0 |
 | wsrep_local_commits | 0 |
 | wsrep_local_index | 1 |
 | wsrep_local_recv_queue | 0 |
 | wsrep_local_recv_queue_avg | 0.125000 |
 | wsrep_local_recv_queue_max | 2 |
 | wsrep_local_recv_queue_min | 0 |
 | wsrep_local_replays | 0 |
 | wsrep_local_send_queue | 0 |
 | wsrep_local_send_queue_avg | 0.000000 |
 | wsrep_local_send_queue_max | 1 |
 | wsrep_local_send_queue_min | 0 |
 | wsrep_local_state | 4 |
 | wsrep_local_state_comment | Synced |
 | wsrep_local_state_uuid | 6173c852-7ca0-11e7-8d8e-0e2551d18de1 |
 | wsrep_protocol_version | 7 |
 | wsrep_provider_name | Galera |
 | wsrep_provider_vendor | Codership Oy <[email protected]> |
 | wsrep_provider_version | 25.3.20(r3703) |
 | wsrep_ready | ON |
 | wsrep_received | 8 |
 | wsrep_received_bytes | 1169 |
 | wsrep_repl_data_bytes | 359 |
 | wsrep_repl_keys | 1 |
 | wsrep_repl_keys_bytes | 31 |
 | wsrep_repl_other_bytes | 0 |
 | wsrep_replicated | 1 |
 | wsrep_replicated_bytes | 454 |
 | wsrep_thread_count | 2 |
 +------------------------------+--------------------------------------------------------+

Testing Replication On The Galera Cluster

First access one of the nodes MariaDB installs

# 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.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Then create a new database

MariaDB [(none)]> create database galera_test;
 Query OK, 1 row affected (0.01 sec)

Then check to ensure it was replicated to the other nodes

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

You should see the ‘galera_test’ database appear on the other nodes as well. That is it for the inital configuration of the MariaDB Galera Cluster on CentOS 7