Konfigurasi Server

Server MySQL bukan kotak hitam dengan perilaku yang terkunci: ratusan system variable mengatur semuanya, mulai dari berapa koneksi yang diterima, seberapa besar memori untuk cache, hingga di mana file log ditulis. Sebagai administrator, kita perlu dua keterampilan dasar — membaca nilai yang sedang berlaku dan mengubahnya dengan cara yang benar tanpa merusak server. Chapter ini membuka section administrasi MySQL dengan membahas file konfigurasi my.cnf/my.ini, perintah SHOW VARIABLES, perbedaan SET GLOBAL dan SET PERSIST, serta aturan scope yang menentukan variabel mana yang bisa diubah dan kapan restart server benar-benar diperlukan.

File Konfigurasi my.cnf dan my.ini #

Saat proses mysqld dinyalakan, server membaca opsi startup dari satu atau lebih file konfigurasi. Di Linux dan macOS file tersebut bernama my.cnf, sedangkan di Windows bernama my.ini. Server membaca file-file ini dari beberapa lokasi standar secara berurutan, dan nilai yang dibaca terakhir menang atas yang lebih awal:

File konfigurasi terbagi menjadi section yang ditandai kurung siku. Section [mysqld] menampung opsi untuk server, [client] untuk seluruh program klien, dan [mysql] khusus untuk klien interaktif mysql. Contoh berikut memperlihatkan isi file yang lazim:

[mysqld]
port=3306
character-set-server=utf8mb4
innodb_buffer_pool_size=1G
max_connections=200

[client]
port=3306

Setiap baris di section [mysqld] di sini adalah server system variable yang nilainya terpasang saat startup. Perubahan pada file ini hanya berlaku setelah server direstart — file konfigurasi tidak pernah dibaca ulang secara otomatis. Di server produksi yang sibuk, restart adalah peristiwa besar, sehingga MySQL menyediakan jalan lain untuk mengubah variabel tanpa mematikan server, yang akan kita bahas di bagian berikutnya.

Membaca Variabel dengan SHOW VARIABLES #

Perintah SHOW VARIABLES menampilkan seluruh variabel yang sedang berlaku. Karena daftarnya panjang, kita hampir selalu menyaringnya dengan klausa LIKE. Dua variabel berikut akan sering kita jumpai sepanjang section ini:

SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'long_query_time';

Output yang Dihasilkan:

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

Nilai max_connections adalah batas jumlah koneksi simultan yang diterima server, sedangkan long_query_time adalah ambang detik sebelum sebuah query dianggap lambat dan dicatat ke slow log. Pola LIKE juga mendukung wildcard %, misalnya SHOW VARIABLES LIKE 'innodb_%' untuk menyaring seluruh variabel InnoDB. Alternatif lain yang berguna di dalam ekspresi adalah sintaks @@nama_variabel:

SELECT @@max_connections AS batas_koneksi;

Output yang Dihasilkan:

+---------------+
| batas_koneksi |
+---------------+
|           151 |
+---------------+
1 row in set (0.00 sec)

Kedua cara membaca ini mengembalikan sumber yang sama — data dictionary server — sehingga hasilnya selalu identik. Sintaks @@ unggul saat nilai variabel perlu diikutsertakan ke dalam query, misalnya dibandingkan dengan angka dari kolom kita sendiri.

Mengubah Variabel Saat Runtime: SET GLOBAL #

Sebagian besar variabel bisa diubah tanpa restart memakai SET GLOBAL. Perubahan berlaku seketika untuk seluruh server dan bertahan selama server hidup. Kita coba dengan max_connect_errors, lalu kembalikan nilainya lagi dalam blok yang sama:

SET GLOBAL max_connect_errors = 150;
SHOW VARIABLES LIKE 'max_connect_errors';
SET GLOBAL max_connect_errors = 100;
SHOW VARIABLES LIKE 'max_connect_errors';

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 150   |
+--------------------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
+--------------------+-------+
1 row in set (0.00 sec)

Setelah SET GLOBAL, perintah SHOW VARIABLES membaca nilai baru — 150 — karena SHOW VARIABLES tanpa kata SESSION menampilkan nilai global untuk variabel ber-scope global. Statement kedua memulihkan nilai 100 supaya server latihan kembali ke kondisi semula; kebiasaan mengembalikan setiap variabel yang kita ubah adalah disiplin penting saat bekerja di server bersama. Satu hal yang SET GLOBAL tidak lakukan: nilainya hilang saat server direstart karena perubahan runtime tidak pernah ditulis ke file konfigurasi.

SET PERSIST: Perubahan yang Bertahan Restart #

Sejak MySQL 8.0, SET PERSIST mengubah variabel sekaligus menyimpannya ke file mysqld-auto.cnf di dalam data directory, sehingga nilai bertahan melewati restart tanpa menyentuh my.cnf:

SET PERSIST max_connect_errors = 150;
SHOW VARIABLES LIKE 'max_connect_errors';
SET PERSIST max_connect_errors = 100;
SHOW VARIABLES LIKE 'max_connect_errors';

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 150   |
+--------------------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
+--------------------+-------+
1 row in set (0.00 sec)

Mekanisme kerjanya sederhana namun rapi: nilai baru diterapkan ke memori seperti SET GLOBAL, lalu server menulisnya dalam format JSON ke mysqld-auto.cnf yang dibaca kembali pada startup berikutnya. Saat kita mengembalikan nilai 100 pada statement kedua, file persist juga diperbarui sehingga tidak meninggalkan sisa konfigurasi yang mengejutkan di kemudian hari. Untuk sekadar menyimpan tanpa langsung menerapkan ada SET PERSIST_ONLY, dan RESET PERSIST menghapus entri dari file tersebut. Perintah SET PERSIST hanya berlaku untuk variabel global — kita buktikan dengan mencoba variabel ber-scope session:

-- @error
SET PERSIST sql_log_bin = 1;

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

ERROR 1228 (HY000): Variable 'sql_log_bin' is a SESSION variable and can't be used with SET GLOBAL
Bye

Error ini muncul karena sql_log_bin hanya punya scope SESSION, sedangkan PERSIST selalu bekerja pada level global. Teks errornya sendiri menarik: MySQL menyebut PERSIST sebagai bentuk dari SET GLOBAL, sehingga penolakannya identik dengan SET GLOBAL pada variabel session.

Scope Global vs Session #

Setiap variabel punya salah satu dari tiga scope: GLOBAL saja, SESSION saja, atau keduanya. Variabel scope SESSION punya satu nilai per koneksi — tiap koneksi baru mewarisi salinan nilai global saat terhubung, lalu perubahannya tidak terlihat oleh koneksi lain. Kita amati langsung dengan wait_timeout:

SHOW VARIABLES LIKE 'wait_timeout';
SET SESSION wait_timeout = 60;
SHOW VARIABLES LIKE 'wait_timeout';

Output yang Dihasilkan:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 60    |
+---------------+-------+
1 row in set (0.00 sec)

Nilainya berubah dari 28800 menjadi 60 hanya untuk sesi kita. Sekarang kita periksa lagi dari sesi yang baru dibuka:

SHOW VARIABLES LIKE 'wait_timeout';

Output yang Dihasilkan:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

Sesi baru membaca 28800 — perubahan SET SESSION lenyap begitu koneksi yang mengubahnya ditutup, dan tidak pernah menyentuh nilai global. Sebaliknya, SET GLOBAL mengubah nilai untuk koneksi-koneksi yang akan datang, sementara sesi yang sudah terbuka tetap membawa salinan lama sampai tersambung ulang. Perbedaan arah ini sering mengejutkan: mengubah SET GLOBAL lalu memeriksanya dari sesi yang sama justru masih menampilkan nilai lama.

Variabel yang Menuntut Restart #

Tidak semua variabel bisa diubah saat runtime. Variabel seperti innodb_data_file_path ber-scope global dan read-only — nilainya menentukan struktur file system tablespace yang dibuat saat server pertama kali dinyalakan, sehingga mengubahnya di tengah jalan tidak masuk akal:

-- @error
SET GLOBAL innodb_data_file_path = 'ibdata1:12M:autoextend';

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

ERROR 1238 (HY000): Variable 'innodb_data_file_path' is a read only variable
Bye

Error 1238 menyatakan dengan gamblang bahwa variabel tersebut hanya bisa diubah lewat file konfigurasi sebelum startup. Dokumentasi resmi menandai setiap variabel dengan atribut Dynamic — bila Yes, variabel bisa di-SET saat runtime; bila No, satu-satunya jalan adalah file konfigurasi atau opsi command line diikuti restart. Untuk memeriksa seluruh opsi startup yang dikenal server (lengkap dengan nilai default-nya), kita bisa menjalankan dari terminal:

mysqld --verbose --help

Output ratusan barisnya memuat setiap opsi --nama-opsi, nilai default, dan variabel sistem yang bersangkutan — referensi cepat saat kita ragu apakah suatu pengaturan bisa diubah tanpa restart.

Referensi #

• Server System Variables — MySQL 8.4 Reference Manual
• SHOW VARIABLES Statement — MySQL 8.4 Reference Manual
• SET Statement — MySQL 8.4 Reference Manual
• The MySQL Server — MySQL 8.4 Reference Manual

Kita kini bisa membaca dan mengubah perilaku server lewat variabel-variabelnya. Namun variabel hanyalah tombol-tombol di panel — mesin yang sebenarnya menyimpan data kita adalah storage engine. Chapter berikutnya membedah engine-engine yang tersedia dan alasan InnoDB menjadi pilihan default.