Membangun High Availability MySQL dengan Percona XtraDB Cluster & ProxySQL

1. Konteks Industri & Analisis Akar Masalah

Dalam lanskap arsitektur web modern, ketersediaan tinggi (High Availability / HA) dan konsistensi data yang ketat adalah dua sisi mata uang yang paling sulit diseimbangkan. Ketika sebuah sistem enterprise menghadapi lonjakan traffic dari jutaan pengguna simultan—baik di platform e-commerce, perbankan digital, maupun SaaS skala masif—kegagalan basis data tunggal (Single Point of Failure / SPOF) bukan lagi sekadar gangguan operasional, melainkan bencana finansial dan reputasi.

Secara tradisional, arsitektur Primary-Replica (Master-Slave) sering digunakan. Namun, arsitektur ini memiliki kelemahan mendasar: ketika Primary mengalami kegagalan (crash), proses failover manual maupun otomatis sering kali menghasilkan Recovery Point Objective (RPO) yang tidak nol. Artinya, ada jeda waktu di mana transaksi yang telah ditulis hilang atau terjadi split-brain syndrome jika replikasi belum sepenuhnya sinkron. Lebih buruk lagi, skalabilitas Write sangat terbatas karena semua beban tulis harus melalui satu simpul (node) saja.

Sebaliknya, pendekatan Active-Active Multi-Master Clustering menggunakan Percona XtraDB Cluster (PXC) yang ditenagai oleh mesin Galera Cluster menawarkan solusi revolusioner. PXC memastikan replikasi sinkron berbasis certification-based replication, yang menjamin Zero RPO dan konsistensi data absolut di semua node. Namun, tantangan baru muncul di sisi aplikasi: bagaimana mendistribusikan beban read dan write secara cerdas tanpa mengubah kode aplikasi secara masif? Di sinilah peran krusial ProxySQL, sebuah high-performance SQL proxy yang bertindak sebagai lapisan perantara cerdas untuk Read/Write Splitting, Connection Pooling, dan Failover otomatis.

Kondisi Sebelum vs Sesudah Penerapan

2. Konsep Inti & Deep Dive Arsitektur

Untuk memahami bagaimana Percona XtraDB Cluster dan ProxySQL bekerja secara harmonis, kita harus membedah arsitektur internal mereka hingga ke tingkat protokol jaringan dan memori.

Arsitektur Galera Cluster & PXC

Percona XtraDB Cluster adalah integrasi dari MySQL, Percona Server untuk MySQL, dan pustaka Galera 4. Galera menggunakan replikasi sinkron virtual dengan model group communication. Setiap node di dalam kluster memiliki salinan data yang identik.

1. State Transfer: Ketika node baru bergabung, ia melakukan IST (Incremental State Transfer) jika perbedaannya kecil, atau SST (State Snapshot Transfer) menggunakan xtrabackup jika perbedaannya besar.
2. Certification-based Replication: Saat transaksi di-commit pada satu node, rangkaian writeset (kumpulan baris yang diubah beserta kunci primernya) disiarkan ke semua node lain melalui lapisan komunikasi grup (GCS). Setiap node memvalidasi writeset tersebut secara lokal (certification). Jika tidak ada konflik, transaksi diterima dan diterapkan di semua node secara bersamaan.

Peran ProxySQL

ProxySQL berada di antara aplikasi (Backend Web PHP, Node.js, Go) dan kluster database. ProxySQL tidak hanya meneruskan kueri, tetapi menganalisisnya secara semantik.

• Read/Write Splitting: Kueri SELECT diarahkan ke node pembaca (readers), sedangkan INSERT, UPDATE, DELETE, dan transaksi eksplisit diarahkan ke node penulis (writer).
• Query Rules & Rewriting: Memungkinkan modifikasi kueri secara on-the-fly atau pemblokiran kueri berbahaya (SQL Injection protection).
• Connection Pooling: Mengurangi beban pembuatan koneksi baru ke MySQL yang memakan banyak sumber daya CPU dan RAM.

+-------------------------------------------------------------+
|                        Application                          | 
|             (PHP-FPM, Node.js, Go Backend)                  |
+------------------------------id-----------------------------+
                               | Port 3306 (MySQL Protocol)
                               v
+-------------------------------------------------------------+
|                           ProxySQL                          |
|  - Connection Pooling                                       |
|  - Read/Write Splitting                                     |
|  - Query Rules & Routing                                    |
+-------+------------------------------+----------------------+
        |                              |                      p
        | (Writes)                     | (Reads)              | (Health Check)
        v                              v                      v
+---------------+              +---------------+      +---------------+
|  PXC Node 1   | <========>   |  PXC Node 2   | <--> |  PXC Node 3   |
| (Active Write)|   (Galera    | (Active Read) |      | (Active Read) |
+---------------+  GCS Sync)   +---------------+      +---------------+

3. Perbandingan Komparatif / Analisis Pendekatan

Dalam membangun sistem HA, arsitek sering kali dihadapkan pada beberapa pilihan teknologi database clustering. Berikut adalah analisis mendalam mengenai trade-off antara Percona XtraDB Cluster dengan pendekatan alternatif.

[!WARNING] Walaupun PXC mendukung multi-master write, melakukan transaksi tulis secara bersamaan pada tabel yang sama di node berbeda dengan hot rows yang tinggi akan memicu konflik certification (deadlock galeri), yang berakibat pada pembatalan transaksi (rollback). Desain aplikasi harus memperhitungkan hal ini.

4. Prasyarat Sistem & Persiapan Lingkungan

Sebelum melangkah ke instalasi, pastikan lingkungan infrastruktur memenuhi spesifikasi enterprise berikut:

Spesifikasi Minimum (Per Node)

• CPU: 4 vCore / Threads (Disarankan 8+ core untuk beban tinggi).
• RAM: 8 GB ECC RAM (Database buffer pool membutuhkan minimal 4GB).
• OS: Ubuntu 22.04 LTS atau AlmaLinux 9.
• Jaringan: Internal LAN dengan latensi rendah (< 2ms antar node). Port berikut wajib dibuka di firewall:
  • 3306: MySQL client port
  • 4567: Galera Cluster replication traffic (TCP & UDP)
  • 4568: Incremental State Transfer (IST)
  • 4444: State Snapshot Transfer (SST)
  • 6033: ProxySQL port
  • 6032: ProxySQL Admin port

Persiapan Konfigurasi Hostname & DNS Lokal

Edit /etc/hosts pada setiap node kluster agar dapat saling mengenali via hostname:

10.0.1.11 db-node1
10.0.1.12 db-node2
10.0.1.13 db-node3
10.0.1.20 proxy-server

5. Panduan Implementasi Langkah demi Langkah

Langkah 1: Instalasi Percona XtraDB Cluster 8.0

Jalankan perintah berikut pada ketiga node database (db-node1, db-node2, db-node3):

# Update sistem dan pasang dependensi dasar
sudo apt update && sudo apt install -y gnupg2 curl lsb-release wget

# Unduh dan pasang Percona Release Repository
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
sudo percona-release setup pxc-80

# Instalasi Percona XtraDB Cluster
sudo apt update
sudo apt install -y percona-xtradb-cluster

Langkah 2: Konfigurasi Galera Cluster (/etc/mysql/percona-xtradb-cluster.conf.d/wsrep.cnf)

Sesuaikan berkas konfigurasi pada db-node1. Perhatikan parameter wsrep_node_address yang harus disesuaikan pada setiap node.

[mysqld]
server-id=1
bind-address=0.0.0.0
default_authentication_plugin=caching_sha2_password

# Galera Provider Configurations
wsrep_provider=/usr/lib/galera4/libgalera_smm.so
wsrep_cluster_name="pxc-production-cluster"
wsrep_cluster_address="gcomm://10.0.1.11,10.0.1.12,10.0.1.13"

# Node Specific Configurations
wsrep_node_address="10.0.1.11"
wsrep_node_name="db-node1"

# SST Configuration
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:SSTSecurePassword2024!"

# InnoDB & Performance Tuning
innodb_buffer_pool_size=4G
innodb_flush_log_at_trx_commit=2
innodb_file_per_table=1

[!TIP] Pada db-node2, ubah server-id=2, wsrep_node_address="10.0.1.12", dan wsrep_node_name="db-node2". Begitu pula untuk db-node3 dengan ID yang sesuai.

