High Availability MySQL dengan Orchestrator dan ProxySQL

1. Konteks Industri & Analisis Akar Masalah

Dalam lanskap arsitektur sistem modern berbasis web, database relasional seperti MySQL sering kali menjadi single point of failure (SPOF) sekaligus bottleneck performa utama. Ketika trafik melonjak drastis akibat lonjakan pengguna, pemeliharaan rutin, atau kegagalan perangkat keras (hardware failure), aplikasi berbasis PHP, Go, maupun Node.js yang mengandalkan MySQL akan mengalami latensi tinggi, kegagalan koneksi (connection timeout), hingga service downtime total.

Secara historis, administrator sistem mengandalkan replikasi master-slave konvensional dengan failover manual. Pendekatan ini memiliki cacat fatal: ketika MySQL Master mati, proses deteksi, promosi replica baru menjadi Master, perubahan konfigurasi DNS atau koneksi aplikasi, dan sinkronisasi ulang slave lainnya memakan waktu berharga (bisa mencapai belasan hingga puluhan menit). Akibatnya, kerugian finansial dan penurunan reputasi brand tidak dapat dihindarkan.

Selain itu, aplikasi web modern sering kali menderita masalah connection starvation dan query routing yang tidak efisien. Seluruh kueri baca (SELECT) dan tulis (INSERT/UPDATE/DELETE) dikirimkan ke satu titik yang sama tanpa pemisahan beban (Read/Write Splitting). Padahal, sebagian besar beban aplikasi web (>80%) adalah operasi pembacaan data.

Perbandingan Kondisi: Sebelum vs Sesudah Implementasi

Dengan mengintegrasikan ProxySQL sebagai lapisan intelligent proxy dan load balancer di depan, serta Orchestrator sebagai orkestrator topologi replikasi MySQL yang mendeteksi kegagalan secara real-time dan melakukan failover otomatis, kita membangun fondasi infrastruktur data yang tangguh, elastis, dan siap menghadapi beban skala perusahaan besar (enterprise-grade).

2. Konsep Inti & Deep Dive Arsitektur

Arsitektur High Availability (HA) MySQL berbasis Orchestrator dan ProxySQL beroperasi melalui sinergi dua komponen utama yang berdiri di antara aplikasi (Backend) dan klaster database.

Alur Kerja di Balik Layar (Under the Hood)

1. Aplikasi mengirim kueri: Aplikasi web (misalnya Node.js atau Go) terhubung ke ProxySQL (bukan langsung ke MySQL) melalui port 6033.
2. ProxySQL Query Routing: ProxySQL menganalisis kueri SQL menggunakan Query Rules.
   • Jika kueri adalah INSERT, UPDATE, DELETE, atau transaksi (BEGIN/COMMIT), ProxySQL secara otomatis merutekannya ke host MySQL Master.
   • Jika kueri adalah SELECT, ProxySQL merutekannya ke kelompok host MySQL Replica (Slave) menggunakan algoritma load balancing (misalnya Round Robin atau Weight-based).
3. Orchestrator Topology Monitoring: Orchestrator secara berkala melakukan polling ke seluruh instance MySQL untuk memetakan topologi replikasi secara akurat (menggunakan GTID - Global Transaction Identifiers).
4. Deteksi Kegagalan & Auto-Failover:
   • Jika Orchestrator mendeteksi MySQL Master down (tidak merespons health check), ia akan memicu proses pemulihan (failover).
   • Orchestrator memilih replica terbaik (yang memiliki posisi GTID paling mutakhir dan replication lag terkecil) untuk dipromosikan menjadi Master baru.
   • Orchestrator mengeksekusi perintah untuk mempromosikan replica tersebut dan mengonfigurasi sisa replica lainnya untuk mereplikasi dari Master baru.
5. Pembaruan Dinamis ProxySQL:
   • Melalui mekanisme Hooks atau integrasi API, Orchestrator memperbarui daftar backend server di ProxySQL, atau ProxySQL sendiri melakukan polling otomatis terhadap status topologi database.
   • ProxySQL segera mengalihkan kueri tulis ke Master baru tanpa memerlukan perubahan kode atau restart pada aplikasi.

Visualisasi Arsitektur (ASCII Flow Chart)

+-------------------------------------------------------------+
|                  Aplikasi Web (PHP / Go / Node)             |
+-------------------------------------------------------------+
                               |
                         Port 6033 (SQL)
                               v
+-------------------------------------------------------------+
|                         ProxySQL                            | <--- (Connection Pooling & Read/Write Splitting)
+-------------------------------------------------------------+
           /                      |                      \
          v                       v                       v
+-------------------+   +-------------------+   +-------------------+
|  MySQL Master     |   |  MySQL Replica 1  |   |  MySQL Replica 2  |
|   (Write / Read)  |   |    (Read Only)    |   |    (Read Only)    |
+-------------------+   +-------------------+   +-------------------+
          ^                       ^
          |                       |
          +-----------+-----------+
                      |
+-------------------------------------------------------------+
|                  Orchestrator Daemon                        | <--- (Topology Mapping & Auto-Failover)
+-------------------------------------------------------------+

3. Perbandingan Komparatif / Analisis Pendekatan

Sebelum memutuskan menggunakan stack Orchestrator + ProxySQL, mari kita bandingkan dengan solusi HA MySQL populer lainnya di industri.

[!TIP] Gunakan PXC / Galera Cluster jika aplikasi Anda menuntut konsistensi mutlak tanpa toleransi replication lag dan jaringan antar node sangat cepat (<2ms). Gunakan ProxySQL + Orchestrator untuk skala beban baca-tulis yang masif, fleksibilitas tinggi, dan integrasi dengan topologi standar MySQL (InnoDB).

4. Prasyarat Sistem & Persiapan Lingkungan

Untuk mengimplementasikan panduan ini secara sukses di lingkungan produksi, siapkan setidaknya 4 unit server (Virtual Private Server atau Bare Metal) dengan spesifikasi minimal:

• OS: Ubuntu 22.04 LTS / Debian 12 / RHEL 9 (Panduan ini menggunakan Ubuntu 22.04 LTS).
• Spesifikasi Minimal: 2 vCPU, 4GB RAM, SSD NVMe.

Peta IP Address Laboratorium Infrastruktur:

• 192.168.10.11: ProxySQL Node
• 192.168.10.12: MySQL Master (db-master)
• 192.168.10.13: MySQL Replica 1 (db-replica1)
• 192.168.10.14: MySQL Replica 2 (db-replica2)
• 192.168.10.15: Orchestrator Node

Pastikan sinkronisasi waktu menggunakan NTP/Chrony aktif di seluruh node agar replication lag dan log terkalibrasi dengan benar.

5. Panduan Implementasi Langkah demi Langkah

Langkah 1: Instalasi & Konfigurasi Dasar MySQL (Semua Node Database)

Lakukan instalasi MySQL 8.0 pada node db-master, db-replica1, dan db-replica2.

sudo apt update
sudo apt install -y mysql-server

Konfigurasikan file /etc/mysql/mysql.conf.d/ha.cnf di setiap node. Berikut adalah konfigurasi untuk MySQL Master (192.168.10.12):

[mysqld]
server-id                 = 101
bind-address              = 0.0.0.0
report_host               = db-master

# Replikasi & GTID Settings
gtid_mode                 = ON
enforce_gtid_consistency  = ON
log_bin                   = mysql-bin
log_slave_updates         = ON
binlog_format             = ROW
expire_logs_days          = 7

# Semi-sync Replication (Direkomendasikan untuk HA)
plugin_load_add           = "semisync_master.so"
plugin_load_add           = "semisync_slave.so"
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_slave_enabled  = 1

# InnoDB Tuning Dasar
innodb_buffer_pool_size   = 2G
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table     = 1

Konfigurasi untuk MySQL Replica 1 (192.168.10.13):

[mysqld]
server-id                 = 102
bind-address              = 0.0.0.0
report_host               = db-replica1

gtid_mode                 = ON
enforce_gtid_consistency  = ON
log_bin                   = mysql-bin
log_slave_updates         = ON
binlog_format             = ROW
read_only                 = ON

