High Availability MySQL 8.4 Group Replication & Load Balancing dengan ProxySQL

1. Konteks Industri & Analisis Akar Masalah

Dalam lanskap arsitektur web modern, ketersediaan tinggi (High Availability) dan skalabilitas basis data bukan lagi sekadar fitur opsional, melainkan fondasi mutlak. Seiring lonjakan traffic yang didorong oleh aplikasi berbasis real-time, microservices, dan arsitektur cloud-native, kegagalan basis data (database downtime) dapat mengakibatkan kerugian finansial yang masif, kerusakan reputasi, serta pelanggaran Service Level Agreement (SLA).

Secara historis, arsitektur Traditional Primary-Replica (Master-Slave) sering kali menghadapi bottleneck parah pada operasi Write karena semua instruksi penulisan terpusat pada satu titik (Single Point of Failure). Ketika Primary node mengalami kegagalan (crash), proses failover manual maupun otomatis berbasis skrip eksternal kerap menyisakan jeda pemulihan yang lama, inkonsistensi data akibat replication lag, serta risiko split-brain di mana dua node mengklaim dirinya sebagai master secara bersamaan.

MySQL 8.4 LTS membawa evolusi signifikan dalam hal stabilitas, performa, dan pengelolaan Group Replication. Dipadukan dengan ProxySQL—sebuah high-performance, high-availability MySQL proxy—infrastruktur Anda dapat melakukan pemisahan Read/Write secara cerdas (intelligent query routing), manajemen koneksi (connection pooling), serta deteksi kegagalan instan tanpa intervensi manual.

Perbandingan Kondisi: Sebelum vs Sesudah Implementasi

2. Konsep Inti & Deep Dive Arsitektur

Arsitektur yang akan kita bangun mengombinasikan lapisan aplikasi (Backend Web, PHP-FPM, Node.js, Go), ProxySQL sebagai lapisan perantara cerdas (SQL-aware proxy), dan kluster MySQL 8.4 Group Replication yang terdiri dari tiga node mandiri.

+-------------------------------------------------------+
|               Application Tier (App Server)           | 
+-------------------------------------------------------+
                           |
             Sends SQL Queries (Port 6033/6034)
                           v
+-------------------------------------------------------+
|               ProxySQL Load Balancer                  |
|  - Query Rules Routing (Read -> Slave, Write -> Master) |
|  - Connection Pooling & Multiplexing                  |
+-------------------------------------------------------+
       /                   |                   \
      / (Read Routing)     | (Write Routing)    \ (Read Routing)
     v                     v                     v
+----------+         +----------+         +----------+
|  Node 1  | <-----> |  Node 2  | <-----> |  Node 3  |
| (Master) |         | (Slave)  |         | (Slave)  |
+----------+         +----------+         +----------+
   \                    (Group)                 /
    \------------------------------------------/
             MySQL 8.4 Group Replication
      (Paxos-based Distributed State Machine)

Cara Kerja di Balik Layar:

1. Aplikasi mengirim query ke ProxySQL pada port Read/Write (biasanya port 6033).
2. ProxySQL menganalisis jenis query menggunakan mesin reguler ekspresi (regex-based query rules). Query seperti SELECT akan diarahkan ke hostgroup pembacaan (Read Pool), sementara INSERT, UPDATE, DELETE, dan transaksi eksplisit diarahkan ke hostgroup penulisan (Write Pool).
3. MySQL Group Replication menggunakan protokol konsensus berbasis Paxos (Group Communication System / GCS) untuk menyinkronkan transaksi lintas node. Setiap transaksi yang dieksekusi pada node primer harus melalui fase sertifikasi (certification process) sebelum dikomit ke penyimpanan lokal setiap node anggota kluster.
4. Deteksi Kegagalan Otomatis: Jika node primary mengalami failure, GCS mendeteksi pemutusan node dan secara otomatis memicu pemilihan primer baru (primary election) tanpa kehilangan integritas data.

3. Perbandingan Komparatif / Analisis Pendekatan

Dalam membangun kluster basis data berskala enterprise, terdapat beberapa pendekatan arsitektur yang sering menjadi perdebatan di kalangan Principal Architect:

