MySQL 8 Galera Cluster Installation Using Percona XtraDB Cluster

MySQL Galera Cluster Introduction

 

MySQL Galera cluster is the common solution for MySQL high availability and bring the highest database high viability rate 99.99.. .
As appose to MySQL replication where the application is working with one master and all replicas are being send to slaves is asynchronously manner any master crash will lead to downtime and data loss until one of the slaves will be promoted to be master .
In addition 

  1. Since replication is being done asynchronously it might be that the slave is not fully sync which can cause addition permanent data loss.
  2. All replicas are not getting new data and data become stale until they are joined to the new operational master.
  3. All of this requires manual, fast and exact work under high pressure when production is down and there is loss of service and money .

Image 1 – MySQL replication cluster when Master Fails 

In Galera Cluster all above issues are being solved out of the box.
Galera cluster offer master – master ring topology where all nodes can actively write and read data as all replication is being done synchronously, every data change should be acknowledged by all nodes until data is being approved to be written. Every cluster must consist of odd number of nodes (starting from 3,5,7 etc.) so for each data conflict or unresponsive server the cluster is making vote between all nodes to decide which node should be rejected as in every democracy the majority wins and the cluster continue with the healthy node.
The broken node needs to be fixed and rejoined to the cluster.
there are 2 types of node Sync when joining or rejoining node to the cluster:

 

  1. SSTState Snapshot Transfer – In this case the joined node is doing full sync automatically with one of the other nodes (donor node) and copy all data to its data directory. Once done the node becomes available for new requests .
  2. ISTIncremental state transfer – In this case the node might be down for some time and when its startup it takes incremental data changes from one of the other nodes cache. This is the fastest way and it’s important to define the parameter gcache.size quite big enough so if the server was down for several minutes it will sync with other nodes via the cache and not do full SST Sync .

Image 2 – Galera Cluster is a Ring Topology where each node talks with all other nodes. its masterless 

Note – Galera Cluster is not a sharding solution, all data is being copied to all nodes, so every node has the same data copy. Galera Cluster scale reads very well but not writes.

Good Use Cases to Use Galera Cluster 

  1. Heavy OLTP systems – many small read and writes  
  2. Web and Mobile application with 10,000s active users.

Galera cluster downsides 

  1. Writes are little bit slower (5-10% overhead) than regular master-slave topology as every data change must be approved by all nodes 
  2. This solution is not good to heavy writing application which are writing huge amount  of data constantly (Like DWH or Analytics systems )
  3. Locks are not supported as they lock can be done only in local instance and not in remote instances 
  4. Every table must have a primary key and use the InnoDB transactional engine.

 

What is Percona XtraDB Cluster

Percona develop open-source database solutions based on MySQL, PostgreSQL, MongoDB and RocksDB.

Their solution is compatible with the base open-source solution with many extras that make their solution even better.

Their MySQL cluster solution XtraDB Cluster is based on Galera Cluster with some safety belts that make their solution more stable and easier to manage.

Install MySQL Percona XtraDB Cluster Step by Step 

The Installation Will be on Ubuntu 20

  1. Update the system:
    sudo apt update

  2. Install the necessary packages:
    sudo apt install -y wget gnupg2 lsb-release curl

  3. Download the repository package:
    wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb

  4. Install the package with dpkg :
    sudo dpkg -i percona-release_latest.generic_all.deb

  5. Refresh the local cache to update the package information:
    sudo apt update

  6. Enable the release repository for Percona XtraDB Cluster:
    sudo percona-release setup pxc80

  7. Install the cluster:
    sudo apt install -y percona-xtradb-cluster

  8. Setup Galera Cluster parameters in my.cnf in the first node:

  •  Cluster name
    wsrep_cluster_name=xtradb-cluster

  • The Location of Galera extension library
    wsrep_provider=/usr/lib/galera4/libgalera_smm.so

  • The Nodes IP’s or server names that are part of Galera Cluster
    wsrep_cluster_address=gcomm://10.10.10.1,10.10.10.2,10.10.10.3

  • Set Galera Cache Size to do incremental IST rather than SST
    wsrep_provider_options          = “gcache.size=2G”

  • Galera cluster support on ROW Binlog format
    binlog_format=ROW

  • The Node IP
    wsrep_node_address =10.10.10.1

  • Node Name
    wsrep_node_name=node-01

  • Galera State Transfer Method – SST . Using Percona xtrabackup
    wsrep_sst_method=xtrabackup-v2

  • Disable data encryption between nodes  (default is on and should be used in prod)
    pxc_encrypt_cluster_traffic = OFF

 

  1. Bootstrap first node 

systemctl start mysql@bootstrap.service

 

  1. Connect to the database and check node and cluster status
    show status like ‘wsrep%’ and check these 2 parameters:

    +—————————+—————+

| Variable_name             | Value      |

+—————————+—————+

| wsrep_local_state_comment | Synced     |

| wsrep_incoming_addresses  |10.10.10.1:3306 |

+—————————+—————-+

  1. Copy galera parameters to other nodes and replace wsrep_node_name and wsrep_node_address with the node details

  2. start the second node. once it’s up. Connect to the database and check the cluster and node status . you should be to see that it joined to the cluster
    show status like ‘wsrep%

    +—————————+—————+

| Variable_name             | Value      |

+—————————+—————+

| wsrep_local_state_comment | Synced     |

| wsrep_incoming_addresses  |10.10.10.1:3306, 10.10.10.2:3306  |

+—————————+—————-+

  1. once the second node is up. Start the third node and check its status as well.

    +—————————+—————+

| Variable_name             | Value      |

+—————————+—————+

| wsrep_local_state_comment | Synced     |

| wsrep_incoming_addresses  |10.10.10.1:3306, 10.10.10.2:3306, 10.10.10.3:3306  |

  1. +—————————+—————-+

  2. Done

     You can read more about MySQL Galera Cluster Troubleshooting 

For More Details and MySQL Consultancy Contact us, We will be more than happy to assist

Share:

More Posts

Hadoop

 Apache Hadoop is free open Source software for massive distributed computation and Big Data storage. It can store

Read More »

We Are Here For You :

Or fill in your details and we will contact you ASAP: