Galera Cluster & MariaDB 10.1+ On CentOS 7 Server

2019-05-04 07:06:31

Galera Cluster & MariaDB 10.1+ On CentOS 7 Server

Some overall info about the Galera Cluster solution. Galera Cluster for MySQL is a true Multimaster Cluster based on synchronous replication. Galera Cluster is an easy-to-use, high-availability solution, which provides high system uptime, no data loss and scalability for future growth. ( Source )

For more information, you could take a look at this official Galera Cluster for MySQL introduction video:

https://youtu.be/n8vM_HVnnfc

With MariaDB 10.1, the wsrep API for Galera Cluster is included by default. This is available as a separate download for MariaDB 10.0 and MariaDB 5.5. If you need to do that for older version you can follow the steps at:s Install And Configure Galera MySQL Cluster On CentOS 7 Server

Preparation

1) Check if SeLinux is disalbed:

    getenforce

Output should be:

    # Disabled

If not - Disable the Selinux service. Configure SELINUX=disabled in the /etc/selinux/config file:

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#       enforcing - SELinux security policy is enforced.
#       permissive - SELinux prints warnings instead of enforcing.
#       disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of these two values:
#       targeted - Targeted processes are protected,
#       mls - Multi Level Security protection.
SELINUXTYPE=targeted

Reboot your system. After reboot, confirm that the getenforce command returns Disabled:

getenforce

Output should be:

# Disabled

2) Disable and Stop Firewalld. To disable firewalld, run the following command as root:

systemctl disable firewalld

systemctl stop firewalld

Check the Status of Firewalld

systemctl status firewalld

If you wish you could install CSF so that anyone could easily manage the network side of things like closing ports and white-listing IPs as this would be needed quite a lot. Here is a guide on how to do that from their official website:

https://download.configserver.com/csf/install.txt

3) Alright, now we are ready to begin! According to many sources, a good practice and also to keep a healthy Quorum, it’s very important to always keep an odd numbers of Galera nodes. In our case the third server can be a smaller server which would be only used as a donor and the load balancer will only redirect requests on 2 nodes instead of 3.

Basically bringing up the new nodes or restarting a node will lock the databases on the donor nodes - if the donor is not defined the donor will be randomly selected. This could bring down heavily used setups. However in our case we would have a third server with the solo purposes of being a donor, which means it would be the only node with its DB getting locked and providing the new nodes with the new data - that way there will be no downtime.

I'll use the following 3 machines:

Server1: 192.168.201.90
Server2: 192.168.201.91
Server3: 192.168.201.92 - the donor

Make sure to change the IP addresses to the IP addresses of your machines.

Galera Cluster Installation - CentOS 7

This needs to be done on all of the 3 servers

1) Remove the default MariaDB library files for all the three servers

yum remove mariadb-libs -y

2) Once the default MariaDB library files have been removed, create the MariaDB 10.0 (If you need to install 10.0 - please follow the steps here repository file for all three servers:

# vim /etc/yum.repos.d/cluster.repo

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

3) Then install the socat dependency package in all the 3 servers:

yum install socat

If you need more information about socat click here: https://centos.pkgs.org/6/repoforge-x86_64/socat-1.7.1.3-1.el6.rf.x86_64.rpm.html

4) Now to install MariaDB Galera cluster 10.0 and its dependencies on all the servers run:

yum install MariaDB-server MariaDB-client MariaDB-shared MariaDB-common rsync

5) Once it is installed, start the MariaDB service for all the servers:

service mysql start

6) Complete the secure MariaDB installation by running the following command (Please set a root password!):

mysql_secure_installation

- NOTE</strong please create a .my.cnf file with the following details:

[client]
user=root
password=THE-ROOT-PASS-THAT-YOU'VE-DEFINED

- We can easily troubleshoot issues that way.

7) Then you can create a MySQL user that you would use for the replication, you can use these queries here - Update the 'my_db_user' and the 'mypassword' and save those somewhere secuere:

grant usage on *.* to my_db_user@'%' identified by 'mypassword';
grant all privileges on *.* to my_db_user@'%' identified by 'mypassword';
flush privileges;

8) Now you can stop the MariaDB service by running the following command. Do the same for all servers:

service mysql stop

