Membangun High Availability Database Cluster dengan MySQL InnoDB Cluster dan ProxySQL

1. Konteks Industri & Analisis Akar Masalah

Dalam lanskap arsitektur sistem modern saat ini, ketersediaan tinggi (High Availability / HA) dan skalabilitas horizontal untuk lapisan basis data adalah landasan absolut yang tidak bisa ditawar. Kegagalan basis data tunggal (Single Point of Failure / SPOF) bukan hanya berarti terhentinya transaksi bisnis, tetapi juga berpotensi merusak integritas data, menurunkan kepercayaan pengguna, serta menimbulkan kerugian finansial yang masif.

Secara tradisional, arsitektur basis data berbasis Primary-Replica (Master-Slave) sering kali menjadi pilihan pertama. Namun, pendekatan ini memiliki kelemahan struktural yang krusial: kegagalan pada simpul Primary memerlukan intervensi manual untuk mempromosikan Replica menjadi Primary yang baru, atau mengandalkan skrip eksternal yang rentan terhadap split-brain syndrome.

[!WARNING] Split-brain syndrome terjadi ketika dua simpul dalam kluster mengira diri mereka adalah Primary yang sah secara bersamaan, menyebabkan penulisan data yang terpecah dan korupsi data permanen yang hampir mustahil untuk dipulihkan secara otomatis.

Botleneck performa lain yang sering dihadapi oleh pengembang aplikasi web (baik menggunakan PHP-FPM, Node.js, maupun Go) adalah ketidakmampuan untuk mendistribusikan beban kueri baca (read queries) secara cerdas tanpa mengubah kode sumber aplikasi secara masif. Di sinilah kombinasi antara MySQL InnoDB Cluster (yang mendasari Group Replication, MySQL Shell, dan MySQL Router) dan ProxySQL menjadi solusi enterprise yang sangat tangguh.

Perbandingan Kondisi: Sebelum vs Sesudah Penerapan

2. Konsep Inti & Deep Dive Arsitektur

Arsitektur yang kita bangun terdiri dari dua pilar utama: lapisan konsensus penyimpanan data (MySQL InnoDB Cluster) dan lapisan perutean serta optimasi lalu lintas jaringan (ProxySQL).

Alur Eksekusi di Bawah Tendangan Mesin (Under the Hood)

1. Klien / Aplikasi Web mengirimkan kueri SQL melalui koneksi TCP standar ke ProxySQL pada port 6033.
2. ProxySQL Query Processor menganalisis kueri menggunakan Query Rules yang telah dikonfigurasi:
   • Jika kueri adalah operasi tulis (INSERT, UPDATE, DELETE, BEGIN, COMMIT), ProxySQL secara otomatis mengarahkannya ke simpul Primary di dalam InnoDB Cluster.
   • Jika kueri adalah operasi baca (SELECT), ProxySQL mendistribusikannya ke simpul-simpul Secondary (Read-Only) menggunakan algoritma Weighted Round-Robin atau Latency-Based.----
3. MySQL Group Replication (MGR) di balik layar menggunakan protokol konsensus Paxos untuk memastikan bahwa setiap transaksi yang dilakukan pada simpul Primary telah divalidasi dan direplikasi ke seluruh simpul anggota kluster sebelum transaksi tersebut dianggap sukses (commit).

+-----------------------+
|  Aplikasi Web / API   |
+-----------------------+
            |
            v (Port 6033)
+-----------------------+
|       ProxySQL        | <---> [Query Rules & Read/Write Splitting]
+-----------------------+
       /    |    \
      /     |     \
     v      v      v
+-------+ +-------+ +-------+
| MySQL | | MySQL | | MySQL |
| Node1 | | Node2 | | Node3 |
+-------+ +-------+ +-------+
\_______________________/
    MySQL InnoDB Cluster
    (Group Replication)

3. Perbandingan Komparatif / Analisis Pendekatan

Dalam membangun kluster MySQL tingkat lanjut, arsitek sering kali dihadapkan pada pilihan antara menggunakan MySQL Router bawaan resmi dari Oracle atau menggunakan ProxySQL pihak ketiga yang kaya fitur.

[!TIP] Gunakan ProxySQL jika aplikasi Anda memerlukan fitur lanjutan seperti query caching, query rewriting untuk memperbaiki kueri buruk tanpa mengubah kode aplikasi, serta manajemen connection pooling yang ketat untuk mencegah kelelahan koneksi (max_connections exceeded) pada MySQL.

4. Prasyarat Sistem & Persiapan Lingkungan

Untuk demonstrasi produksi ini, kita asumsikan menggunakan infrastruktur dengan spesifikasi minimum 4 Virtual Machine (VM) berbasis Ubuntu Server 22.04 LTS:

• ProxySQL Node: 1 IP (Contoh: 192.168.1.10) - 2 vCPU, 4GB RAM
• MySQL Node 1 (Primary/Bootstrap): 1 IP (192.168.1.11) - 4 vCPU, 8GB RAM, SSD NVMe
• MySQL Node 2: 1 IP (192.168.1.12) - 4 vCPU, 8GB RAM, SSD NVMe
• MySQL Node 3: 1 IP (192.168.1.13) - 4 vCPU, 8GB RAM, SSD NVMe