Langkah 3: Bootstrap Kluster Pertama

Hentikan layanan MySQL pada semua node terlebih dahulu:

sudo systemctl stop mysql

Pada db-node1 saja, jalankan perintah bootstrap untuk memulai kluster baru:

sudo systemctl start mysql@bootstrap

Verifikasi bahwa kluster aktif dengan masuk ke MySQL dan menjalankan:

SHOW STATUS LIKE 'wsrep_cluster_size';

Nilai harus menunjukkan 1. Selanjutnya, pada db-node2 dan db-node3, jalankan layanan MySQL secara normal:

sudo systemctl start mysql

Periksa kembali ukuran kluster. Seharusnya nilai wsrep_cluster_size berubah menjadi 3.

Langkah 4: Instalasi dan Konfigurasi ProxySQL

Pindahkan ke server proxy-server (IP: 10.0.1.20).

# Tambahkan repository Percona untuk ProxySQL
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
sudo percona-release setup proxysql

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

Akses antarmuka admin ProxySQL menggunakan default credentials (admin / admin):

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

Konfigurasikan node backend pada tabel mysql_servers:

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

-- Buat hostgroup pembaca (read-only / hostgroup 2) dan penulis (write / hostgroup 1)
-- Untuk PXC, semua node bisa menjadi writer, tapi kita arahkan primary write ke hostgroup 1
-- dan read kueri didistribusikan ke hostgroup 2 (atau 1 jika multi-master murni).
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, '10.0.1.11', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, '10.0.1.12', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, '10.0.1.13', 3306);

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

Konfigurasikan pengguna database di ProxySQL (mysql_users):

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

Terapkan aturan pemisahan baca/tulis (Read/Write Splitting) melalui mysql_query_rules:

-- Aturan 1: Arahkan semua kueri yang dimulai dengan SELECT ke hostgroup pembaca (2)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (1, 1, '^SELECT.*', 2, 1);

