High Availability MySQL Cluster dengan Group Replication & ProxySQL

1. Konteks Industri & Analisis Akar Masalah

Dalam lanskap infrastruktur web enterprise modern, database relasional tetap menjadi fondasi paling kritis sekaligus single point of failure (SPOF) yang paling ditakuti. Kegagalan basis data tidak hanya berakibat pada hilangnya pendapatan, tetapi juga merusak reputasi merek secara instan. Di masa lalu, arsitektur Primary-Replica (Master-Slave) konvensional dengan replikasi asinkron atau semi-sinkron menjadi standar industri. Namun, pendekatan ini memiliki kelemahan mendasar: replikasi asinkron rentan terhadap kehilangan data (data loss) saat terjadi failover manual jika replica belum menerima semua transaksi dari primary yang lama. Sebaliknya, failover otomatis pada arsitektur lama sering kali memicu masalah split-brain, di mana dua node mengklaim diri sebagai primary secara bersamaan, mengakibatkan korupsi data yang fatal.

Selain masalah konsistensi data, pengelolaan koneksi aplikasi juga menjadi tantangan tersendiri. Ketika terjadi perpindahan primary, aplikasi harus secara cerdas memutuskan koneksi lama, mendeteksi node baru, dan mengarahkan ulang (reroute) kueri write ke node baru tersebut tanpa menyebabkan downtime atau connection leak. Di sinilah kebutuhan mendesak akan infrastruktur High Availability (HA) yang benar-benar otomatis, konsisten, dan transparan muncul.

Sebelum penerapan kluster High Availability berbasis MySQL Group Replication (MGR) dan ProxySQL:

• Arsitektur: Menggunakan satu Primary dan dua Replica asinkron.
• Failover: Membutuhkan intervensi manual (Human-in-the-loop) melalui DNS switch atau perubahan konfigurasi aplikasi, menyebabkan downtime rata-rata 15 hingga 45 menit.
• Konsistensi Data: Sering terjadi replication lag, mengakibatkan pengguna membaca data lama sesaat setelah melakukan pembaruan (read-your-writes consistency rusak).
• Beban Koneksi: Aplikasi langsung menembak alamat IP database, mengakibatkan lonjakan koneksi mendadak saat reconnect massal yang melumpuhkan sumber daya database.

Sesudah penerapan kluster MySQL Group Replication + ProxySQL:

• Arsitektur: Multi-Primary atau Single-Primary group dengan deteksi konsensus Paxos.
• Failover: Sepenuhnya otomatis di tingkat database dalam hitungan detik, dan secara transparan dialihkan oleh ProxySQL ke node baru tanpa mengubah kode aplikasi.
• Konsistensi Data: Menggunakan mekanisme certified transactions dan group communication engine (XCom), memastikan tidak ada data yang hilang (zero data loss).
• Manajemen Koneksi: ProxySQL bertindak sebagai intelligent connection pooler dan query router, meredam lonjakan koneksi dan memisahkan read/write splitting secara presisi.

[!IMPORTANT] Kegagalan merancang arsitektur jaringan yang stabil antara node MySQL Group Replication akan memicu pemisahan kelompok (group expulsion) akibat masalah latensi, yang berujung pada penurunan performa kluster secara keseluruhan.

2. Konsep Inti & Deep Dive Arsitektur

MySQL Group Replication adalah plugin mandiri yang dibangun di atas kerangka kerja replikasi MySQL tradisional. Plugin ini memungkinkan pembuatan kluster database yang toleran terhadap kesalahan (fault-tolerant). Di balik layar, MGR menggunakan algoritma konsensus terdistribusi berdasarkan protokol Paxos (XCom) untuk memastikan bahwa setiap transaksi yang dieksekusi divalidasi, diurutkan, dan disetujui oleh mayoritas node di dalam kelompok sebelum di-commit secara lokal.

Alur Kerja Transaksi dalam Group Replication

