MySQL Binlog, Jurus Ampuh Recover Data

MySQL adalah salah satu database server, yang bisa dikatakan paling populer di seluruh dunia. MySQL memiliki sejarah panjang, sejak tahun 1979 (hayo, sudah lahir belum?), yang kemudian mulai digunakan untuk pengembangan aplikasi berbasis web pada tahun 1994. MySQL diakuisisi oleh Oracle pada tahun 2008, kemudian MySQL membuat branding sendiri, tetap open source, dengan branding nama MariaDB. Konon akuisisi Oracle terhadap MySQL ini karena Oracle merasa tersaingi. Padahal sudah jelas, pangsa penggunanya berbeda. Oracle vs MySQL sering dianalogikan seperti truk vs sedan. Sedan memang akan berlari kencang di jalan kecil, dengan beban yang pas. Oracle tanpa data pun tidak akan sekencang mobil sedan. Namun ketika semua diberikan beban besar, sedan akan segera turun performa dengan cepat, dan Oracle masih tidak akan ada perubahan.

MySQL/MariaDB masih menjadi primadona para developer masa kini, meski sudah muncul banyak database server lainnya, baik model SQL maupun No-SQL. Aplikasi-aplikasi CMS web yang banyak digunakan seperti WordPress, Joomla, ataupun CMS akademiks seperti Moodle, OJS, masih mendukung MySQL. Bahkan cPanel masih mendukung database server MySQL saja. Untuk di eCampuz, sebagian besar aplikasi menggunakan MySQL sebagai database servernya, baik aplikasi utama seperti eAkademik, maupun aplikasi-aplikasi yang digunakan untuk mendukung akademik seperti Eprints, ataupun Moodle dan aplikasi CMS Website.

Mengenal MySQL Binlog (MySQL Binary Logs)

Sebagai software layanan penyimpanan data, maka MySQL juga tetap memiliki risiko bermasalah dengan data. Entah data yang secara human error terhapus, atau data yang bermasalah karena hardware error, atau segudang potensi insiden lainnya. Atas hal tersebut, dan beberapa hal lainnya, MySQL membuat beberapa skema untuk mempermudah recover data. Salah satunya adalah Binary Logs, atau Binlog. Binlog dirancang untuk memudahkan transaction logging.

Saat kita membuat database, membuat tabel, mengisi tabel, menghapus record, drop tables, maka semua proses tersebut akan tersimpan dalam MySQL Binlog. Sehingga segala macam transaksi tersebut dapat dikembalikan saat dibutuhkan, ataupun dapat dijadikan replikasi di lain tempat. Benar, salah satu goal besar dari MySQL Binlog sendiri adalah replikasi database, yaitu membuat ganda database lintas server. Beda server. Asyik ya sepertinya kalau suatu ketika kita cerita ini juga? 😁

Binary logging akan mencatatkan seluruh proses transaksi data ke dalam file binlog. Lokasi file binlog ini ada di datadir mysql. Secara default, pada OS Linux, datadir mysql ada di /var/lib/mysql. Nih, di sini ada cara untuk melihat lokasi datadir.

mysql> select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

Di dalam datadir tersebut, tersimpan semua binary logs, file ibdata, direktori-direktori database dan log. Database di MySQL akan diletakkan di dalam sebuah folder yang berisi file-file yang merepresentasikan struktur tabel, index dan data. Jika dilihat pada struktur table, juga tentu akan memunculkan seperti ini misal :

