How To Install & Configure a Galera Cluster with MySQL on Centos 7

Introduction

Clustering contributes high availability to your database by replicating changes to different servers. If one of the databases fails, others are available to act.

Galera Clustering can be configured in two configurations, active-passive and active-active:

active-passive: All writes and changes are done on a single active server and then replicated to one or more passive servers. The passive servers take over only if the active server failure.

active-active: All nodes is read-write and every change made to node is replicated to all.

In this guide, we will configure active-active clustering with three nodes.

Enviroment

To create this Galera clustering testing lab we will need 3 Servers.

For the demonstration we will have 3 Servers with Centos 7 Installed with the following hostnames:

mysqlcluster01 – 192.168.2.10

mysqlcluster02 – 192.168.2.11

mysqlcluster03 –  192.168.2.12

Step 1 — Make a cleanup

-Preform this step on all servers(nodes)

Lets make sure we are working on a clean environment, We will delete everything that belongs MariaDB and Galera Clustering.

yum remove MariaDB* -y

rm -rf /var/lib/mysql

rm -rf /var/lib/mysql/galera.cache /var/lib/mysql/grastate.dat /var/lib/mysql/gvwstate.dat /var/lib/mysql/tc.log /var/lib/mysql/rsync_sst_complete

Step 2 — Install dependency packages

-Preform this step on all servers(nodes)

Install “Development Tools”, These tools include core development tools such as automake, gcc, perl, python, and debuggers.

yum --nogpgcheck group install "Development Tools" -y

Install rsync & Socat
Rsync is a fast and extraordinarily versatile file copying tool that galera will use to replicate the data.
Socat is a command line based utility that establishes two bidirectional byte streams and transfers data between them.

yum --nogpgcheck install rsync socat -y

Step 3 — Adding the Galera Repository

-Preform this step on all servers(nodes)

MySQL with the Galera extension isn’t included in Centos default repositories, So we will create a new one:

cat << EOF > /etc/yum.repos.d/MariaDB.repo
# MariaDB 10.1 CentOS repository list - created 2016-06-26 08:49 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
EOF

Once you added the repository on all servers, we’re ready to proceed to the installation.

Step 4 — Installing MySQL

-Preform this step on all servers(nodes)

Run the following command  to install a version of MySQL with Galera extension:

yum --nogpgcheck install MariaDB-server MariaDB-client -y

Start the mysql service:

/usr/bin/systemctl start mysql

Now, create a mysql root password:

DATABASE_PASS=”MyPassword”

mysql -u root <<-EOF
UPDATE mysql.user SET Password=PASSWORD('$DATABASE_PASS') WHERE User='root';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.db WHERE Db='test' OR Db='test\_%';
FLUSH PRIVILEGES;
EOF

 

Update the root password on /root/.my.cnf  to login mysql without password authentication:

echo '[client]
password='$DATABASE_PASS'
user=root' > /root/.my.cnf

For convenience, Add the mysql hostnames to the host file of each node:

cat << EOF > /etc/hosts
192.168.2.10 mysqlcluster01
192.168.2.11 mysqlcluster02
192.168.2.12 mysqlcluster03
EOF

 

Step 5 — Configure Galera Clustering

Every node in the cluster needs to have a almost identical configuration. we will configure our first node, and then copy it to the other nodes.

First, Stop the mysql service:

service mysql stop

Second, Create the cluster configuration file:

cat << EOF > /etc/my.cnf.d/cluster.cnf
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address='gcomm://'
wsrep_cluster_name='galera'
wsrep_node_address='192.168.2.10'
wsrep_node_name='mysqlcluster01'
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
EOF

Now configure the remaining nodes:

Node 2 :

service mysql stop

cat << EOF > /etc/my.cnf.d/cluster.cnf
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address='gcomm://mysqlcluster01,mysqlcluster02,mysqlcluster03'
wsrep_cluster_name='galera'
wsrep_node_address='192.168.2.11'
wsrep_node_name='mysqlcluster02'
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
EOF

Node 3 :

service mysql stop

cat << EOF > /etc/my.cnf.d/cluster.cnf
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address='gcomm://mysqlcluster01,mysqlcluster02,mysqlcluster03'
wsrep_cluster_name='galera'
wsrep_node_address='192.168.2.12'
wsrep_node_name='mysqlcluster03'
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
EOF

 

Step 6 — Opening the Firewall on Every Node

Galera make use of the following ports:

• 3306 For MySQL client connections and State Snapshot Transfer that use the mysqldump method.
• 4567 For Galera Cluster replication traffic, multicast replication uses both UDP transport and TCP on this port.
• 4568 For Incremental State Transfer.
• 4444 For all other State Snapshot Transfer.

My recommendation is to whitelist the server IP addresses on each server.

But, you can open the ports with the following command:

firewall-cmd --add-port=4567/tcp --permanent
firewall-cmd --add-port=4568/tcp --permanent
firewall-cmd --add-port=4444/tcp --permanent
firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --reload

Step 7 — Initialize The First Cluster Node

-Make this step only on the first node

service mysql start --wsrep-cluster-address="gcomm://"

Check the cluster status:

 mysql -u root -e "show status like 'wsrep%'";

Search for the following OUTPUT:

| wsrep_connected              | ON    
| wsrep_ready                  | ON 
| wsrep_local_state_comment    | Synced         

Now Connect the other nodes to the cluster:

Node 02:

 service mysql start

Node 03:

 service mysql start

Check the cluster Members:

mysql -u root -e "SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size';"

Step 8 — Testing

At this point our cluster can perform replication from any node to other, known as active-active method. Let’s make sure that the replication is working as expected.

 

Select node and create database:

mysql -u root -e 'CREATE DATABASE clusterTest;'

 

Go to ohter node(server) and check if you can see the database you created:

mysql -u root -e 'show databases;'