Log Management di MySQL: Memantau dan Menganalisis Query yang Berjalan

Mengelola log di MySQL sangat penting untuk memantau aktivitas database, menganalisis kinerja query, dan mendeteksi potensi masalah. Dengan log yang tepat, kita dapat memahami bagaimana MySQL bekerja, mengoptimalkan query, serta meningkatkan keamanan database.

1. Jenis-Jenis Log di MySQL

MySQL memiliki beberapa jenis log yang dapat digunakan untuk memantau dan menganalisis query:

Jenis Log	Fungsi
General Query Log	Mencatat semua perintah SQL yang dieksekusi
Slow Query Log	Mencatat query yang berjalan lebih lama dari batas yang ditentukan
Binary Log (Binlog)	Mencatat semua perubahan data (DML) untuk replikasi atau recovery
Error Log	Menyimpan informasi error, startup, dan shutdown MySQL
Audit Log	Mencatat aktivitas pengguna (khusus MySQL Enterprise)

2. Mengaktifkan dan Menggunakan General Query Log

General Query Log adalah log yang mencatat semua perintah SQL yang dijalankan oleh MySQL.

Mengaktifkan General Query Log

Edit file konfigurasi my.cnf atau my.ini:
general_log = 1
general_log_file = /var/log/mysql_general.log

Kemudian restart MySQL:

systemctl restart mysql

Atau, aktifkan secara dinamis:

SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql_general.log';

Melihat Isi General Query Log

Gunakan perintah berikut untuk melihat log secara real-time:

tail -f /var/log/mysql_general.log

Contoh isi log:

2024-04-10 15:30:10   root[localhost]    Query   SELECT * FROM users;
2024-04-10 15:30:15   admin[192.168.1.100]   Query   UPDATE products SET price = 500 WHERE id = 1;

Dari log ini, kita bisa melihat:

• Waktu eksekusi query
• User yang menjalankan perintah
• IP asal permintaan
• Perintah SQL yang dieksekusi

Catatan: General Query Log dapat menghasilkan file log yang sangat besar, sehingga disarankan hanya mengaktifkannya saat debugging.

3. Mengaktifkan dan Menganalisis Slow Query Log

Slow Query Log sangat berguna untuk mengidentifikasi query yang lambat, membantu dalam optimasi performa database.

Mengaktifkan Slow Query Log

Tambahkan konfigurasi berikut ke my.cnf:

slow_query_log = 1
slow_query_log_file = /var/log/mysql_slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

• long_query_time = 2 → Merekam query yang berjalan lebih dari 2 detik.
• log_queries_not_using_indexes = 1 → Merekam query yang tidak menggunakan indeks.

Kemudian restart MySQL:

systemctl restart mysql

Atau aktifkan secara langsung:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

Menganalisis Slow Query Log

Gunakan perintah berikut untuk melihat isi log:

cat /var/log/mysql_slow.log

Contoh isi log:

# Time: 2024-04-10T15:45:12
# User@Host: admin[admin] @ 192.168.1.100
# Query_time: 5.24  Lock_time: 0.01 Rows_sent: 10000 Rows_examined: 500000
SELECT * FROM orders WHERE order_date > '2024-01-01';

Kita bisa melihat:

• Query_time → Waktu eksekusi query.
• Lock_time → Waktu yang dihabiskan untuk mengunci tabel.
• Rows_examined → Jumlah baris yang diperiksa.

Untuk analisis lebih lanjut, gunakan mysqldumpslow:

mysqldumpslow -s t /var/log/mysql_slow.log

Sortir query yang paling lambat berdasarkan waktu eksekusi:

mysqldumpslow -s t -t 5 /var/log/mysql_slow.log

4. Menggunakan Binary Log (Binlog) untuk Melacak Perubahan Data

Binary Log (Binlog) mencatat semua perubahan pada database yang berkaitan dengan INSERT, UPDATE, DELETE. Binlog berguna untuk replikasi dan recovery database.

Mengaktifkan Binlog

Tambahkan di my.cnf:

[mysqld]
log_bin = /var/log/mysql-bin
expire_logs_days = 7
binlog_format = ROW

Kemudian restart MySQL:

systemctl restart mysql

Cek status binlog:

SHOW BINARY LOGS;

Menganalisis Binlog dengan mysqlbinlog

mysqlbinlog /var/log/mysql-bin.000001 | less

Contoh isi binlog:

# at 12345
# User@Host: admin[admin] @ 192.168.1.100
# Query_time: 0.2
INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]');

Binlog sangat penting untuk rollback perubahan data dan replikasi database.

5. Mengelola Log MySQL

Log dapat menjadi sangat besar, sehingga perlu dikelola dengan baik.

Menghapus Log Lama Secara Otomatis

Tambahkan cron job untuk membersihkan log lama setiap minggu:

0 0 * * 7 find /var/log/mysql* -type f -mtime +7 -delete

Atau gunakan perintah SQL untuk menghapus binlog lama:

PURGE BINARY LOGS BEFORE '2024-04-01 00:00:00';

Menonaktifkan Log Sementara

Jika log terlalu besar, Anda bisa menonaktifkan sementara:

SET GLOBAL general_log = 'OFF';
SET GLOBAL slow_query_log = 'OFF';