plugin_load_add           = "semisync_master.so"
plugin_load_add           = "semisync_slave.so"
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_slave_enabled  = 1

innodb_buffer_pool_size   = 2G
innodb_flush_log_at_trx_commit = 1

Ulangi konfigurasi serupa untuk db-replica2 dengan server-id = 103 dan report_host = db-replica2.

Restart layanan MySQL di semua node:

sudo systemctl restart mysql

Langkah 2: Pembuatan Pengguna Replikasi dan Orchestrator

Masuk ke konsol MySQL Master dan buat akun pengguna yang diperlukan oleh Orchestrator dan Replikasi:

CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'ReplPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

CREATE USER 'orchestrator'@'%' IDENTIFIED WITH mysql_native_password BY 'OrcPassword123!';
GRANT SUPER, PROCESS, REPLICATION SLAVE, REPLICATION CLIENT, SELECT, RELOAD ON *.* TO 'orchestrator'@'%';
GRANT CREATE, DROP, INSERT, UPDATE, DELETE, INDEX, ALTER, CREATE VIEW, SHOW VIEW ON orchestrator.* TO 'orchestrator'@'%';

CREATE USER 'proxysql_monitor'@'%' IDENTIFIED WITH mysql_native_password BY 'MonitorPassword123!';
GRANT REPLICATION CLIENT ON *.* TO 'proxysql_monitor'@'%';

FLUSH PRIVILEGES;

(Catatan: Jalankan perintah pembuatan user ini di semua node atau pastikan replikasi sudah berjalan agar user tereplikasi otomatis). Setup replikasi awal ke replica:

CHANGE MASTER TO 
  MASTER_HOST='192.168.10.12',
  MASTER_USER='repl',
  MASTER_PASSWORD='ReplPassword123!',
  MASTER_AUTO_POSITION=1;
START SLAVE;

Langkah 3: Instalasi dan Konfigurasi Orchestrator

Pada node Orchestrator (192.168.10.15), unduh dan instal paket 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 database backend untuk Orchestrator di salah satu instance MySQL (atau lokal di node dedicated):

CREATE DATABASE orchestrator;

Konfigurasikan file /etc/orchestrator.conf.json:

{
  "Debug": false,
  "EnableSyslog": true,
  "ListenAddress": ":3000",
  "MySQLTopologyCredentialsConfigFile": "",
  "Backend": "mysql",
  "MySQLConnectTimeout": 2,
  "DatabaseConfig": {
    "DbConfig": {
      "Driver": "mysql",
      "User": "orchestrator",
      "Password": "OrcPassword123!",
      "Name": "orchestrator",
      "Host": "192.168.10.12",
      "Port": 3306,
      "Parameters": {
        "parseTime": "true"
      }
    }
  },
  "STMTServer": "192.168.10.12:3306",
  "DiscoveryPollSeconds": 5,
  "RAFT": [],
  "SlaveLagQuery": "SELECT 0",
  "RecoveryIgnoreHostnameFilters": [],
  "ReleaseLockAfterFailure": true,
  "AutoMasterRecovery": true,
  "AutoIntermediateMasterRecovery": true,
  "FailureDetectionPeriod": 5,
  "RecoverMasterClusterFilters": [
    "*"
  ],
  "PostGracefulPromotionHooks": [
    "/usr/local/bin/orchestrator-proxysql-sync.sh {newMasterHost} {newMasterPort}"
  ],
  "PostMasterRecoveryHooks": [
    "/usr/local/bin/orchestrator-proxysql-sync.sh {newMasterHost} {newMasterPort}"
  ]
}

Jalankan layanan Orchestrator:

sudo systemctl enable orchestrator
sudo systemctl start orchestrator

Akses antarmuka web Orchestrator di http://192.168.10.15:3000 untuk memonitor topologi klaster.

Langkah 4: Instalasi dan Konfigurasi ProxySQL

Pada node ProxySQL (192.168.10.11), instal ProxySQL:

