Arsitektur High Availability MySQL dengan ProxySQL & Orchestrator

1. Konteks Industri & Analisis Akar Masalah

Dalam lanskap infrastruktur web modern, database relasional seperti MySQL tetap menjadi fondasi utama (source of truth) bagi sebagian besar aplikasi enterprise. Namun, seiring dengan pertumbuhan basis pengguna dan volume transaksi yang eksponensial, arsitektur database tunggal (standalone) atau skema replikasi master-slave konvensional sering kali menemui jalan buntu. Bottleneck kinerja muncul dari dua sisi utama: keterbatasan kapasitas tulis (write bottleneck) pada satu node master tunggal dan latensi aplikasi akibat kurangnya manajemen koneksi yang efisien (connection exhaustion).

Secara tradisional, aplikasi web modern—baik yang dibangun di atas stack PHP-FPM (Laravel, WordPress), Node.js, Go, maupun Python—membuka koneksi baru ke database untuk setiap request HTTP masuk. Ketika beban lonjakan traffic (traffic spikes) terjadi, ribuan koneksi konkuren ini membanjiri MySQL, memicu antrean thread, menghabiskan memori RAM untuk setiap proses koneksi, dan pada akhirnya merontokkan server database melalui error Too many connections.

Selain masalah performa koneksi, ketersediaan tinggi (high availability) adalah harga mati. Dalam skenario failover manual tradisional, ketika node master MySQL mengalami kegagalan perangkat keras atau kernel panic, waktu pemulihan rata-rata (Mean Time to Recovery / MTTR) bisa memakan waktu belasan hingga puluhan menit. Selama jendela waktu tersebut, aplikasi mengalami down total. Penggunaan arsitektur High Availability MySQL dengan menggabungkan ProxySQL dan Orchestrator hadir untuk menyelesaikan masalah ini secara tuntas melalui pendekatan smart routing, connection pooling, dan automated failover tanpa intervensi manual manusia.

Perbandingan Kondisi: Sebelum vs Sesudah Implementasi

2. Konsep Inti & Deep Dive Arsitektur

Untuk membangun ekosistem database yang tangguh, kita harus memahami bagaimana setiap komponen bekerja sama di balik layar. Arsitektur ini terdiri dari tiga lapis utama: Application Layer, Proxy & Routing Layer, dan Database Replication Layer.

+-----------------------------------------------------------------------------------+
|                           APPLICATION LAYER (PHP/Go/Node)                         | 
+----------------------------------------------------------------------------------+
                                          |
                                          v (Port 6033)
+-----------------------------------------------------------------------------------+
|                        PROXY & ROUTING LAYER (ProxySQL)                           |
|  - Connection Pooling                                                             |
|  - Read/Write Query Splitting (Regex Matching)                                    |
+----------------------------------------------------------------------------------+
                    |                                           |
                    v (Writes -> Master)                        v (Reads -> Replicas)
+----------------------------------------+      +-----------------------------------+
|          DATABASE LAYER                |      |         DATABASE LAYER            |
|     [MySQL Master Node]                | <--> |   [MySQL Replica Nodes 1 & 2]     |
+----------------------------------------+      +-----------------------------------+
                    ^                                             ^
                    |                                             |
                    +-------------------[Orchestrator]------------+
                               (Topology Discovery & Auto-Failover)

Alur Kerja di Balik Layar (Under the Hood)

1. Koneksi Aplikasi: Aplikasi web mengirimkan query SQL ke ProxySQL melalui port virtual (biasanya port 6033). Aplikasi menganggap ProxySQL sebagai satu database server tunggal.
2. ProxySQL Query Routing: ProxySQL menganalisis query yang masuk menggunakan aturan routing (Query Rules). Jika query mengandung perintah INSERT, UPDATE, DELETE, atau SELECT ... FOR UPDATE, ProxySQL secara otomatis meneruskannya ke node MySQL Master. Sebaliknya, query baca biasa (SELECT) dirutekan secara round-robin atau berdasarkan bobot ke kluster MySQL Replicas.
3. Connection Pooling: Alih-alih membuka koneksi baru ke MySQL setiap kali ada request, ProxySQL mempertahankan kolam koneksi persisten (persistent connection pool) yang sudah siap pakai ke setiap node MySQL, memangkas overhead pembuatan koneksi TCP dan autentikasi.
4. Topology Management & Failover (Orchestrator): Orchestrator terus-menerus memantau kesehatan topologi replikasi MySQL (posisi GTID, status binlog, dan detak jantung replikasi). Jika Orchestrator mendeteksi bahwa node Master mati, ia akan secara otomatis mempromosikan replica yang paling mutakhir (most up-to-date replica) menjadi master baru, dan meregenerasi konfigurasi replikasi node lainnya.
5. Sinkronisasi ProxySQL: Setelah Orchestrator mempromosikan master baru, ProxySQL mendeteksi perubahan topologi ini (baik melalui integrasi hook skrip atau polling internal) dan memperbarui hostgroups backend secara instan tanpa memutus koneksi aplikasi yang sedang berjalan.

