MariaDB Galera Cluster Configuration and Validation with Different Types of testings

Configuring MariaDB Cluster on RHEL :-

------------------------------------------

This tutorial describes how to install and configure three MariaDB Galera Cluster nodes on RHEL 8 servers, using the packages from MariaDB repositories.

In this Document Deployed 3 EC2 Machines in AWS [ RHEL 8 ]

mariadb-galera-node1 ===> 172.31.42.167 [ Private Ipaddress ] 

mariadb-galera-node2 ===> 172.31.38.74  [ Private Ipaddress ] 

mariadb-galera-node3 ===> 172.31.35.40  [ Private Ipaddress ]

MariaDB Database Version = 10.5.12

Step -1 Configuring MariaDB Galera Cluster on RHEL :-

Node 1

Host name : mariadb-galera-node1 

IP address: 172.31.42.167

Node 2

Host name : mariadb-galera-node2 

IP address: 172.31.38.74

Node 3

Host name : mariadb-galera-node3 

IP address: 172.31.35.40

Basic Information:-

-----------------------

MariaDB Galera Cluster is a virtually synchronous multi-primary cluster for MariaDB. It is available on Linux only, and only supports the InnoDB storage engine (although there is experimental support for MyISAM and, from MariaDB 10.6, Aria.

Features

• Virtually synchronous replication
• Active-active multi-primary topology
• Read and write to any cluster node
• Automatic membership control, failed nodes drop from the cluster
• Automatic node joining
• True parallel replication, on row level
• Direct client connections, native MariaDB look & feel

Benefits

The above features yield several benefits for a DBMS clustering solution, including:

• No replica lag
• No lost transactions
• Read scalability
• Smaller client latencies

Clustering adds high availability to your database by distributing changes to different servers. In the event that one of the instances fails, others are quickly available to continue serving.

Clusters come in two general configurations, active-passive and active-active. 

In active-passive clusters, all writes are done on a single active server and then copied to one or more passive servers that are poised to take over only in the event of an active server failure. Some active-passive clusters also allow SELECT operations on passive nodes. 

In an active-active cluster, every node is read-write and a change made to one is replicated to all.

MariaDB is an open source relational database system that is fully compatible with the popular MySQL RDBMS system. Galera is a database clustering solution that enables you to set up multi-master clusters using synchronous replication. Galera automatically handles keeping the data on different nodes in sync while allowing you to send read and write queries to any of the nodes in the cluster.

In this guide, you will configure an active-active MariaDB Galera cluster. For demonstration purposes, will configure with 3 Nodes.

Section - I 

-------------------

Step - 1 Pre-requisites

Step - 2 Installing MariaDB Database

Step - 3 Configuring the first second and Third nodes [ From this step will be MariaDB Galera Cluster Setup ]

Section - II 

-------------------

Step - 4 Basic Testing Phase - 1 with MariaDB Galera Cluster

Step - 5 Testing Phase - 2 [ --> shutdown mysql service in node 1 --> And creating the database in node 2 --> Start the mysql service in Node 1 --> Checking the created database in all 3 Nodes ]

Step - 6 Testing Phase - 3 

      [ --> First shutdown the mysql service in Node 1 

        --> Create the new database in Node 2

        --> Stop the Node 3 mysql service 

        --> Stop the Node 2 mysql service

        --> Start the mysql service with bootstrap 

        --> Start the mysql service by simple command in Node 3

        --> Start the mysql service by simple command in Node 1 ]

Step - 7 Testing Phase - 4 

  [ --> Kill the mysql process in Node 2

         -->  Create the new database , table , insert some data in Node 1 

    --> Verify the new database , new table and data in Node 3

    --> Start the Node 2 mysql service 

    --> Verify the new database ,new table and data in Node 2 

    --> Verify all nodes of /var/lib/mysql/grastate.dat ]

Step - 8   All servers are down , how to start bootstrap or how to start MariaDB Galera Cluster ..etc    

Step - 9   Start / Stop / Restart the MariaDB Galera Cluster

Step - 10  Additional information about binlog , read_only and gtid variables

 

-------------------------------------------------------------------

 Step - 1 Prerequisites

-------------------------------------------------------------------

The procedure described in this document requires the following:

(i)   All three nodes have RHEL 8 installed.

(ii)  Open firewall ports:

(iii) SELinux on all nodes is disabled.

(iv)  Dependency pacakges

(i) Pre-checks:

Add below entries in your /etc/hosts file ——> on all server [mariadb-galera-node1,percona- xtradb-2,mariadb-galera-node3]

Server - 1

[root@mariadb-galera-node1 ~]# hostname -I 

172.31.42.167

[root@mariadb-galera-node1 ~]# hostname 

mariadb-galera-node1

[root@mariadb-galera-node1 ~]# cat /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

172.31.42.167 mariadb-galera-node1 mariadb-galera-node1

172.31.38.74  mariadb-galera-node2 mariadb-galera-node2

172.31.35.40  mariadb-galera-node3 mariadb-galera-node3

Server - 2

[root@mariadb-galera-node2 ~]# hostname -I 

172.31.38.74

[root@mariadb-galera-node2 ~]# hostname 

mariadb-galera-node2

[root@mariadb-galera-node2 ~]# cat /etc/hosts

 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

172.31.42.167 mariadb-galera-node1 mariadb-galera-node1

172.31.38.74  mariadb-galera-node2 mariadb-galera-node2

172.31.35.40  mariadb-galera-node3 mariadb-galera-node3

Server - 3

[root@mariadb-galera-node3 ~]# hostname -I 

172.31.35.40

[root@mariadb-galera-node3 ~]# hostname 

mariadb-galera-node3

[root@mariadb-galera-node3 ~]# cat /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

172.31.42.167 mariadb-galera-node1 mariadb-galera-node1

172.31.38.74  mariadb-galera-node2 mariadb-galera-node2

172.31.35.40  mariadb-galera-node3 mariadb-galera-node3

(ii) Open firewall ports:

We need to open below ports on all server [ mariadb-galera-node1 , mariadb-galera-node2 , mariadb-galera-node3]

Galera Ports :-

------------------

MariaDB Default Traffic       — TCP 3306

Galera Cluster Communications — TCP & UDP 4567

Incremental State Transfers   — TCP 4444

State Snapshot Transfers      — TCP 4568

Galera can make use of four ports:

3306 For MariaDB 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 Transfers, or IST, the process by which a missing state is received by other nodes in the cluster.

4444 For all other State Snapshot Transfers, or SST, the mechanism by which a joiner node gets its state and data from a donor node.

Server - 1 :

Creating a SELinux Policy

[root@mariadb-galera-node1 ~]# semanage port -a -t mysqld_port_t -p tcp 3306

[root@mariadb-galera-node1 ~]# semanage port -a -t mysqld_port_t -p tcp 4444

[root@mariadb-galera-node1 ~]# semanage port -a -t mysqld_port_t -p tcp 4567

[root@mariadb-galera-node1 ~]# semanage port -a -t mysqld_port_t -p udp 4567

[root@mariadb-galera-node1 ~]# semanage port -a -t mysqld_port_t -p tcp 4568

Note: You may receive a ValueError when allowing access to some of these ports. This means that the SELinux status of that port has already been set.

In these commands, you are using the SELinux management tool semanage with the -a flag to add specified ports and to ignore the database server.

Next, run the following command on all three servers, which sets the MySQL SELinux domain to permissive mode temporarily.

[root@mariadb-galera-node1 ~]# semanage permissive -a mysqld_t

This command can take a minute to complete and will not display any output.

[root@mariadb-galera-node1 ~]# systemctl enable firewalld

[root@mariadb-galera-node1 ~]# systemctl start firewalld

[root@mariadb-galera-node1 ~]# firewall-cmd --zone=public --add-service=mysql --permanent

success

[root@mariadb-galera-node1 ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent

success

[root@mariadb-galera-node1 ~]# firewall-cmd --zone=public --add-port=4444/tcp --permanent

success

[root@mariadb-galera-node1 ~]# firewall-cmd --zone=public --add-port=4567/tcp --permanent

success

[root@mariadb-galera-node1 ~]# firewall-cmd --zone=public --add-port=4567/udp --permanent

success

[root@mariadb-galera-node1 ~]# firewall-cmd --zone=public --add-port=4568/tcp --permanent

success

Using --zone=public and --add-port= here, firewall-cmd is opening up these ports to public traffic. --permanent ensures that these rules persist.

Reload the firewall to apply the changes:

[root@mariadb-galera-node1 ~]# firewall-cmd --reload

success

[root@mariadb-galera-node1 ~]# firewall-cmd --list-all

public (active)

  target: default

  icmp-block-inversion: no

  interfaces: eth0

  sources:

  services: cockpit dhcpv6-client mysql ssh

  ports: 3306/tcp 4567/tcp 4568/tcp 4444/tcp 4567/udp

  protocols:

  masquerade: no

  forward-ports:

  source-ports:

  icmp-blocks:

  rich rules:

[root@mariadb-galera-node1 ~]#

Run all these commands in node 2 and node 3 also.

(iii) SELinux on all nodes is disabled.

Disable SELinux ===> On all servers [mariadb-galera-node1 , mariadb-galera-node2 , mariadb-galera-node3] 

setenforce 0

This will put the SELinux in permissive mode for the current session but we need to disable it permanently.

Edit /etc/sysconfig/selinux file set selinux variable as disabled on all servers[mariadb-galera-node1 , mariadb-galera-node2 , mariadb-galera-node3] :

vi /etc/sysconfig/selinux 

SELINUX=disabled

All servers should be accessible to each other, In order to check the accessibility ping each server.

Or

$ setenforce 0

$ sed -i 's/^SELINUX=.*/SELINUX=disabled/g' /etc/sysconfig/selinux 

Server - 1 :-

[root@mariadb-galera-node1 ~]# getenforce 

Enforcing

[root@mariadb-galera-node1 ~]# vi /etc/sysconfig/selinux 

[root@mariadb-galera-node1 ~]# getenforce

Enforcing

[root@mariadb-galera-node1 ~]# reboot

[root@mariadb-galera-node1 ~]# getenforce 

Disabled

Server - 2 :-

[root@mariadb-galera-node2 ~]# getenforce 

Enforcing

[root@mariadb-galera-node2 ~]# vi /etc/sysconfig/selinux 

[root@mariadb-galera-node2 ~]# reboot

[root@mariadb-galera-node2 ~]# getenforce 

Disabled

Server - 3 :-

[root@mariadb-galera-node3 ~]# getenforce 

Enforcing

[root@mariadb-galera-node3 ~]# vi /etc/sysconfig/selinux 

[root@mariadb-galera-node3 ~]# reboot

[root@mariadb-galera-node3 ~]# getenforce 

Disabled

 

Permanently set hostname in EC2 Machine :- https://aws.amazon.com/premiumsupport/knowledge-center/linux-static-hostname/

(iv) Dependency pacakges

Instll below prereq RPM ==> On all servers [mariadb-galera-node1,mariadb-galera-node2,mariadb-galera-node3] If required

epel-release libev

socat firewalld

Server - 1 :-

[root@mariadb-galera-node1 ~]# rpm -qa | grep -i epel-release 

[root@mariadb-galera-node1 ~]# rpm -qa | grep -i libev 

libevent-2.1.8-5.el8.x86_64

[root@mariadb-galera-node1 ~]# rpm -qa | grep -i socat 

[root@mariadb-galera-node1 ~]# rpm -qa | grep -i firewalld 

firewalld-filesystem-0.8.2-7.el8_4.noarch

firewalld-0.8.2-7.el8_4.noarch

[root@mariadb-galera-node1 ~]# sudo dnf install https://dl.fedoraproject.org/pub/epel/epel-release-latest-8.noarch.rpm -y

[root@mariadb-galera-node1 ~]# yum install libev 

[root@mariadb-galera-node1 ~]# yum install socat 

[root@mariadb-galera-node1 ~]# rpm -qa | grep -i epel-release 

epel-release-8-11.el8.noarch

 

[root@mariadb-galera-node1 ~]# rpm -qa | grep -i libev 

libevent-2.1.8-5.el8.x86_64

libev-4.24-6.el8.x86_64

[root@mariadb-galera-node1 ~]# rpm -qa | grep -i socat 

socat-1.7.3.3-2.el8.x86_64

[root@mariadb-galera-node1 ~]# rpm -qa | grep -i firewalld 

firewalld-filesystem-0.8.2-7.el8_4.noarch

firewalld-0.8.2-7.el8_4.noarch

Server - 2 :-

[root@mariadb-galera-node2 ~]# rpm -qa | grep -i epel-release 

[root@mariadb-galera-node2 ~]# rpm -qa | grep -i libev 

libevent-2.1.8-5.el8.x86_64

[root@mariadb-galera-node2 ~]# rpm -qa | grep -i socat 

[root@mariadb-galera-node2 ~]# rpm -qa | grep -i firewalld 

firewalld-filesystem-0.8.2-7.el8_4.noarch

firewalld-0.8.2-7.el8_4.noarch

[root@mariadb-galera-node2 ~]# sudo dnf install https://dl.fedoraproject.org/pub/epel/epel-release-latest-8.noarch.rpm -y

[root@mariadb-galera-node2 ~]# yum install libev 

[root@mariadb-galera-node2 ~]# yum install socat 

[root@mariadb-galera-node2 ~]# rpm -qa | grep -i epel-release 

epel-release-8-11.el8.noarch

 

[root@mariadb-galera-node2 ~]# rpm -qa | grep -i libev 

libevent-2.1.8-5.el8.x86_64

libev-4.24-6.el8.x86_64

[root@mariadb-galera-node2 ~]# rpm -qa | grep -i socat 

socat-1.7.3.3-2.el8.x86_64

[root@mariadb-galera-node2 ~]# rpm -qa | grep -i firewalld 

firewalld-filesystem-0.8.2-7.el8_4.noarch

firewalld-0.8.2-7.el8_4.noarch

Server - 3 :-

[root@mariadb-galera-node3 ~]# rpm -qa | grep -i epel-release 

[root@mariadb-galera-node3 ~]# rpm -qa | grep -i libev 

libevent-2.1.8-5.el8.x86_64

[root@mariadb-galera-node3 ~]# rpm -qa | grep -i socat 

[root@mariadb-galera-node3 ~]# rpm -qa | grep -i firewalld 

firewalld-filesystem-0.8.2-7.el8_4.noarch

firewalld-0.8.2-7.el8_4.noarch

[root@mariadb-galera-node3 ~]# sudo dnf install https://dl.fedoraproject.org/pub/epel/epel-release-latest-8.noarch.rpm -y

[root@mariadb-galera-node3 ~]# yum install libev 

[root@mariadb-galera-node3 ~]# yum install socat 

[root@mariadb-galera-node3 ~]# rpm -qa | grep -i epel-release 

epel-release-8-11.el8.noarch

 

[root@mariadb-galera-node3 ~]# rpm -qa | grep -i libev 

libevent-2.1.8-5.el8.x86_64

libev-4.24-6.el8.x86_64

[root@mariadb-galera-node3 ~]# rpm -qa | grep -i socat 

socat-1.7.3.3-2.el8.x86_64

[root@mariadb-galera-node3 ~]# rpm -qa | grep -i firewalld 

firewalld-filesystem-0.8.2-7.el8_4.noarch

firewalld-0.8.2-7.el8_4.noarch

-------------------------------------------------------------------              Step - 2 Installing MariaDB Database 

-------------------------------------------------------------------

Installing MariaDB Database :-

Install MariaDB Database on all three nodes on Red Hat Enterprise Linux.

Reference Link is :- 

https://downloads.mariadb.org/mariadb/repositories/#distro=RedHat&distro_release=rhel8-amd64--rhel8&mirror=digitalocean-nyc&version=10.5

(i) Installing from MariaDB Repository on all Servers [mariadb-galera-node1,percona-xtradb- 2,mariadb-galera-node3]

i.e ,

Configure repo by using below methods in all 3 Servers.

First, you’ll add the MariaDB repository key by creating a repository file with a text editor. This tutorial will use vi:

[root@mariadb-galera-node1 yum.repos.d]# pwd

/etc/yum.repos.d

[root@mariadb-galera-node1 yum.repos.d]# ls -ltr

-rw-r--r--  1 root root 1516 Jun  8 00:29 epel-testing.repo

-rw-r--r--  1 root root 1579 Jun  8 00:29 epel-testing-modular.repo

-rw-r--r--  1 root root 1417 Jun  8 00:29 epel.repo

-rw-r--r--  1 root root 1562 Jun  8 00:29 epel-playground.repo

-rw-r--r--  1 root root 1480 Jun  8 00:29 epel-modular.repo

-rw-r--r--. 1 root root 4782 Aug 18 11:07 redhat-rhui-beta.repo.disabled

-rw-r--r--. 1 root root  482 Aug 20 15:49 redhat-rhui-client-config.repo

-rw-r--r--. 1 root root 5768 Aug 20 16:17 redhat-rhui.repo

[root@mariadb-galera-node1 yum.repos.d]# vi mariadb.repo

[root@mariadb-galera-node1 yum.repos.d]# cat mariadb.repo

# MariaDB 10.5 RedHat repository list - created 2021-09-08 07:26 UTC

# http://downloads.mariadb.org/mariadb/repositories/

[mariadb]

name = MariaDB

baseurl = http://yum.mariadb.org/10.5/rhel8-amd64

module_hotfixes=1

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

gpgcheck=1

[root@mariadb-galera-node1 yum.repos.d]# cd

[root@mariadb-galera-node1 ~]# yum repolist

[root@mariadb-galera-node1 ~]# yum repolist all

Once you have created the repository file, enable it with the following command:

[root@mariadb-galera-node1 ~]# sudo yum makecache --disablerepo='*' --enablerepo='mariadb'

The makecache command caches the repository metadata so that the package manager can install MariaDB, with --disablerepo and --enablerepo targeting the command to the mariadb repo file that you just created.

Once you have enabled the repository on your first server, repeat for your second and third servers.

Now that you have successfully added the package repository on all three of your servers, you’re ready to install MariaDB in the next section.

(ii) Installing MariaDB on All Servers :-

----------------------------------------

In this step, you will install the actual MariaDB packages on your three servers.

Beginning with version 10.5, the MariaDB Server and MariaDB Galera Server packages are combined, so installing MariaDB-server will automatically install Galera and several dependencies:

In Node - 1 :-

------------------

[root@mariadb-galera-node1 ~]# sudo dnf install MariaDB-server

[root@mariadb-galera-node1 ~]# rpm -qa | grep -i mariadb

MariaDB-common-10.5.12-1.el8.x86_64

MariaDB-shared-10.5.12-1.el8.x86_64

MariaDB-server-10.5.12-1.el8.x86_64

MariaDB-client-10.5.12-1.el8.x86_64

[root@mariadb-galera-node1 ~]# rpm -qa | grep -i galera

galera-4-26.4.9-1.el8.x86_64

[root@mariadb-galera-node1 ~]#

[root@mariadb-galera-node1 ~]# yum info mariadb

Updating Subscription Management repositories.

Unable to read consumer identity

This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.Last metadata expiration check: 2:24:51 ago on Wed 08 Sep 2021 07:31:58 AM UTC.

Available Packages

Name         : MariaDB

Version      : 10.5.12

Release      : 1.el8

Architecture : src

Size         : 91 M

Source       : None

Repository   : mariadb

Summary      : MariaDB: a very fast and robust SQL database server

License      : GPLv2

Description  : MariaDB: a very fast and robust SQL database server

             :

             : It is GPL v2 licensed, which means you can use the it free of charge under the

             : conditions of the GNU General Public License Version 2 (http://www.gnu.org/licenses/).

             :

             : MariaDB documentation can be found at https://mariadb.com/kb

             : MariaDB bug reports should be submitted through https://jira.mariadb.org

Name         : mariadb

Epoch        : 3

Version      : 10.3.28

Release      : 1.module+el8.3.0+10472+7adc332a

Architecture : x86_64

Size         : 6.0 M

Source       : mariadb-10.3.28-1.module+el8.3.0+10472+7adc332a.src.rpm

Repository   : rhel-8-appstream-rhui-rpms

Summary      : A very fast and robust SQL database server

URL          : http://mariadb.org

License      : GPLv2 with exceptions and LGPLv2 and BSD

Description  : MariaDB is a community developed branch of MySQL - a multi-user, multi-threaded

             : SQL database server. It is a client/server implementation consisting of

             : a server daemon (mysqld) and many different client programs and libraries.

             : The base package contains the standard MariaDB/MySQL client programs and

             : generic MySQL files.

[root@mariadb-galera-node1 ~]# yum info galera

Updating Subscription Management repositories.

Unable to read consumer identity

This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.Last metadata expiration check: 2:27:06 ago on Wed 08 Sep 2021 07:31:58 AM UTC.

Available Packages

Name         : galera

Version      : 25.3.32

Release      : 1.module+el8.3.0+10472+7adc332a

Architecture : x86_64

Size         : 1.3 M

Source       : galera-25.3.32-1.module+el8.3.0+10472+7adc332a.src.rpm

Repository   : rhel-8-appstream-rhui-rpms

Summary      : Synchronous multi-master wsrep provider (replication engine)

URL          : http://galeracluster.com/

License      : GPLv2

Description  : Galera is a fast synchronous multi-master wsrep provider (replication engine)

             : for transactional databases and similar applications. For more information

             : about wsrep API see http://launchpad.net/wsrep. For a description of Galera

             : replication engine see http://www.codership.com.

[root@mariadb-galera-node1 ~]#

Please follow the above steps in all other 2 nodes...

When the installation is complete on all 3 Nodes, start the mariadb service by running:

[root@mariadb-galera-node1 ~]# sudo systemctl start mariadb

[root@mariadb-galera-node1 ~]# sudo systemctl status mariadb

● mariadb.service - MariaDB 10.5.12 database server

   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)

  Drop-In: /etc/systemd/system/mariadb.service.d

           └─migrated-from-my.cnf-settings.conf

   Active: active (running) since Wed 2021-09-08 07:43:23 UTC; 11s ago

Start the MariaDB service in all other 2 nodes also..

[root@mariadb-galera-node2 ~]# sudo systemctl start mariadb

[root@mariadb-galera-node2 ~]# sudo systemctl status mariadb

● mariadb.service - MariaDB 10.5.12 database server

   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)

  Drop-In: /etc/systemd/system/mariadb.service.d

           └─migrated-from-my.cnf-settings.conf

   Active: active (running) since Wed 2021-09-08 07:43:25 UTC; 1min 17s ago

[root@mariadb-galera-node3 ~]# sudo systemctl start mariadb

[root@mariadb-galera-node3 ~]# sudo systemctl status mariadb

● mariadb.service - MariaDB 10.5.12 database server

   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)

  Drop-In: /etc/systemd/system/mariadb.service.d

           └─migrated-from-my.cnf-settings.conf

   Active: active (running) since Wed 2021-09-08 07:43:27 UTC; 1min 42s ago