[!TIP]
Pilihlah ProxySQL + MySQL 8.4 Group Replication jika aplikasi Anda memiliki rasio Read-to-Write yang sangat tinggi (misal 90:10), memerlukan pemisahan query secara otomatis tanpa mengubah kode sumber aplikasi, serta menuntut pengelolaan ribuan koneksi konkuren melalui connection multiplexing.

4. Prasyarat Sistem & Persiapan Lingkungan

Sebelum melangkah ke tahap implementasi, pastikan Anda telah menyiapkan infrastruktur dengan spesifikasi minimum berikut:

Spesifikasi Lingkungan:

• OS: Ubuntu 24.04 LTS (Noble Numbat) atau Debian 12 Bookworm.
• Nodes:
  • 3x Virtual Machine untuk MySQL 8.4 Nodes (IP: 192.168.10.11, 192.168.10.12, 192.168.10.13)
  • 1x Virtual Machine untuk ProxySQL & Aplikasi (IP: 192.168.10.10)
• Hardware Minimum per Node: 2 vCPU, 4 GB RAM, 50 GB SSD NVMe.
• Koneksi Jaringan: Jaringan lokal berkecepatan tinggi (Gigabit LAN) dengan latensi antar node < 2ms (Mutlak diperlukan untuk replikasi Paxos Group Replication).

Verifikasi Awal Hostname & Resolusi DNS Lokal:

Pada setiap node, pastikan file /etc/hosts dikonfigurasi dengan benar:

192.168.10.10  proxysql-01
192.168.10.11  mysql-node-01
192.168.10.12  mysql-node-02
192.168.10.13  mysql-node-03

5. Panduan Implementasi Langkah demi Langkah

Langkah 1: Instalasi MySQL 8.4 di Semua Node MySQL

Jalankan perintah berikut pada mysql-node-01, mysql-node-02, dan mysql-node-03:

sudo apt update && sudo apt upgrade -y
sudo apt install -y wget lsb-release gnupg

# Unduh dan pasang repository MySQL resmi
wget https://dev.mysql.com/get/mysql-apt-config_0.8.30-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.30-1_all.deb
# Pilih MySQL 8.4 LTS pada prompt interaktif yang muncul

sudo apt update
sudo apt install -y mysql-server

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

Sesuaikan file konfigurasi pada setiap node. Berikut adalah contoh konfigurasi untuk mysql-node-01 (sesuaikan server_id dan alamat IP pada node lainnya):

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

# Pengaturan InnoDB
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

# Pengaturan Binary Log & GTID
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_format = ROW
log_slave_updates = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE

# Pengaturan Group Replication
plugin_load_add = 'group_replication.so'
loose_group_replication_group_name = "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11"
loose_group_replication_start_on_boot = OFF
loose_group_replication_local_address = "192.168.10.11:33061"
loose_group_replication_group_seeds = "192.168.10.11:33061,192.168.10.12:33061,192.168.10.13:33061"
loose_group_replication_bootstrap_group = OFF
loose_group_replication_single_primary_mode = ON
loose_group_replication_enforce_update_every_where_checks = OFF

[!IMPORTANT] Ubah nilai server_id menjadi 2 untuk mysql-node-02 dan 3 untuk mysql-node-03. Sesuaikan juga loose_group_replication_local_address sesuai IP masing-masing node.

Langkah 3: Inisialisasi Group Replication pada MySQL

Restart layanan MySQL pada semua node:

sudo systemctl restart mysql

Buat user replikasi khusus pada mysql-node-01 (yang nantinya akan disinkronkan otomatis melalui kluster):

CREATE USER 'repl_user'@'%' IDENTIFIED BY 'StrongReplPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

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

Verifikasi status kluster pada node pertama:

SELECT * FROM performance_schema.replication_group_members;

Selanjutnya, pada mysql-node-02 dan mysql-node-03, jalankan perintah berikut untuk bergabung ke dalam kluster:

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

Langkah 4: Instalasi dan Konfigurasi ProxySQL

Pindah ke mesin proxysql-01 (192.168.10.10):

wget https://repo.proxysql.com/ProxySQL/proxysql-2.x.x/repo_pub_key
sudo apt-key add repo_pub_key
echo "deb https://repo.proxysql.com/ProxySQL/proxysql-2.8.x/$(lsb_release -sc)/ ./" | sudo tee /etc/apt/sources.list.d/proxysql.list
sudo apt update && sudo apt install -y proxysql
sudo systemctl start proxysql

