ปกติแล้วใน MySQL จะมีการเก็บ log query ไว้เพื่อเก็บประวัติการแก้ไขข้อมูลสำหรับที่เกิดขึ้นบนฐานข้อมูล ถ้า data บนฐานข้อมูลเรามีการเปลี่ยนแปลงบ่อย ๆ ไฟล์ binlog นี้ก็จะโตกินพื้นที่บน Server เราไปเรื่อย ๆ เราสามารถใช้ คำสั่ง PURGE เพื่อทำการเคลียร์ไฟล์ binlog พวกนี้ออกไปจาก Server ของเราได้ครับ

รู้จักกับ Binary Log

ไฟล์ Binary log เป็นไฟล์ที่เก็บข้อมูลเกี่ยวกับ ประวัติการแก้ไขขอมูลในฐานข้อมูล MySQL ของเรา ใน log จะประกอบไปด้วย Log files และ Index File โดยจะเก็บ "เหตุการณ์" ที่เกิดขึ้นกับข้อมูลของเรา เช่น คำสั่ง DELETE, INSERT หรือ UPDATE ข้อมูลในฐานข้อมูล เหตุผลสำคัญที่มีการเก็บ binary log มี 2 ข้อคือ

1. สำหรับการทำ Replication - Binary Log บน MySQL ที่เป็น Source Server สำหรับการทำ Replication จะเก็บบันทึกการเปลี่ยนแปลงที่เกิดขึ้นกับข้อมูลของเราเพื่อส่งให้ Server ที่เป็น replicas โดยข้อมูลที่ส่งไปจะส่ง transaction เพื่อให้การการเปลี่ยนแปลงเดียวกันกับข้อมูลที่เพิ่งเกิดขึ้นบน Source Server
2. สำหรับการทำ Data Recovery - การกู้คืนข้อมูลบางอย่างบน MySQL อาศัยข้อมูลที่เก็บอยู่ใน Binary Log หลังจากที่ Backup มีการกู้คืน แล้วพวก "เหตุการณ์" ที่เก็บไว้ใน log หลังจากที่ฐานข้อมูลเคยถูก back up ไว้จะถูกประมวลผลอีกครั้ง ด้วยความสามารถนี้เอง ทำให้ฐานข้อมูล MySQL ที่ถูกกู้คืนมาจากการ back up จะมีข้อมูลล่าสุดถึงเวลาปัจจุบันได้

แต่ถ้าไม่ต้องการใช้ Binary log จะต้องทำอย่างไร

อันนี้เป็นตัวอย่างของฐานข้อมูลบน web นึงที่ผมดูแลอยู่นะครับ โดยระบบนี้จะมีการเก็บพวก log api  บน web และมีการ insert / update ข้อมูล transaction ต่อวันค่อนข้างบ่อยพอสมควร จะสังเกตได้ว่าไฟล์ binlog ที่เก็บไว้รวม ๆ แล้วมีขนาดไม่น้อยเลยทีเดียว (17.4GB ตามตัวอย่าง 😫)

เราสามารถ Run คำสั่ง PURGE เพื่อทำการเคลียร์ข้อมูลในไฟล์ Bin log ด้วยคำสั่งนี้

PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;

โดยคำสั่ง SQL ด้านบนจะเคลียร์ binlog ไฟล์ทั้งหมดที่มีอายุเก่ากว่า 3 วันออก

หลังจาก run คำสั่ง PURGE ไปแล้ว เห็นได้ว่าไฟล์ binlog จะหายไป ลดลงมาเหลือ 3.46 GB

ปิดการใช้งาน Binary Log

ทีนี้ แม้ว่าเราลดขนาดไฟล์ binlog ที่ค้างใน Server เราได้แล้ว แต่ถ้าฐานข้อมูลของเราไม่มีความจำเป็นต้องใช้งาน Binary Log เลยหล่ะ เราไม่ได้มีการทำ Replication หรือ ไม่ได้กังวลเรื่องการ Restore ข้อมูลมากนัก เราสามารถปิดการใช้งาน Binary Log ไปเลยได้มั้ย จะได้ไม่ต้องเปลืองขนาดพื้นที่บน Server ของเรา - คำตอบคือ ได้ครับ ✅

วิธีการคือ ให้เราแก้ไขไฟล์ my.ini (บน Windows หรือ my.cnf บน Linux) โดยทำการเพิ่ม option ต่อไปนี้

[mysqld]
skip-log-bin

ให้ Restart Server แล้วต่อไป MySQL จะหยุดเก็บ Binary Log เพิ่มเติมแล้วครับ

อ้างอิง

- MySQL Binary Log 

- MySQL Purge Statement