(iii) Enable the mariadb service to be automatically started on boot by executing:

[root@mariadb-galera-node1 ~]# sudo systemctl enable mariadb

Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.

[root@mariadb-galera-node1 ~]#

[root@mariadb-galera-node2 ~]# sudo systemctl enable mariadb

Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.

[root@mariadb-galera-node2 ~]#

[root@mariadb-galera-node3 ~]# sudo systemctl enable mariadb

Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.

[root@mariadb-galera-node3 ~]#

(iii) From MariaDB version 10.4 onwards, the root MariaDB user does not have a password by default. To set a password for the root user, start by logging into MariaDB:

Configure MariaDB for first use by running the command below, then run through the configuration appropriately.

[root@mariadb-galera-node1 ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and

haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none):<Just Press Enter > 

OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] n

 ... skipping.

You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] y

New password:

Re-enter new password:

Password updated successfully!

Reloading privilege tables..

 ... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them.  This is intended only for testing, and to make the installation go a bit smoother.  You should remove them before moving into a

production environment.

Remove anonymous users? [Y/n] y

 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n

 ... skipping.

By default, MariaDB comes with a database named 'test' that anyone can access.  This is also intended only for testing, and should be removed

before moving into a production environment.

Remove test database and access to it? [Y/n] y

 - Dropping test database...

 ... Success!

 - Removing privileges on test database...

 ... Success!

Reloading the privilege tables will ensure that all changes made so far will take effect immediately.

Reload privilege tables now? [Y/n] y

 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB installation should now be secure.

Thanks for using MariaDB!

[root@mariadb-galera-node1 ~]#

Run this command in other 2 nodes also..

(iv) Test connection to each of the databases by running the command below:

You will be prompted to input a password that you had setup in the previous step.

[root@mariadb-galera-node1 ~]# mysql -u root -p

Enter password: root

Welcome to the MariaDB monitor.  Commands end with ; or \g. Your MariaDB connection id is 10.Server version: 10.5.12-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> status;

--------------

mysql  Ver 15.1 Distrib 10.5.12-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          10

Current database:

Current user:           root@localhost

SSL:                    Not in use

Current pager:          stdout

Using outfile:          ''

Using delimiter:        ;

Server:                 MariaDB

Server version:         10.5.12-MariaDB MariaDB Server

Protocol version:       10

Connection:             Localhost via UNIX socket

Server characterset:    latin1

Db     characterset:    latin1

Client characterset:    utf8

Conn.  characterset:    utf8

UNIX socket:            /var/lib/mysql/mysql.sock

Uptime:                 1 hour 44 min 46 sec

Threads: 1  Questions: 19  Slow queries: 0  Opens: 21  Open tables: 14  Queries per second avg: 0.003

--------------

MariaDB [(none)]> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

+--------------------+

3 rows in set (0.000 sec)

MariaDB [(none)]> show variables like '%data%';

+------------------------------------------+------------------------+

| Variable_name                               | Value               |

+------------------------------------------+------------------------+

| binlog_row_metadata                         | NO_LOG              |

| character_set_database                      | latin1              |

| collation_database                          | latin1_swedish_ci   |

| datadir                                     | /var/lib/mysql/     |

| innodb_background_scrub_data_check_interval | 0                   |

| innodb_background_scrub_data_compressed     | OFF                 |

| innodb_background_scrub_data_interval       | 0                   |

| innodb_background_scrub_data_uncompressed   | OFF                 |

| innodb_data_file_path                       | ibdata1:12M:autoextend |

| innodb_data_home_dir                        |                     |

| innodb_immediate_scrub_data_uncompressed    | OFF                 |

| innodb_stats_on_metadata                    | OFF                 |

| innodb_temp_data_file_path                  | ibtmp1:12M:autoextend  |

| max_length_for_sort_data                    | 1024                   |

| metadata_locks_cache_size                   | 1024                   |

| metadata_locks_hash_instances               | 8                      |

| myisam_data_pointer_size                    | 6                      |

| performance_schema_max_metadata_locks       | -1                     |

| skip_show_database                          | OFF                    |

| updatable_views_with_limit                  | YES                    |

| wsrep_data_home_dir                         | /var/lib/mysql/        |

| wsrep_load_data_splitting                   | OFF                    |

+---------------------------------------------+------------------------+

22 rows in set (0.001 sec)

MariaDB [(none)]> show variables like 'socket%';

+---------------+---------------------------+

| Variable_name | Value                     |

+---------------+---------------------------+

| socket        | /var/lib/mysql/mysql.sock |

+---------------+---------------------------+

1 row in set (0.001 sec)

MariaDB [(none)]> show variables like 'pid%';

+---------------+-----------------------------------------+

| Variable_name | Value                                   |

+---------------+-----------------------------------------+

| pid_file      | /var/lib/mysql/mariadb-galera-node1.pid |

+---------------+-----------------------------------------+

1 row in set (0.001 sec)

MariaDB [(none)]> show variables like 'char%';

+--------------------------+----------------------------+

| Variable_name            | Value                      |

+--------------------------+----------------------------+

| character_set_client     | utf8                       |

| character_set_connection | utf8                       |

| character_set_database   | latin1                     |

| character_set_filesystem | binary                     |

| character_set_results    | utf8                       |

| character_set_server     | latin1                     |

| character_set_system     | utf8                       |

| character_sets_dir       | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

8 rows in set (0.001 sec)

MariaDB [(none)]> show variables like 'collat%';

+----------------------+-------------------+

| Variable_name        | Value             |

+----------------------+-------------------+

| collation_connection | utf8_general_ci   |

| collation_database   | latin1_swedish_ci |

| collation_server     | latin1_swedish_ci |

+----------------------+-------------------+

3 rows in set (0.001 sec)

MariaDB [(none)]> show variables like 'server_id';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id     | 1     |

+---------------+-------+

1 row in set (0.001 sec)

MariaDB [(none)]> show variables like 'log_bin%';

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| log_bin                         | OFF   |

| log_bin_basename                |       |

| log_bin_compress                | OFF   |

| log_bin_compress_min_len        | 256   |

| log_bin_index                   |       |

| log_bin_trust_function_creators | OFF   |

+---------------------------------+-------+

6 rows in set (0.001 sec)

MariaDB [(none)]> show variables like 'read_only';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| read_only     | OFF   |

+---------------+-------+

1 row in set (0.001 sec)

MariaDB [(none)]> show variables like 'lower_case%';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| lower_case_file_system | OFF   |

| lower_case_table_names | 0     |

+------------------------+-------+

2 rows in set (0.001 sec)

MariaDB [(none)]> show variables like 'wsrep%';

+---------------------------------+-----------------------------------------+

| Variable_name                   | Value                                   |

+---------------------------------+-----------------------------------------+

| wsrep_osu_method                | TOI                                     |

| wsrep_sr_store                  | table                                   |

| wsrep_auto_increment_control    | ON                                      |

| wsrep_causal_reads              | OFF                                     |

| wsrep_certification_rules       | strict                                  |

| wsrep_certify_nonpk             | ON                                      |

| wsrep_cluster_address           |                                         |

| wsrep_cluster_name              | my_wsrep_cluster                        |

| wsrep_convert_lock_to_trx       | OFF                                     |

| wsrep_data_home_dir             | /var/lib/mysql/                         |

| wsrep_dbug_option               |                                         |

| wsrep_debug                     | NONE                                    |

| wsrep_desync                    | OFF                                     |

| wsrep_dirty_reads               | OFF                                     |

| wsrep_drupal_282555_workaround  | OFF                                     |

| wsrep_forced_binlog_format      | NONE                                    |

| wsrep_gtid_domain_id            | 0                                       |

| wsrep_gtid_mode                 | OFF                                     |

| wsrep_gtid_seq_no               | 0                                       |

| wsrep_ignore_apply_errors       | 7                                       |

| wsrep_load_data_splitting       | OFF                                     |

| wsrep_log_conflicts             | OFF                                     |

| wsrep_max_ws_rows               | 0                                       |

| wsrep_max_ws_size               | 2147483647                              |

| wsrep_mysql_replication_bundle  | 0                                       |

| wsrep_node_address              |                                         |

| wsrep_node_incoming_address     | AUTO                                    |

| wsrep_node_name                 | mariadb-galera-node1                    |

| wsrep_notify_cmd                |                                         |

| wsrep_on                        | OFF                                     |

| wsrep_patch_version             | wsrep_26.22                             |

| wsrep_provider                  | none                                    |

| wsrep_provider_options          |                                         |

| wsrep_recover                   | OFF                                     |

| wsrep_reject_queries            | NONE                                    |

| wsrep_replicate_myisam          | OFF                                     |

| wsrep_restart_slave             | OFF                                     |