1. Eksekusi Lokal: Klien mengirimkan kueri write (INSERT, UPDATE, DELETE) ke salah satu node di dalam grup.
2. Sertifikasi (Certification Phase): Sebelum transaksi di-commit secara lokal, transaksi tersebut dikirimkan ke seluruh node lain melalui Group Communication System (GCS). Setiap node memeriksa apakah ada konflik baris data dengan transaksi lain yang sedang diproses secara paralel (certification check).
3. Konsensus: Jika mayoritas node menyetujui bahwa tidak ada konflik, transaksi lolos dan dinyatakan valid.
4. Commit / Rollback: Node menerapkan transaksi ke mesin penyimpanan InnoDB. Jika terjadi konflik, transaksi dibatalkan (rollback) secara otomatis pada node asal.

+-------------+
| Application |
+-------------+
       |
       v
+-------------+
|  ProxySQL   | ---> (Healthcheck / Ping)
+-------------+
       |
       +-------------------+-------------------+
       | (Read/Write)      | (Read Only)       |
       v                   v                   v
+---------------+   +---------------+   +---------------+
| MySQL Node 1  |   | MySQL Node 2  |   | MySQL Node 3  |
|  (PRIMARY)    |   | (SECONDARY)   |   | (SECONDARY)   |
+---------------+   +---------------+   +---------------+
       \                   /                   /
        \                 /                   /
         +--- [ XCom / Paxos Consensus ] -----+

ProxySQL bertindak sebagai lapisan perantara (middleware) yang berdiri di antara aplikasi web (PHP, Node.js, Go) dan kluster MySQL. ProxySQL memantau kesehatan kluster secara real-time dengan mengeksekusi kueri pemeriksaan kesehatan (healthcheck) secara berkala. Berdasarkan status node (apakah ia bertindak sebagai PRIMARY atau SECONDARY), ProxySQL secara dinamis memperbarui perutean kueri SQL. Kueri write dikirim ke PRIMARY, sementara kueri read didistribusikan ke seluruh node SECONDARY (atau PRIMARY jika diperlukan) menggunakan algoritma penyeimbangan beban seperti weighted round-robin.

3. Perbandingan Komparatif / Analisis Pendekatan

Untuk memahami mengapa kombinasi MGR dan ProxySQL merupakan standar emas saat ini, mari kita bandingkan dengan pendekatan arsitektur database lainnya.

[!TIP] Untuk beban kerja enterprise dengan tingkat konkurensi tinggi, arsitektur Single-Primary Mode pada Group Replication jauh lebih disukai daripada Multi-Primary. Mode ini menghindari konflik penulisan konkuren yang mahal (certification failures) pada baris data yang sama.

4. Prasyarat Sistem & Persiapan Lingkungan

Sebelum memulai instalasi, pastikan lingkungan infrastruktur memenuhi spesifikasi teknis minimum berikut:

• Jumlah Node: Minimal 3 node server berbasis Linux (Ubuntu 22.04 LTS / Rocky Linux 9 direkomendasikan) untuk memastikan kuorum (quorum) tercapai (3 node dapat menoleransi 1 node mati).
• Spesifikasi Hardware per Node: Minimal 4 vCPU, 8 GB RAM, dan penyimpanan SSD NVMe dengan performa IOPS tinggi.
• Jaringan: Jaringan lokal (VPC) dengan latensi ultra-rendah (< 5ms antar node). Semua node harus dapat saling berkomunikasi melalui port internal (TCP 3306 dan 33061).
• Software: MySQL Server 8.0+ dan ProxySQL 2.5+

Konfigurasi Firewall & DNS Internal

Pastikan port berikut terbuka di masing-masing node database:

• 3306: Port klien MySQL standar.
• 33061: Port komunikasi internal Group Replication (XCom).

# Jalankan perintah ini pada setiap node database (Ubuntu UFW)
sudo ufw allow 3306/tcp
sudo ufw allow 33061/tcp
sudo ufw reload

5. Panduan Implementasi Langkah demi Langkah

Langkah 1: Konfigurasi Dasar MySQL Server (/etc/mysql/mysql.conf.d/mysqld.cnf)

Terapkan konfigurasi berikut pada ketiga node database. Sesuaikan server_id dan alamat IP pada setiap node.