Akses antarmuka admin ProxySQL:

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

Jalankan konfigurasi SQL berikut di dalam console admin ProxySQL (mysql -u admin -padmin -h 127.0.0.1 -P 6032):

-- 1. Daftarkan MySQL Nodes ke dalam MySQL Servers
INSERT INTO mysql_servers(hostgroup_id, port, status, weight, server_is_readonly, max_connections) VALUES 
(10, 3306, 'ONLINE', 1, 0, 1000),
(20, 3306, 'ONLINE', 1, 1, 1000),
(10, 3306, 'ONLINE', 1, 1, 1000),
(20, 3306, 'ONLINE', 1, 1, 1000),
(10, 3306, 'ONLINE', 1, 1, 1000),
(20, 3306, 'ONLINE', 1, 1, 1000);

-- Perbarui IP aktual sesuai topologi
UPDATE mysql_servers SET hostname='192.168.10.11' WHERE hostgroup_id=10 AND port=3306 AND status='ONLINE';
UPDATE mysql_servers SET hostname='192.168.10.12' WHERE hostgroup_id=20 AND port=3306 AND status='ONLINE';
UPDATE mysql_servers SET hostname='192.168.10.13' WHERE hostgroup_id=20 AND port=3306 AND status='ONLINE';

-- 2. Konfigurasi Credential Aplikasi
INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('app_user', '*4ACFE3202A5FF5CF467898FC58AAB1D615029441', 20);

-- 3. Konfigurasi Query Rules untuk Pemisahan Read/Write
-- Segala bentuk perintah tulis (INSERT, UPDATE, DELETE, REPLACE) diarahkan ke hostgroup 10 (Master)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES 
(1, 1, '^SELECT.*FOR UPDATE', 10, 1),
(2, 1, '^(INSERT|UPDATE|DELETE|REPLACE|CREATE|DROP|ALTER|TRUNCATE)', 10, 1);

-- Segala bentuk SELECT biasa diarahkan ke hostgroup 20 (Slaves/Read Pool)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES 
(3, 1, '^SELECT', 20, 1);

-- Simpan konfigurasi ke disk dan muat ulang memori
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

6. Bedah Kode & Penjelasan Detail Baris Kunci

Mari kita bedah parameter krusial dalam file konfigurasi MySQL dan ProxySQL di atas:

• loose_group_replication_single_primary_mode = ON: Mengaktifkan mode Single-Primary di mana hanya satu node yang menerima operasi Write dalam satu waktu. Ini menghindari konflik deadlock multileader yang sering terjadi pada skenario Multi-Primary beban tinggi.
• gtid_mode = ON & enforce_gtid_consistency = ON: GTID (Global Transaction Identifier) adalah syarat wajib untuk Group Replication. Setiap transaksi diberi tanda pengenal unik global sehingga replikasi lintas node dapat melacak posisi transaksi secara presisi tanpa mengandalkan binary log file name dan position tradisional.
• hostgroup_id = 10 (Write Pool) dan hostgroup_id = 20 (Read Pool) pada ProxySQL: Pemisahan ini memungkinkan ProxySQL bertindak sebagai load balancer cerdas. Ketika aplikasi mengirimkan SELECT, ProxySQL mencocokkan pola dengan aturan ID 3 dan meneruskannya ke hostgroup 20 menggunakan algoritma Round-Robin atau Weight-based.

7. Skenario Produksi & Studi Kasus Dunia Nyata

Kasus: E-Commerce Flash Sale dengan Lonjakan 50.000 Concurrent Requests

Dalam skenario Flash Sale, lalu lintas pembacaan katalog produk melonjak secara eksponensial, sementara transaksi Checkout (penulisan pesanan) membebani tabel inventaris.