| wsrep_retry_autocommit          | 1                                       |

| wsrep_slave_fk_checks           | ON                                      |

| wsrep_slave_uk_checks           | OFF                                     |

| wsrep_slave_threads             | 1                                       |

| wsrep_sst_auth                  |                                         |

| wsrep_sst_donor                 |                                         |

| wsrep_sst_donor_rejects_queries | OFF                                     |

| wsrep_sst_method                | rsync                                   |

| wsrep_sst_receive_address       | AUTO                                    |

| wsrep_start_position            | 00000000-0000-0000-0000-000000000000:-1 |

| wsrep_strict_ddl                | OFF                                     |

| wsrep_sync_wait                 | 0                                       |

| wsrep_trx_fragment_size         | 0                                       |

| wsrep_trx_fragment_unit         | bytes                                   |

+---------------------------------+-----------------------------------------+

51 rows in set (0.001 sec)

MariaDB [(none)]> show global variables like 'wsrep%';

+---------------------------------+-----------------------------------------+

| Variable_name                   | Value                                   |

+---------------------------------+-----------------------------------------+

| wsrep_osu_method                | TOI                                     |

| wsrep_sr_store                  | table                                   |

| wsrep_auto_increment_control    | ON                                      |

| wsrep_causal_reads              | OFF                                     |

| wsrep_certification_rules       | strict                                  |

| wsrep_certify_nonpk             | ON                                      |

| wsrep_cluster_address           |                                         |

| wsrep_cluster_name              | my_wsrep_cluster                        |

| wsrep_convert_lock_to_trx       | OFF                                     |

| wsrep_data_home_dir             | /var/lib/mysql/                         |

| wsrep_dbug_option               |                                         |

| wsrep_debug                     | NONE                                    |

| wsrep_desync                    | OFF                                     |

| wsrep_dirty_reads               | OFF                                     |

| wsrep_drupal_282555_workaround  | OFF                                     |

| wsrep_forced_binlog_format      | NONE                                    |

| wsrep_gtid_domain_id            | 0                                       |

| wsrep_gtid_mode                 | OFF                                     |

| wsrep_ignore_apply_errors       | 7                                       |

| wsrep_load_data_splitting       | OFF                                     |

| wsrep_log_conflicts             | OFF                                     |

| wsrep_max_ws_rows               | 0                                       |

| wsrep_max_ws_size               | 2147483647                              |

| wsrep_mysql_replication_bundle  | 0                                       |

| wsrep_node_address              |                                         |

| wsrep_node_incoming_address     | AUTO                                    |

| wsrep_node_name                 | mariadb-galera-node1                    |

| wsrep_notify_cmd                |                                         |

| wsrep_on                        | OFF                                     |

| wsrep_patch_version             | wsrep_26.22                             |

| wsrep_provider                  | none                                    |

| wsrep_provider_options          |                                         |

| wsrep_recover                   | OFF                                     |

| wsrep_reject_queries            | NONE                                    |

| wsrep_replicate_myisam          | OFF                                     |

| wsrep_restart_slave             | OFF                                     |

| wsrep_retry_autocommit          | 1                                       |

| wsrep_slave_fk_checks           | ON                                      |

| wsrep_slave_uk_checks           | OFF                                     |

| wsrep_slave_threads             | 1                                       |

| wsrep_sst_auth                  |                                         |

| wsrep_sst_donor                 |                                         |

| wsrep_sst_donor_rejects_queries | OFF                                     |

| wsrep_sst_method                | rsync                                   |

| wsrep_sst_receive_address       | AUTO                                    |

| wsrep_start_position            | 00000000-0000-0000-0000-000000000000:-1 |

| wsrep_strict_ddl                | OFF                                     |

| wsrep_sync_wait                 | 0                                       |

| wsrep_trx_fragment_size         | 0                                       |

| wsrep_trx_fragment_unit         | bytes                                   |

+---------------------------------+-----------------------------------------+

50 rows in set (0.001 sec)

MariaDB [(none)]> show global variables like 'innodb_file_per_table';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| innodb_file_per_table | ON    |

+-----------------------+-------+

1 row in set (0.001 sec)

---------------------------------------------------------------------Step - 3 Configuring the first second and Third nodes [ From this step will be MariaDB Galera Cluster Setup ]

---------------------------------------------------------------------

(i) Base on above outputs by default values of char and Collat values we are going to change to utf8mb4 along with galera parameters .

cd /etc

[root@mariadb-galera-node1 etc]# mv my.cnf /tmp

[root@mariadb-galera-node2 etc]# mv my.cnf /tmp

[root@mariadb-galera-node3 etc]# mv my.cnf /tmp

[root@mariadb-galera-node1 ~]# service mysql stop

Redirecting to /bin/systemctl stop mysql.service

[root@mariadb-galera-node1 ~]# vi /etc/my.cnf

[root@mariadb-galera-node1 ~]# cat /etc/my.cnf

[client]

port = 3306

socket = /var/lib/mysql/mysql.sock

[mysqld]

user = mysql

server-id = 1

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error = /var/log/mysqld.log

pid-file = /var/lib/mysql/mysqld.pid

bind-address = 0.0.0.0

character_set_server = utf8mb4

collation_server = utf8mb4_unicode_520_ci

query_cache_type = 0

query_cache_size = 0

#lower_case_table_names = 1

#sync_binlog = 1

innodb_log_file_size = 512M

innodb_buffer_pool_size = 256M

default_storage_engine = InnoDB

innodb_flush_log_at_trx_commit = 0

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Galera Provider Configuration

[galera]

wsrep_on = ON

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

# Galera Cluster Configuration

wsrep_cluster_name    = 'MariaDB Galera Cluster'

wsrep_cluster_address = "gcomm://172.31.42.167,172.31.38.74,172.31.35.40"

# Galera Synchronization Configuration

wsrep_sst_method = rsync

# Galera Node Configuration

wsrep_node_address = "172.31.42.167"

wsrep_node_name = 'mariadb-galera-node1'

# In order for Galera to work correctly binlog format should be ROW

binlog_format = ROW

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera

innodb_autoinc_lock_mode = 2

# SST method

wsrep_sst_method = rsync

# Galera Other Options

wsrep_provider_options = "gcache.size=300M;gcache.page_size=300M"

wsrep_slave_threads = 4

[root@mariadb-galera-node1 ~]#

(ii) Configure in 2nd Node :-

[root@mariadb-galera-node2 ~]# service mysql stop

Redirecting to /bin/systemctl stop mysql.service

[root@mariadb-galera-node2 ~]# hostname -I

172.31.38.74

[root@mariadb-galera-node2 ~]# vi /etc/my.cnf

[root@mariadb-galera-node2 ~]# cat /etc/my.cnf

[client]

port = 3306

socket = /var/lib/mysql/mysql.sock

[mysqld]

user = mysql

server-id = 2

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error = /var/log/mysqld.log

pid-file = /var/lib/mysql/mysqld.pid

bind-address = 0.0.0.0

character_set_server = utf8mb4

collation_server = utf8mb4_unicode_520_ci

query_cache_type = 0

query_cache_size = 0

#lower_case_table_names = 1

#sync_binlog = 1

innodb_log_file_size = 512M

innodb_buffer_pool_size = 256M

default_storage_engine = InnoDB

innodb_flush_log_at_trx_commit = 0

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Galera Provider Configuration

[galera]

wsrep_on = ON

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

# Galera Cluster Configuration

wsrep_cluster_name    = 'MariaDB Galera Cluster'

wsrep_cluster_address = "gcomm://172.31.42.167,172.31.38.74,172.31.35.40"

# Galera Synchronization Configuration

wsrep_sst_method = rsync

# Galera Node Configuration

wsrep_node_address = "172.31.38.74"

wsrep_node_name = 'mariadb-galera-node2'

# In order for Galera to work correctly binlog format should be ROW

binlog_format = ROW

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera

innodb_autoinc_lock_mode = 2

# SST method

wsrep_sst_method = rsync

# Galera Other Options

wsrep_provider_options = "gcache.size=300M;gcache.page_size=300M"

wsrep_slave_threads = 4

[root@mariadb-galera-node2 ~]#

(iii) Configure in 3rd Node :-

[root@mariadb-galera-node3 ~]# service mysql stop

Redirecting to /bin/systemctl stop mysql.service

[root@mariadb-galera-node3 ~]# vi /etc/my.cnf

[root@mariadb-galera-node3 ~]# cat /etc/my.cnf

[client]

port = 3306

socket = /var/lib/mysql/mysql.sock

[mysqld]

user = mysql

server-id = 3

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error = /var/log/mysqld.log

pid-file = /var/lib/mysql/mysqld.pid

bind-address = 0.0.0.0

character_set_server = utf8mb4

collation_server = utf8mb4_unicode_520_ci

query_cache_type = 0

query_cache_size = 0

#lower_case_table_names = 1

#sync_binlog = 1

innodb_log_file_size = 512M

innodb_buffer_pool_size = 256M

default_storage_engine = InnoDB

innodb_flush_log_at_trx_commit = 0

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Galera Provider Configuration

[galera]

wsrep_on = ON

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

# Galera Cluster Configuration

wsrep_cluster_name    = 'MariaDB Galera Cluster'

wsrep_cluster_address = "gcomm://172.31.42.167,172.31.38.74,172.31.35.40"

# Galera Synchronization Configuration

wsrep_sst_method = rsync

# Galera Node Configuration

wsrep_node_address = "172.31.35.40"

wsrep_node_name = 'mariadb-galera-node3'

# In order for Galera to work correctly binlog format should be ROW

binlog_format = ROW

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera

innodb_autoinc_lock_mode = 2

# SST method

wsrep_sst_method = rsync

# Galera Other Options

wsrep_provider_options = "gcache.size=300M;gcache.page_size=300M"

wsrep_slave_threads = 4

[root@mariadb-galera-node3 ~]#

With Galera configured on all of your nodes, you’re almost ready to bring up the cluster. 

(iv) Start the MariaDB Galera Cluster :-

Now ,stop the database server on all the nodes so that you will be able to bootstrap the database cluster with shared SELinux policies.

[root@mariadb-galera-node1 ~]# service mysql stop

Redirecting to /bin/systemctl stop mysql.service

[root@mariadb-galera-node2 ~]# service mysql stop

Redirecting to /bin/systemctl stop mysql.service

[root@mariadb-galera-node3 ~]# service mysql stop

Redirecting to /bin/systemctl stop mysql.service

Now, bootstrap the cluster to generate inter-node communication events that will be added to the SELinux policy. On the first node, bootstrap the cluster by executing:

Bring Up the First Node :-

To bring up the first node, you’ll need to use a special startup script. The way you’ve configured your cluster, each node that comes online tries to connect to at least one other node specified in its my.cnf file to get its initial state. Without using the galera_new_cluster script that allows systemd to pass the --wsrep-new-cluster parameter, a normal systemctl start mariadb would fail because there are no nodes running for the first node to connect with.

i.e 

--> Bootstrap Database & Galera on Seed Node [ Here Node - 1 ]

MariaDB —   galera_new_cluster

[root@mariadb-galera-node1 ~]# sudo galera_new_cluster

[root@mariadb-galera-node1 ~]#

--> Start Database & Galera Normally on Additional Nodes [ Node 2 and Node 3 ]

systemctl start mariadb

[root@mariadb-galera-node2 ~]# systemctl start mariadb

[root@mariadb-galera-node2 ~]#

[root@mariadb-galera-node3 ~]# systemctl start mariadb

[root@mariadb-galera-node3 ~]#

Detailed Log Information on Node 1 while starting the galera Cluster :-

---------------------------------------------------------------------

2021-09-08 10:57:42 0 [Note] WSREP: Loading provider /usr/lib64/galera-4/libgalera_smm.so initial position: 00000000-0000-0000-0000-000000000000:-1

2021-09-08 10:57:42 0 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib64/galera-4/libgalera_smm.so'

2021-09-08 10:57:42 0 [Note] WSREP: wsrep_load(): Galera 26.4.9(r819f29cb) by Codership Oy <info@codership.com> loaded successfully.

2021-09-08 10:57:42 0 [Note] WSREP: CRC-32C: using 64-bit x86 acceleration.

2021-09-08 10:57:42 0 [Warning] WSREP: Could not open state file for reading: '/var/lib/mysql//grastate.dat'

2021-09-08 10:57:42 0 [Note] WSREP: Found saved state: 00000000-0000-0000-0000-000000000000:-1, safe_to_bootstrap: 1

2021-09-08 10:57:42 0 [Note] WSREP: GCache DEBUG: opened preamble:

Version: 0

UUID: 00000000-0000-0000-0000-000000000000

Seqno: -1 - -1

Offset: -1

Synced: 0

2021-09-08 10:57:42 0 [Note] WSREP: Skipped GCache ring buffer recovery: could not determine history UUID.

2021-09-08 10:57:42 0 [Note] WSREP: Passing config to GCS: base_dir = /var/lib/mysql/; base_host = 172.31.42.167; base_port = 4567; cert.log_conflicts = no; cert.optimistic_pa = yes; debug = no; evs.auto_evict = 0; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.join_retrans_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 2; evs.view_forget_timeout = PT24H; gcache.dir = /var/lib/mysql/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = galera.cache; gcache.page_size = 300M; gcache.recover = yes; gcache.size = 300M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 16; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announce_timeout = PT3S; pc.checksum =

2021-09-08 10:57:42 0 [Note] WSREP: Start replication

2021-09-08 10:57:42 0 [Note] WSREP: Connecting with bootstrap option: 1

2021-09-08 10:57:42 0 [Note] WSREP: Setting GCS initial position to 00000000-0000-0000-0000-000000000000:-1

2021-09-08 10:57:42 0 [Note] WSREP: protonet asio version 0

2021-09-08 10:57:42 0 [Note] WSREP: Using CRC-32C for message checksums.

2021-09-08 10:57:42 0 [Note] WSREP: backend: asio

2021-09-08 10:57:42 0 [Note] WSREP: gcomm thread scheduling priority set to other:0