3. Perbandingan Komparatif / Analisis Pendekatan

Memilih tool routing dan failover database memerlukan pemahaman mendalam mengenai trade-off sumber daya komputasi dan kompleksitas operasional.

Tabel Komparasi Solusi HA MySQL

[!TIP]
Gunakan kombinasi ProxySQL + Orchestrator jika beban aplikasi Anda didominasi oleh operasi baca (read-heavy dengan rasio 80% Read : 20% Write) dan Anda membutuhkan stabilitas replikasi master-slave tradisional yang dikontrol secara otomatis.

4. Prasyarat Sistem & Persiapan Lingkungan

Sebelum masuk ke tahap implementasi teknis, pastikan environment Anda memenuhi spesifikasi minimum berikut agar kluster berjalan stabil di level enterprise.

Spesifikasi Infrastruktur (Minimum 4 Nodes)

• Node 1: db-master-01 (OS: Ubuntu 22.04 LTS / Debian 12, RAM: 4GB+, CPU: 2 Cores)
• Node 2: db-replica-01 (OS: Ubuntu 22.04 LTS / Debian 12, RAM: 4GB+, CPU: 2 Cores)
• Node 3: db-replica-02 (OS: Ubuntu 22.04 LTS / Debian 12, RAM: 4GB+, CPU: 2 Cores)
• Node 4: proxy-orchestrator-01 (Menjalankan ProxySQL & Orchestrator, RAM: 2GB+, CPU: 2 Cores)

Prasyarat Jaringan & DNS

• Pastikan seluruh node berada dalam satu jaringan lokal (VPC) dengan akses firewall internal yang terbuka untuk port:
  • 3306 (MySQL)
  • 6033 (ProxySQL Client Interface)
  • 6032 (ProxySQL Admin Interface)
  • 3000 (Orchestrator Web/API)
  • 3308 (Orchestrator Backend Raft/HTTP)
• Atur resolusi /etc/hosts di semua node untuk mempermudah komunikasi:

  192.168.10.11 db-master-01
  192.168.10.12 db-replica-01
  192.168.10.13 db-replica-02
  192.168.10.14 proxy-orchestrator-01
  

5. Panduan Implementasi Langkah demi Langkah

Langkah 1: Konfigurasi MySQL Replikasi (Master & Replicas)

Masuk ke db-master-01, edit file konfigurasi MySQL /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
server-id               = 1
bind-address            = 0.0.0.0
log-bin                 = mysql-bin
binlog_format           = ROW
gtid_mode               = ON
enforce_gtid_consistency = ON
log_slave_updates       = ON
relay-log               = mysql-relay-bin
read_only               = 0

Ulangi konfigurasi serupa untuk db-replica-01 dan db-replica-02, namun sesuaikan server-id (misal: 2 dan 3) serta aktifkan parameter read_only = 1 pada kedua replica tersebut.

Restart layanan MySQL di semua node:

sudo systemctl restart mysql

Buat akun khusus replikasi dan pemantauan Orchestrator di node Master (db-master-01):

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

CREATE USER 'orchestrator'@'%' IDENTIFIED BY 'OrchPass123!';
GRANT SUPER, PROCESS, REPLICATION SLAVE, RELOAD, SELECT ON *.* TO 'orchestrator'@'%';
GRANT GRANT OPTION ON *.* TO 'orchestrator'@'%';
FLUSH PRIVILEGES;

Langkah 2: Instalasi & Konfigurasi Orchestrator

Di node proxy-orchestrator-01, unduh dan pasang Orchestrator:

wget https://github.com/openark/orchestrator/releases/download/v3.2.6/orchestrator_3.2.6_amd64.deb
sudo dpkg -i orchestrator_3.2.6_amd64.deb

Buat file konfigurasi Orchestrator di /etc/orchestrator.conf.json:

{
  "ListenAddress": ":3000",
  "MySQLTopologyCredentialsAccountId": "orchestrator",
  "MySQLTopologyCredentialsAccountPassword": "OrchPass123!",
  "Backend": "sqlite",
  "SQLiteDataFile": "/var/lib/orchestrator/orchestrator.db",
  "DefaultFailoverProcesses": [
    "echo 'Failover started' >> /var/log/orchestrator.log"
  ],
  "RecoveryIgnoreHostnameFilters": [],
  "MasterAutoHealingIntervalSeconds": 5,
  "RecoverMasterClusterTimeout": 10,
  "FailureDetectionPeriod": "2s"
}

Jalankan layanan Orchestrator:

sudo systemctl enable orchestrator
sudo systemctl start orchestrator

Daftarkan topologi ke Orchestrator:

orchestrator -c discover -i db-master-01:3306

Langkah 3: Instalasi & Konfigurasi ProxySQL

Di node proxy-orchestrator-01, pasang ProxySQL:

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

Masuk ke Admin Interface ProxySQL (default user admin, password admin pada port 6032):

mysql -u admin -padmin -h 127.0.0.1 -P6032

Jalankan skrip SQL konfigurasi awal di dalam prompt admin ProxySQL:

-- 1. Daftarkan Server MySQL ke Hostgroups
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, '192.168.10.11', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, '192.168.10.12', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, '192.168.10.13', 3306);

-- 2. Konfigurasi User Database
INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('app_user', 'AppSecretPass123!', 1);

-- 3. Konfigurasi Aturan Query (Read/Write Splitting)
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (1, 1, '^SELECT.*FOR UPDATE$', 1, 1);
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (2, 1, '^SELECT', 2, 1);

-- 4. Simpan ke Runtime dan Disk
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 beberapa konfigurasi krusial yang menentukan performa dan ketahanan sistem:

1. gtid_mode = ON dan enforce_gtid_consistency = ON: Parameter ini sangat penting bagi Orchestrator. Dengan Global Transaction Identifiers (GTID), setiap transaksi diberi ID unik di seluruh kluster. Ketika terjadi failover, Orchestrator tidak perlu menebak posisi log file (binlog filename and position), melainkan cukup mencocokkan transaksi terakhir yang dieksekusi berdasarkan GTID, membuat proses promosi replica menjadi 100% akurat tanpa risiko data hilang (zero-loss replication).

2. ProxySQL Query Rules (destination_hostgroup): Pada tabel mysql_query_rules, aturan dengan rule_id = 1 menangkap query SELECT ... FOR UPDATE dan mengarahkannya ke Hostgroup 1 (Master). Mengapa ini penting? Karena query jenis ini mengunci baris data untuk keperluan transaksi tulis, sehingga harus dieksekusi di Master. Aturan rule_id = 2 menangkap sisa query SELECT standar dan melemparkannya ke Hostgroup 2 (Replicas) untuk mendistribusikan beban baca.

3. mysql_servers Hostgroup Separation: Pemisahan Hostgroup 1 (Master) dan Hostgroup 2 (Replicas) memungkinkan ProxySQL melakukan load balancing otomatis di sisi pembacaan sekaligus memastikan operasi tulis terisolasi secara aman.

7. Skenario Produksi & Studi Kasus Dunia Nyata

Studi Kasus: E-Commerce Flash Sale (100.000 Konkuensi Pengguna)

Sebuah platform e-commerce besar menghadapi lonjakan traffic ekstrem saat kampanye Flash Sale. Pada kondisi normal, beban database didominasi oleh katalog produk (Read-heavy, rasio 90:10). Ketika ribuan pengguna menekan tombol "Beli", lonjakan Write langsung menghantam database.

Solusi Arsitektur yang Diterapkan:

• Koneksi Klien: Aplikasi web diarahkan ke ProxySQL cluster di port 6033 dengan ukuran max_connections diatur ke 5000.
• Connection Pool Optimization di ProxySQL: ProxySQL membatasi koneksi fisik aktual ke node MySQL Master hanya sebanyak 100 koneksi persisten. Teknik ini mencegah MySQL mengalami thread thrashing akibat kelebihan proses.
• Read-Scaling: Permintaan pencarian produk dan detail item disebar secara merata ke db-replica-01 dan db-replica-02 menggunakan algoritma weight-based round-robin di ProxySQL.
• Failover Darurat: Ketika db-master-01 mengalami kegagalan daya mendadak, Orchestrator mendeteksi kegagalan dalam waktu 2 detik, mempromosikan replica tercepat menjadi master baru, dan aplikasi tetap berjalan tanpa mengalami error koneksi putus (dropping connection), karena ProxySQL secara mulus mengalihkan antrean tulis ke master baru.

8. Common Pitfalls, Anti-Patterns & Solusi

Berikut adalah jebakan umum yang sering dilakukan oleh arsitek pemula saat mengimplementasikan stack ini:

[!WARNING]
Anti-Pattern 1: Mengabaikan Lag Replikasi saat Read Splitting
Masalah: Aplikasi membaca data langsung dari Replica sesaat setelah melakukan Write ke Master, yang mengakibatkan data tampak belum berubah (karena jeda latensi jaringan asinkron). Solusi: Gunakan teknik Read-Your-Writes Consistency di tingkat aplikasi (menyimpan penanda waktu session di Redis) atau arahkan query kritis langsung ke Hostgroup Master (SELECT ... FOR UPDATE).

[!WARNING]
Anti-Pattern 2: Tidak Mengatur max_connections pada ProxySQL dan MySQL
Masalah: Menyetel koneksi tanpa batas memicu kehabisan memori RAM fisik pada server database. Solusi: Selalu pastikan batas koneksi fisik MySQL lebih tinggi daripada alokasi maksimum koneksi pool di ProxySQL.

9. Benchmark, Metrik Kinerja & Tuning Performa

Untuk menguji ketahanan kluster, kita dapat menggunakan tool benchmark seperti sysbench untuk melakukan simulasi beban OLTP (Online Transaction Processing).

Menjalankan Benchmark Sysbench melalui ProxySQL

1. Persiapkan tabel uji pada kluster melalui ProxySQL:

   sysbench /usr/share/sysbench/oltp_read_write.lua \
     --mysql-host=192.168.10.14 \
     --mysql-port=6033 \
     --mysql-user=app_user \
     --mysql-password=AppSecretPass123! \
     --tables=10 \
     --table-size=100000 \
     prepare
   

2. Jalankan pengujian beban konkuren tinggi selama 5 menit:

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

Interpretasi Hasil & Tuning Lanjutan

• QPS (Queries Per Second): Perhatikan peningkatan throughput berkat connection pooling ProxySQL.
• Latency 95th percentile: Pastikan latensi rata-rata tetap berada di bawah 10ms di bawah beban puncak.
• Tuning Kernel Linux (/etc/sysctl.conf): Sesuaikan parameter jaringan untuk menangani koneksi TCP masif:

  net.ipv4.tcp_fin_timeout = 15
  net.core.somaxconn = 65535
  net.ipv4.tcp_max_syn_backlog = 16384
  

10. Checklist Penerapan Produksi & Kesimpulan

Sebelum meluncurkan arsitektur ini ke lingkungan produksi, pastikan Anda telah memverifikasi seluruh item dalam checklist berikut:

• Semua node MySQL sudah menggunakan GTID mode (gtid_mode = ON).
• Orchestrator terkonfigurasi dengan interval deteksi kegagalan yang optimal (2-5 detik).
• Aturan query ProxySQL (mysql_query_rules) telah diuji dengan unit test aplikasi.
• Backup otomatis harian (menggunakan mysqldump atau Percona XtraBackup) aktif pada node replica.
• Monitoring metrik (Prometheus + Grafana) terpasang untuk memantau status ProxySQL, beban CPU MySQL, dan lag replikasi.

Dengan menerapkan arsitektur High Availability MySQL berbasis ProxySQL dan Orchestrator, organisasi Anda tidak hanya mendapatkan skalabilitas performa baca-tulis yang optimal, tetapi juga jaminan ketahanan sistem (resilience) tingkat enterprise yang siap menghadapi dinamika bisnis digital masa kini.