Persiapan Sistem Operasi (Diterapkan di Semua Node MySQL)

Lakukan penonaktifan Swap dan optimasi parameter kernel Linux untuk performa database optimal.

# Menonaktifkan swap secara permanen
sudo swapoff -a
sudo sed -i '/ swap / s/^/#/' /etc/fstab

# Optimasi sysctl untuk jaringan dan memori
cat <<EOF | sudo tee /etc/sysctl.d/99-mysql-cluster.conf
net.core.somaxconn = 65535
fs.file-max = 2097152
vm.swappiness = 1
vm.dirty_background_ratio = 5
vm.dirty_ratio = 10
EOF

sudo sysctl --system

5. Panduan Implementasi Langkah demi Langkah

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

Sesuaikan parameter berikut pada setiap simpul MySQL (192.168.1.11, 192.168.1.12, 192.168.1.13). Perhatikan variabel server_id yang harus unik.

[mysqld]
server-id               = 11
port                    = 3306
bind-address            = 0.0.0.0
mysqlx                  = 0

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

# Pengaturan Replikasi yang Diperlukan untuk Group Replication
gtid_mode               = ON
enforce_gtid_consistency = ON
master_info_repository  = TABLE
relay_log_info_repository = TABLE
binary_log_checksum     = NONE
log_slave_updates       = ON
binlog_format           = ROW

# Parameter Group Replication
group_replication_group_name = "335661b0-e74b-11ee-8962-0242ac120002"
group_replication_start_on_boot = OFF
group_replication_local_address = "192.168.1.11:33061"
group_replication_group_seeds = "192.168.1.11:33061,192.168.1.12:33061,192.168.1.13:33061"
group_replication_bootstrap_group = OFF

[!IMPORTANT] Pastikan Anda mengganti nilai server-id (misalnya 12 untuk Node 2, 13 untuk Node 3) dan group_replication_local_address sesuai dengan IP lokal masing-masing simpul.

Langkah 2: Bootstrap dan Pembentukan InnoDB Cluster Menggunakan MySQL Shell

Masuk ke MySQL Node 1 dan jalankan MySQL Shell untuk membuat kluster.

mysqlsh --js -h 192.168.1.11 -u root -p

Di dalam prompt MySQL Shell, jalankan perintah berikut:

// Validasi kesiapan instans untuk kluster
dba.checkInstanceConfiguration('root@192.168.1.11:3306');

// Membuat kluster baru dan melakukan bootstrap pada node pertama
var cluster = dba.createCluster('prodCluster');

// Menambahkan node kedua dan ketiga ke dalam kluster
cluster.addInstance('root@192.168.1.12:3306');
cluster.addInstance('root@192.168.1.13:3306');

// Periksa status kluster
cluster.status();

Langkah 3: Instalasi dan Konfigurasi ProxySQL (192.168.1.10)

Instal ProxySQL dari repositori resmi.

sudo apt-get update && sudo apt-get install -y lsb-release gnupg
wget -O - 'https://repo.proxysql.com/ProxySQL/repo_pub_key' | sudo apt-key add -
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-get update && sudo apt-get install -y proxysql
sudo systemctl start proxysql

Masuk ke antarmuka administratif ProxySQL:

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

Jalankan perintah SQL konfigurasi berikut di dalam konsol administratif ProxySQL:

-- 1. Konfigurasi Server MySQL Backend
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES 
(1, '192.168.1.11', 3306),
(1, '192.168.1.12', 3306),
(1, '192.168.1.13', 3306);

-- 2. Konfigurasi Pengguna Database (Ganti dengan kredensial asli Anda)
INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('app_user', '*4ACFE3202A5FF5CF467898FC58AAB1D615029441', 1);

-- 3. Konfigurasi Aturan Pemisahan Baca/Tulis (Read/Write Splitting)
-- Kueri tulis diarahkan ke Hostgroup 1 (Primary)
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) VALUES 
(1, 1, '^SELECT.*FOR UPDATE', 1, 1),
(2, 1, '^INSERT', 1, 1),
(3, 1, '^UPDATE', 1, 1),
(4, 1, '^DELETE', 1, 1);

