What is MySQL cluster and how to use it?

MariaDB Galera Cluster is a synchronous multi-master cluster for MariaDB, available on Linux only, and only supports the XtraDB/InnoDB storage engines . It is designed to provide high availability and high throughput with low latency, while allowing for near linear scalability. MySQL Cluster is implemented through the NDB or NDBCLUSTER storage engine for MySQL ("NDB" stands for Network Database). MySQL features include: synchronous replication, active-active multi-master topology, read and write to any cluster node, automatic membership control, parallel replication on row level, direct client connections, and native MariaDB look & feel. These features yield several benefits for a DBMS clustering solution, including: no slave lag, no lost transactions, both read and write scalability,  and smaller client latencies.


Steps to configure a MySQL Cluster

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"

Starting MySQL Cluster for the first time

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     |
+--------------------+-------+

Verify the Configuration

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)

Complete cluster shutdown and restart

Step 23: In the event of a total shutdown of a MySQL Cluster nodes will not start automatically. The first step to restarting the cluster is to find which node has the safe_to_bootstrap set to 1 in the grastate.dat file.

[root@localhost]# cat /var/lib/mysql/grastate.dat

# GALERA saved state
version: 2.1
uuid:    41c64140-8c05-11e7-ac47-876b279facfa
seqno:   -1
safe_to_bootstrap: 1

Step 24: On the MySQL node with safe_to_bootstrap set to 1 start MySQL with the below command.

[root@localhost]# galera_new_cluster

Step 25: After the initial node is started start the remaining nodes with the below command. Notice the command to start the mariadb service is different on subsequent member nodes.

[root@localhost]# systemctl start mariadb

Step 26: Login to MySQL and run the below queries again to confirm all three nodes are online.

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

MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.00 sec)+
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       |
+------------------------------+----------------------------------------------------+
Categories: CentOSLinuxMySQL

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts

Application Containers

Docker container management using Rancher

What is container management and why to use it?A container management platform is a solution used to o create cloud-native, distributed applications and package legacy applications that were not originally designed for virtual environments. Container Read more…

CentOS

Install MySQL Galera Cluster on Centos 7

What is MySQL cluster and how to use it?MySQL Galera Cluster is a synchronous multi-master cluster, available on Linux only, and only supports the XtraDB/InnoDB storage engines . It is designed to provide high availability Read more…

Application Containers

Installing Docker on Centos 7

What are Docker containers and how to use them?Docker is a software technology providing containers. Docker provides an additional layer of abstraction and automation of operating-system-level virtualization on Windows and Linux. Docker uses the resource Read more…