2021-09-08 10:57:42 0 [Warning] WSREP: access file(/var/lib/mysql//gvwstate.dat) failed(No such file or directory)

2021-09-08 10:57:42 0 [Note] WSREP: restore pc from disk failed

2021-09-08 10:57:42 0 [Note] WSREP: GMCast version 0

2021-09-08 10:57:42 0 [Note] WSREP: (971d9824-8fdd, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567

2021-09-08 10:57:42 0 [Note] WSREP: (971d9824-8fdd, 'tcp://0.0.0.0:4567') multicast: , ttl: 1

2021-09-08 10:57:42 0 [Note] WSREP: EVS version 1

2021-09-08 10:57:42 0 [Note] WSREP: gcomm: bootstrapping new group 'MariaDB Galera Cluster'

2021-09-08 10:57:42 0 [Note] WSREP: start_prim is enabled, turn off pc_recovery

2021-09-08 10:57:42 0 [Note] WSREP: EVS version upgrade 0 -> 1

2021-09-08 10:57:42 0 [Note] WSREP: PC protocol upgrade 0 -> 1

2021-09-08 10:57:42 0 [Note] WSREP: Node 971d9824-8fdd state prim

2021-09-08 10:57:42 0 [Note] WSREP: view(view_id(PRIM,971d9824-8fdd,1) memb {

        971d9824-8fdd,0

} joined {

} left {

} partitioned {

})

2021-09-08 10:57:42 0 [Note] WSREP: save pc into disk

2021-09-08 10:57:42 0 [Note] WSREP: discarding pending addr without UUID: tcp://172.31.35.40:4567

2021-09-08 10:57:42 0 [Note] WSREP: discarding pending addr proto entry 0x55f93df17720

2021-09-08 10:57:42 0 [Note] WSREP: discarding pending addr without UUID: tcp://172.31.38.74:4567

2021-09-08 10:57:42 0 [Note] WSREP: discarding pending addr proto entry 0x55f93df3bac0

2021-09-08 10:57:42 0 [Note] WSREP: discarding pending addr without UUID: tcp://172.31.42.167:4567

2021-09-08 10:57:42 0 [Note] WSREP: discarding pending addr proto entry 0x55f93df442f0

2021-09-08 10:57:42 0 [Note] WSREP: gcomm: connected

2021-09-08 10:57:42 0 [Note] WSREP: Changing maximum packet size to 64500, resulting msg size: 32636

2021-09-08 10:57:42 0 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)

2021-09-08 10:57:42 0 [Note] WSREP: Opened channel 'MariaDB Galera Cluster'

2021-09-08 10:57:42 1 [Note] WSREP: Starting rollbacker thread 1

2021-09-08 10:57:42 2 [Note] WSREP: Starting applier thread 2

2021-09-08 10:57:42 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 0, memb_num = 1

2021-09-08 10:57:42 0 [Note] WSREP: Starting new group from scratch: 971e2a01-1093-11ec-992f-f77eb891b847

2021-09-08 10:57:42 0 [Note] WSREP: STATE_EXCHANGE: sent state UUID: 971e2b6c-1093-11ec-89ee-4b514ebe2f38

2021-09-08 10:57:42 0 [Note] WSREP: STATE EXCHANGE: sent state msg: 971e2b6c-1093-11ec-89ee-4b514ebe2f38

2021-09-08 10:57:42 0 [Note] WSREP: STATE EXCHANGE: got state msg: 971e2b6c-1093-11ec-89ee-4b514ebe2f38 from 0 (mariadb-galera-node1)

2021-09-08 10:57:42 0 [Note] WSREP: Quorum results:

        version    = 6,

        component  = PRIMARY,

        conf_id    = 0,

        members    = 1/1 (joined/total),

        act_id     = 0,

        last_appl. = 0,

        protocols  = 2/10/4 (gcs/repl/appl),

        vote policy= 0,

        group UUID = 971e2a01-1093-11ec-992f-f77eb891b847

2021-09-08 10:57:42 0 [Note] WSREP: Flow-control interval: [16, 16]

2021-09-08 10:57:42 0 [Note] WSREP: Restored state OPEN -> JOINED (1)

2021-09-08 10:57:42 0 [Note] WSREP: Member 0.0 (mariadb-galera-node1) synced with group.

2021-09-08 10:57:42 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 1)

2021-09-08 10:57:42 2 [Note] WSREP: ####### processing CC 1, local, ordered

2021-09-08 10:57:42 2 [Note] WSREP: Process first view: 971e2a01-1093-11ec-992f-f77eb891b847 my uuid: 971d9824-1093-11ec-8fdd-dbfb2cdeeedb

2021-09-08 10:57:42 2 [Note] WSREP: Server mariadb-galera-node1 connected to cluster at position 971e2a01-1093-11ec-992f-f77eb891b847:1 with ID 971d9824-1093-11ec-8fdd-dbfb2cdeeedb

2021-09-08 10:57:42 2 [Note] WSREP: Server status change disconnected -> connected

2021-09-08 10:57:42 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2021-09-08 10:57:42 2 [Note] WSREP: ####### My UUID: 971d9824-1093-11ec-8fdd-dbfb2cdeeedb

2021-09-08 10:57:42 2 [Note] WSREP: Cert index reset to 00000000-0000-0000-0000-000000000000:-1 (proto: 10), state transfer needed: no

2021-09-08 10:57:42 0 [Note] WSREP: Service thread queue flushed.

2021-09-08 10:57:42 2 [Note] WSREP: ####### Assign initial position for certification: 00000000-0000-0000-0000-000000000000:-1, protocol version: -1

2021-09-08 10:57:42 2 [Note] WSREP: REPL Protocols: 10 (5)

2021-09-08 10:57:42 2 [Note] WSREP: ####### Adjusting cert position: -1 -> 1

2021-09-08 10:57:42 0 [Note] WSREP: Service thread queue flushed.

2021-09-08 10:57:42 2 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> 971e2a01-1093-11ec-992f-f77eb891b847:0

2021-09-08 10:57:42 2 [Note] WSREP: ================================================

View:

  id: 971e2a01-1093-11ec-992f-f77eb891b847:1

  status: primary

  protocol_version: 4

  capabilities: MULTI-MASTER, CERTIFICATION, PARALLEL_APPLYING, REPLAY, ISOLATION, PAUSE, CAUSAL_READ, INCREMENTAL_WS, UNORDERED, PREORDERED, STREAMING, NBO

  final: no

  own_index: 0

  members(1):

        0: 971d9824-1093-11ec-8fdd-dbfb2cdeeedb, mariadb-galera-node1

=================================================

2021-09-08 10:57:42 2 [Note] WSREP: Server status change connected -> joiner

2021-09-08 10:57:42 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2021-09-08 10:57:42 2 [Note] WSREP: Server status change joiner -> initializing

2021-09-08 10:57:42 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2021-09-08 10:57:42 0 [Note] InnoDB: Uses event mutexes

2021-09-08 10:57:42 0 [Note] InnoDB: Compressed tables use zlib 1.2.11

2021-09-08 10:57:42 0 [Note] InnoDB: Number of pools: 1

2021-09-08 10:57:42 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions

2021-09-08 10:57:42 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)

2021-09-08 10:57:42 0 [Note] InnoDB: Using Linux native AIO

2021-09-08 10:57:42 0 [Note] InnoDB: Initializing buffer pool, total size = 268435456, chunk size = 134217728

2021-09-08 10:57:42 0 [Note] InnoDB: Completed initialization of buffer pool

2021-09-08 10:57:42 0 [Note] InnoDB: 128 rollback segments are active.

2021-09-08 10:57:42 0 [Note] InnoDB: Creating shared tablespace for temporary tables

2021-09-08 10:57:42 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

2021-09-08 10:57:42 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.

2021-09-08 10:57:42 0 [Note] InnoDB: 10.5.12 started; log sequence number 45592; transaction id 20

2021-09-08 10:57:42 0 [Note] Plugin 'FEEDBACK' is disabled.

2021-09-08 10:57:42 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool

2021-09-08 10:57:42 0 [Note] InnoDB: Buffer pool(s) load completed at 210908 10:57:42

2021-09-08 10:57:42 0 [Note] Server socket created on IP: '0.0.0.0'.

2021-09-08 10:57:42 0 [Note] WSREP: wsrep_init_schema_and_SR 0x0

2021-09-08 10:57:42 0 [Note] WSREP: Server initialized

2021-09-08 10:57:42 0 [Note] WSREP: Server status change initializing -> initialized

2021-09-08 10:57:42 0 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2021-09-08 10:57:42 2 [Note] WSREP: Bootstrapping a new cluster, setting initial position to 00000000-0000-0000-0000-000000000000:-1

2021-09-08 10:57:42 4 [Note] WSREP: Cluster table is empty, not recovering transactions

2021-09-08 10:57:42 2 [Note] WSREP: Server status change initialized -> joined

2021-09-08 10:57:42 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2021-09-08 10:57:42 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2021-09-08 10:57:42 5 [Note] WSREP: Starting applier thread 5

2021-09-08 10:57:42 6 [Note] WSREP: Starting applier thread 6

2021-09-08 10:57:42 0 [Note] Reading of all Master_info entries succeeded

2021-09-08 10:57:42 0 [Note] Added new Master_info '' to hash table

2021-09-08 10:57:42 0 [Note] /usr/sbin/mariadbd: ready for connections.

Version: '10.5.12-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server

2021-09-08 10:57:42 2 [Note] WSREP: Lowest cert index boundary for CC from group: 1

2021-09-08 10:57:42 2 [Note] WSREP: Min available from gcache for CC from group: 1

2021-09-08 10:57:42 2 [Note] WSREP: Server mariadb-galera-node1 synced with group

2021-09-08 10:57:42 2 [Note] WSREP: Server status change joined -> synced

2021-09-08 10:57:42 2 [Note] WSREP: Synchronized with group, ready for connections

2021-09-08 10:57:42 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2021-09-08 10:57:42 9 [Note] WSREP: Starting applier thread 9

[root@mariadb-galera-node1 log]#

Detailed Log Information on Node 2 while starting the galera Cluster :-

---------------------------------------------------------------------

[root@mariadb-galera-node2 ~]# tail -f /var/log/mysqld.log

2021-09-08 11:03:15 0 [Note] WSREP: Loading provider /usr/lib64/galera-4/libgalera_smm.so initial position: 00000000-0000-0000-0000-000000000000:-1

2021-09-08 11:03:15 0 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib64/galera-4/libgalera_smm.so'

2021-09-08 11:03:15 0 [Note] WSREP: wsrep_load(): Galera 26.4.9(r819f29cb) by Codership Oy <info@codership.com> loaded successfully.

2021-09-08 11:03:15 0 [Note] WSREP: CRC-32C: using 64-bit x86 acceleration.

2021-09-08 11:03:15 0 [Warning] WSREP: Could not open state file for reading: '/var/lib/mysql//grastate.dat'

2021-09-08 11:03:15 0 [Note] WSREP: Found saved state: 00000000-0000-0000-0000-000000000000:-1, safe_to_bootstrap: 1

2021-09-08 11:03:15 0 [Note] WSREP: GCache DEBUG: opened preamble:

Version: 0

UUID: 00000000-0000-0000-0000-000000000000

Seqno: -1 - -1

Offset: -1

Synced: 0

2021-09-08 11:03:15 0 [Note] WSREP: Skipped GCache ring buffer recovery: could not determine history UUID.

2021-09-08 11:03:15 0 [Note] WSREP: Passing config to GCS: base_dir = /var/lib/mysql/; base_host = 172.31.38.74; base_port = 4567; cert.log_conflicts = no; cert.optimistic_pa = yes         ; debug = no; evs.auto_evict = 0; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.join_retrans_perio         d = PT1S; evs.max_install_timeouts = 3; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 2; evs.view_forget_timeout = PT24H;          gcache.dir = /var/lib/mysql/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = galera.cache; gcache.page_size = 300M; gcache.recover = yes; gcache.size = 300M; gcomm.         thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 16; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 92         23372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announce_timeout = PT3S; pc.checksum =

2021-09-08 11:03:15 0 [Note] WSREP: Start replication

2021-09-08 11:03:15 0 [Note] WSREP: Connecting with bootstrap option: 0

2021-09-08 11:03:15 0 [Note] WSREP: Setting GCS initial position to 00000000-0000-0000-0000-000000000000:-1

2021-09-08 11:03:15 0 [Note] WSREP: protonet asio version 0

2021-09-08 11:03:15 0 [Note] WSREP: Using CRC-32C for message checksums.

2021-09-08 11:03:15 0 [Note] WSREP: backend: asio

2021-09-08 11:03:15 0 [Note] WSREP: gcomm thread scheduling priority set to other:0

2021-09-08 11:03:15 0 [Warning] WSREP: access file(/var/lib/mysql//gvwstate.dat) failed(No such file or directory)

2021-09-08 11:03:15 0 [Note] WSREP: restore pc from disk failed

2021-09-08 11:03:15 0 [Note] WSREP: GMCast version 0

2021-09-08 11:03:15 0 [Note] WSREP: (5daffd19-aedd, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567

2021-09-08 11:03:15 0 [Note] WSREP: (5daffd19-aedd, 'tcp://0.0.0.0:4567') multicast: , ttl: 1

2021-09-08 11:03:15 0 [Note] WSREP: EVS version 1

2021-09-08 11:03:15 0 [Note] WSREP: gcomm: connecting to group 'MariaDB Galera Cluster', peer '172.31.42.167:,172.31.38.74:,172.31.35.40:'

2021-09-08 11:03:15 0 [Note] WSREP: (5daffd19-aedd, 'tcp://0.0.0.0:4567') Found matching local endpoint for a connection, blacklisting address tcp://172.31.38.74:4567

2021-09-08 11:03:15 0 [Note] WSREP: (5daffd19-aedd, 'tcp://0.0.0.0:4567') connection established to 971d9824-8fdd tcp://172.31.42.167:4567

2021-09-08 11:03:15 0 [Note] WSREP: (5daffd19-aedd, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers:

2021-09-08 11:03:15 0 [Note] WSREP: EVS version upgrade 0 -> 1

2021-09-08 11:03:15 0 [Note] WSREP: declaring 971d9824-8fdd at tcp://172.31.42.167:4567 stable

2021-09-08 11:03:15 0 [Note] WSREP: PC protocol upgrade 0 -> 1

2021-09-08 11:03:15 0 [Note] WSREP: Node 971d9824-8fdd state prim

2021-09-08 11:03:15 0 [Note] WSREP: view(view_id(PRIM,5daffd19-aedd,2) memb {

        5daffd19-aedd,0

        971d9824-8fdd,0

} joined {

} left {

} partitioned {

})

2021-09-08 11:03:15 0 [Note] WSREP: save pc into disk

2021-09-08 11:03:15 0 [Note] WSREP: discarding pending addr without UUID: tcp://172.31.35.40:4567

2021-09-08 11:03:16 0 [Note] WSREP: gcomm: connected

2021-09-08 11:03:16 0 [Note] WSREP: Changing maximum packet size to 64500, resulting msg size: 32636

2021-09-08 11:03:16 0 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)

2021-09-08 11:03:16 0 [Note] WSREP: Opened channel 'MariaDB Galera Cluster'

2021-09-08 11:03:16 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 0, memb_num = 2

2021-09-08 11:03:16 0 [Note] WSREP: STATE_EXCHANGE: sent state UUID: 5e48ddcc-1094-11ec-a751-be4ef7fdefdb

2021-09-08 11:03:16 1 [Note] WSREP: Starting rollbacker thread 1

2021-09-08 11:03:16 2 [Note] WSREP: Starting applier thread 2

2021-09-08 11:03:16 0 [Note] WSREP: STATE EXCHANGE: sent state msg: 5e48ddcc-1094-11ec-a751-be4ef7fdefdb

2021-09-08 11:03:16 0 [Note] WSREP: STATE EXCHANGE: got state msg: 5e48ddcc-1094-11ec-a751-be4ef7fdefdb from 0 (mariadb-galera-node2)

2021-09-08 11:03:16 0 [Note] WSREP: STATE EXCHANGE: got state msg: 5e48ddcc-1094-11ec-a751-be4ef7fdefdb from 1 (mariadb-galera-node1)

2021-09-08 11:03:16 0 [Note] WSREP: Quorum results:

        version    = 6,

        component  = PRIMARY,

        conf_id    = 1,

        members    = 1/2 (joined/total),

        act_id     = 1,

        last_appl. = 0,

        protocols  = 2/10/4 (gcs/repl/appl),

        vote policy= 0,

        group UUID = 971e2a01-1093-11ec-992f-f77eb891b847

2021-09-08 11:03:16 0 [Note] WSREP: Flow-control interval: [23, 23]

2021-09-08 11:03:16 0 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 2)

2021-09-08 11:03:16 2 [Note] WSREP: ####### processing CC 2, local, ordered

2021-09-08 11:03:16 2 [Note] WSREP: Process first view: 971e2a01-1093-11ec-992f-f77eb891b847 my uuid: 5daffd19-1094-11ec-aedd-d3f383681fb8

2021-09-08 11:03:16 2 [Note] WSREP: Server mariadb-galera-node2 connected to cluster at position 971e2a01-1093-11ec-992f-f77eb891b847:2 with ID 5daffd19-1094-11ec-aedd-d3f383681fb8

2021-09-08 11:03:16 2 [Note] WSREP: Server status change disconnected -> connected

2021-09-08 11:03:16 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2021-09-08 11:03:16 2 [Note] WSREP: ####### My UUID: 5daffd19-1094-11ec-aedd-d3f383681fb8

2021-09-08 11:03:16 2 [Note] WSREP: Cert index reset to 00000000-0000-0000-0000-000000000000:-1 (proto: 10), state transfer needed: yes

2021-09-08 11:03:16 0 [Note] WSREP: Service thread queue flushed.

2021-09-08 11:03:16 2 [Note] WSREP: ####### Assign initial position for certification: 00000000-0000-0000-0000-000000000000:-1, protocol version: -1

2021-09-08 11:03:16 2 [Note] WSREP: State transfer required:

        Group state: 971e2a01-1093-11ec-992f-f77eb891b847:2

        Local state: 00000000-0000-0000-0000-000000000000:-1

2021-09-08 11:03:16 2 [Note] WSREP: Server status change connected -> joiner

2021-09-08 11:03:16 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2021-09-08 11:03:16 0 [Note] WSREP: Joiner monitor thread started to monitor

2021-09-08 11:03:16 0 [Note] WSREP: Running: 'wsrep_sst_rsync --role 'joiner' --address '172.31.38.74' --datadir '/var/lib/mysql/' --parent '15925' --mysqld-args --wsrep_start_posi         tion=00000000-0000-0000-0000-000000000000:-1'

2021-09-08 11:03:16 2 [Note] WSREP: ####### IST uuid:00000000-0000-0000-0000-000000000000 f: 0, l: 2, STRv: 3

2021-09-08 11:03:16 2 [Note] WSREP: IST receiver addr using tcp://172.31.38.74:4568

2021-09-08 11:03:16 2 [Note] WSREP: Prepared IST receiver for 0-2, listening at: tcp://172.31.38.74:4568

2021-09-08 11:03:16 0 [Note] WSREP: Member 0.0 (mariadb-galera-node2) requested state transfer from '*any*'. Selected 1.0 (mariadb-galera-node1)(SYNCED) as donor.

2021-09-08 11:03:16 0 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 2)

