Install MariaDB Galera Cluster on Centos 7

Step 1: Add the MariaDB repository to three MySQL nodes.

[root@localhost]# vi /etc/yum.repos.d/MariaDB10.1.repo

# 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

Step 2: Install MariaDB and the prerequisite packages.

[root@localhost]# yum -y install MariaDB-server MariaDB-client MariaDB-common rsync lsof policycoreutils-python firewalld

Step 3: Enable the Mariadb service to start at boot, but do not start the service just yet.

[root@localhost]# systemctl enable mariadb.service

Step 4: Start the firewalld service and enable the service to start at boot.

[root@localhost ~]# systemctl enable firewalld
[root@localhost ~]# systemctl start firewalld

Step 5: Configure firewall exceptions to allow both replication and remote MySQL connections and reload the firewall.

[root@localhost ~]# firewall-cmd --zone=public --add-service=mysql --permanent
[root@localhost ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
[root@localhost ~]# firewall-cmd --zone=public --add-port=4444/tcp --permanent
[root@localhost ~]# firewall-cmd --zone=public --add-port=4567/tcp --permanent
[root@localhost ~]# firewall-cmd --zone=public --add-port=4567/udp --permanent
[root@localhost ~]# firewall-cmd --zone=public --add-port=4568/tcp --permanent
[root@localhost ~]# firewall-cmd --reload

Step 6: Configure SELinux to permissive mode for MySQL.

[root@localhost]# semanage port -a -t mysqld_port_t -p tcp 4567
[root@localhost]# semanage port -a -t mysqld_port_t -p udp 4567
[root@localhost]# semanage port -a -t mysqld_port_t -p tcp 4568
[root@localhost]# semanage port -a -t mysqld_port_t -p tcp 4444
[root@localhost]# semanage permissive -a mysqld_t

NOTE: Some of the SELinux policies may already be defined.

Step 7: Make a backup of the server.cnf file and then edit the file to include the below.

[root@localhost]# cp /etc/my.cnf.d/server.cnf /etc/my.cnf.d/server.cnf.bak
[root@localhost]# vi /etc/my.cnf.d/server.cnf

[mysqld]
log_error=/var/log/mariadb.log

#
# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.1.0.11,10.1.0.12,10.1.0.13"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

## Galera Cluster Configuration
wsrep_cluster_name="cluster1"

## Galera Synchronization Configuration
wsrep_sst_method=rsync

## Galera Node Configuration
server_id=1
wsrep_node_address="10.1.0.11"
wsrep_node_name="mysql01"

#
# Allow server to accept connections on all interfaces.
#
bind-address=0.0.0.0

Step 8: Create the mariadb.log and give the mysql account the appropriate permissions.

[root@localhost]# touch /var/log/mariadb.log
[root@localhost]# chown mysql:mysql /var/log/mariadb.log

Step 9: Repeat the above steps on each MySQL node that will participate in the MySQL Cluster. Be sure to alter the below fields in the /etc/my.cnf.d/server.cnf file to reflect the correct IP address and hostname that is applicable to each node.

server_id=1
wsrep_node_address="10.1.0.11"
wsrep_node_name="mysql01"

Step 10: Start the MySQL on the first node, mysql01.

[root@localhost]# galera_new_cluster

Step 11: Improve the MySQL installation security by running the command below. Take all of the default which are the most secure. You will also be asked to change the MySQL root password which is recommended you change. By default the MySQL root user password is blank.

[root@localhost]# /usr/bin/mysql_secure_installation

NOTE: The root password should be a well-documented password and should not match the root user password of the Linux system.

Step 12: Login to MySQL and run the below query to confirm the number of nodes in the cluster.

[root@localhost]# mysql -u root -p
MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+

Step 13: Start MySQL on the other member nodes, mysql02 and mysql03. Notice the command to start the mariadb service is different on subsequent member nodes.

[root@localhost]# systemctl start mariadb

Step 14: Login to MySQL and run the below query again to confirm the number of nodes in the cluster has increased.

[root@localhost]# mysql -u root -p
MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

Step 15: Confirm port 4567 is actively listening for replication connections.

[root@localhost]# lsof -i:4567
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  2889 mysql   11u  IPv4  26890      0t0  TCP *:tram (LISTEN)

Step 16: Confirm port 3306 is actively listening for MySQL connections.

[root@localhost]#  lsof -i:3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  2889 mysql   26u  IPv4  26894      0t0  TCP *:mysql (LISTEN)

Step 17: Test replication by creating a new database on one node and then verify the database is replicated to other nodes. Use the below query on the source node used to create the database and on member nodes to verify replication.

[root@localhost]# mysql -u root -p

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

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

Step 18: Shutdown mysql01 and check the cluster size from one of the member nodes.

MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+
1 row in set (0.00 sec)

Step 19: Bring mysql01 back online and confirm all three nodes are connected.

MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.00 sec)

Step 20: Confirm all three node IPs are listed in wsrep_incoming_addresses. Disconnected nodes are removed from replication automatically.

MariaDB [(none)]> SHOW STATUS LIKE 'wsrep%';

+------------------------------+----------------------------------------------------+
| Variable_name                | Value                                              |
+------------------------------+----------------------------------------------------+
| wsrep_incoming_addresses     | 10.1.0.11:3306,10.1.0.12:3306,10.1.0.13:3306       |
+------------------------------+----------------------------------------------------+

Step 21: The below query will confirm which version of MySQL you are running.

MariaDB [(none)]> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------------------+
| Variable_name           | Value                           |
+-------------------------+---------------------------------+
| innodb_version          | 5.6.36-82.1                     |
| protocol_version        | 10                              |
| slave_type_conversions  |                                 |
| version                 | 10.1.26-MariaDB                 |
| version_comment         | MariaDB Server                  |
| version_compile_machine | x86_64                          |
| version_compile_os      | Linux                           |
| version_malloc_library  | system jemalloc                 |
| version_ssl_library     | OpenSSL 1.0.1e-fips 11 Feb 2013 |
| wsrep_patch_version     | wsrep_25.19                     |
+-------------------------+---------------------------------+
10 rows in set (0.00 sec)

Step 22: The below query will confirm which format is the default on the MySQL Server.

MariaDB [(none)]> SHOW VARIABLES LIKE 'innodb_file_format';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| innodb_file_format | Antelope |
+--------------------+----------+
1 row in set (0.01 sec)