Salin dan Bagikan
MySQL Cluster High Availability
Setup MySQL Cluster untuk High Availability: Tutorial NDB Cluster Lengkap - Panduan lengkap setup MySQL NDB Cluster untuk high availability dengan multiple node, failover …

Setup MySQL Cluster untuk High Availability: Tutorial NDB Cluster Lengkap

Setup MySQL Cluster untuk High Availability: Tutorial NDB Cluster Lengkap

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 root@192.168.1.11:/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', 'user1@example.com'),
('user2', 'user2@example.com'),
('user3', 'user3@example.com');

-- 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', 'user4@example.com');

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

# 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.

Artikel Terkait

Link Postingan : https://www.tirinfo.com/setup-mysql-cluster-high-availability/

Hendra WIjaya
Tirinfo
5 minutes.
3 February 2026