2021-09-08 11:03:16 2 [Note] WSREP: Requesting state transfer: success, donor: 1

2021-09-08 11:03:16 2 [Note] WSREP: Resetting GCache seqno map due to different histories.

2021-09-08 11:03:16 2 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> 971e2a01-1093-11ec-992f-f77eb891b847:2

2021-09-08 11:03:18 0 [Note] WSREP: (5daffd19-aedd, 'tcp://0.0.0.0:4567') turning message relay requesting off

2021-09-08 11:03:22 0 [Note] WSREP: 1.0 (mariadb-galera-node1): State transfer to 0.0 (mariadb-galera-node2) complete.

2021-09-08 11:03:22 0 [Note] WSREP: Member 1.0 (mariadb-galera-node1) synced with group.

WSREP_SST: [INFO] Joiner cleanup: rsync PID=16033, stunnel PID=0 (20210908 11:03:22.925)

WSREP_SST: [INFO] Joiner cleanup done. (20210908 11:03:23.441)

2021-09-08 11:03:23 3 [Note] WSREP: SST received

2021-09-08 11:03:23 3 [Note] WSREP: Server status change joiner -> initializing

2021-09-08 11:03:23 3 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2021-09-08 11:03:23 0 [Note] InnoDB: Uses event mutexes

2021-09-08 11:03:23 0 [Note] InnoDB: Compressed tables use zlib 1.2.11

2021-09-08 11:03:23 0 [Note] InnoDB: Number of pools: 1

2021-09-08 11:03:23 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions

2021-09-08 11:03:23 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)

2021-09-08 11:03:23 0 [Note] InnoDB: Using Linux native AIO

2021-09-08 11:03:23 0 [Note] InnoDB: Initializing buffer pool, total size = 268435456, chunk size = 134217728

2021-09-08 11:03:23 0 [Note] InnoDB: Completed initialization of buffer pool

2021-09-08 11:03:23 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=45592,45592

2021-09-08 11:03:23 0 [Note] InnoDB: Starting final batch to recover 22 pages from redo log.

2021-09-08 11:03:24 0 [Note] InnoDB: 128 rollback segments are active.

2021-09-08 11:03:24 0 [Note] InnoDB: Creating shared tablespace for temporary tables

2021-09-08 11:03:24 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

2021-09-08 11:03:24 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.

2021-09-08 11:03:24 0 [Note] InnoDB: 10.5.12 started; log sequence number 57091; transaction id 46

2021-09-08 11:03:24 0 [Note] Plugin 'FEEDBACK' is disabled.

2021-09-08 11:03:24 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool

2021-09-08 11:03:24 0 [Note] InnoDB: Buffer pool(s) load completed at 210908 11:03:24

2021-09-08 11:03:24 0 [Note] Server socket created on IP: '0.0.0.0'.

2021-09-08 11:03:24 0 [Note] WSREP: wsrep_init_schema_and_SR 0x0

2021-09-08 11:03:24 0 [Note] WSREP: Server initialized

2021-09-08 11:03:24 0 [Note] WSREP: Server status change initializing -> initialized

2021-09-08 11:03:24 0 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2021-09-08 11:03:24 3 [Note] WSREP: Server status change initialized -> joined

2021-09-08 11:03:24 3 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2021-09-08 11:03:24 3 [Note] WSREP: Recovered position from storage: 971e2a01-1093-11ec-992f-f77eb891b847:2

2021-09-08 11:03:24 3 [Note] WSREP: Recovered view from SST:

  id: 971e2a01-1093-11ec-992f-f77eb891b847:2

  status: primary

  protocol_version: 4

  capabilities: MULTI-MASTER, CERTIFICATION, PARALLEL_APPLYING, REPLAY, ISOLATION, PAUSE, CAUSAL_READ, INCREMENTAL_WS, UNORDERED, PREORDERED, STREAMING, NBO

  final: no

  own_index: 0

  members(2):

        0: 5daffd19-1094-11ec-aedd-d3f383681fb8, mariadb-galera-node2

        1: 971d9824-1093-11ec-8fdd-dbfb2cdeeedb, mariadb-galera-node1

2021-09-08 11:03:24 3 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2021-09-08 11:03:24 5 [Note] WSREP: Recovered cluster id 971e2a01-1093-11ec-992f-f77eb891b847

2021-09-08 11:03:24 3 [Note] WSREP: SST received: 971e2a01-1093-11ec-992f-f77eb891b847:2

2021-09-08 11:03:24 3 [Note] WSREP: SST succeeded for position 971e2a01-1093-11ec-992f-f77eb891b847:2

2021-09-08 11:03:24 2 [Note] WSREP: Installed new state from SST: 971e2a01-1093-11ec-992f-f77eb891b847:2

2021-09-08 11:03:24 0 [Note] WSREP: Joiner monitor thread ended with total time 8 sec

2021-09-08 11:03:24 6 [Note] WSREP: Starting applier thread 6

2021-09-08 11:03:24 7 [Note] WSREP: Starting applier thread 7

2021-09-08 11:03:24 0 [Note] Reading of all Master_info entries succeeded

2021-09-08 11:03:24 0 [Note] Added new Master_info '' to hash table

2021-09-08 11:03:24 0 [Note] /usr/sbin/mariadbd: ready for connections.

Version: '10.5.12-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server

2021-09-08 11:03:24 2 [Note] WSREP: Cert. index preload up to 2

2021-09-08 11:03:24 0 [Note] WSREP: ####### IST applying starts with 3

2021-09-08 11:03:24 0 [Note] WSREP: ####### IST current seqno initialized to 2

2021-09-08 11:03:24 0 [Note] WSREP: Receiving IST...  0.0% (0/1 events) complete.

2021-09-08 11:03:24 0 [Note] WSREP: IST preload starting at 2

2021-09-08 11:03:24 0 [Note] WSREP: REPL Protocols: 10 (5)

2021-09-08 11:03:24 0 [Note] WSREP: Service thread queue flushed.

2021-09-08 11:03:24 0 [Note] WSREP: ####### Assign initial position for certification: 971e2a01-1093-11ec-992f-f77eb891b847:1, protocol version: 5

2021-09-08 11:03:24 0 [Note] WSREP: REPL Protocols: 10 (5)

2021-09-08 11:03:24 0 [Note] WSREP: ####### Adjusting cert position: 1 -> 2

2021-09-08 11:03:24 0 [Note] WSREP: Service thread queue flushed.

2021-09-08 11:03:24 0 [Note] WSREP: Lowest cert index boundary for CC from preload: 2

2021-09-08 11:03:24 0 [Note] WSREP: Min available from gcache for CC from preload: 2

2021-09-08 11:03:24 0 [Note] WSREP: Receiving IST...100.0% (1/1 events) complete.

2021-09-08 11:03:24 2 [Note] WSREP: IST received: 971e2a01-1093-11ec-992f-f77eb891b847:2

2021-09-08 11:03:24 2 [Note] WSREP: Lowest cert index boundary for CC from sst: 2

2021-09-08 11:03:24 2 [Note] WSREP: Min available from gcache for CC from sst: 2

2021-09-08 11:03:24 0 [Note] WSREP: 0.0 (mariadb-galera-node2): State transfer from 1.0 (mariadb-galera-node1) complete.

2021-09-08 11:03:24 0 [Note] WSREP: Shifting JOINER -> JOINED (TO: 2)

2021-09-08 11:03:24 10 [Note] WSREP: Starting applier thread 10

2021-09-08 11:03:24 0 [Note] WSREP: Member 0.0 (mariadb-galera-node2) synced with group.

2021-09-08 11:03:24 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 2)

2021-09-08 11:03:24 2 [Note] WSREP: Server mariadb-galera-node2 synced with group

2021-09-08 11:03:24 2 [Note] WSREP: Server status change joined -> synced

2021-09-08 11:03:24 2 [Note] WSREP: Synchronized with group, ready for connections

2021-09-08 11:03:24 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

[root@mariadb-galera-node2 ~]#

Detailed Log Information on Node 3 while starting the galera Cluster

---------------------------------------------------------------------

[root@mariadb-galera-node3 ~]# tail -f /var/log/mysqld.log

2021-09-08 11:05:25 0 [Note] WSREP: Loading provider /usr/lib64/galera-4/libgalera_smm.so initial position: 00000000-0000-0000-0000-000000000000:-1

2021-09-08 11:05:25 0 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib64/galera-4/libgalera_smm.so'

2021-09-08 11:05:25 0 [Note] WSREP: wsrep_load(): Galera 26.4.9(r819f29cb) by Codership Oy <info@codership.com> loaded successfully.

2021-09-08 11:05:25 0 [Note] WSREP: CRC-32C: using 64-bit x86 acceleration.

2021-09-08 11:05:25 0 [Warning] WSREP: Could not open state file for reading: '/var/lib/mysql//grastate.dat'

2021-09-08 11:05:25 0 [Note] WSREP: Found saved state: 00000000-0000-0000-0000-000000000000:-1, safe_to_bootstrap: 1

2021-09-08 11:05:25 0 [Note] WSREP: GCache DEBUG: opened preamble:

Version: 0

UUID: 00000000-0000-0000-0000-000000000000

Seqno: -1 - -1

Offset: -1

Synced: 0

2021-09-08 11:05:25 0 [Note] WSREP: Skipped GCache ring buffer recovery: could not determine history UUID.

2021-09-08 11:05:25 0 [Note] WSREP: Passing config to GCS: base_dir = /var/lib/mysql/; base_host = 172.31.35.40; base_port = 4567; cert.log_conflicts = no; cert.optimistic_pa = yes;         debug = no; evs.auto_evict = 0; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.join_retrans_period         = PT1S; evs.max_install_timeouts = 3; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 2; evs.view_forget_timeout = PT24H; gca        che.dir = /var/lib/mysql/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = galera.cache; gcache.page_size = 300M; gcache.recover = yes; gcache.size = 300M; gcomm.thre        ad_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 16; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372        036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announce_timeout = PT3S; pc.checksum =

2021-09-08 11:05:25 0 [Note] WSREP: Start replication

2021-09-08 11:05:25 0 [Note] WSREP: Connecting with bootstrap option: 0

2021-09-08 11:05:25 0 [Note] WSREP: Setting GCS initial position to 00000000-0000-0000-0000-000000000000:-1

2021-09-08 11:05:25 0 [Note] WSREP: protonet asio version 0

2021-09-08 11:05:25 0 [Note] WSREP: Using CRC-32C for message checksums.

2021-09-08 11:05:25 0 [Note] WSREP: backend: asio

2021-09-08 11:05:25 0 [Note] WSREP: gcomm thread scheduling priority set to other:0

