Lewati ke konten
Kembali ke Blog
Cluster High Availability MySQL

Setup MySQL Cluster untuk High Availability: Tutorial NDB Cluster Lengkap

Hendra Wijaya
· · 7 menit baca

Bagi aplikasi mission-critical, single point of failure adalah tidak bisa diterima. Setelah mengimplementasikan MySQL Cluster untuk beberapa client di industri telekomunikasi dan perbankan, saya akan berbagi setup yang terbukti reliable untuk high availability.

Arsitektur MySQL NDB Cluster

1. Node Types

Management Node (ndb_mgmd):
– Mengelola konfigurasi cluster
– Monitoring node status
– Tidak menyimpan data
– Minimum 1, recommended 2 untuk HA

Data Node (ndbd/ndbmtd):
– Menyimpan data (partitioned)
– Replicated (2+ replicas)
– Minimal 2 node (1 node group)
– Recommended 4 node (2 node groups)

SQL Node (mysqld):
– Akses point untuk aplikasi
– Standard MySQL server dengan NDB engine
– Bisa multiple untuk load balancing

2. Minimum Requirements

Untuk production HA:
– 2 Management Nodes
– 4 Data Nodes (2 node groups)
– 2+ SQL Nodes
– Minimal 4GB RAM per Data Node
– Network: Low latency (< 1ms) antar node
– Disk: SSD untuk optimal performance

Persiapan Server

1. Network Setup

IP Assignment:
– Management Node 1: 192.168.1.10
– Management Node 2: 192.168.1.11
– Data Node 1: 192.168.1.20
– Data Node 2: 192.168.1.21
– Data Node 3: 192.168.1.22
– Data Node 4: 192.168.1.23
– SQL Node 1: 192.168.1.30
– SQL Node 2: 192.168.1.31

2. System Preparation (Semua Node)

# Update sistem
sudo apt update && sudo apt upgrade -y

Install dependencies


sudo apt install -y libaio1 libncurses5


Disable SELinux/AppArmor untuk testing


sudo systemctl stop apparmor
sudo systemctl disable apparmor


Setup hosts file


sudo tee -a /etc/hosts <<EOF
192.168.1.10 mgm-node1
192.168.1.11 mgm-node2
192.168.1.20 data-node1
192.168.1.21 data-node2
192.168.1.22 data-node3
192.168.1.23 data-node4
192.168.1.30 sql-node1
192.168.1.31 sql-node2
EOF


Setup firewall


sudo ufw allow from 192.168.1.0/24

3. Download MySQL Cluster

# Download MySQL Cluster
wget https://dev.mysql.com/get/Downloads/MySQL-Cluster-8.0/mysql-cluster-gpl-8.0.35-linux-glibc2.28-x86_64.tar.gz

Extract


sudo tar -xzvf mysql-cluster-gpl-8.0.35-linux-glibc2.28-x86_64.tar.gz
sudo mv mysql-cluster-gpl-8.0.35-linux-glibc2.28-x86_64 /usr/local/mysql


Setup symlink