sudo apt install -y lsb-release gnupg curl
curl -s https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key.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 update
sudo apt install -y proxysql

Masuk ke CLI Admin ProxySQL:

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

Jalankan skrip konfigurasi SQL berikut di dalam Admin ProxySQL:

-- Konfigurasi Global
UPDATE global_variables SET variable_value='3306' WHERE variable_name='mysql-interfaces';

-- Tambahkan MySQL Servers ke Hostgroups
-- Hostgroup 10: Master (Write + Read)
-- Hostgroup 20: Replicas (Read Only)
INSERT INTO mysql_servers(hostgroup_id, port, status, weight, server_ip) VALUES (10, 3306, 'ONLINE', 100, '192.168.10.12');
INSERT INTO mysql_servers(hostgroup_id, port, status, weight, server_ip) VALUES (20, 3306, 'ONLINE', 100, '192.168.10.13');
INSERT INTO mysql_servers(hostgroup_id, port, status, weight, server_ip) VALUES (20, 3306, 'ONLINE', 100, '192.168.10.14');

-- Konfigurasi Monitor User
UPDATE global_variables SET variable_value='proxysql_monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='MonitorPassword123!' WHERE variable_name='mysql-monitor_password';

-- Tambahkan Aplikasi DB User
INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('app_user', '*4ACFE3202A5FF5CF467898FC58AAB1D615029441', 10);

-- Konfigurasi Query Rules untuk Read/Write Splitting
-- Aturan 1: Semua kueri WRITE/INSERT/UPDATE/DELETE dilempar 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);

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (2, 1, '^(INSERT|UPDATE|DELETE|REPLACE|CREATE|DROP|ALTER)', 10, 1);

-- Aturan 2: Kueri SELECT biasa dilempar ke Hostgroup 20 (Replicas)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (3, 1, '^SELECT', 20, 1);

-- Simpan konfigurasi ke memori 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;

Langkah 5: Skrip Integrasi Orchestrator & ProxySQL

Buat skrip bash di /usr/local/bin/orchestrator-proxysql-sync.sh untuk memperbarui ProxySQL secara otomatis saat failover terjadi:

#!/bin/bash
NEW_MASTER=$1
PROXYSQL_IP="192.168.10.11"
PROXYSQL_ADMIN_PORT="6032"

# Update Master di Hostgroup 10
mysql -u admin -padmin -h $PROXYSQL_IP -P $PROXYSQL_ADMIN_PORT -e "
UPDATE mysql_servers SET status='OFFLINE_SOFT' WHERE hostgroup_id=10;
DELETE FROM mysql_servers WHERE hostgroup_id=10;
INSERT INTO mysql_servers(hostgroup_id, port, status, weight, server_ip) VALUES (10, 3306, 'ONLINE', 100, '$NEW_MASTER');
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
"

echo "ProxySQL Master successfully updated to $NEW_MASTER"

Berikan izin eksekusi pada skrip tersebut:

sudo chmod +x /usr/local/bin/orchestrator-proxysql-sync.sh

6. Bedah Kode & Penjelasan Detail Baris Kunci

Mari kita bedah beberapa parameter krusial dari konfigurasi di atas untuk memastikan pemahaman mendalam:

1. gtid_mode = ON & enforce_gtid_consistency = ON:
   • Global Transaction Identifier adalah jantung dari replikasi MySQL modern. Tanpa GTID, Orchestrator akan kesulitan melacak posisi binlog secara presisi saat terjadi failover, sehingga risiko kehilangan data atau inkonsistensi replikasi meningkat drastis.
2. rpl_semi_sync_master_enabled = 1:
   • Konfigurasi ini memastikan transaksi tidak dianggap commit secara utuh oleh Master sebelum setidaknya satu replica menerima dan menuliskan event transaksi tersebut ke relay log mereka. Ini mencegah Data Loss (RPO = 0) saat terjadi kegagalan mendadak pada Master.
3. mysql_query_rules pada ProxySQL:
   • Regular expression ^(INSERT|UPDATE|DELETE...) memastikan bahwa kueri modifikasi data tidak pernah bocor ke replica (yang berstatus read-only), mencegah error fatal Read-only server pada aplikasi.

