Arsitektur High Availability Database Cluster: MySQL Group Replication & ProxySQL

1. Konteks Industri & Analisis Akar Masalah

Dalam lanskap arsitektur sistem terdistribusi modern, database relasional tradisional seperti MySQL sering kali menjadi titik tunggal kegagalan (Single Point of Failure / SPOF) serta bottleneck performa utama ketika menghadapi lonjakan transaksi konkurensi tinggi. Pada era web berskala besar, di mana aplikasi backend web (PHP, Go, Node.js), server web (Nginx, Caddy), dan arsitektur microservices menuntut ketersediaan tinggi (High Availability / HA) 99.999%, pendekatan Standalone Database dengan replikasi Master-Slave konvensional sudah tidak lagi memadai.

Replikasi Master-Slave asinkron atau semi-sinkron tradisional memiliki kelemahan mendasar: potensi kehilangan data (data loss) saat terjadi failover manual, latensi replikasi yang tidak terprediksi, dan kompleksitas manajemen routing koneksi aplikasi. Ketika node Master mengalami crash, administrator harus melakukan intervensi manual untuk mempromosikan Slave, mengubah konfigurasi koneksi aplikasi, atau menghadapi masalah split-brain jika pemulihan dilakukan secara prematur.

Kondisi Sebelum Penerapan (Traditional Setup):

• Arsitektur mengandalkan 1 Master dan N Slaves.
• Failover bersifat manual atau mengandalkan skrip eksternal rentan rasio race condition.
• Aplikasi harus membedakan secara eksplisit koneksi Read dan Write pada level kode, menyebabkan tight coupling.
• Waktu pemulihan (Recovery Time Objective / RTO) mencapai puluhan menit hingga jam, serta Target Pemulihan Titik Data (Recovery Point Objective / RPO) tidak nol.

Kondisi Sesudah Penerapan (MySQL Group Replication + ProxySQL):

• Klaster multi-node berbasis protokol Paxos (XCom) yang menjamin konsistensi data kuat (strong consistency).
• Failover otomatis terjadi dalam hitungan detik tanpa kehilangan data (RPO = 0 untuk Single-Primary mode).
• Aplikasi terhubung secara transparan ke satu alamat virtual/ProxySQL, yang secara otomatis merutekan kueri SELECT ke klaster Read dan INSERT/UPDATE/DELETE ke Write node.
• Skalabilitas horizontal untuk pembacaan (read-scaling) dapat dilakukan secara instan dengan menambahkan node baru ke dalam grup.

[!IMPORTANT] Membangun klaster HA bukan sekadar memasang banyak server, melainkan memastikan konsistensi transaksional, isolasi jaringan yang andal, dan mekanisasi deteksi kegagalan yang presisi.

2. Konsep Inti & Deep Dive Arsitektur

Arsitektur High Availability yang tangguh ini menggabungkan dua pilar utama: MySQL Group Replication (MGR) di sisi penyimpanan data dan ProxySQL di sisi perantara jaringan (middleware).

MySQL Group Replication (MGR)

MGR memungkinkan pembuatan klaster database toleran terhadap kesalahan (fault-tolerant). MGR berjalan dalam dua mode utama:

1. Single-Primary Mode: Hanya satu node yang bertindak sebagai Primary (bisa menulis dan membaca), sementara node lainnya bertindak sebagai Secondary (hanya membaca). Mode ini menjamin tidak ada konflik write-write pada level basis data.
2. Multi-Primary Mode: Semua node dapat menerima operasi tulis secara bersamaan. Mode ini membutuhkan penanganan konflik yang ketat dan umumnya tidak disarankan untuk beban kerja transaksional kompleks karena potensi certifying conflicts yang tinggi.

Di balik layar, MGR menggunakan mesin konsensus berbasis Paxos (diimplementasikan melalui pustaka XCom) untuk memesan transaksi sebelum divalidasi dan diterapkan ke mesin penyimpanan InnoDB. Setiap transaksi melalui fase certification sebelum di-commit secara lokal.

ProxySQL

ProxySQL adalah high-performance SQL proxy berulir banyak (multi-threaded) yang bertindak sebagai lapisan abstraksi antara aplikasi dan klaster MySQL. ProxySQL memantau kesehatan klaster secara real-time, memeriksa status node melalui query health check, dan mendistribusikan lalu lintas berdasarkan aturan perutean (query rules) yang dikonfigurasi.

+-------------------------------------------------------------+
|                        Application Layer                    | 
|            (PHP / Go / Node.js / Python Services)           | 
+------------------------------+------------------------------+
                               | (SQL Traffic)
                               v
+-------------------------------------------------------------+
|                          ProxySQL                           |
|       (Query Routing, Connection Pooling, Failover)         |
+-------+------------------------------+--------------+-------+
        |                              |              |
        | (Writes)                     | (Reads)      | (Health Check)
        v                              v              v
+---------------+              +---------------+  +---------------+
|  MySQL Node 1 | <---XCom---> |  MySQL Node 2 |  |  MySQL Node 3 |
| (PRIMARY/RW)  |              | (SECONDARY/RO)|  | (SECONDARY/RO)|
+---------------+              +---------------+  +---------------+

