Galera Cluster adalah solusi high availability yang menyediakan true multi-master synchronous replication untuk MariaDB. Setelah mengimplementasikan Galera untuk beberapa enterprise clients dengan 99.999% uptime requirement, saya akan berbagi setup yang terbukti reliable.

Apa itu Galera Cluster?

Arsitektur Galera

Komponen:
– Galera Library: Synchronous replication plugin
– wsrep API: Interface antara MariaDB dan Galera
– Group Communication: Cert-based replication dengan voting

Benefits Galera

• True Multi-Master: Semua node bisa baca dan tulis
• Synchronous Replication: No lag, data selalu consistent
• Automatic Failover: Node failure tidak impact availability
• Read/Write Scaling: Distribusi load ke semua nodes
• No Single Point of Failure: Minimal 3 nodes direkomendasikan

Requirements

• Minimum 3 nodes (odd number untuk voting quorum)
• Low latency network (< 1ms antar nodes ideal)
• MariaDB 10.x dengan wsrep provider
• Linux OS (Ubuntu, CentOS, Debian supported)

Persiapan Infrastruktur

1. Network Setup

Node Configuration:

Node 1: 192.168.1.101 (Primary)
Node 2: 192.168.1.102
Node 3: 192.168.1.103

Network Ports:

3306: MySQL client connections
4567: Galera Cluster replication traffic, multicast (UDP and TCP)
4568: IST (Incremental State Transfer)
4444: SST (State Snapshot Transfer) - default

2. Firewall Configuration

UFW (Ubuntu):

# Allow MySQL
sudo ufw allow 3306/tcp
Allow Galera ports
sudo ufw allow 4567/tcp
sudo ufw allow 4567/udp
sudo ufw allow 4568/tcp
sudo ufw allow 4444/tcp
Allow dari cluster nodes
sudo ufw allow from 192.168.1.101
sudo ufw allow from 192.168.1.102
sudo ufw allow from 192.168.1.103

Firewalld (CentOS):

sudo firewall-cmd --permanent --add-port=3306/tcp
sudo firewall-cmd --permanent --add-port=4567/tcp
sudo firewall-cmd --permanent --add-port=4567/udp
sudo firewall-cmd --permanent --add-port=4568/tcp
sudo firewall-cmd --permanent --add-port=4444/tcp
sudo firewall-cmd --reload

Instalasi Galera Cluster

1. Install MariaDB dengan Galera

Ubuntu/Debian:

# Update dan install dependencies
sudo apt update
sudo apt install -y software-properties-common
Add MariaDB repository
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] 
https://mirrors.xtom.nl/mariadb/repo/10.11/ubuntu jammy main'
Install MariaDB server dengan Galera
sudo apt update
sudo apt install -y mariadb-server galera-4 rsync
Start MariaDB
sudo systemctl start mariadb
sudo systemctl enable mariadb

CentOS/RHEL:

# Add MariaDB repo
cat > /etc/yum.repos.d/mariadb.repo << EOF
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.11/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF
Install
sudo yum install -y MariaDB-server galera rsync
Start
sudo systemctl start mariadb
sudo systemctl enable mariadb

2. Secure Installation

# Run secure installation di semua nodes
sudo mysql_secure_installation
Set root password, remove anonymous, disable remote root, remove test db

3. Konfigurasi Galera

Create config di semua nodes:

sudo nano /etc/mysql/mariadb.conf.d/60-galera.cnf

Node 1 (192.168.1.101):

[mysqld]
# Galera Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
Cluster name
wsrep_cluster_name="my_galera_cluster"
Node addresses (semua nodes)
wsrep_cluster_address="gcomm://192.168.1.101,192.168.1.102,192.168.1.103"
This node's address
wsrep_node_address="192.168.1.101"
wsrep_node_name="node1"
SST (State Snapshot Transfer) method
wsrep_sst_method=rsync
SST authentication
wsrep_sst_auth=galera_user:galera_pass
Node is primary (bootstrap untuk node pertama)
wsrep_new_cluster=ON  # Hanya untuk node pertama saat inisialisasi
InnoDB settings untuk Galera
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=2G
Galera sync settings
wsrep_sync_wait=0
wsrep_slave_threads=4

Node 2 (192.168.1.102):

