Menu
📱 Lihat versi lengkap (non-AMP)
MariaDB Galera High Availability

Galera Cluster MariaDB: Setup Multi-Master Synchronous Replication dengan Zero Downtime

Editor: Hendra WIjaya
Update: 3 February 2026
Baca: 7 menit

Galera Cluster MariaDB: Setup Multi-Master Synchronous Replication dengan Zero Downtime

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="admin@example.com"
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.

Artikel Terkait

Bagikan:

Link Postingan: https://www.tirinfo.com/galera-cluster-mariadb-multi-master-replication/