3. Perbandingan Komparatif / Analisis Pendekatan

[!TIP] Gunakan MGR Single-Primary untuk aplikasi umum yang memiliki banyak operasi tulis transaksional guna menghindari transaction rollback akibat konflik sertifikasi pada Multi-Primary.

4. Prasyarat Sistem & Persiapan Lingkungan

Untuk membangun klaster produksi yang stabil, siapkan 3 node server Virtual Machine (atau Bare Metal) dengan spesifikasi minimal sebagai berikut:

• OS: Ubuntu 22.04 LTS / Debian 12 / Rocky Linux 9
• RAM: Minimal 4 GB per node
• CPU: Minimal 2 vCPU
• Jaringan: Jaringan lokal privat (Private Network / VLAN) dengan latensi < 2ms antar node.

Topologi IP Address:

• db1.internal: 10.0.1.11 (MySQL Node 1)
• db2.internal: 10.0.1.12 (MySQL Node 2)
• db3.internal: 10.0.1.13 (MySQL Node 3)
• proxysql.internal: 10.0.1.20 (ProxySQL & App Gateway)

Prasyarat Konfigurasi Sistem Operasi (Jalankan di semua node MySQL):

# Nonaktifkan Swap untuk performa InnoDB yang konsisten
sudo swapoff -a
sudo sed -i '/swap/s/^/#/' /etc/fstab

# Konfigurasi batas file descriptor sistem
echo "* soft nofile 65535" | sudo tee -a /etc/security/limits.conf
echo "* hard nofile 65535" | sudo tee -a /etc/security/limits.conf

5. Panduan Implementasi Langkah demi Langkah

Langkah 1: Instalasi MySQL Server 8.0 di Semua Node

sudo apt update
sudo apt install -y mysql-server
sudo systemctl stop mysql

Langkah 2: Konfigurasi MySQL (/etc/mysql/mysql.conf.d/mysqld.cnf)

Terapkan konfigurasi berikut pada ketiga node, sesuaikan server_id (1, 2, dan 3) serta report_host masing-masing.

[mysqld]
server_id = 1
port = 3306
bind-address = 0.0.0.0

# Pengaturan InnoDB
default_authentication_plugin = mysql_native_password
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1

# Konfigurasi Binary Log & Replikasi
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_checksum = NONE
log_slave_updates = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE

# Konfigurasi MySQL Group Replication
plugin_load_add = 'group_replication.so'
group_replication_group_name = "a8504380-6054-11ee-8c99-0242ac120002"
group_replication_start_on_boot = OFF
group_replication_local_address = "10.0.1.11:33061"
group_replication_group_seeds = "10.0.1.11:33061,10.0.1.12:33061,10.0.1.13:33061"
group_replication_bootstrap_group = OFF
group_replication_single_primary_mode = ON
group_replication_enforce_update_every_where_checks = OFF

(Catatan: Pada db2.internal ubah server_id = 2 dan group_replication_local_address = "10.0.1.12:33061". Begitu pula untuk db3)

Langkah 3: Menyalakan MySQL dan Bootstrap Klaster

Mulai layanan MySQL pada node pertama (db1):

sudo systemctl start mysql

Masuk ke MySQL shell di db1 dan bootstrap grup:

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

-- Buat pengguna replikasi untuk komunikasi antar node
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'StrongReplPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

Pada node kedua (db2) dan ketiga (db3), jalankan:

sudo systemctl start mysql

Masuk ke MySQL shell di db2 dan db3:

CHANGE MASTER TO MASTER_USER='repl_user', MASTER_PASSWORD='StrongReplPassword123!' FOR CHANNEL 'group_replication_applier';
START GROUP_REPLICATION;

Verifikasi status klaster:

SELECT * FROM performance_schema.replication_group_members;

Langkah 4: Instalasi dan Konfigurasi ProxySQL

Instal ProxySQL pada server khusus (10.0.1.20):

wget https://github.com/sysown/proxysql/releases/download/v2.5.5/proxysql_2.5.5-ubuntu22_amd64.deb
sudo dpkg -i proxysql_2.5.5-ubuntu22_amd64.deb
sudo systemctl start proxysql

Masuk ke antarmuka admin ProxySQL (Default port 6032, user: admin, pass: admin):

mysql -u admin -padmin -h 127.0.0.1 -P 6032

Konfigurasi Server Database ke dalam ProxySQL:

-- Masukkan node ke dalam hostgroup
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES 
(1, '10.0.1.11', 3306),
(1, '10.0.1.12', 3306),
(1, '10.0.1.13', 3306);

-- Load dan Simpan ke Disk
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Konfigurasi Pengguna Database:

INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('app_user', '*4ACFE3202A5FF5CF467898FC58AAB1D615029441', 1);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

(Catatan: Hash password di atas adalah hasil PASSWORD('AppPassword123!'))

Aturan Perutean Kueri (Query Routing Rules):

