Cara menggunakan sql_slave_skip_counter untuk mengatasi error replikasi pada MySQL

Pada artikel sebelumnya saya sudah membahas soal mengerikannya menggunakan statement sql_slave_skip_counter untuk mengatasi error pada repikasi MySQL. Bisa dibaca lagi Beberapa statement pada replikasi yang tidak bisa digunakan pada service RDS – cloud AWS.

Nah, pada artikel ini saya akan menjabarkan cara penggunaannya dengan benar agar error replikasinya tidak menjadi lebih parah.

SQL_SLAVE_SKIP_COUNTER

sql_slave_skip_counter digunakan untuk meng-skip event replikasi. Eventnya berupa statement (INSERT, UPDATE, DELETE, ALTER, dll) yang terdapat pada file binlog.

Statement di binlog? Sebagai contoh misal kita punya file binlog di /var/lib/mysql/ dengan nama mydatabase-relay-bin.090680. Jika kita buka file tersebut maka hasilnya adalah binary:

Kita harus convert agar bisa dibaca:

mysqlbinlog mydatabase-relay-bin.090680 --base64-output=DECODE-ROWS > mydatabase-relay-bin.090680.sql

Diatas terdapat 2 statement UPDATE:

• UPDATE orders SET state = ’20’ WHERE order_no = 65703649701
• UPDATE order_detail SET cost = 37100 WHERE order_id = 96226125 AND item_code = ‘LK998819271’

Kedua UPDATE tersebut merupakan event dari binlog yang bisa kita skip jika terjadi error pada replikasi.

Contoh Kasus

Supaya ada bayangan, saya akan gambarkan real casenya.

Saya mempunyai replikasi yang delaynya cukup parah, yaitu 41864 seconds. Cara mengeceknya dengan menjalankan statement berikut di server replikasi:

SHOW SLAVE STATUS;

Outputnya:

Saya coba cari penyebabnya dengan mengecek proses apa saja yang sedang running:

SHOW FULL PROCESSLIST;

Ternyata ada UPDATE yang tidak kelar prosesnya dan itu memakan waktu lama (42481 seconds) :

UPDATE tersebut saya KILL supaya delay replikasinya bisa teratasi.

KILL <threadID>;

Yang terjadi setelah itu replikasinya malah menjadi error:

SHOW SLAVE STATUS;

Outputnya (kolom Last_SQL_Error) :

Error `Query execution was interrupted` on query. Default database: 'db_online'. Query: 'UPDATE orders SET state = '20' WHERE order_no = 65703649701'

Dan output dikolom lain (Relay_Log_File) berisi mydatabase-relay-bin.090680, artinya file tersebut merupakan file binlog terakhir yang sedang diexecute di server replikasi dan menghasilkan error tersebut (query execution was interrupted).

Jika kita buka file mydatabase-relay-bin.090680 yang sudah didecode, akan ada statement UPDATE sesuai isi dari kolom Last_SQL_Error. Lihat no 1 dibawah

Berarti server replikasi saat ini sedang mengexecute statement UPDATE pada no 1 tersebut. Namun karena tadi kita KILL maka menghasilkan error: query execution was interrupted.

Solusi untuk mengatasi error tersebut adalah dengan menggunakan sql_slave_skip_counter.

Karena kita sudah mengetahui posisi terakhir event/statement yang sedang diexecute, maka kita bisa jalankan sql_slave_skip_counter dengan nilai 1:

SET GLOBAL sql_slave_skip_counter = 1

Artinya kita hanya men-skip 1 event/statement saja, yaitu:

UPDATE orders SET state = '20' WHERE order_no = 65703649701

Jadi isi valuenya dengan 1 saja seperti diatas, jangan 2 seperti ini:

SET GLOBAL sql_slave_skip_counter = 2

Karena kalau valuenya 2 akan mengakibatkan 2 statement ter-skip:

• UPDATE orders SET state = ’20’ WHERE order_no = 65703649701
• UPDATE order_detail SET cost = 37100 WHERE order_id = 96226125 AND item_code = ‘LK998819271’

Efeknya data pada table order_detail menjadi tidak sinkron juga, padahal error replikasinya hanya di table orders saja.

Setelah menjalankan sql_slave_skip_counter=1, data pada table orders menjadi tidak terupdate. Maka kita harus jalankan secara manual statement yang terskip tadi:

UPDATE orders SET state = '20' WHERE order_no = '65703649701';

Saya tambahkan kutip pada 65703649701 karena tipe data pada kolom order_no adalah VARCHAR agar ketika dijalankan tidak lemot lagi (seperti yang sudah saya jelaskan issuenya diatas).

Kesimpulan

Jangan asal-asalan memberi value ke sql_slave_skip_counter, misal sql_slave_skip_counter=1000 maka akan ada 1000 statement dibinlog yang akan terskip dan mengakibatkan banyak data menjadi tidak sinkron. Mengerikannya, server replikasi akan menjadi error terus-terusan dan solusi terakhirnya men-dump ulang .sql nya dan buat replikasi baru. Dan itu butuh waktu yang lama tentunya jika database sudah besar sizenya.