[mysqld]
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="my_galera_cluster"
wsrep_cluster_address="gcomm://192.168.1.101,192.168.1.102,192.168.1.103"
wsrep_node_address="192.168.1.102"
wsrep_node_name="node2"
wsrep_sst_method=rsync
wsrep_sst_auth=galera_user:galera_pass
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=2G
wsrep_sync_wait=0
wsrep_slave_threads=4

Node 3 (192.168.1.103):

[mysqld]
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="my_galera_cluster"
wsrep_cluster_address="gcomm://192.168.1.101,192.168.1.102,192.168.1.103"
wsrep_node_address="192.168.1.103"
wsrep_node_name="node3"
wsrep_sst_method=rsync
wsrep_sst_auth=galera_user:galera_pass
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=2G
wsrep_sync_wait=0
wsrep_slave_threads=4

4. Create SST User

-- Di semua nodes, create user untuk State Snapshot Transfer
mysql -u root -p
CREATE USER 'galera_user'@'%' IDENTIFIED BY 'galera_pass';
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON 
. TO 'galera_user'@'%';
FLUSH PRIVILEGES;

5. Bootstrap Cluster

Start Node 1 sebagai Primary:

# Stop MariaDB
sudo systemctl stop mariadb
Bootstrap cluster
sudo galera_new_cluster
Atau manual method:
sudo mysqld_bootstrap
Verifikasi
mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
Should show: 1

Start Node 2 dan 3:

# Di node 2 dan 3
sudo systemctl start mariadb
Tunggu SST selesai (bisa memakan waktu tergantung data size)
Check log: tail -f /var/log/mysql/error.log

6. Verify Cluster Status

-- Di node mana saja
SHOW STATUS LIKE 'wsrep%';
-- Key indicators:
-- wsrep_cluster_size: 3 (jumlah nodes)
-- wsrep_cluster_status: Primary
-- wsrep_connected: ON
-- wsrep_local_state_comment: Synced
-- wsrep_ready: ON

Testing Galera Cluster

1. Write di Node 1, Read di Node 2

-- Di Node 1 (192.168.1.101)
CREATE DATABASE galera_test;
USE galera_test;
CREATE TABLE test_data (
id INT AUTO_INCREMENT PRIMARY KEY,
node_name VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO test_data (node_name) VALUES ('Node 1');

-- Di Node 2 (192.168.1.102), tanpa delay
SELECT * FROM galera_test.test_data;
-- Should show row inserted from Node 1 immediately

2. Multi-Master Write Test

-- Di Node 2
INSERT INTO galera_test.test_data (node_name) VALUES ('Node 2');
-- Di Node 3
INSERT INTO galera_test.test_data (node_name) VALUES ('Node 3');
-- Di semua nodes
SELECT * FROM galera_test.test_data ORDER BY id;
-- Should show all 3 rows consistently

3. Failover Test

# Kill Node 1
sudo systemctl stop mariadb
Check cluster di Node 2 atau 3
echo "SHOW STATUS LIKE 'wsrep_cluster_size';" | mysql -u root -p
Should show: 2
Continue operations di remaining nodes
mysql -u root -p -e "INSERT INTO galera_test.test_data (node_name) VALUES ('After Node 1 down')"
Restart Node 1
sudo systemctl start mariadb
Check rejoin
mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size';"
Should return to: 3

Application Configuration

1. Load Balancing dengan ProxySQL

Install ProxySQL:

# Ubuntu/Debian
sudo apt install -y proxysql
CentOS/RHEL
sudo yum install -y proxysql

Configure ProxySQL:

-- Connect ke ProxySQL admin (port 6032)
mysql -u admin -p -h 127.0.0.1 -P 6032
-- Configure backend servers
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES
(1, '192.168.1.101', 3306, 100),
(1, '192.168.1.102', 3306, 100),
(1, '192.168.1.103', 3306, 100);
-- Configure users
INSERT INTO mysql_users (username, password, default_hostgroup) VALUES
('app_user', 'app_password', 1);
-- Load configuration
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;

2. Application Connection String

# Python example dengan failover
import mysql.connector
from mysql.connector import Error
config = {
'user': 'app_user',
'password': 'app_password',
'host': '192.168.1.101',  # Load balancer IP
'port': 6033,  # ProxySQL port
'database': 'myapp',
'failover': [
{'host': '192.168.1.102', 'port': 6033, 'priority': 2},
{'host': '192.168.1.103', 'port': 6033, 'priority': 3}
]
}
try:
conn = mysql.connector.connect(**config)
cursor = conn.cursor()
cursor.execute("SELECT 1")
result = cursor.fetchall()
print(result)
except Error as e:
print(f"Error: {e}")
finally:
if conn.is_connected():
cursor.close()
conn.close()

Maintenance dan Monitoring

1. Cluster Health Check

-- Check cluster status
SHOW STATUS LIKE 'wsrep_cluster_size';
SHOW STATUS LIKE 'wsrep_cluster_status';
SHOW STATUS LIKE 'wsrep_connected';
SHOW STATUS LIKE 'wsrep_local_state_comment';
SHOW STATUS LIKE 'wsrep_ready';
SHOW STATUS LIKE 'wsrep_flow_control_paused';

2. Flow Control Monitoring

-- Check if cluster is slowing down due to slow nodes
SHOW STATUS LIKE 'wsrep_flow_control_paused';
-- Should be close to 0.0 (0% means no throttling)
SHOW STATUS LIKE 'wsrep_flow_control_sent';
SHOW STATUS LIKE 'wsrep_flow_control_recv';

3. IST vs SST Monitoring

-- Check node status
SHOW STATUS LIKE 'wsrep_local_state_comment';
-- Synced: Normal operation
-- Joiner: Joining cluster
-- Donor/Desynced: Sending SST to joining node

4. Automated Health Check Script

sudo nano /usr/local/bin/check_galera.sh

#!/bin/bash
ALERT_EMAIL="[email protected]"
CLUSTER_SIZE=$(mysql -e "SHOW STATUS LIKE 'wsrep_cluster_size';" | awk 'NR==2{print $2}')
CLUSTER_STATUS=$(mysql -e "SHOW STATUS LIKE 'wsrep_cluster_status';" | awk 'NR==2{print $2}')
CONNECTED=$(mysql -e "SHOW STATUS LIKE 'wsrep_connected';" | awk 'NR==2{print $2}')
READY=$(mysql -e "SHOW STATUS LIKE 'wsrep_ready';" | awk 'NR==2{print $2}')
if [ "$CLUSTER_SIZE" -lt 2 ]; then
echo "Galera Alert: Cluster size is $CLUSTER_SIZE" | \
mail -s "[CRITICAL] Galera Cluster Size Low" $ALERT_EMAIL
fi
if [ "$CLUSTER_STATUS" != "Primary" ]; then
echo "Galera Alert: Cluster status is $CLUSTER_STATUS" | \
mail -s "[CRITICAL] Galera Cluster Not Primary" $ALERT_EMAIL
fi
if [ "$CONNECTED" != "ON" ]; then
echo "Galera Alert: Node not connected" | \
mail -s "[CRITICAL] Galera Node Disconnected" $ALERT_EMAIL
fi
if [ "$READY" != "ON" ]; then
echo "Galera Alert: Node not ready" | \
mail -s "[WARNING] Galera Node Not Ready" $ALERT_EMAIL
fi

Troubleshooting

1. Split-Brain Recovery

# Jika cluster split, pilih node dengan data terlengkap
# Di node dengan most recent data:
Stop MariaDB
sudo systemctl stop mariadb
Bootstrap sebagai new primary
sudo galera_new_cluster
Di nodes lain
sudo systemctl start mariadb

2. SST Failure

# Check disk space
df -h
Check permissions
ls -la /var/lib/mysql/
Manual SST dengan mysqldump
Di donor node:
mysqldump --all-databases > /tmp/full_dump.sql
Di joiner node:
mysql < /tmp/full_dump.sql
sudo systemctl start mariadb

3. Node Won’t Join

# Check logs
tail -f /var/log/mysql/error.log
Reset node
sudo systemctl stop mariadb
sudo rm -rf /var/lib/mysql/*
sudo systemctl start mariadb
Node akan melakukan SST otomatis

Best Practices

1. Minimum 3 nodes: Odd number untuk quorum voting
2. Low latency network: < 1ms antar nodes
3. Regular backups: SST tidak menggantikan backups
4. Monitor flow control: Pastikan tidak ada node yang slowing cluster
5. Test failover regularly: Verify automatic recovery works
6. Use load balancer: Application tidak direct connect ke nodes
7. Backup before major changes: Schema changes bisa trigger SST

Kesimpulan

Galera Cluster menyediakan:
– True multi-master synchronous replication
– Automatic failover tanpa data loss
– Linear read/write scaling
– No single point of failure

Dengan setup yang tepat, Anda memiliki database dengan 99.999% availability.