-- Semua kueri tulis ke hostgroup 1 (Primary)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (1, 1, '^SELECT.*FOR UPDATE', 1, 1);

-- Semua kueri baca standar ke hostgroup 2 (Secondary / Readers)
-- Konfigurasikan hostgroup pembaca terpisah jika diperlukan, atau arahkan secara otomatis.
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

6. Bedah Kode & Penjelasan Detail Baris Kunci

• gtid_mode = ON & enforce_gtid_consistency = ON: Parameter esensial di MySQL 8.0+ yang memastikan setiap transaksi dilacak menggunakan Global Transaction Identifier. MGR sangat bergantung pada GTID untuk menyinkronkan posisi transaksi antar node.
• group_replication_single_primary_mode = ON: Menginstruksikan klaster untuk hanya memilih satu node sebagai penulis aktif. Hal ini menghilangkan risiko deadlock lintas node dan konflik sertifikasi transaksi.
• innodb_flush_log_at_trx_commit = 1: Menjamin bahwa setiap transaksi yang di-commit langsung ditulis ke media penyimpanan fisik (disk), mencegah potensi korupsi data saat terjadi pemadaman listrik mendadak.

7. Skenario Produksi & Studi Kasus Dunia Nyata

Dalam sistem e-commerce berskala besar dengan ribuan transaksi per detik (High Concurrency), pemisahan beban kerja (Read/Write Splitting) sangat menentukan kelangsungan operasional.

Aplikasi backend berbasis Go/PHP terhubung langsung ke ProxySQL pada port 6033 (port standar client traffic ProxySQL). Ketika terjadi lonjakan pencarian produk (Read-heavy), ProxySQL secara cerdas mendistribusikan kueri SELECT ke db2 dan db3 secara seimbang menggunakan algoritma Round-Robin atau Weight-based. Sementara itu, transaksi pembayaran (INSERT/UPDATE) secara otomatis dilewatkan ke node Primary (db1). Jika db1 mati, ProxySQL mendeteksi perubahan status node melalui monitor daemon internal dan mengalihkan traffic tulis ke node baru yang dipromosikan oleh MGR.

8. Common Pitfalls, Anti-Patterns & Solusi

1. Anti-Pattern: Menggunakan Multi-Primary Mode tanpa validasi aplikasi yang ketat terhadap konflik tulis serentak.
   • Solusi: Selalu gunakan Single-Primary Mode kecuali aplikasi Anda dirancang khusus dengan penanganan retry transaction berbasis optimistik konkurensi.
2. Pitfall: Mengabaikan latensi jaringan antar node MGR.
   • Solusi: Pastikan node klaster berada di Availability Zone atau Data Center yang sama dengan inter-node latency di bawah 2 milidetik. Latensi tinggi menyebabkan transaksi mengalami timeout pada fase sertifikasi Paxos.
3. Kesalahan Konfigurasi Firewall: Port komunikasi grup (33061) diblokir oleh UFW/iptables.
   • Solusi: Buka port 3306, 33061, dan 6032/6033 secara spesifik hanya untuk IP internal jaringan privat.

9. Benchmark, Metrik Kinerja & Tuning Performa

Pengujian performa klaster dapat dilakukan menggunakan perangkat sysbench untuk mensimulasikan beban transaksi OLTP.

# Inisialisasi data benchmark sysbench
sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-host=10.0.1.20 --mysql-port=6033 \
  --mysql-user=app_user --mysql-password=AppPassword123! \
  --mysql-db=test --tables=10 --table-size=100000 prepare

# Jalankan eksekusi benchmark
sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-host=10.0.1.20 --mysql-port=6033 \
  --mysql-user=app_user --mysql-password=AppPassword123! \
  --mysql-db=test --threads=64 --time=60 run

Parameter Tuning Lanjutan pada mysqld.cnf:

• innodb_buffer_pool_instances: Sesuaikan dengan jumlah inti CPU untuk mengurangi kontensi memori.
• table_open_cache: Tingkatkan nilainya (misal: 4096 atau lebih tinggi) untuk aplikasi dengan banyak tabel aktif.

10. Checklist Penerapan Produksi & Kesimpulan

Sebelum meluncurkan arsitektur ini ke lingkungan Production, pastikan seluruh item berikut terpenuhi:

• Semua node menggunakan sinkronisasi waktu NTP (Network Time Protocol) yang akurat.
• Konfigurasi cadangan (Backup Strategy) otomatis menggunakan xtrabackup berjalan secara berkala.
• Aturan Health Check ProxySQL telah diuji dengan mematikan node Primary secara paksa (Chaos Engineering).
• Pemantauan metrik (Prometheus + Grafana) terpasang untuk memantau status replikasi, penggunaan koneksi ProxySQL, dan kapasitas buffer pool.

Dengan mengintegrasikan MySQL Group Replication dan ProxySQL, Anda telah membangun fondasi infrastruktur database enterprise yang tangguh, otomatis, transparan, dan siap menghadapi skala bisnis global tanpa rasa khawatir.