Posted on February 21, 2023 by darshin

MySQL FailOver Cluster (Set UP)

Step1: check with sestatus if selinux is enabled or disabled

if Selinux is enabled then edit /etc/selinux/config and disable it reboot nad disable firewalld

Step2: Remove mariadb and mysqal library:

yum remove mariadb-libs -y

Step3: Create mariadb repo file :

vi /etc/yum.repos.d/cluster.repo

Paste the below config:

[mariadb]

name = MariaDB

baseurl =

gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck=1

Step4: Install following packages package (it is dependent file of mariadb galera cluster):

yum install socat -y

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

Step5: Start the mysql service:

systemctl start mysql

Step6: Set mariadb root login with following command: make sure you setup a root pass and allow remote logins.

mysql_secure_installation

Step7: create a file /root/.my.cnf and paste the following: change the pass to the root pass you just set in above step

[client]

user=root

password=darshin123

Step8: Log into mysql cli with mysql command (it wont ask for the pass now)  and run below commands

GRANT USAGE ON *.* to db_user@’%’ IDENTIFIED BY ‘admin’;

grant all privileges on *.* to db_user@’%’ IDENTIFIED BY ‘admin’;

flush privileges;

Step9: Stop mariadb service with following command:

systemctl stop mysql

Step10: Configure galero cluster with following command:

vi /etc/my.cnf.d/server.cnf

below mariadb-10.0  label add following content:

[mariadb-10.0]

max-connections=10000

binlog_format=ROW

default-storage-engine=innodb

innodb_autoinc_lock_mode=2

innodb_locks_unsafe_for_binlog=1

query_cache_size=0

query_cache_type=0

bind-address=0.0.0.0

datadir=/var/lib/mysql

innodb_log_file_size=100M

innodb_file_per_table

innodb_flush_log_at_trx_commit=2

wsrep_provider=/usr/lib64/galera/libgalera_smm.so

wsrep_cluster_address=”gcomm://104.248.20.61,142.93.165.195,46.101.129.87″

wsrep_cluster_name=’galera_cluster’

wsrep_node_address=’104.248.20.61′

wsrep_node_name=’node1′

wsrep_sst_method=rsync

wsrep_sst_auth=db_user:admin

Enter all 3 nodes IPs in wsrep_cluster_address
Enter node IP address in wsrep_node_address
Enter node name in wsrep_node_name field

Do the above steps in remaining 2 nodes:

Step11: On node1 run the following command to start the cluster:

/etc/init.d/mysql start –wsrep-new-cluster

Step12: On second and third node start mysql with following command:

systemctl start mysql

Step13: On all three nodes start mysql cli and run below command to check status of replication.

show status like ‘wsrep%’;

Create a database on node1 and insert a table with field and values. it will get replicated on other 2 nodes
same way you can insert values in node2 and node 3 and it will get replicated on other nodes.

For adding more nodes:

1. First shutdown last node mysql then the second last and finally the first node.
2. Do Step1 to step10 on new node.
3. Edit /etc/my.cnf.d/server.cnf on all nodes and add the new node’s IP in wsrep_cluster_address Field (in the last)

start the mysql on the first node with /etc/init.d/mysql start –wsrep-new-cluster command.

start the mysql on other nodes with  systemctl start mysql  command

check status in mysql on each node with command  show status like ‘wsrep%’;