1. Distribusi Beban Pembacaan: 50.000 permintaan SELECT per detik didistribusikan secara merata oleh ProxySQL ke mysql-node-02 dan mysql-node-03 (hostgroup 20).
2. Proteksi Penulisan: Permintaan INSERT INTO orders ... secara otomatis dirutekan ke mysql-node-01 (hostgroup 10).
3. Failover Otomatis di Tengah Acara: Jika mysql-node-01 tiba-tiba mengalami kegagalan perangkat keras, Group Replication secara otomatis memilih mysql-node-02 sebagai Master baru dalam waktu < 3 detik. ProxySQL mendeteksi perubahan status ini melalui pemantauan health check internal, lalu secara instan mengalihkan Write Pool ke node baru tanpa memerlukan perubahan konfigurasi pada aplikasi backend.

8. Common Pitfalls, Anti-Patterns & Solusi

1. Anti-Pattern: Menggunakan Autocommit OFF secara Berlebihan di Aplikasi
   • Dampak: Transaksi yang berjalan panjang (long-running transactions) menghambat proses sertifikasi di Group Replication, menyebabkan penumpukan antrean (backlog) dan penurunan performa drastis.
   • Solusi: Pastikan setiap ORM atau kode aplikasi menutup transaksi secepat mungkin (COMMIT atau ROLLBACK).
2. Pitfalls: Latency Jaringan Antar Node > 5ms
   • Dampak: Protokol konsensus Paxos sangat sensitif terhadap latensi jaringan. Latensi tinggi menyebabkan transaksi mengalami timeout sertifikasi.
   • Solusi: Tempatkan semua node database dalam satu Availability Zone (AZ) atau Rack fisik yang sama dengan koneksi serat optik berkecepatan tinggi.
3. Kesalahan Konfigurasi Hostgroup ProxySQL untuk Transaksi Eksplisit
   • Dampak: Query SELECT di dalam blok transaksi START TRANSACTION; ... COMMIT; kadang secara keliru dikirim ke Read Pool, padahal membutuhkan pembacaan konsisten dari Master.
   • Solusi: Gunakan fitur Transaction Tracking pada ProxySQL (mysql-hidel_query_rules atau konfigurasi multiplexing = 0 saat transaksi aktif).

9. Benchmark, Metrik Kinerja & Tuning Performa

Untuk mengukur ketahanan kluster, gunakan tool benchmarking seperti sysbench:

# Persiapan data uji sysbench
sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-host=192.168.10.10 \
  --mysql-port=6033 \
  --mysql-user=app_user \
  --mysql-password=StrongReplPassword123! \
  --table-size=1000000 \
  --tables=10 prepare

# Eksekusi uji beban campuran (Read & Write)
sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-host=192.168.10.10 \
  --mysql-port=6033 \
  --mysql-user=app_user \
  --mysql-password=StrongReplPassword123! \
  --table-size=1000000 \
  --tables=10 \
  --threads=64 \
  --time=300 \
  --report-interval=10 run

Parameter Tuning Lanjutan (/etc/mysql/mysql.conf.d/override_gr.cnf):

• Atur innodb_thread_concurrency = 0 untuk membiarkan InnoDB mengelola jumlah thread secara optimal berdasarkan jumlah core CPU.
• Sesuaikan group_replication_flow_control_mode ke DISABLED atau QUOTA tergantung pada karakteristik beban tulis aplikasi Anda untuk mencegah pencekikan lalu lintas replikasi yang terlalu agresif.

10. Checklist Penerapan Produksi & Kesimpulan

Sebelum meluncurkan arsitektur ini ke lingkungan Production, pastikan seluruh poin dalam checklist berikut terpenuhi:

• Seluruh node MySQL memiliki sinkronisasi NTP yang akurat (chrony / ntpd).
• Konfigurasi Firewall (ufw / iptables) mengamankan port 3306, 33061, 6032, dan 6033 hanya dari IP internal yang sah.
• Skrip pencadangan otomatis (automated backup) harian dengan mysqldump atau Percona XtraBackup telah diuji pada node Secondary.
• Monitoring kesehatan ProxySQL dan MySQL terintegrasi dengan Prometheus dan Grafana.
• Pengujian failover simulasi (Chaos Engineering) telah dilakukan di lingkungan staging untuk memvalidasi RTO dan RPO.

Dengan mengintegrasikan MySQL 8.4 Group Replication dan ProxySQL, Anda telah membangun infrastruktur basis data kelas enterprise yang tahan banting, memiliki skalabilitas tinggi, serta siap menghadapi lonjakan beban aplikasi paling ekstrem sekalipun.