Galera Cluster Configuration - CentOS 7

1) We can now start with the Galera cluster configuration by adding the cluster address, cluster name, node address, etc. We need to do that for all servers:

vim /etc/my.cnf.d/server.cnf

Under the [galera] line section add:

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.201.90,192.168.201.91,192.168.201.92"
binlog_format=row
innodb_autoinc_lock_mode=2

#
# Allow server to accept connections on all interfaces.
#
bind-address=0.0.0.0
#
# Optional setting
wsrep_cluster_name="cluster_name"
wsrep_node_address="192.168.201.90"
wsrep_node_name="server1"
wsrep_sst_method=rsync

2) For completing the cluster configuration on server2 and server3 do not forget to adjust the wsrep_node_address and wsrep_node_name variables. Before doing that you need to stop the MySQL service on both servers!

Server2:

wsrep_node_address='192.168.201.91'
wsrep_node_name='server2'

Server3:

wsrep_node_address='192.168.201.92'
wsrep_node_name='donor'

3) To define the donor on server1 and server2 add the following line

wsrep_sst_donor="donor"

4) Once all the nodes are configured, you can start first cluster server.

NOTE This command should be executed only on server1:

[[email protected] ~]# galera_new_cluster

NOTE if the cluster isn't starting change the following in the /var/lib/mysql/grastate.dat file: safe_to_bootstrap = 1

5) After that, start MySQL on both server2 and serevr3 as well. Run the following command on both the remaining servers:

service mysql start

6) Now check the status of configured Galera cluster on server1. Repeat this command on server2 and server3 to check the status:

mysql

show status like 'wsrep%';

You should see something like:

This is pretty much it! Now you can play a little bit by creating databases on all servers and etc. Here is an example:

Testing

On server1:

mysql

 
create database test1;

show databases;

    +--------------------+
    | Database           |
    +--------------------+
    | test1              |
    | information_schema |
    | mysql              |
    | performance_schema |
    +--------------------+

After that login MySQL in server2 and serevr3 and check the replication process there too:

mysql

show databases;

    +--------------------+
    | Database           |
    +--------------------+
    | test1              |
    | information_schema |
    | mysql              |
    | performance_schema |
    +--------------------+

You can create some tables or import some data to fully test the replication. Here are some examples on how to test the replication

- Stop server2
- Import data to server1
- Start server2
- Check if the data has been imported
- Then stop server1
- Drop the data from server2
- Start server1 and see if the data has been dropped from there as well

If all of that works, then you've completed the setup successfully.

If you want to enable logging you can add the following lines in the config file on the nodes:

wsrep_log_conflicts=ON

The Debug log will be at:

/var/lib/mysql/node-name-here.err

To enable the debug log you can add:

wsrep_debug=ON

Galera Load Balancer (glb)

The Galera Load Balancer provides TCP connection balancing developed with scalability and performance in mind.

Galera Load Balancer Installation

Go to your database loadbalancer and follow the steps here.

To save yourself some headache, install those dependencies first:

yum install libtool.x86_64  autoconf  automake  gcc-c++ nc

Basically you can follow the steps here - NOTE follow only the installation part, we will cover the configuration bellow:

http://galeracluster.com/documentation-webpages/glb.html

If you get any errors, try googling the error - it would probably be a missing dependency or something simple.

Galera Load Balancer Configuration

Before starting the service, open the init file for glb on line 80 and change

     kill $PID > /dev/null

To:

     kill -9 $PID > /dev/null

After that update the glb config file:

    /etc/sysconfig/glbd.cfg

# Galera Load Balancer COnfigurations
LISTEN_ADDR="8010"
DEFAULT_TARGETS="192.168.1.1:3306 192.168.1.2:3306"
OTHER_OPTIONS="--interval 0.2"

If your server1 is larger than server2 you can increase the priority of server one by editing the DEFAULT_TARGETS part.

Also increase the THREADS - It is always a good idea to have a few per CPU core.

If you get an error that the sysconffile is missing create the following symlink:

ln -s /etc/sysconfig/glbd.cfg /etc/sysconfig/glbd

As default the loadbalancing policy is Least Connected - but you could use Round Robin if you think it would be better for your setup. You need to add the following in the OTHER__OPTIONS parameter in the config: