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 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, and direct client connections. 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: Enabling the Galera Cluster yum repositories.

[root@localhost ~]# vi /etc/yum.repos.d/galera.repo

[galera]
name = Galera
baseurl = http://releases.galeracluster.com/galera-3/centos/7/x86_64/
gpgkey = http://releases.galeracluster.com/GPG-KEY-galeracluster.com
gpgcheck = 1

[mysql-wsrep]
name = MySQL-wsrep
baseurl =  http://releases.galeracluster.com/mysql-wsrep-5.7.18-25.12/centos/7/x86_64/
gpgkey = http://releases.galeracluster.com/GPG-KEY-galeracluster.com
gpgcheck = 1

Step 2: Install Galera and the prerequisite packages.

[root@test-dev-mysql01 ~]# yum -y install galera-3 mysql-wsrep-5.7 rsync lsof policycoreutils-python firewalld

Step 3: Enable the Galera service to start at boot, but do not start the service just yet.

[root@localhost]# systemctl enable mysqld

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 my.cnf file and then edit the file to include the below.

[root@localhost]# cp /etc/my.cnf /etc/my.cnf.bak
[root@localhost]# vi /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
binlog_format=ROW
bind-address=0.0.0.0
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=122M
wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so
wsrep_provider_options="gcache.size=300M; gcache.page_size=300M"
wsrep_cluster_name="galera_cluster1"
wsrep_cluster_address="gcomm://10.1.0.11:3306,10.1.0.12:3306,10.1.0.13:3306"
wsrep_sst_method=rsync
server_id=1
wsrep_node_address="10.1.0.11"
wsrep_node_name="mysql01"

[mysql_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Step 8: Create the mysqld.log and give the mysql account the appropriate permissions.

[root@localhost]# touch /var/log/mysqld.log
[root@localhost]# chown mysql:mysql /var/log/mysqld.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 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]# /usr/bin/mysqld_bootstrap

Step 11: After you installed MySQL 5.7 you will need to find the temporary password from /var/log/mysqld.log to login as root.

[root@localhost]# grep 'temporary password' /var/log/mysqld.log
2017-08-31T12:36:15.644109Z 1 [Note] A temporary password is generated for root@localhost: passwordhere

Step 12: 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.

[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 13: Login to MySQL and run the below query to confirm the number of nodes in the cluster.

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

mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+
1 row in set (0.00 sec)

Step 14: Start MySQL on the other member nodes, mysql02 and mysql03. Notice the command to start the MySQL service is different on subsequent member nodes.

[root@localhost]# systemctl start mysqld

Step 15: 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

mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.00 sec)

Verify the Configuration

Step 16: 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  2958 mysql   12u  IPv4  68974      0t0  TCP *:tram (LISTEN)

Step 17: 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  2958 mysql   39u  IPv4  68990      0t0  TCP *:mysql (LISTEN)

Step 18: 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

mysql> create database galera_test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| galera_test        |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

Step 19: Shutdown mysql01 and check the cluster size from one of the member nodes.

mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+
1 row in set (0.00 sec)

Step 20: Bring mysql01 back online and confirm all three nodes are connected.

mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.00 sec)

Step 21: Confirm all three node IPs are listed in wsrep_incoming_addresses. Disconnected nodes are removed from replication automatically.

mysql> 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 22: The below query will confirm which version of MySQL you are running.

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------------------------------+
| Variable_name           | Value                                       |
+-------------------------+---------------------------------------------+
| innodb_version          | 5.7.18                                      |
| protocol_version        | 10                                          |
| slave_type_conversions  |                                             |
| tls_version             | TLSv1,TLSv1.1                               |
| version                 | 5.7.18                                      |
| version_comment         | MySQL Community Server - (GPL), wsrep_25.12 |
| version_compile_machine | x86_64                                      |
| version_compile_os      | Linux                                       |
+-------------------------+---------------------------------------------+
8 rows in set (0.01 sec)

Step 23: The below query will confirm which format is the default on the MySQL Server.

mysql> SHOW VARIABLES LIKE 'innodb_file_format';
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| innodb_file_format | Barracuda |
+--------------------+-----------+
1 row in set (0.00 sec)

Complete cluster shutdown and restart

Step 24: 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:    a403c026-8e57-11e7-9cba-43155149803a
seqno:   -1
safe_to_bootstrap: 1

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

[root@localhost]# /usr/bin/mysqld_bootstrap

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

[root@localhost]# systemctl start mysqld

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

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

mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> 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…

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…

CentOS

How to Clear RAM Memory Cache, Buffer and Swap Space on Linux

Clearing PageCache, dentries and inodes in LinuxLike any other operating system, GNU/Linux has implemented a memory management efficiently and even more than that. But if any process is eating away your memory and you want Read more…