[bimo@ecampuz ~]$ ls -alh /var/lib/mysql/
total 6.8G
drwxr-xr-x 16 mysql mysql 4.0K Mar 1 06:45 .
drwxr-xr-x. 19 root root 4.0K Mar 1 04:02 ..
.
.
.
.
-rw-rw---- 1 mysql mysql 746M Mar 1 10:16 ibdata1
-rw-rw---- 1 mysql mysql 5.0M Mar 1 10:16 ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M Mar 1 10:16 ib_logfile1
.
.
.
.
drwx------ 2 mysql mysql 4.0K Aug 16 2017 mysql
-rw-rw---- 1 mysql mysql 201M Feb 22 06:45 mysql-bin.001047
-rw-rw---- 1 mysql mysql 202M Feb 22 12:30 mysql-bin.001048
-rw-rw---- 1 mysql mysql 202M Feb 22 18:05 mysql-bin.001049
-rw-rw---- 1 mysql mysql 201M Feb 22 23:55 mysql-bin.001050
-rw-rw---- 1 mysql mysql 35M Feb 23 01:00 mysql-bin.001051
-rw-rw---- 1 mysql mysql 201M Feb 23 06:45 mysql-bin.001052
-rw-rw---- 1 mysql mysql 201M Feb 23 12:35 mysql-bin.001053
-rw-rw---- 1 mysql mysql 202M Feb 23 18:25 mysql-bin.001054
-rw-rw---- 1 mysql mysql 201M Feb 24 00:15 mysql-bin.001055
-rw-rw---- 1 mysql mysql 23M Feb 24 01:00 mysql-bin.001056
-rw-rw---- 1 mysql mysql 201M Feb 24 06:45 mysql-bin.001057
-rw-rw---- 1 mysql mysql 201M Feb 24 12:35 mysql-bin.001058
-rw-rw---- 1 mysql mysql 202M Feb 24 18:25 mysql-bin.001059
.
.
.

File-file dengan nama ‘mysql-bin.xxxx’ tersebut adalah wujud penampakan MySQL Binlog tersebut.

Mengaktivasi MySQL Binlog

Lho emang MySQL Binlog perlu diaktivasi? Ya. Binary Logs MySQL ini bisa diaktivasi dan bisa dideaktivasi. Konfigurasi aktivasinya ada di konfigurasi umum MySQL kita.

1. Windows : my.ini (lokasi menyesuaikan direktori aplikasi)
2. CentOS : /etc/my.cnf (termasuk untuk mariadb)
3. MySQL Debian: /etc/mysql/mysql.conf.d/mysqld.cnf
4. MariaDB Debian : /etc/mysql/mysql.conf.d/50-server.cnf
5. MySQL Ubuntu : /etc/mysql/mysql.conf.d/mysqld.cnf
6. MariaDB Ubuntu : /etc/mysql/mysql.conf.d/50-server.cnf

Lokasi konfigurasi MySQL Binlog ada di bagian variable :

log-bin=mysql-bin
max_binlog_size=200M
expire_logs_days=7

Mari kita bahas tiga baris di atas tersebut.

1. log-bin, dapat di isi dengan nama binary logs yang akan digunakan. Untuk MySQL sebelum 5.7 jika log-bin kosong berarti binary logs tidak diaktifkan. Namun di MySQL setelah 5.7, log-bin kosong berarti kondisi menyala, dengan nama binary logs : binlog
2. max_binlog_size, adalah batasa besarnya tiap file MySQL Binlog. Hal ini digunakan untuk menghemat space hardisk.
3. expure_logs_days, adalah batasan waktu tiap file binlog itu boleh ada di server. Misalnya tertulis value 7, maka setiap 7 hari akan dihapus.

Beberapa Perintah Operasional MySQL Binlog

1. Cara Melihat List Binary Logs

Perintah yang dapat digunakan untuk melihat list binary logs ini adalah :

SHOW BINARY LOGS:

Contohnya adalah :

mysql> show binary logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| ecampuz.bin.000060 | 875667866 |
| ecampuz.bin.000061 | 1073741946|
| ecampuz.bin.000062 | 179279083 |
+--------------------+-----------+

Tentu bagian ini juga bisa kita cocokkan dengan yang ada di datadir. Biasanya file tersebut akan ada di datadir (/var/lib/mysql)

2. Cara Membaca MySQL Binlog

