Bin log disable / set expire date of MySQL 8 on Ubuntu 22.04

Binary Log (Binlog) ของ MySQL เป็นไฟล์ที่บันทึกกิจกรรมทั้งหมดที่เปลี่ยนแปลงข้อมูลหรือโครงสร้างฐานข้อมูล เช่น คำสั่ง INSERT, UPDATE, DELETE, และการเปลี่ยนแปลงโครงสร้างเช่นการเพิ่มหรือลบตาราง เป็นต้น โดยไม่รวมคำสั่งที่เป็นการดึงข้อมูล (SELECT) ซึ่งมีประโยชน์สำคัญหลายด้าน ดังนี้:

ประโยชน์ของ Binlog:

1. การกู้คืนข้อมูล (Data Recovery):

   • ในกรณีที่เกิดความเสียหายหรือข้อมูลหาย สามารถใช้ Binlog ร่วมกับสำรองข้อมูล (Backup) เพื่อคืนค่าข้อมูลล่าสุดก่อนเกิดปัญหาได้
   • เช่น การ Restore ข้อมูลจาก Backup และนำ Binlog มาเล่นซ้ำ (Replay) เพื่อคืนค่าข้อมูลถึงเวลาที่ต้องการ

2. Replication (การทำสำเนาฐานข้อมูล):

   • ใช้ในการทำงานร่วมกับ MySQL Replication เพื่อคัดลอกการเปลี่ยนแปลงจากเซิร์ฟเวอร์ต้นทาง (Master) ไปยังเซิร์ฟเวอร์ปลายทาง (Slave) เพื่อความทนทานและแบ่งโหลด (Load Balancing)

3. การตรวจสอบ (Auditing):

   • สามารถตรวจสอบประวัติการเปลี่ยนแปลงในฐานข้อมูล เช่น ใครแก้ไขอะไรเมื่อไหร่

4. Point-in-Time Recovery (PITR):

   • ช่วยให้สามารถกู้คืนข้อมูลไปยังสถานะที่แน่นอนในช่วงเวลาหนึ่ง (เช่น ก่อนคำสั่งที่ผิดพลาด)

ปัญหา

• ปัญหาที่เกิดขึ้นใน server  เราคือ พื้นที่เต็มจาก bin log ใช้เยอะสุด $ sudo du -shc /var/lib/mysql/*
• ผลลัพธ์ที่อยากให้เป็นคือ ได้พื้นที่ว่างกลับมาใช้งาน

Environment

• MySQL 8.0
• Ubuntu 22.04

ปิดการทำงาน

• ก่อนแก้ไขคอนฟิกกันเหนียว

$ sudo cp /etc/mysql/mysql.conf.d/myssqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.bak

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

• ต่อจาก section [mysqld] เพิ่ม (อยู่ด้านล่าง section นี้ลงไปตำแหน่งใหนก็ได้)

skip-log-bin หรือ skip-log-bin = true

• หรือ เปิดใช้งานเหมือนเดิมเพิ่มเติมคือ ให้มีวันหมดอายุ โดยแก้ไขไฟล์เดิม

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

• ต่อจาก section [mysqld] เพิ่ม (อยู่ด้านล่าง section นี้ลงไปตำแหน่งใหนก็ได้)

expire_logs_days = 3

• จากนั้นบันทึกไฟล์ แล้ว restart service

$ sudo systemctl restart mysql.service

เพิ่มเติม

• ตรวจสอบ bin log ปิด หรือ เปิดอยู่ โดย

mysql > show variables like 'log_bin';

• ดูพื้นที่ระบบ

$ sudo df -h

• ดูขนาดไฟล์ใน dir

$ sudo du -shc /path/*

Ref

• https://chatgpt.com/share/678f962a-18ec-8010-b694-7de50f1b6489
• https://www.veeble.com/kb/how-to-clear-or-purge-mysql-bin-log-files/
• https://stackoverflow.com/questions/73427460/how-to-disable-mysql-binlog-on-docker-compose
• https://ploi.io/documentation/server/disable-bin-logs-for-mysql-on-ubuntu
• https://askubuntu.com/questions/1286583/how-i-can-disable-the-mysql-binlog
• https://www.youant.net/mysql-tip/mysql-tip-%E0%B8%A7%E0%B8%B4%E0%B8%98%E0%B8%B5%E0%B8%A5%E0%B8%9A-binary-log-%E0%B8%AB%E0%B8%A3%E0%B8%B7%E0%B8%AD-mysql-bin-xx-%E0%B8%82%E0%B8%AD%E0%B8%87-mysql/#google_vignette