-- Kueri baca biasa diarahkan ke Hostgroup 2 (Secondary / Readers)
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) VALUES 
(5, 1, '^SELECT', 2, 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 beberapa parameter krusial dalam konfigurasi mysqld.cnf dan ProxySQL:

• gtid_mode = ON dan enforce_gtid_consistency = ON: Memastikan setiap transaksi memiliki pengidentifikasi global unik (Global Transaction Identifier). Ini adalah fondasi wajib bagi Group Replication agar simpul dapat melacak posisi replikasi secara absolut tanpa bergantung pada nama file binary log manual.
• binlog_format = ROW: Menyatakan bahwa perubahan data dicatat berdasarkan baris yang diubah, bukan pernyataan SQL mentah. Ini menghilangkan potensi inkondisi data akibat fungsi non-deterministik (NOW(), RAND()) saat direplikasi.
• destination_hostgroup pada ProxySQL: Dengan memisahkan hostgroup_id 1 (untuk Primary) dan hostgroup_id 2 (untuk Secondary), ProxySQL bertindak sebagai lapisan abstraksi cerdas yang memotong kebutuhan aplikasi backend untuk mengetahui topologi fisik database.

7. Skenario Produksi & Studi Kasus Dunia Nyata

Dalam skenario flash sale e-commerce dengan tingkat konkurensi mencapai 15.000 permintaan per detik (RPS), arsitektur ini mendemonstrasikan ketahanan yang luar biasa:

• Pemisahan Beban: 80% lalu lintas adalah kueri baca (pencarian produk, katalog), yang didistribusikan secara merata oleh ProxySQL ke Node 2 dan Node 3.
• Proteksi Primary: Node 1 hanya menerima 20% kueri tulis (checkout, pengurangan stok), menjaganya tetap berada di bawah ambang batas penggunaan CPU yang aman (< 60%).
• Failover Transparan: Ketika Node 1 (Primary) tiba-tiba mengalami gangguan perangkat keras (kegagalan daya), Group Replication secara otomatis memilih Node 2 sebagai Primary baru dalam waktu kurang dari 3 detik. ProxySQL mendeteksi perubahan peran ini melalui mekanisme pemantauan kesehatan internal (mysql_servers), dan secara instan mengalihkan lalu lintas tulis ke Node 2 tanpa memutus koneksi aplikasi web.

8. Common Pitfalls, Anti-Patterns & Solusi

1. Anti-Pattern: Menggunakan Auto-Commit = 0 Tanpa Manajemen Koneksi yang Baik
   • Masalah: Transaksi yang menggantung (long-running transactions) akan menahan kunci baris dan menyebabkan antrean di dalam Group Replication, memicu lonjakan memori dan latensi tinggi.
   • Solusi: Selalu pastikan aplikasi menutup koneksi dan melakukan commit atau rollback secara eksplisit. Gunakan fitur Query Timeout di ProxySQL.
2. Kesalahan Fatal: Mengabaikan Latensi Jaringan Antar Simpul
   • Masalah: Group Replication sangat sensitif terhadap latensi jaringan. Jika latensi antar simpul melebihi 10ms, transaksi akan mengalami timeout dan simpul dapat dikeluarkan dari kluster (* expelled *).
   • Solusi: Tempatkan seluruh simpul database dalam satu zona ketersediaan (Availability Zone) yang sama dengan koneksi jaringan minimal 10Gbps.

9. Benchmark, Metrik Kinerja & Tuning Performa

Untuk menguji ketahanan kluster, kita dapat menggunakan alat benchmark sysbench untuk mensimulasikan beban tulis dan baca secara bersamaan.

# Menyiapkan data uji sysbench pada ProxySQL (Port 6033)
sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-host=192.168.1.10 \
  --mysql-port=6033 \
  --mysql-user=app_user \
  --mysql-password='password_anda' \
  --mysql-db=test \
  --tables=10 \
  --table-size=100000 \
  prepare

# Menjalankan pengujian beban (Load Testing)
sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-host=192.168.1.10 \
  --mysql-port=6033 \
  --mysql-user=app_user \
  --mysql-password='password_anda' \
  --mysql-db=test \
  --tables=10 \
  --table-size=100000 \
  --threads=64 \
  --time=300 \
  --report-interval=10 \
  run

Interpretasi Metrik Kinerja

• Transactions Per Second (TPS): Perhatikan kestabilan angka TPS. Penurunan tajam menandakan adanya hambatan pada InnoDB Buffer Pool atau antrean replikasi Paxos.
• Latency (P99): Pastikan latensi persentil ke-99 tetap berada di bawah 25ms di bawah beban puncak.

10. Checklist Penerapan Produksi & Kesimpulan

Sebelum merilis arsitektur ini ke lingkungan produksi langsung, pastikan Anda telah memverifikasi daftar periksa berikut:

• Semua simpul MySQL memiliki sinkronisasi waktu NTP yang akurat.
• Konfigurasi Firewall (UFW/iptables) hanya mengizinkan lalu lintas antar simpul pada port replikasi (33061) dan port klien (3306).
• Kata sandi akun administratif ProxySQL dan MySQL telah diubah dari nilai default.
• Pengujian kegagalan tiruan (Chaos Engineering / Chaos Testing) telah dilakukan dengan mematikan simpul Primary secara paksa untuk memvalidasi otomatisasi failover.
• Sistem pencatatan (monitoring) seperti Prometheus dan Grafana telah dihubungkan ke ProxySQL Stats dan MySQL Performance Schema.

Dengan menerapkan arsitektur MySQL InnoDB Cluster yang dikombinasikan dengan ProxySQL, Anda telah membangun fondasi basis data enterprise yang tahan banting, aman, dan siap menghadapi skala transaksi web modern tanpa kompromi.