-- Aturan 2: Transaksi eksplisit atau write dialihkan ke hostgroup penulis (1)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (2, 1, '^SELECT.*FOR UPDATE', 1, 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

6. Bedah Kode & Penjelasan Detail Baris Kunci

Mari kita bedah beberapa parameter penting yang menentukan stabilitas kluster:

1. wsrep_provider_options="gcache.size=1G"
   • Fungsi: Menentukan ukuran memori Write Set Cache (gcache). Parameter ini sangat krusial untuk mencegah SST (State Snapshot Transfer) yang mahal secara sumber daya ketika jaringan terputus sesaat. Jika node terputus dan kembali dalam rentang waktu di mana perubahan datanya masih muat di dalam gcache 1GB, node tersebut hanya akan melakukan IST (Incremental State Transfer).
2. innodb_flush_log_at_trx_commit=2
   • Fungsi: Mengatur mekanisme flush log transaksi ke disk. Nilai 2 berarti log transaksi ditulis ke disk setiap detik, bukan setiap transaksi. Ini meningkatkan performa tulis secara drastis pada sistem dengan throughput tinggi, dengan kompromi risiko kehilangan data maksimal 1 detik jika terjadi kegagalan daya total pada server (namun aman dari kegagalan proses MySQL itu sendiri).
3. destination_hostgroup pada ProxySQL:
   • Fungsi: Memetakan pola kueri SQL reguler (match_pattern) ke grup node tertentu. Dengan memisahkan kueri analitik/pembacaan berat ke hostgroup sekunder, kita melindungi node utama dari kelelahan sumber daya CPU.

7. Skenario Produksi & Studi Kasus Dunia Nyata

Dalam implementasi di platform e-commerce dengan 50.000 requests per second (RPS) saat flash sale, pola beban kerja didominasi oleh pembacaan katalog produk (95%) dan sedikit penulisan transaksi pembayaran (5%).

Strategi Routing Lanjutan di ProxySQL

Untuk menangani beban ini secara optimal, kita menggunakan fitur Multiplexing di ProxySQL. Multiplexing memungkinkan ProxySQL menggunakan kembali koneksi fisik ke backend MySQL untuk banyak koneksi klien yang berbeda secara bergantian.

Pastikan parameter berikut dikonfigurasi pada tabel global_variables di ProxySQL:

UPDATE global_variables SET variable_value='true' WHERE variable_name='mysql-multiplexing';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

[!IMPORTANT] Nonaktifkan sementara multiplexing (mysql-multiplexing = false) jika aplikasi Anda menggunakan Prepared Statements kompleks atau transaksi yang bergantung pada session variables kustom, karena hal ini dapat menyebabkan kebocoran konteks sesi antar klien yang berbeda.

8. Common Pitfalls, Anti-Patterns & Solusi

1. Anti-Pattern: Menulis ke Tabel Tanpa Primary Key

   • Masalah: Galera Cluster mewajibkan setiap tabel InnoDB memiliki Primary Key. Jika sebuah tabel tidak memiliki Primary Key, replikasi sinkron akan gagal atau memicu konflik besar saat operasi UPDATE/DELETE karena writeset tidak dapat mengidentifikasi baris unik secara deterministik.
   • Solusi: Selalu terapkan Primary Key pada setiap pembuatan tabel (CREATE TABLE). Aktifkan variabel MySQL sql_require_primary_key=ON secara global.

2. Pitfalls: Split-Brain karena Jaringan Terisolasi (Network Partition)

   • Masalah: Jika kluster beranggotakan 3 node terpecah menjadi dua kubu (misal 1 node di satu zona, 2 node di zona lain), kluster dengan jumlah node lebih sedikit akan masuk ke status non-operational untuk mencegah korupsi data.
   • Solusi: Pastikan jumlah node ganjil (3 atau 5), atau gunakan Galera Arbitrator (garbd) di server ketiga yang terpisah secara fisik untuk bertindak sebagai penengah quorum tanpa menanggung beban data.

9. Benchmark, Metrik Kinerja & Tuning Performa

Lakukan pengujian beban (load testing) menggunakan tool seperti sysbench untuk memvalidasi ketahanan kluster sebelum dilepas ke produksi:

# Menyiapkan 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=SecurePassword \
  --mysql-db=testdb \
  prepare

# Menjalankan pengujian beban selama 300 detik dengan 64 thread
sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-host=10.0.1.20 \
  --mysql-port=6033 \
  --mysql-user=app_user \
  --mysql-password=SecurePassword \
  --mysql-db=testdb \
  --time=300 \
  --threads=64 \
  --report-interval=10 \
  run

Metrik Utama yang Harus Dipantau (Prometheus & Grafana)

• wsrep_local_send_q dan wsrep_local_recv_q: Jika nilai antrean ini > 0 secara konsisten, artinya ada hambatan jaringan atau node tidak mampu memproses writeset secepat yang dikirimkan.
• ProxySQL Backend Query Latency: Memastikan waktu respons kueri rata-rata tetap di bawah 5ms.
• mysql_connection_pool_used: Memantau utilisasi connection pool agar tidak mencapai batas maksimum (max_connections).

10. Checklist Penerapan Produksi & Kesimpulan

Sebelum meluncurkan arsitektur ini ke lingkungan produksi, pastikan seluruh item dalam daftar periksa berikut telah terpenuhi:

• Semua node PXC memiliki sinkronisasi waktu NTP yang akurat.
• Konfigurasi firewall (UFW / iptables) membatasi akses port internal Galera (4567, 4568, 4444) hanya antar node kluster.
• Pengguna replikasi (sstuser) menggunakan sandi yang kuat dan aman.
• ProxySQL dikonfigurasi dengan failover otomatis dan pemeriksaan kesehatan berkala (ping interval).
• Sistem pencadangan otomatis (Percona Backup for MySQL) terjadwal secara berkala ke penyimpanan eksternal (S3/MinIO).
• Monitoring metrik dasar (CPU, RAM, Disk I/O, Galera Status) aktif di Prometheus & Grafana.

Dengan memadukan ketahanan mutlak Percona XtraDB Cluster dan kecerdasan perutean ProxySQL, infrastruktur database enterprise Anda kini siap menghadapi beban kerja ekstrem dengan ketersediaan tinggi tanpa kompromi pada konsistensi data.