Setting up failover cluster for MariaDB on CentOS 7

The term Failover Cluster describes the process of connecting multiple computers to maintain high availability of applications and services.When a failure occurs on one computer in a cluster, resources are redirected and the workload is redistributed to another computer in the cluster. Failover clusters are used to ensure that users have  constant access to important server-based resources. In this article we will create High availability for MariaDB database,so if one server goes down,another database server will take over services and users won’t be affected. We will be using 2 servers: server1.example.com (192.168.122.80) server2.example.com (192.168.122.90) On both servers do the following steps:

Install MariaDB Galera cluster packages

Galera is generic synchronous multi-master replication plugin for transactional applications.Cluster package is not available in default repositories,so we must create one,create new file named mariadb.repo in /etc/yum.repos.d/ directory.File content should be like this one:

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/rhel7-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Now in terminal type:

yum install MariaDB-Galera-server MariaDB-client galera -y

MariaDB Galera Cluster is a synchronous multi-master cluster for MariaDB. It supports synchronous replication,Active-active multi-master topology (allows data to be stored by a group of computers,and updated by any member of the group.All members respond to queries from client computers.It propagates the data modifications made by each member to the rest of the group, and resolving any conflicts that might arise between concurrent changes made by different members),failed nodes drop from the cluster,reads and writes to any cluster nodes,controls,memberships,automatically joins nodes.

Set root account and password,start mariadb service and enable service start during boot

mysql_secure_installation
systemctl start mariadb
systemctl enable mariadb

Allow root user with database password 1234 full acccess to MariaDB server,from network 192.168.122.0

grant all privileges on *.* to 'root'@'192.168.122.%' identified by '1234' with grant option;

On both server open tcp port 4567

firewall-cmd --add-port=4567/tcp --permanent;firewall-cmd --reload

stop mysql service on both servers:

systemctl stop mysql

Edit  /etc/my.cnf.d/server.cnf file on both servers

On server1 (IP 192.168.122.90) should be:

[galera]
# Mandatory settings
wsrep_provider=/usr/lib64/galera/libgalera_smm.so #Synchronous multi-master wsrep provider
wsrep_cluster_address='gcomm://192.168.122.80,192.168.122.90'
wsrep_cluster_name='mariadb_cluster'
wsrep_node_address='192.168.122.90'
wsrep_sst_method=rsync   #replication method rsync (rsync package is installed with galerapackage).This is the fastest and recommended method, especially for large datasets since i#t copies binary data
wsrep_sst_auth=root:1234  #using root user with password 1234 (set during MariaDB configuration
binlog_format=row  #SQL logging format,Records events affecting individual table rows.
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0 # local addresses that MySQL will listen for connections. The defaultis 0.0.0.0 which is all interfaces

On server 2 (IP 192.168.122.80):

[galera]
# Mandatory settings

wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address='gcomm://192.168.122.80,192.168.122.90'
wsrep_cluster_name='mariadb_cluster'
wsrep_node_address=192.168.122.80
wsrep_sst_method=rsync
wsrep_sst_auth=root:1234
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

Initialize primary cluster node

On server1 type

/etc/init.d/mysql restart --wsrep-new-cluster
Starting MySQL.. SUCCESS!

–wsrep-new-cluster switch defines server1 as cluster node which can continue to modify the database state to avoid history divergence,in short,server1 became Primary component (PC).

Add server2 to cluster by starting mysql service:

systemctl start mysql

If you face error starting services on any node,check log file in /var/lib/mysql/.err

From any node check if cluster is runninng:

mysql -u root -p -e "show status like 'wsrep%'"
wsrep_cluster_size            2  #number of nodes
wsrep_connected              ON
wsrep_ready                    ON   #It's running,awesome !
wsrep_incoming_addresses     | 192.168.122.80:3306,192.168.122.90:3306

Test if replication works:

On server1 create database named cluster:

MariaDB [(none)]> create database cluster;

On server2 check if database is visible:

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