sudo ln -s /usr/local/mysql/bin/* /usr/bin/

Setup Management Node

1. Management Node 1 (192.168.1.10)

Buat direktori:

sudo mkdir -p /var/lib/mysql-cluster
sudo mkdir -p /usr/local/mysql/bin
sudo mkdir -p /var/log/mysql

Buat config.ini:

sudo nano /var/lib/mysql-cluster/config.ini

[ndbd default]
NoOfReplicas=2
DataMemory=2G
IndexMemory=512M
MaxNoOfConcurrentOperations=100000
MaxNoOfLocalOperations=110000
StringMemory=25
MaxNoOfTables=4096
MaxNoOfOrderedIndexes=2048
MaxNoOfUniqueHashIndexes=512
MaxNoOfAttributes=24576
MaxNoOfTriggers=14336
DiskCheckpointSpeed=10M
DiskCheckpointSpeedInRestart=100M
FragmentLogFileSize=256M
NoOfFragmentLogFiles=6
RedoBuffer=64M
MaxNoOfExecutionThreads=2
TransactionDeadlockDetectionTimeout=5000

[tcp default]
AllowUnresolvedHostnames=1
SendBufferMemory=4M
ReceiveBufferMemory=4M


[ndb_mgmd]
NodeId=1
Hostname=192.168.1.10
DataDir=/var/lib/mysql-cluster
PortNumber=1186


[ndb_mgmd]
NodeId=2
Hostname=192.168.1.11
DataDir=/var/lib/mysql-cluster
PortNumber=1186


[ndbd]
NodeId=3
Hostname=192.168.1.20
DataDir=/var/lib/mysql-data


[ndbd]
NodeId=4
Hostname=192.168.1.21
DataDir=/var/lib/mysql-data


[ndbd]
NodeId=5
Hostname=192.168.1.22
DataDir=/var/lib/mysql-data


[ndbd]
NodeId=6
Hostname=192.168.1.23
DataDir=/var/lib/mysql-data


[mysqld]
NodeId=7
Hostname=192.168.1.30


[mysqld]
NodeId=8
Hostname=192.168.1.31


[api]
NodeId=9
Hostname=192.168.1.30


[api]
NodeId=10
Hostname=192.168.1.31

2. Copy config ke Management Node 2

scp /var/lib/mysql-cluster/config.ini [email protected]:/var/lib/mysql-cluster/

3. Start Management Node 1

sudo ndb_mgmd --config-file=/var/lib/mysql-cluster/config.ini --reload

Verifikasi


sudo ndb_mgm -e show

Setup Data Nodes

1. Data Node Configuration (Semua Data Nodes)

Buat my.cnf:

sudo nano /etc/my.cnf

[mysqld]
ndbcluster
ndb-connectstring=192.168.1.10,192.168.1.11

[ndbd]
connect-string=192.168.1.10,192.168.1.11

Buat direktori data:

sudo mkdir -p /var/lib/mysql-data
sudo chown mysql:mysql /var/lib/mysql-data

2. Start Data Nodes

Di setiap data node:

sudo ndbd --initial

Verifikasi di management node


sudo ndb_mgm -e show

Output akan menunjukkan status “started” untuk semua data nodes.

Setup SQL Nodes

1. SQL Node Configuration

Buat my.cnf:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
# NDB Cluster Configuration
ndbcluster
ndb-connectstring=192.168.1.10,192.168.1.11

Server Configuration


server-id=1
bind-address=0.0.0.0


InnoDB untuk non-NDB tables


innodb_buffer_pool_size=512M


NDB specific


ndb-batch-size=524288
ndb-cluster-connection-pool=1
ndb-extra-logging=1
ndb-use-exact-count=0


Binary log untuk replication (jika perlu)


log_bin=mysql-bin
binlog_format=ROW

2. Start SQL Node

sudo systemctl restart mysql

Verifikasi cluster connection


mysql -u root -p -e "SHOW ENGINE NDB STATUS;"

Testing Cluster

1. Create NDB Table

-- Create database
CREATE DATABASE cluster_db;
USE cluster_db;

-- Create table dengan NDB engine
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_username (username)
) ENGINE=NDBCLUSTER;

2. Test Data Distribution

-- Insert data
INSERT INTO users (username, email) VALUES 
('user1', '[email protected]'),
('user2', '[email protected]'),
('user3', '[email protected]');

-- Verifikasi di semua SQL nodes
SELECT * FROM users;


-- Check partition info
SELECT 
TABLE_NAME,
PARTITION_NAME,
SUBPARTITION_NAME,
TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'cluster_db' AND TABLE_NAME = 'users';

3. Test Failover

Kill satu data node:

# Di data node 1
sudo killall -9 ndbd

Verifikasi cluster masih berjalan:

# Di management node
sudo ndb_mgm -e show
# Seharusnya menunjukkan node failed tapi cluster accessible

Insert data lagi untuk verifikasi:

INSERT INTO users (username, email) VALUES 
('user4', '[email protected]');

SELECT * FROM users;

Restart data node:

sudo ndbd

Monitoring Cluster

1. Management Console

# Interactive management
sudo ndb_mgm

Commands:


ndb_mgm> SHOW
ndb_mgm> ALL STATUS
ndb_mgm> 3 STATUS  # Status node 3
ndb_mgm> 3 RESTART -N  # Restart node 3
ndb_mgm> 3 STOP  # Stop node 3
ndb_mgm> EXIT

2. Cluster Status Query

-- Check cluster status
SHOW STATUS LIKE 'Ndb_cluster%';

-- Check node status
SELECT * FROM ndbinfo.nodes;


-- Check memory usage
SELECT 
node_id,
memory_type,
used,
total,
ROUND(used/total*100, 2) as usage_pct
FROM ndbinfo.memoryusage;

3. Monitoring Script

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

#!/bin/bash

ALERT_EMAIL="[email protected]"


Check cluster status


STATUS=$(ndb_mgm -e "show" 2>&1)


Check if any node is not connected


if echo "$STATUS" | grep -q "not connected|failed"; then
echo "MySQL Cluster Issue Detected:
$STATUS" | mail -s "[CRITICAL] MySQL Cluster Alert" $ALERT_EMAIL
fi


Check data memory usage


MEMORY=$(mysql -e "SELECT ROUND(SUM(used)/SUM(total)*100, 2) as pct FROM ndbinfo.memoryusage WHERE memory_type='Data memory';")


if (( $(echo "$MEMORY > 85" | bc -l) )); then
echo "MySQL Cluster Memory High: ${MEMORY}%" | mail -s "[WARNING] MySQL Cluster Memory" $ALERT_EMAIL
fi

Load Balancing SQL Nodes

1. HAProxy Setup

# Install HAProxy
sudo apt install haproxy

Konfigurasi haproxy.cfg:

frontend mysql_front
    bind *:3306
    default_backend mysql_back

backend mysql_back
balance roundrobin
option mysql-check user haproxy_check
server sql1 192.168.1.30:3306 check
server sql2 192.168.1.31:3306 check

Buat user monitoring:

-- Di setiap SQL node
CREATE USER 'haproxy_check'@'%' IDENTIFIED BY 'Check#Pass123';

2. Application Connection

# Connect ke HAProxy (192.168.1.100:3306)
conn = mysql.connector.connect(
    host='192.168.1.100',
    port=3306,
    user='app_user',
    password='App#Pass123'
)

Best Practices MySQL Cluster

1. Data Design

  • Selalu pakai PRIMARY KEY
  • Gunakan proper data types
  • Minimalis kolom BLOB/TEXT
  • Partition large tables

2. Configuration

  • Minimal 2 replicas (NoOfReplicas=2)
  • Monitor memory usage
  • Backup regular dari SQL nodes
  • Test failover secara rutin

3. Network

  • Dedicated network untuk cluster
  • Low latency antar node
  • Redundant network paths
  • Monitor network partition

Troubleshooting

1. Node Not Starting

# Check logs
tail -f /var/lib/mysql-data/ndb_3_out.log

Reset dengan initial (hati-hati!)


sudo ndbd --initial

2. Split-Brain Scenario

# Stop semua management nodes
# Start satu management node sebagai arbitrator
ndb_mgmd --config-file=/var/lib/mysql-cluster/config.ini --reload

Start data nodes satu per satu

3. Memory Exhaustion

-- Check memory usage
SELECT * FROM ndbinfo.memoryusage;

-- Increase di config.ini dan rolling restart
DataMemory=4G
IndexMemory=1G

Kesimpulan

MySQL NDB Cluster menyediakan true high availability dengan:
– No single point of failure
– Automatic failover
– Horizontal scalability
– Synchronous replication

Dengan setup yang tepat, Anda mendapatkan 99.999% uptime untuk mission-critical applications.

Ditulis oleh

Hendra Wijaya
Artikel Sebelumnya Instagram Stories Strategy 2026: Cara Mendapatkan Engagement Tertinggi Artikel Selanjutnya Strategi Traffic Organik untuk AdSense: SEO dan Content Marketing

Artikel Terkait

Cara Install MySQL di Windows 10 dan 11: Panduan Lengkap Server Setup

Februari 3, 2026

Cara Rename Database, Tabel, dan Column di MySQL dengan Aman

Februari 3, 2026

MySQL Data Types: Panduan Lengkap Memilih Tipe Data yang Tepat

Februari 3, 2026

Tinggalkan Komentar

Email tidak akan ditampilkan.