2021-09-08 11:05:25 0 [Warning] WSREP: access file(/var/lib/mysql//gvwstate.dat) failed(No such file or directory)

2021-09-08 11:05:25 0 [Note] WSREP: restore pc from disk failed

2021-09-08 11:05:25 0 [Note] WSREP: GMCast version 0

2021-09-08 11:05:25 0 [Note] WSREP: (aafb978c-9b1e, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567

2021-09-08 11:05:25 0 [Note] WSREP: (aafb978c-9b1e, 'tcp://0.0.0.0:4567') multicast: , ttl: 1

2021-09-08 11:05:25 0 [Note] WSREP: EVS version 1

2021-09-08 11:05:25 0 [Note] WSREP: gcomm: connecting to group 'MariaDB Galera Cluster', peer '172.31.42.167:,172.31.38.74:,172.31.35.40:'

2021-09-08 11:05:25 0 [Note] WSREP: (aafb978c-9b1e, 'tcp://0.0.0.0:4567') Found matching local endpoint for a connection, blacklisting address tcp://172.31.35.40:4567

2021-09-08 11:05:25 0 [Note] WSREP: (aafb978c-9b1e, 'tcp://0.0.0.0:4567') connection established to 5daffd19-aedd tcp://172.31.38.74:4567

2021-09-08 11:05:25 0 [Note] WSREP: (aafb978c-9b1e, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers:

2021-09-08 11:05:25 0 [Note] WSREP: (aafb978c-9b1e, 'tcp://0.0.0.0:4567') connection established to 971d9824-8fdd tcp://172.31.42.167:4567

2021-09-08 11:05:25 0 [Note] WSREP: EVS version upgrade 0 -> 1

2021-09-08 11:05:25 0 [Note] WSREP: declaring 5daffd19-aedd at tcp://172.31.38.74:4567 stable

2021-09-08 11:05:25 0 [Note] WSREP: declaring 971d9824-8fdd at tcp://172.31.42.167:4567 stable

2021-09-08 11:05:25 0 [Note] WSREP: PC protocol upgrade 0 -> 1

2021-09-08 11:05:25 0 [Note] WSREP: Node 5daffd19-aedd state prim

2021-09-08 11:05:25 0 [Note] WSREP: view(view_id(PRIM,5daffd19-aedd,3) memb {

        5daffd19-aedd,0

        971d9824-8fdd,0

        aafb978c-9b1e,0

} joined {

} left {

} partitioned {

})

2021-09-08 11:05:25 0 [Note] WSREP: save pc into disk

2021-09-08 11:05:26 0 [Note] WSREP: gcomm: connected

2021-09-08 11:05:26 0 [Note] WSREP: Changing maximum packet size to 64500, resulting msg size: 32636

2021-09-08 11:05:26 0 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)

2021-09-08 11:05:26 0 [Note] WSREP: Opened channel 'MariaDB Galera Cluster'

2021-09-08 11:05:26 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 2, memb_num = 3

2021-09-08 11:05:26 0 [Note] WSREP: STATE EXCHANGE: Waiting for state UUID.

2021-09-08 11:05:26 0 [Note] WSREP: STATE EXCHANGE: sent state msg: ab48e01c-1094-11ec-b001-ea61514c042f

2021-09-08 11:05:26 0 [Note] WSREP: STATE EXCHANGE: got state msg: ab48e01c-1094-11ec-b001-ea61514c042f from 0 (mariadb-galera-node2)

2021-09-08 11:05:26 0 [Note] WSREP: STATE EXCHANGE: got state msg: ab48e01c-1094-11ec-b001-ea61514c042f from 1 (mariadb-galera-node1)

2021-09-08 11:05:26 1 [Note] WSREP: Starting rollbacker thread 1

2021-09-08 11:05:26 2 [Note] WSREP: Starting applier thread 2

2021-09-08 11:05:26 0 [Note] WSREP: STATE EXCHANGE: got state msg: ab48e01c-1094-11ec-b001-ea61514c042f from 2 (mariadb-galera-node3)

2021-09-08 11:05:26 0 [Note] WSREP: Quorum results:

        version    = 6,

        component  = PRIMARY,

        conf_id    = 2,

        members    = 2/3 (joined/total),

        act_id     = 2,

        last_appl. = 0,

        protocols  = 2/10/4 (gcs/repl/appl),

        vote policy= 0,

        group UUID = 971e2a01-1093-11ec-992f-f77eb891b847

2021-09-08 11:05:26 0 [Note] WSREP: Flow-control interval: [28, 28]

2021-09-08 11:05:26 0 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 3)

2021-09-08 11:05:26 2 [Note] WSREP: ####### processing CC 3, local, ordered

2021-09-08 11:05:26 2 [Note] WSREP: Process first view: 971e2a01-1093-11ec-992f-f77eb891b847 my uuid: aafb978c-1094-11ec-9b1e-771cc3d0b281

2021-09-08 11:05:26 2 [Note] WSREP: Server mariadb-galera-node3 connected to cluster at position 971e2a01-1093-11ec-992f-f77eb891b847:3 with ID aafb978c-1094-11ec-9b1e-771cc3d0b281

2021-09-08 11:05:26 2 [Note] WSREP: Server status change disconnected -> connected

2021-09-08 11:05:26 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2021-09-08 11:05:26 2 [Note] WSREP: ####### My UUID: aafb978c-1094-11ec-9b1e-771cc3d0b281

2021-09-08 11:05:26 2 [Note] WSREP: Cert index reset to 00000000-0000-0000-0000-000000000000:-1 (proto: 10), state transfer needed: yes

2021-09-08 11:05:26 0 [Note] WSREP: Service thread queue flushed.

2021-09-08 11:05:26 2 [Note] WSREP: ####### Assign initial position for certification: 00000000-0000-0000-0000-000000000000:-1, protocol version: -1

2021-09-08 11:05:26 2 [Note] WSREP: State transfer required:

        Group state: 971e2a01-1093-11ec-992f-f77eb891b847:3

        Local state: 00000000-0000-0000-0000-000000000000:-1

2021-09-08 11:05:26 2 [Note] WSREP: Server status change connected -> joiner

2021-09-08 11:05:26 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2021-09-08 11:05:26 0 [Note] WSREP: Joiner monitor thread started to monitor

2021-09-08 11:05:26 0 [Note] WSREP: Running: 'wsrep_sst_rsync --role 'joiner' --address '172.31.35.40' --datadir '/var/lib/mysql/' --parent '16182' --mysqld-args --wsrep_start_posit        ion=00000000-0000-0000-0000-000000000000:-1'

2021-09-08 11:05:26 2 [Note] WSREP: ####### IST uuid:00000000-0000-0000-0000-000000000000 f: 0, l: 3, STRv: 3

2021-09-08 11:05:26 2 [Note] WSREP: IST receiver addr using tcp://172.31.35.40:4568

2021-09-08 11:05:26 2 [Note] WSREP: Prepared IST receiver for 0-3, listening at: tcp://172.31.35.40:4568

2021-09-08 11:05:26 0 [Note] WSREP: Member 2.0 (mariadb-galera-node3) requested state transfer from '*any*'. Selected 0.0 (mariadb-galera-node2)(SYNCED) as donor.

2021-09-08 11:05:26 0 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 3)

2021-09-08 11:05:26 2 [Note] WSREP: Requesting state transfer: success, donor: 0

2021-09-08 11:05:26 2 [Note] WSREP: Resetting GCache seqno map due to different histories.

2021-09-08 11:05:26 2 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> 971e2a01-1093-11ec-992f-f77eb891b847:3

2021-09-08 11:05:28 0 [Note] WSREP: (aafb978c-9b1e, 'tcp://0.0.0.0:4567') turning message relay requesting off

2021-09-08 11:05:32 0 [Note] WSREP: 0.0 (mariadb-galera-node2): State transfer to 2.0 (mariadb-galera-node3) complete.

2021-09-08 11:05:32 0 [Note] WSREP: Member 0.0 (mariadb-galera-node2) synced with group.

WSREP_SST: [INFO] Joiner cleanup: rsync PID=16290, stunnel PID=0 (20210908 11:05:32.576)

WSREP_SST: [INFO] Joiner cleanup done. (20210908 11:05:33.091)

2021-09-08 11:05:33 3 [Note] WSREP: SST received

2021-09-08 11:05:33 3 [Note] WSREP: Server status change joiner -> initializing

2021-09-08 11:05:33 3 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2021-09-08 11:05:33 0 [Note] InnoDB: Uses event mutexes

2021-09-08 11:05:33 0 [Note] InnoDB: Compressed tables use zlib 1.2.11

2021-09-08 11:05:33 0 [Note] InnoDB: Number of pools: 1

2021-09-08 11:05:33 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions

2021-09-08 11:05:33 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)

2021-09-08 11:05:33 0 [Note] InnoDB: Using Linux native AIO

2021-09-08 11:05:33 0 [Note] InnoDB: Initializing buffer pool, total size = 268435456, chunk size = 134217728

2021-09-08 11:05:33 0 [Note] InnoDB: Completed initialization of buffer pool

2021-09-08 11:05:33 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=45592,45592

2021-09-08 11:05:33 0 [Note] InnoDB: Starting final batch to recover 22 pages from redo log.

2021-09-08 11:05:33 0 [Note] InnoDB: 128 rollback segments are active.

2021-09-08 11:05:33 0 [Note] InnoDB: Creating shared tablespace for temporary tables

2021-09-08 11:05:33 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

2021-09-08 11:05:33 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.

2021-09-08 11:05:33 0 [Note] InnoDB: 10.5.12 started; log sequence number 58388; transaction id 54

2021-09-08 11:05:33 0 [Note] Plugin 'FEEDBACK' is disabled.

2021-09-08 11:05:33 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool

2021-09-08 11:05:33 0 [Note] InnoDB: Buffer pool(s) load completed at 210908 11:05:33

2021-09-08 11:05:33 0 [Note] Server socket created on IP: '0.0.0.0'.

2021-09-08 11:05:33 0 [Note] WSREP: wsrep_init_schema_and_SR 0x0

2021-09-08 11:05:33 0 [Note] WSREP: Server initialized

2021-09-08 11:05:33 0 [Note] WSREP: Server status change initializing -> initialized

2021-09-08 11:05:33 0 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2021-09-08 11:05:33 3 [Note] WSREP: Server status change initialized -> joined

2021-09-08 11:05:33 3 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2021-09-08 11:05:33 3 [Note] WSREP: Recovered position from storage: 971e2a01-1093-11ec-992f-f77eb891b847:3

2021-09-08 11:05:33 3 [Note] WSREP: Recovered view from SST:

  id: 971e2a01-1093-11ec-992f-f77eb891b847:3

  status: primary

  protocol_version: 4

  capabilities: MULTI-MASTER, CERTIFICATION, PARALLEL_APPLYING, REPLAY, ISOLATION, PAUSE, CAUSAL_READ, INCREMENTAL_WS, UNORDERED, PREORDERED, STREAMING, NBO

  final: no

  own_index: 2

  members(3):

        0: 5daffd19-1094-11ec-aedd-d3f383681fb8, mariadb-galera-node2

        1: 971d9824-1093-11ec-8fdd-dbfb2cdeeedb, mariadb-galera-node1

        2: aafb978c-1094-11ec-9b1e-771cc3d0b281, mariadb-galera-node3

2021-09-08 11:05:33 3 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2021-09-08 11:05:33 5 [Note] WSREP: Recovered cluster id 971e2a01-1093-11ec-992f-f77eb891b847

2021-09-08 11:05:33 3 [Note] WSREP: SST received: 971e2a01-1093-11ec-992f-f77eb891b847:3

2021-09-08 11:05:33 3 [Note] WSREP: SST succeeded for position 971e2a01-1093-11ec-992f-f77eb891b847:3

2021-09-08 11:05:33 2 [Note] WSREP: Installed new state from SST: 971e2a01-1093-11ec-992f-f77eb891b847:3

2021-09-08 11:05:33 0 [Note] WSREP: Joiner monitor thread ended with total time 7 sec

2021-09-08 11:05:33 6 [Note] WSREP: Starting applier thread 6

2021-09-08 11:05:33 7 [Note] WSREP: Starting applier thread 7

2021-09-08 11:05:33 0 [Note] Reading of all Master_info entries succeeded

2021-09-08 11:05:33 0 [Note] Added new Master_info '' to hash table

2021-09-08 11:05:33 0 [Note] /usr/sbin/mariadbd: ready for connections.

Version: '10.5.12-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server

2021-09-08 11:05:33 2 [Note] WSREP: Cert. index preload up to 3

2021-09-08 11:05:33 0 [Note] WSREP: ####### IST applying starts with 4

2021-09-08 11:05:33 0 [Note] WSREP: ####### IST current seqno initialized to 3

2021-09-08 11:05:33 0 [Note] WSREP: Receiving IST...  0.0% (0/1 events) complete.

2021-09-08 11:05:33 0 [Note] WSREP: IST preload starting at 3

2021-09-08 11:05:33 0 [Note] WSREP: REPL Protocols: 10 (5)

2021-09-08 11:05:33 0 [Note] WSREP: Service thread queue flushed.

2021-09-08 11:05:33 0 [Note] WSREP: ####### Assign initial position for certification: 971e2a01-1093-11ec-992f-f77eb891b847:2, protocol version: 5

2021-09-08 11:05:33 0 [Note] WSREP: REPL Protocols: 10 (5)

2021-09-08 11:05:33 0 [Note] WSREP: ####### Adjusting cert position: 2 -> 3

2021-09-08 11:05:33 0 [Note] WSREP: Service thread queue flushed.

2021-09-08 11:05:33 0 [Note] WSREP: Lowest cert index boundary for CC from preload: 3

2021-09-08 11:05:33 0 [Note] WSREP: Min available from gcache for CC from preload: 3

2021-09-08 11:05:33 0 [Note] WSREP: Receiving IST...100.0% (1/1 events) complete.

2021-09-08 11:05:33 2 [Note] WSREP: IST received: 971e2a01-1093-11ec-992f-f77eb891b847:3

2021-09-08 11:05:33 2 [Note] WSREP: Lowest cert index boundary for CC from sst: 3

2021-09-08 11:05:33 2 [Note] WSREP: Min available from gcache for CC from sst: 3

2021-09-08 11:05:33 10 [Note] WSREP: Starting applier thread 10

2021-09-08 11:05:33 0 [Note] WSREP: 2.0 (mariadb-galera-node3): State transfer from 0.0 (mariadb-galera-node2) complete.

2021-09-08 11:05:33 0 [Note] WSREP: Shifting JOINER -> JOINED (TO: 3)

2021-09-08 11:05:33 0 [Note] WSREP: Member 2.0 (mariadb-galera-node3) synced with group.

2021-09-08 11:05:33 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 3)

2021-09-08 11:05:33 10 [Note] WSREP: Server mariadb-galera-node3 synced with group

2021-09-08 11:05:33 10 [Note] WSREP: Server status change joined -> synced

2021-09-08 11:05:33 10 [Note] WSREP: Synchronized with group, ready for connections

2021-09-08 11:05:33 10 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

[root@mariadb-galera-node3 ~]#

(v) Validate Galera Settings

Login to any of the three nodes as the root user, then confirm that the cluster settings are OK.

Node 1 :-

------------

[root@mariadb-galera-node1 ~]# mysql -u root -p

Enter password:

Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 10.Server version: 10.5.12-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> system hostname

mariadb-galera-node1

MariaDB [(none)]> show status like 'wsrep_%';

+-------------------------------+----------------------------------------------------

| Variable_name                 | Value                                                                                                                                          |

+-------------------------------+----------------------------------------------------

| wsrep_local_state_uuid        | 971e2a01-1093-11ec-992f-f77eb891b847                                                                                                           |

| wsrep_protocol_version        | 10                                                                                                                                             |

| wsrep_last_committed          | 3                                                                                                                                              |

| wsrep_replicated              | 0                                                                                                                                              |

| wsrep_replicated_bytes        | 0                                                                                                                                              |

| wsrep_repl_keys               | 0                                                                                                                                              |

| wsrep_repl_keys_bytes         | 0                                                                                                                                              |

| wsrep_repl_data_bytes         | 0                                                                                                                                              |

| wsrep_repl_other_bytes        | 0                                                                                                                                              |

| wsrep_received                | 7                                                                                                                                              |

| wsrep_received_bytes          | 762                                                                                                                                            |

| wsrep_local_commits           | 0                                                                                                                                              |

| wsrep_local_cert_failures     | 0                                                                                                                                              |

| wsrep_local_replays           | 0                                                                                                                                              |

| wsrep_local_send_queue        | 0                                                                                                                                              |

| wsrep_local_send_queue_max    | 1                                                                                                                                              |

| wsrep_local_send_queue_min    | 0                                                                                                                                              |

| wsrep_local_send_queue_avg    | 0                                                                                                                                              |

| wsrep_local_recv_queue        | 0                                                                                                                                              |

| wsrep_local_recv_queue_max    | 2                                                                                                                                              |

| wsrep_local_recv_queue_min    | 0                                                                                                                                              |

| wsrep_local_recv_queue_avg    | 0.142857                                                                                                                                       |

| wsrep_local_cached_downto     | 1                                                                                                                                              |

| wsrep_flow_control_paused_ns  | 0                                                                                                                                              |

| wsrep_flow_control_paused     | 0                                                                                                                                              |

| wsrep_flow_control_sent       | 0                                                                                                                                              |

| wsrep_flow_control_recv       | 0                                                                                                                                              |

| wsrep_flow_control_active     | false                                                                                                                                          |

| wsrep_flow_control_requested  | false                                                                                                                                          |

| wsrep_cert_deps_distance      | 0                                                                                                                                              |

| wsrep_apply_oooe              | 0                                                                                                                                              |

| wsrep_apply_oool              | 0                                                                                                                                              |

| wsrep_apply_window            | 0                                                                                                                                              |

| wsrep_apply_waits             | 0                                                                                                                                              |

| wsrep_commit_oooe             | 0                                                                                                                                              |

| wsrep_commit_oool             | 0                                                                                                                                              |

| wsrep_commit_window           | 0                                                                                                                                              |

| wsrep_local_state             | 4                                                                                                                                              |

| wsrep_local_state_comment     | Synced                                                                                                                                         |

| wsrep_cert_index_size         | 0                                                                                                                                              |

| wsrep_causal_reads            | 0                                                                                                                                              |

| wsrep_cert_interval           | 0                                                                                                                                              |

| wsrep_open_transactions       | 0                                                                                                                                              |

| wsrep_open_connections        | 0                                                                                                                                              |

| wsrep_incoming_addresses      | AUTO,AUTO,AUTO                                                                                                                                 |

| wsrep_cluster_weight          | 3                                                                                                                                              |

| wsrep_desync_count            | 0                                                                                                                                              |

| wsrep_evs_delayed             |                                                                                                                                                |

| wsrep_evs_evict_list          |                                                                                                                                                |

| wsrep_evs_repl_latency        | 0/0/0/0/0                                                                                                                                      |

| wsrep_evs_state               | OPERATIONAL                                                                                                                                    |

| wsrep_gcomm_uuid              | 971d9824-1093-11ec-8fdd-dbfb2cdeeedb                                                                                                           |