7. Skenario Produksi & Studi Kasus Dunia Nyata

Pada platform E-commerce dengan 50.000 concurrent users, lonjakan trafik saat Flash Sale menghasilkan puluhan ribu kueri per detik.

• Strategi Beban Baca: Aplikasi mengirim seluruh kueri pencarian produk (SELECT * FROM products...) melalui ProxySQL port 6033. ProxySQL mendistribusikan beban secara merata ke db-replica1 dan db-replica2 menggunakan algoritma Weight-based Round-Robin.
• Strategi Beban Tulis: Kueri keranjang belanja dan transaksi pembayaran (INSERT INTO orders...) secara otomatis dirutekan oleh ProxySQL ke db-master (192.168.10.12).
• Skalabilitas & Resiliensi: Ketika db-master mengalami gangguan kelistrikan, Orchestrator mendeteksi kegagalan dalam waktu 5 detik, mempromosikan db-replica1 menjadi Master baru, memanggil skrip sinkronisasi ProxySQL, dan aplikasi tetap berjalan tanpa mengalami terputusnya koneksi database (seamless failover).

8. Common Pitfalls, Anti-Patterns & Solusi

[!WARNING] Kesalahan Fatal #1: Mengabaikan Pengaturan max_connections pada ProxySQL dan MySQL Aplikasi sering kali membuka terlalu banyak koneksi paralel. Jika max_connections MySQL lebih kecil dari kapasitas tampung ProxySQL, database akan menolak koneksi baru. Solusi: Selalu aktifkan Connection Pooling di ProxySQL (mysql-max_connections_per_mysql_server) dan atur nilai yang proporsional pada variabel max_connections di file my.cnf.

[!WARNING] Kesalahan Fatal #2: Masalah Replikasi Terhenti Akibat Duplikasi Primary Key Saat terjadi failover darurat, terkadang ada transaksi yang belum sepenuhnya tersinkronisasi (slave lag). Solusi: Selalu gunakan protokol Semi-Sync Replication dan pastikan parameter slave_parallel_workers diaktifkan untuk mempercepat proses penerapan binlog di sisi replica.

9. Benchmark, Metrik Kinerja & Tuning Performa

Untuk mengukur ketahanan klaster, lakukan pengujian beban menggunakan tool seperti sysbench:

# Inisialisasi data benchmark sysbench
sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-host=192.168.10.11 \
  --mysql-port=6033 \
  --mysql-user=app_user \
  --mysql-password=AppPassword123! \
  --mysql-db=test \
  prepare

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

Metrik Kinerja Utama yang Harus Dipantau:

1. QPS (Queries Per Second): Memantau kapasitas throughput ProxySQL.
2. Replication Lag: Memastikan durasi sinkronisasi master ke replica mendekati 0 detik.
3. Connection Pool Utilization: Memastikan koneksi dari ProxySQL ke backend MySQL tidak mengalami antrean panjang.

10. Checklist Penerapan Produksi & Kesimpulan

Sebelum meluncurkan arsitektur ini ke lingkungan production, pastikan checklist berikut terpenuhi:

• GTID aktif di seluruh node MySQL.
• Semi-sync replication diaktifkan untuk mencegah kehilangan data.
• Orchestrator terkonfigurasi dengan auto-master recovery dan hook ke ProxySQL.
• ProxySQL Query Rules telah diuji untuk memastikan pemisahan baca/tulis berfungsi sempurna.
• Uji coba Chaos Engineering (mematikan paksa node master menggunakan sudo poweroff) telah dilakukan dan berhasil melakukan failover otomatis di bawah 10 detik.
• Monitoring Prometheus & Grafana terpasang untuk memantau metrik kesehatan database secara real-time.

Dengan memadukan ketangguhan pemantauan Orchestrator dan kecepatan perutean cerdas ProxySQL, infrastruktur database MySQL Anda kini memiliki daya tahan tingkat enterprise yang handal, aman, dan siap menghadapi skala pertumbuhan bisnis tanpa batas.