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
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:
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
Galera cluster downsides
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.
The Installation Will be on Ubuntu 20
Update the system:
sudo apt update
Install the necessary packages:
sudo apt install -y wget gnupg2 lsb-release curl
Download the repository package:
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
Install the package with dpkg :
sudo dpkg -i percona-release_latest.generic_all.deb
Refresh the local cache to update the package information:
sudo apt update
Enable the release repository for Percona XtraDB Cluster:
sudo percona-release setup pxc80
Install the cluster:
sudo apt install -y percona-xtradb-cluster
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
Bootstrap first node
systemctl start mysql@bootstrap.service
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 |
+—————————+—————-+
Copy galera parameters to other nodes and replace wsrep_node_name and wsrep_node_address with the node details
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 |
+—————————+—————-+
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 |
+—————————+—————-+
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
Data Engineer Data Engineer’s role, responsibilities, skills , and what is the background they come from? More and
Data Warehouse is a data platform where organisations store all their information from external or internal sources .
MySQL 8 Galera Cluster High Availability In MySQL 8 Galera Cluster Installation we described how to set up
Apache Hadoop is free open Source software for massive distributed computation and Big Data storage. It can store