Bagaimana cara membaca MySQL Binlog? Cara membaca binary logs adalah dengan menggunakan perintah : mysqlbinlog.

mysqlbinlog namafile.bin.xxx

Contohnya adalah :

mysqlbinlog ecampuz.bin.000062

Misal contohnya adalah :

Dari sana cukup terlihat isi dari file MySQL Binlog tersebut.

3. Merestore MySQL Binlog ke dalam File Text

MySQL Binlog file ini tidak berupa file text. Untuk mengubah binlog file ke dalam wujud text maka dapat dilakukan pengubahan tersebut dengan cara seperti berikut :

mysqlbinlog namafile.bin.xxx > filetext.txt

Contohnya adalah :

mysqlbinlog ecampuz.bin.00016 > ecampuz.binlog.txt

4. Cara Membaca FIle Binlog dengan Interval Waktu Tertentu

Binlog yang akan dibaca, ternyata memiliki catatan tanggal ter-log-nya aktivitas database tersebut. Untuk membaca binlog file pada interval tertentu dapat dengan cara :

mysqlbinlog --start-datetime="yyyy-mm-dd hh:mm:ss" --stop-datetime="yyyy-mm-dd hh:mm:ss" namafile.bin.xxxx

Jika dikehendaki hanya binary log yang ada sejak tanggal tertentu, dapat dipanggil dengan :

mysqlbinlog --start-datetime="yyyy-mm-dd hh:mm:ss" --stop-datetime="yyyy-mm-dd hh:mm:ss" namafile.bin.xxxx

Sedangkan MySQL binlog yang ada sampai waktu tertentu, dapat dibaca dengan cara :

mysqlbinlog --stop-datetime="yyyy-mm-dd hh:mm:ss" namafile.bin.xxxx

Contohnya :

mysqlbinlog --start-datetime="2021-01-01 15:30:00" --stop-datetime="2021-06-01 00:00:00" ecampuz.bin.00016

Nah sudah tahu kan cara merestore binary logs dengan interval waktu tertentu ke dalam text file? 😉😉

5. Membaca MySQL Binlog yang Ada dalam Posisi Tertentu Saja

MySQL Binlog memiliki penanda posisi dengan bentuk angka, dan dimulai dengan kata “at”

Kita dapat membaca binlog dengan posisi tertentu, untuk keperluan restore data baik ke dalam database maupun ke file text. Dapat kita ektraksi dengan cara berikut ini

mysqlbinlog -j nomerstart -H nomerstop namafile.bin.xxx

atau

mysqlbinlog --start-position nomerstart --stop-position nomerstop namafile.bin.xxx

Contoh :

mysqlbinlog --start-position 145 --stop-position 245 ecampuz.bin.00016

Adapun nomer posisi tertentu juga dapat dilompati dengan opsi -o, misal :

mysqlbinlog --start-position 145 --stop-position 245 -o 160 ecampuz.bin.00016

6. Cara Merestore MySQL Binlog dalam Database

Bagaimana cara merestore Binary Logs dalam database? Paling penting dalam melakukan hal ini adalah melakukan backup database sebelumnya. Setelah dibackup, dapat dilakukan restore binlog file ke dalam database. Caranya adalah

mysqlbinlog namafile.bin.xxx | mysql -u user -p

Contoh :

mysqlbinlog ecampuz.bin.00016 | mysql -u root -p

Kita juga dapat membaca mysqlbinlog pada interval waktu tertentu, untuk direstore ke dalam database.

Nah, seluruh perintah ini, manual helpnya sudah ada di man page untuk mysqlbinlog di Linux. Kita dapat membaca di sana secara lengkap. Di sini hanya dituliskan beberapa yang biasa digunakan. Demikian sedikit tutorialnya, semoga tulisan ini bermanfaat buat para admin kampus.

Baca juga: 3 Langkah Cara Instal SSL Letsencrypt