| wsrep_gmcast_segment          | 0                                                                                                                                              |

| wsrep_applier_thread_count    | 4                                                                                                                                              |

| wsrep_cluster_capabilities    |                                                                                                                                                |

| wsrep_cluster_conf_id         | 3                                                                                                                                              |

| wsrep_cluster_size            | 3                                                                                                                                              |

| wsrep_cluster_state_uuid      | 971e2a01-1093-11ec-992f-f77eb891b847                                                                                                           |

| wsrep_cluster_status          | Primary                                                                                                                                        |

| wsrep_connected               | ON                                                                                                                                             |

| wsrep_local_bf_aborts         | 0                                                                                                                                              |

| wsrep_local_index             | 1                                                                                                                                              |

| wsrep_provider_capabilities   | :MULTI_MASTER:CERTIFICATION:PARALLEL_APPLYING:TRX_REPLAY:ISOLATION:PAUSE:CAUSAL_READS:INCREMENTAL_WRITESET:UNORDERED:PREORDERED:STREAMING:NBO: |

| wsrep_provider_name           | Galera                                                                                                                                         |

| wsrep_provider_vendor         | Codership Oy <info@codership.com>                                                                                                              |

| wsrep_provider_version        | 26.4.9(r819f29cb)                                                                                                                              |

| wsrep_ready                   | ON                                                                                                                                             |

| wsrep_rollbacker_thread_count | 1                                                                                                                                              |

| wsrep_thread_count            | 5                                                                                                                                              |

+-------------------------------+-------------------------------------------------+

69 rows in set (0.001 sec)

MariaDB [(none)]> show status like 'wsrep_cluster_size';

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| wsrep_cluster_size | 3     |

+--------------------+-------+

1 row in set (0.001 sec)

MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'wsrep_cluster_status';

+----------------------+---------+

| Variable_name        | Value   |

+----------------------+---------+

| wsrep_cluster_status | Primary |

+----------------------+---------+

1 row in set (0.001 sec)

MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'wsrep_incoming_addresses';

+--------------------------+----------------+

| Variable_name            | Value          |

+--------------------------+----------------+

| wsrep_incoming_addresses | AUTO,AUTO,AUTO |

+--------------------------+----------------+

1 row in set (0.001 sec)

MariaDB [mysql]> select * from mysql.wsrep_cluster;

+--------------------------------------+---------+------------+------------------+---

| cluster_uuid                         | view_id | view_seqno | protocol_version | capabilities |

+--------------------------------------+---------+------------+------------------+---

| 971e2a01-1093-11ec-992f-f77eb891b847 |       3 |          3 |                4 |       184703 |

+--------------------------------------+---------+------------+------------------+---

1 row in set (0.000 sec)

MariaDB [mysql]> select * from wsrep_cluster_members;

+--------------------------------------+-----------------------------

| node_uuid                            | cluster_uuid                         | node_name            | node_incoming_address |

+--------------------------------------+-----------------------------

| 5daffd19-1094-11ec-aedd-d3f383681fb8 | 971e2a01-1093-11ec-992f-f77eb891b847 | mariadb-galera-node2 | AUTO                  |

| 971d9824-1093-11ec-8fdd-dbfb2cdeeedb | 971e2a01-1093-11ec-992f-f77eb891b847 | mariadb-galera-node1 | AUTO                  |

| aafb978c-1094-11ec-9b1e-771cc3d0b281 | 971e2a01-1093-11ec-992f-f77eb891b847 | mariadb-galera-node3 | AUTO                  |

+--------------------------------------+-----------------------------

3 rows in set (0.000 sec)

Confirm that we have a cluster size of 3 under:

wsrep_cluster_size    3

Check in 2 and 3 rd Nodes :-

-------------------------------

[root@mariadb-galera-node2 ~]# mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

Enter password: root

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| wsrep_cluster_size | 3     |

+--------------------+-------+

[root@mariadb-galera-node2 ~]#

[root@mariadb-galera-node3 ~]# mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

Enter password: root

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| wsrep_cluster_size | 3     |

+--------------------+-------+

[root@mariadb-galera-node3 ~]#

Further Additional information :-

-------------------------------------------

[root@mariadb-galera-node1 mysql]# cat grastate.dat

# GALERA saved state

version: 2.1

uuid:    971e2a01-1093-11ec-992f-f77eb891b847

seqno:   -1

safe_to_bootstrap: 0

[root@mariadb-galera-node1 mysql]#

[root@mariadb-galera-node2 ~]# cat /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    971e2a01-1093-11ec-992f-f77eb891b847

seqno:   -1

safe_to_bootstrap: 0

[root@mariadb-galera-node2 ~]#

[root@mariadb-galera-node3 ~]# cat /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    971e2a01-1093-11ec-992f-f77eb891b847

seqno:   -1

safe_to_bootstrap: 0

[root@mariadb-galera-node3 ~]#

 ........  MariaDB Galera Cluster is ready for testing ........

 

 -------------------------------------------------------------------

  Step - 4 Basic Testing Phase - 1 with MariaDB Galera Cluster

 -------------------------------------------------------------------

Testing Replication :-

-----------------------------

We have gone through the steps up to this point so that your cluster can perform replication from any node to any other node, known as active-active replication. Follow the following steps to test and see if the replication is working as expected.

Write to the First Node :-

----------------------------

We will start by making database changes on your first node. The following commands will create a database called playground and a table inside of this database called equipment.

[root@mariadb-galera-node1 ~]# mysql -u root -p -e 'CREATE DATABASE playground; CREATE TABLE playground.equipment ( id INT NOT NULL AUTO_INCREMENT, type VARCHAR(50), quant INT, color VARCHAR(25), PRIMARY KEY(id)); INSERT INTO playground.equipment (type, quant, color) VALUES ("slide", 2, "blue");'

Enter password: root

[root@mariadb-galera-node1 ~]#

In the previous command, the CREATE DATABASE statement creates a database named playground. The CREATE statement creates a table named equipment inside the playground database having an auto-incrementing identifier column called id and other columns. The type column, quant column, and color column are defined to store the type, quantity, and color of the equipment respectively. The INSERT statement inserts an entry of type slide, quantity 2, and color blue.

You now have one value in your table.

Read and Write on the Second Node :-

------------------------------------

Next, look at the second node to verify that replication is working:

[root@mariadb-galera-node2 ~]# mysql -u root -p -e 'SELECT * FROM playground.equipment;'

Enter password: root

+----+-------+-------+-------+

| id | type  | quant | color |

+----+-------+-------+-------+

|  2 | slide |     2 | blue  |

+----+-------+-------+-------+

[root@mariadb-galera-node2 ~]#

[root@mariadb-galera-node3 ~]# mysql -u root -p -e 'SELECT * FROM playground.equipment;'

Enter password: root

+----+-------+-------+-------+

| id | type  | quant | color |

+----+-------+-------+-------+

|  2 | slide |     2 | blue  |

+----+-------+-------+-------+

[root@mariadb-galera-node3 ~]#

From the 2nd Node  you can write data to the cluster:

-----------------------------------------------------------

[root@mariadb-galera-node2 ~]# mysql -u root -p -e 'INSERT INTO playground.equipment (type, quant, color) VALUES ("swing", 10, "yellow");'

Enter password: root

[root@mariadb-galera-node2 ~]#

Read and Write on the Third Node :-

---------------------------------------

From the third node, you can read all of this data by querying the table again:

[root@mariadb-galera-node3 ~]# mysql -u root -p -e 'SELECT * FROM playground.equipment;'

Enter password: root

+----+-------+-------+--------+

| id | type  | quant | color  |

+----+-------+-------+--------+

|  2 | slide |     2 | blue   |

|  4 | swing |    10 | yellow |

+----+-------+-------+--------+

[root@mariadb-galera-node3 ~]#

Again, you can add another value from this node: 

[root@mariadb-galera-node3 ~]# mysql -u root -p -e 'INSERT INTO playground.equipment (type, quant, color) VALUES ("seesaw", 3, "green");'

Enter password: root

[root@mariadb-galera-node3 ~]#

Read on the First Node :-

-----------------------------------

Back on the first node, you can verify that your data is available everywhere:

[root@mariadb-galera-node1 ~]# mysql -u root -p -e 'SELECT * FROM playground.equipment;'

Enter password: root 

+----+--------+-------+--------+

| id | type   | quant | color  |

+----+--------+-------+--------+

|  2 | slide  |     2 | blue   |

|  4 | swing  |    10 | yellow |

|  6 | seesaw |     3 | green  |

+----+--------+-------+--------+

[root@mariadb-galera-node1 ~]#

---------------------------------------------------------------------

 Step - 5 Testing Phase - 2 

           [ --> shutdown mysql service in node 1

             --> And creating the database in node 2

             --> Start the mysql service in Node 1

             --> Checking the created database in all 3 Nodes ]

---------------------------------------------------------------

[root@mariadb-galera-node1 ~]# service mysql stop

Redirecting to /bin/systemctl stop mysql.service

[root@mariadb-galera-node1 ~]#

[root@mariadb-galera-node2 ~]# mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

Enter password:

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| wsrep_cluster_size | 2     |

+--------------------+-------+

[root@mariadb-galera-node2 ~]# mysql -u root -p -e "CREATE DATABASE failover;"

Enter password: root 

[root@mariadb-galera-node2 ~]# cat /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    971e2a01-1093-11ec-992f-f77eb891b847

seqno:   -1

safe_to_bootstrap: 0

[root@mariadb-galera-node2 ~]#

[root@mariadb-galera-node1 ~]# service mysql start

Redirecting to /bin/systemctl start mysql.service

[root@mariadb-galera-node1 ~]# mysql -u root -p -e "show databases;"

Enter password: root 

+--------------------+

| Database           |

+--------------------+

| failover           |

| information_schema |

| mysql              |

| performance_schema |

| playground         |

| test               |

+--------------------+

[root@mariadb-galera-node1 ~]# cat  /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    971e2a01-1093-11ec-992f-f77eb891b847

seqno:   -1

safe_to_bootstrap: 0

[root@mariadb-galera-node1 ~]#

[root@mariadb-galera-node3 ~]#  mysql -u root -p -e "show databases;"

Enter password: root

+--------------------+

| Database           |

+--------------------+

| failover           |

| information_schema |

| mysql              |

| performance_schema |

| playground         |

| test               |

+--------------------+

[root@mariadb-galera-node3 ~]# cat /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    971e2a01-1093-11ec-992f-f77eb891b847

seqno:   -1

safe_to_bootstrap: 0

[root@mariadb-galera-node3 ~]#

So all Nodes showing created database ...

[root@mariadb-galera-node1 ~]# cat  /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    971e2a01-1093-11ec-992f-f77eb891b847

seqno:   -1

safe_to_bootstrap: 0

[root@mariadb-galera-node1 ~]#

[root@mariadb-galera-node2 ~]# cat /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    971e2a01-1093-11ec-992f-f77eb891b847

seqno:   -1

safe_to_bootstrap: 0

[root@mariadb-galera-node2 ~]#

[root@mariadb-galera-node3 ~]# cat /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    971e2a01-1093-11ec-992f-f77eb891b847

seqno:   -1

safe_to_bootstrap: 0

[root@mariadb-galera-node3 ~]#

                  

---------------------------------------------------------------------

 Step - 6 Testing Phase - 3 

[   --> First shutdown the mysql service in Node 1 

    --> Create the new database in

    --> Stop the Node 3 mysql service 

    --> Stop the Node 2 mysql serviStart the mysql service with 

        bootstrap command in Node 2

    --> Start the mysql service by simple command in Node 3

    --> Start the mysql service by simple command in Node 1 ]

 --------------------------------------------------------------------

[root@mariadb-galera-node1 ~]# service mysql stop

Redirecting to /bin/systemctl stop mysql.service

[root@mariadb-galera-node1 ~]# cat  /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    971e2a01-1093-11ec-992f-f77eb891b847

seqno:   13

safe_to_bootstrap: 0

[root@mariadb-galera-node1 ~]#

[root@mariadb-galera-node2 ~]# mysql -u root -p -e "CREATE DATABASE failover_full;"

Enter password:

[root@mariadb-galera-node2 ~]#

[root@mariadb-galera-node3 ~]# service mysql stop

Redirecting to /bin/systemctl stop mysql.service

[root@mariadb-galera-node3 ~]# cat /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    971e2a01-1093-11ec-992f-f77eb891b847

seqno:   15

safe_to_bootstrap: 0

[root@mariadb-galera-node3 ~]#

[root@mariadb-galera-node2 ~]# service mysql stop

Redirecting to /bin/systemctl stop mysql.service

[root@mariadb-galera-node2 ~]# cat /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    971e2a01-1093-11ec-992f-f77eb891b847

seqno:   16

safe_to_bootstrap: 1

[root@mariadb-galera-node2 ~]#

[root@mariadb-galera-node2 ~]# sudo galera_new_cluster

[root@mariadb-galera-node2 ~]# cat /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    971e2a01-1093-11ec-992f-f77eb891b847

seqno:   -1

safe_to_bootstrap: 1

[root@mariadb-galera-node2 ~]#

[root@mariadb-galera-node1 ~]# service mysql start

Redirecting to /bin/systemctl start mysql.service

[root@mariadb-galera-node1 ~]# cat  /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    971e2a01-1093-11ec-992f-f77eb891b847

seqno:   -1

safe_to_bootstrap: 0

[root@mariadb-galera-node1 ~]#

[root@mariadb-galera-node3 ~]# service mysql start

Redirecting to /bin/systemctl start mysql.service

[root@mariadb-galera-node3 ~]# cat /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    971e2a01-1093-11ec-992f-f77eb891b847

seqno:   -1

safe_to_bootstrap: 0

[root@mariadb-galera-node3 ~]#

Verify in all 3 nodes having failover_full database or not ..

[root@mariadb-galera-node1 ~]# mysql -u root -p -e "show databases;"

Enter password:

+--------------------+

| Database           |

+--------------------+

| failover           |

| failover_full      |

| information_schema |

| mysql              |

| performance_schema |

| playground         |

| test               |

+--------------------+

[root@mariadb-galera-node1 ~]#

[root@mariadb-galera-node2 ~]# mysql -u root -p -e "show databases;"

Enter password:

+--------------------+

| Database           |

+--------------------+

| failover           |

| failover_full      |

| information_schema |

| mysql              |

| performance_schema |

| playground         |

| test               |

+--------------------+

[root@mariadb-galera-node2 ~]#

[root@mariadb-galera-node3 ~]# mysql -u root -p -e "show databases;"

Enter password:

+--------------------+

| Database           |

+--------------------+

| failover           |

| failover_full      |

| information_schema |

| mysql              |

| performance_schema |

| playground         |

| test               |

+--------------------+

[root@mariadb-galera-node3 ~]#

---------------------------------------------------------------------

 Step - 7 Testing Phase - 4 

    [ --> Kill the mysql process in Node 2

      --> Create the new database , table ,insert some data in Node1  

      --> Verify the new database , new table and data in Node 3

      --> Start the Node 2 mysql service 

      --> Verify the new database ,new table and data in Node 2 

      --> Verify all nodes of /var/lib/mysql/grastate.dat ]

---------------------------------------------------------------------

[root@mariadb-galera-node2 ~]# ps -ef | grep -i mysql

mysql       4945       1  0 00:48 ?        00:00:00 /usr/sbin/mariadbd --wsrep_start_position=971e2a01-1093-11ec-992f-f77eb891b847:20

root        5103    4787  0 00:49 pts/0    00:00:00 grep --color=auto -i mysql

[root@mariadb-galera-node2 ~]# kill -9 4945

[root@mariadb-galera-node2 ~]# ps -ef | grep -i mysql

root        5106    4787  0 00:49 pts/0    00:00:00 grep --color=auto -i mysql

[root@mariadb-galera-node2 ~]#