[mysqld]
server_id                    = 1
port                         = 3306
bind-address                 = 0.0.0.0
datadir                      = /var/lib/mysql
socket                       = /var/run/mysqld/mysqld.sock

# InnoDB Tuning untuk Performa Tinggi
innodb_buffer_pool_size       = 4G
innodb_log_file_size          = 1G
innodb_flush_log_at_trx_commit = 1
innodb_flush_method          = O_DIRECT

# Binary Logging Configuration (Wajib untuk MGR)
gtid_mode                    = ON
enforce_gtid_consistency     = ON
binlog_checksum              = NONE
log_slave_updates            = ON
master_info_repository       = TABLE
relay_log_info_repository    = TABLE

# Group Replication Configuration
plugin_load_add              = "group_replication.so"
loose_group_replication_group_name         = "10d65a04-585a-11ee-8c99-0242ac120002"
loose_group_replication_start_on_boot      = OFF
loose_group_replication_local_address      = "192.168.1.101:33061"
loose_group_replication_group_seeds        = "192.168.1.101:33061,192.168.1.102:33061,192.168.1.103:33061"
loose_group_replication_bootstrap_group    = OFF
loose_group_replication_single_primary_mode = ON
loose_group_replication_enforce_update_everywhere_checks = OFF

Langkah 2: Memulai Kluster dan Membuat Akun Replikasi

Restart layanan MySQL pada semua node:

sudo systemctl restart mysql

Masuk ke konsol MySQL pada Node 1 (192.168.1.101) untuk membuat pengguna replikasi dan melakukan bootstrap kluster:

SET SQL_LOG_BIN=0;
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'S3cr3tP@ssw0rd!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
GRANT SELECT ON mysql.user TO 'repl_user'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='repl_user', MASTER_PASSWORD='S3cr3tP@ssw0rd!' FOR CHANNEL 'group_replication_recovery';

-- Bootstrap kluster pertama kali hanya di Node 1
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

Verifikasi bahwa Node 1 telah berstatus sebagai PRIMARY:

SELECT * FROM performance_schema.replication_group_members;

Selanjutnya, pada Node 2 dan Node 3, jalankan perintah berikut:

SET SQL_LOG_BIN=0;
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'S3cr3tP@ssw0rd!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
GRANT SELECT ON mysql.user TO 'repl_user'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='repl_user', MASTER_PASSWORD='S3cr3tP@ssw0rd!' FOR CHANNEL 'group_replication_recovery';

START GROUP_REPLICATION;

Langkah 3: Instalasi dan Konfigurasi ProxySQL

Instal ProxySQL pada server terpisah (atau pada server aplikasi):

sudo apt update && sudo apt install -y proxysql
sudo systemctl start proxysql

Akses antarmuka administratif ProxySQL (Default port 6032, user: admin, password: admin):

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

Jalankan konfigurasi SQL berikut di konsol ProxySQL:

-- Tambahkan server MySQL ke hostgroup
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES 
(1, '192.168.1.101', 3306),
(1, '192.168.1.102', 3306),
(1, '192.168.1.103', 3306);

-- Definisikan Hostgroup: 1 untuk Write (Primary), 2 untuk Read (Secondary)
-- Buat pengguna database untuk ProxySQL
INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('app_user', 'AppS3cr3tP@ss!', 1);

-- Konfigurasi monitor untuk mendeteksi Primary secara otomatis
UPDATE global_variables SET variable_value='sys' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='S3cr3tP@ssw0rd!' WHERE variable_name='mysql-monitor_password';
UPDATE global_variables SET variable_value='2000' WHERE variable_name='mysql-monitor_connect_interval';

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL VARIABLES TO RUNTIME;

6. Bedah Kode & Penjelasan Detail Baris Kunci

Mari kita bedah beberapa parameter kritis yang sering kali disalahartikan dalam konfigurasi di atas:

• gtid_mode = ON & enforce_gtid_consistency = ON: Global Transaction Identifiers sangat esensial dalam MGR. Setiap transaksi diberi ID unik di seluruh kluster. Tanpa GTID, mesin replikasi tidak dapat melacak posisi transaksi yang disetujui di antara node yang berbeda.
• loose_group_replication_single_primary_mode = ON: Menginstruksikan kluster untuk memilih hanya satu node sebagai penulis (PRIMARY) aktif, sementara node lainnya menjadi pembaca (SECONDARY). Awalan loose_ memastikan bahwa jika plugin belum dimuat saat startup awal, MySQL tidak akan gagal melakukan booting.
• mysql-monitor_username pada ProxySQL: ProxySQL menggunakan akun khusus ini untuk menjalankan healthcheck berkala (biasanya berupa kueri sederhana atau pemeriksaan status MGR) guna menentukan node mana yang sedang memegang peran PRIMARY.

7. Skenario Produksi & Studi Kasus Dunia Nyata

Dalam lingkungan produksi dengan lonjakan traffic tinggi (misalnya Flash Sale e-commerce dengan 50.000 requests per second), arsitektur ini diuji kemampuannya:

• Pemisahan Lalu Lintas (Read/Write Splitting): Aplikasi backend berbasis Go mengirimkan seluruh kueri INSERT/UPDATE melalui port pembatas ProxySQL (6033), yang secara otomatis diarahkan ke Node PRIMARY tunggal. Kueri SELECT (seperti katalog produk) disebarkan secara merata ke Node 2 dan Node 3.
• Keseimbangan Beban (Load Balancing): ProxySQL menggunakan metrik latensi dan status replikasi untuk mengalihkan read queries dari node yang mengalami beban CPU tinggi.

8. Common Pitfalls, Anti-Patterns & Solusi

1. Anti-Pattern: Menggunakan Multi-Primary Mode tanpa validasi aplikasi yang ketat.
   • Dampak: Sering terjadi certification deadlock dan rollback massal saat dua transaksi mengubah baris yang sama secara bersamaan.
   • Solusi: Selalu gunakan Single-Primary Mode kecuali aplikasi Anda dirancang secara spesifik dengan conflict-free replicated data types (CRDT).
2. Pitfall: Mengabaikan ukuran innodb_log_file_size.
   • Dampak: Node sekunder tertinggal (lag) karena ukuran redo log terlalu kecil untuk menampung transaksi kluster yang masif.
   • Solusi: Tetapkan minimal 1GB atau lebih besar sesuai dengan volume transaksi harian.

9. Benchmark, Metrik Kinerja & Tuning Performa

Lakukan pengujian beban menggunakan tool seperti sysbench untuk memvalidasi ketahanan kluster:

sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-host=127.0.0.1 \
  --mysql-port=6033 \
  --mysql-user=app_user \
  --mysql-password=AppS3cr3tP@ss! \
  --tables=10 \
  --table-size=100000 \
  --threads=64 \
  --time=300 run

Interpretasi Metrik:

• Perhatikan Transaction Latency (p99). Jika latencies melonjak tajam, periksa apakah bandwidth jaringan antar node database menjadi hambatan (bottleneck).
• Pantau metrik group_replication_primary_contact dan handler_commit untuk memastikan distribusi beban berjalan optimal.

10. Checklist Penerapan Produksi & Kesimpulan

Sebelum merilis arsitektur ini ke lingkungan produksi, pastikan checklist berikut terpenuhi:

• Semua 3 node database berjalan pada zona ketersediaan (Availability Zone) cloud yang berbeda namun dalam satu region berlatensi rendah.
• Konfigurasi systemd untuk ProxySQL dan MySQL diatur dengan kebijakan auto-restart (Restart=always).
• Pengujian chaos engineering (mematikan paksa node PRIMARY) telah dilakukan dan failover otomatis terbukti sukses dalam waktu < 10 detik tanpa data loss.
• Sistem monitoring (Prometheus + Grafana) terpasang untuk memantau status replikasi MGR dan metrik pool koneksi ProxySQL.

Dengan memadukan ketahanan konsensus MySQL Group Replication dan kecerdasan perutean ProxySQL, Anda telah membangun fondasi database enterprise yang tahan banting, aman, dan siap menghadapi skala web modern apa pun.