[root@mariadb-galera-node1 ~]# mysql -u root -p -e "CREATE DATABASE failover_switch;"

Enter password:

[root@mariadb-galera-node1 ~]# mysql -u root -p -e "CREATE TABLE failover_switch.emp(id int);"

Enter password:

[root@mariadb-galera-node1 ~]# mysql -u root -p -e "insert into failover_switch.emp values (1); insert into failover_switch.emp values (2);"

Enter password:

[root@mariadb-galera-node1 ~]#

[root@mariadb-galera-node3 ~]# mysql -u root -p -e "select * from failover_switch.emp;"

Enter password:

+------+

| id   |

+------+

|    1 |

|    2 |

+------+

[root@mariadb-galera-node3 ~]#

[root@mariadb-galera-node2 ~]# service mysql start

Redirecting to /bin/systemctl start mysql.service

[root@mariadb-galera-node2 ~]#

[root@mariadb-galera-node2 ~]# mysql -u root -p -e "select * from failover_switch.emp;"

Enter password:

+------+

| id   |

+------+

|    1 |

|    2 |

+------+

[root@mariadb-galera-node2 ~]#

[root@mariadb-galera-node1 ~]# cat  /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    971e2a01-1093-11ec-992f-f77eb891b847

seqno:   -1

safe_to_bootstrap: 0

[root@mariadb-galera-node1 ~]#

[root@mariadb-galera-node2 ~]# cat /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    971e2a01-1093-11ec-992f-f77eb891b847

seqno:   -1

safe_to_bootstrap: 0

[root@mariadb-galera-node2 ~]#

[root@mariadb-galera-node3 ~]# cat /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    971e2a01-1093-11ec-992f-f77eb891b847

seqno:   -1

safe_to_bootstrap: 0

[root@mariadb-galera-node3 ~]#

---------------------------------------------------------------------

Step - 8 All servers are down , how to start bootstrap or how to start MariaDB Galera Cluster ..etc

---------------------------------------------------------------------

Node – 1 :-

------------

[root@mariadb-galera-node1 ~]# service mysql stop

Redirecting to /bin/systemctl stop mysql.service

[root@mariadb-galera-node1 ~]# ps -ef | grep -i mysql

root        5176    4786  0 00:59 pts/0    00:00:00 grep --color=auto -i mysql

[root@mariadb-galera-node1 ~]# cat /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    971e2a01-1093-11ec-992f-f77eb891b847

seqno:   30

safe_to_bootstrap: 0

[root@mariadb-galera-node1 ~]#

Node – 2 :-

------------

[root@mariadb-galera-node2 ~]# service mysql stop

Redirecting to /bin/systemctl stop mysql.service

[root@mariadb-galera-node2 ~]# ps -ef | grep -i mysql

root        5459    4787  0 01:00 pts/0    00:00:00 grep --color=auto -i mysql

[root@mariadb-galera-node2 ~]# cat /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    971e2a01-1093-11ec-992f-f77eb891b847

seqno:   31

safe_to_bootstrap: 0

[root@mariadb-galera-node2 ~]#

Node – 3 :-

------------

[root@mariadb-galera-node3 ~]# service mysql stop

Redirecting to /bin/systemctl stop mysql.service

[root@mariadb-galera-node3 ~]# cat /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    971e2a01-1093-11ec-992f-f77eb891b847

seqno:   32

safe_to_bootstrap: 1

[root@mariadb-galera-node3 ~]#

Here all Servers are down. So based on grastate.dat file or " seqno " value having high in Node 3 , so , i have ran bootstrap command in Node 3 . Other Node 1 and Node 2 are i have started service with normal way..i.e " Service mysql start " . So after started all Nodes Services , i can see " seqno = -1 " That is all are in synch.

[root@mariadb-galera-node3 ~]# sudo galera_new_cluster

[root@mariadb-galera-node3 ~]# cat /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    971e2a01-1093-11ec-992f-f77eb891b847

seqno:   -1

safe_to_bootstrap: 0

[root@mariadb-galera-node3 ~]# ps -ef | grep -i mysql

mysql       5457       1  0 01:02 ?        00:00:00 /usr/sbin/mariadbd --wsrep-new-cluster --wsrep_start_position=971e2a01-1093-11ec-992f-f77eb891b847:32

root        5663    4764  0 01:03 pts/0    00:00:00 grep --color=auto -i mysql

[root@mariadb-galera-node3 ~]#

[root@mariadb-galera-node2 ~]# service mysql start

Redirecting to /bin/systemctl start mysql.service

[root@mariadb-galera-node2 ~]# cat /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    971e2a01-1093-11ec-992f-f77eb891b847

seqno:   -1

safe_to_bootstrap: 0

[root@mariadb-galera-node2 ~]# ps -ef | grep -i mysql

mysql       5614       1  0 01:02 ?        00:00:00 /usr/sbin/mariadbd --wsrep_start_position=971e2a01-1093-11ec-992f-f77eb891b847:31

root        5767    4787  0 01:03 pts/0    00:00:00 grep --color=auto -i mysql

[root@mariadb-galera-node2 ~]#

[root@mariadb-galera-node1 ~]# service mysql start

Redirecting to /bin/systemctl start mysql.service

[root@mariadb-galera-node1 ~]# cat /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    971e2a01-1093-11ec-992f-f77eb891b847

seqno:   -1

safe_to_bootstrap: 0

[root@mariadb-galera-node1 ~]# ps -ef | grep -i mysql

mysql       5324       1  0 01:03 ?        00:00:00 /usr/sbin/mariadbd --wsrep_start_position=971e2a01-1093-11ec-992f-f77eb891b847:30

root        5478    4786  0 01:03 pts/0    00:00:00 grep --color=auto -i mysql

[root@mariadb-galera-node1 ~]#

---------------------------------------------------------------------Step    -   9   Start / Stop / Restart the MariaDB Galera Clust     ----------------------------------------------------------------------

MariaDB Galera Cluster :-

Bootstrap Database & Galera on Seed Node

---------------------------------------------

Starting  MariaDB Nodes - Seed Node 

galera_new_cluster

Start Database & Galera Normally on Additional Nodes - When the first node is live, run the following command on the subsequent nodes: 

systemctl start mariadb      or   service mysql start

systemctl stop mariadb       or   service mysql stop

systemctl status mariadb     or   service mysql status

Warning: Never bootstrap when you want to reconnect a node to an existing cluster, and NEVER run bootstrap on more than one node.

 

---------------------------------------------------------------------Step - 10  Additional information about binlog , read_only and gtid variables

---------------------------------------------------------------------

MariaDB [(none)]> system hostname ;  show variables like '%gtid%'; show variables like '%log_bin%'; show variables like 'read_only'; status; show processlist;

mariadb-galera-node1

+-------------------------+-------+

| Variable_name           | Value |

+-------------------------+-------+

| gtid_binlog_pos         |       |

| gtid_binlog_state       |       |

| gtid_cleanup_batch_size | 64    |

| gtid_current_pos        |       |

| gtid_domain_id          | 0     |

| gtid_ignore_duplicates  | OFF   |

| gtid_pos_auto_engines   |       |

| gtid_seq_no             | 0     |

| gtid_slave_pos          |       |

| gtid_strict_mode        | OFF   |

| last_gtid               |       |

| wsrep_gtid_domain_id    | 0     |

| wsrep_gtid_mode         | OFF   |

| wsrep_gtid_seq_no       | 0     |

+-------------------------+-------+

14 rows in set (0.001 sec)

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| log_bin                         | OFF   |

| log_bin_basename                |       |

| log_bin_compress                | OFF   |

| log_bin_compress_min_len        | 256   |

| log_bin_index                   |       |

| log_bin_trust_function_creators | OFF   |

| sql_log_bin                     | ON    |

+---------------------------------+-------+

7 rows in set (0.001 sec)

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| read_only     | OFF   |

+---------------+-------+

1 row in set (0.001 sec)

--------------

mysql  Ver 15.1 Distrib 10.5.12-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          11

Current database:

Current user:           root@localhost

SSL:                    Not in use

Current pager:          stdout

Using outfile:          ''

Using delimiter:        ;

Server:                 MariaDB

Server version:         10.5.12-MariaDB MariaDB Server

Protocol version:       10

Connection:             Localhost via UNIX socket

Server characterset:    utf8mb4

Db     characterset:    utf8mb4

Client characterset:    utf8

Conn.  characterset:    utf8

UNIX socket:            /var/lib/mysql/mysql.sock

Uptime:                 16 min 32 sec

Threads: 6  Questions: 26  Slow queries: 0  Opens: 20  Open tables: 13  Queries per second avg: 0.026

--------------

+----+-------------+-----------+------+---------+------+-------------

| Id | User        | Host      | db   | Command | Time | State              | Info             | Progress |

+----+-------------+-----------+------+---------+------+-------------

|  1 | system user |           | NULL | Sleep   |  991 | wsrep aborter idle | NULL             |    0.000 |

|  2 | system user |           | NULL | Sleep   |  991 | closing tables     | NULL             |    0.000 |

|  6 | system user |           | NULL | Sleep   |  989 | closing tables     | NULL             |    0.000 |

|  7 | system user |           | NULL | Sleep   |  989 | closing tables     | NULL             |    0.000 |

| 10 | system user |           | NULL | Sleep   |  989 | closing tables     | NULL             |    0.000 |

| 11 | root        | localhost | NULL | Query   |    0 | starting           | show processlist |    0.000 |

+----+-------------+-----------+------+---------+------+-------------

6 rows in set (0.000 sec)

MariaDB [(none)]> system hostname ;  show variables like '%gtid%'; show variables like '%log_bin%'; show variables like 'read_only'; status; show processlist;

mariadb-galera-node2

+-------------------------+-------+

| Variable_name           | Value |

+-------------------------+-------+

| gtid_binlog_pos         |       |

| gtid_binlog_state       |       |

| gtid_cleanup_batch_size | 64    |

| gtid_current_pos        |       |

| gtid_domain_id          | 0     |

| gtid_ignore_duplicates  | OFF   |

| gtid_pos_auto_engines   |       |

| gtid_seq_no             | 0     |

| gtid_slave_pos          |       |

| gtid_strict_mode        | OFF   |

| last_gtid               |       |

| wsrep_gtid_domain_id    | 0     |

| wsrep_gtid_mode         | OFF   |

| wsrep_gtid_seq_no       | 0     |

+-------------------------+-------+

14 rows in set (0.001 sec)

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| log_bin                         | OFF   |

| log_bin_basename                |       |

| log_bin_compress                | OFF   |

| log_bin_compress_min_len        | 256   |

| log_bin_index                   |       |

| log_bin_trust_function_creators | OFF   |

| sql_log_bin                     | ON    |

+---------------------------------+-------+

7 rows in set (0.001 sec)

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| read_only     | OFF   |

+---------------+-------+

1 row in set (0.001 sec)

--------------

mysql  Ver 15.1 Distrib 10.5.12-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          11

Current database:

Current user:           root@localhost

SSL:                    Not in use

Current pager:          stdout

Using outfile:          ''

Using delimiter:        ;

Server:                 MariaDB

Server version:         10.5.12-MariaDB MariaDB Server

Protocol version:       10

Connection:             Localhost via UNIX socket

Server characterset:    utf8mb4

Db     characterset:    utf8mb4

Client characterset:    utf8

Conn.  characterset:    utf8

UNIX socket:            /var/lib/mysql/mysql.sock

Uptime:                 15 min 53 sec

hreads: 6  Questions: 18  Slow queries: 0  Opens: 20  Open tables: 13  Queries per second avg: 0.018

--------------

+----+-------------+-----------+------+---------+------+-------------

| Id | User        | Host      | db   | Command | Time | State              | Info             | Progress |

+----+-------------+-----------+------+---------+------+-------------

|  1 | system user |           | NULL | Sleep   |  952 | wsrep aborter idle | NULL             |    0.000 |

|  2 | system user |           | NULL | Sleep   |  952 | closing tables     | NULL             |    0.000 |

|  6 | system user |           | NULL | Sleep   |  950 | closing tables     | NULL             |    0.000 |

|  7 | system user |           | NULL | Sleep   |  950 | closing tables     | NULL             |    0.000 |

| 10 | system user |           | NULL | Sleep   |  950 | wsrep applier idle | NULL             |    0.000 |

| 11 | root        | localhost | NULL | Query   |    0 | starting           | show processlist |    0.000 |

+----+-------------+-----------+------+---------+------+-------------

6 rows in set (0.000 sec)

MariaDB [(none)]> system hostname ;  show variables like '%gtid%'; show variables like '%log_bin%'; show variables like 'read_only'; status; show processlist;

mariadb-galera-node3

+-------------------------+-------+

| Variable_name           | Value |

+-------------------------+-------+

| gtid_binlog_pos         |       |

| gtid_binlog_state       |       |

| gtid_cleanup_batch_size | 64    |

| gtid_current_pos        |       |

| gtid_domain_id          | 0     |

| gtid_ignore_duplicates  | OFF   |

| gtid_pos_auto_engines   |       |

| gtid_seq_no             | 0     |

| gtid_slave_pos          |       |

| gtid_strict_mode        | OFF   |

| last_gtid               |       |

| wsrep_gtid_domain_id    | 0     |

| wsrep_gtid_mode         | OFF   |

| wsrep_gtid_seq_no       | 0     |

+-------------------------+-------+

14 rows in set (0.001 sec)

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| log_bin                         | OFF   |

| log_bin_basename                |       |

| log_bin_compress                | OFF   |

| log_bin_compress_min_len        | 256   |

| log_bin_index                   |       |

| log_bin_trust_function_creators | OFF   |

| sql_log_bin                     | ON    |

+---------------------------------+-------+

7 rows in set (0.001 sec)

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| read_only     | OFF   |

+---------------+-------+

1 row in set (0.001 sec)

--------------

mysql  Ver 15.1 Distrib 10.5.12-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          10

Current database:

Current user:           root@localhost

SSL:                    Not in use

Current pager:          stdout

Using outfile:          ''

Using delimiter:        ;

Server:                 MariaDB

Server version:         10.5.12-MariaDB MariaDB Server

Protocol version:       10

Connection:             Localhost via UNIX socket

Server characterset:    utf8mb4

Db     characterset:    utf8mb4

Client characterset:    utf8

Conn.  characterset:    utf8

UNIX socket:            /var/lib/mysql/mysql.sock

Uptime:                 15 min 56 sec

Threads: 6  Questions: 16  Slow queries: 0  Opens: 20  Open tables: 13  Queries per second avg: 0.016

--------------

+----+-------------+-----------+------+---------+------+-------------

| Id | User        | Host      | db   | Command | Time | State              | Info             | Progress |

+----+-------------+-----------+------+---------+------+-------------

|  1 | system user |           | NULL | Sleep   |  956 | wsrep aborter idle | NULL             |    0.000 |

|  2 | system user |           | NULL | Sleep   |  956 | closing tables     | NULL             |    0.000 |

|  5 | system user |           | NULL | Sleep   |  956 | wsrep applier idle | NULL             |    0.000 |

|  6 | system user |           | NULL | Sleep   |  956 | wsrep applier idle | NULL             |    0.000 |

|  9 | system user |           | NULL | Sleep   |  956 | closing tables     | NULL             |    0.000 |

| 10 | root        | localhost | NULL | Query   |    0 | starting           | show processlist |    0.000 |

+----+-------------+-----------+------+---------+------+-------------

6 rows in set (0.000 sec)

Conclusion :-

----------------------

At this point, we have a working three-node Galera test cluster configured. If you plan on using a Galera cluster in a production situation, it’s recommended that you begin with no fewer than five nodes.

Before production use, you may want to take a look at some of the other state snapshot transfer (SST) agents like XtraBackup, which allows you to set up new nodes very quickly and without large interruptions to your active nodes. This does not affect the actual replication, but is a concern when nodes are being initialized.

Have a Nice Day ...........