How to remove/purge MySQL binary logs

MySQL ถ้าหากมีการเปิดใช้งาน binary log จะทำให้มีการเสียเนื้อที่บางส่วนไป ซึ่งถ้าหากไม่มีการกำหนดวันและเวลาในการ expire ของ binary log ดีๆแล้วจะทำให้มีโอกาสเกิดปัญหาเรื่องเนื้อที่ไม่พอใช้งานเกิดขึ้นอย่างแน่นอน

ถ้าหากเกิดปัญหา binary log ใช้เนื้อที่ในการเก็บข้อมูลเยอะมาก และต้องการลบออกไปบางส่วนเพื่อให้มีเนื้อที่พอใช้งานจำเป็นต้องรันคำสั่งบน MySQL เท่านั้น ไม่สามารถรันคำสั่งลบจาก OS ได้เหมือนการลบไฟล์ทั่วไป ดังนั้นควรทำตามขั้นตอนการลบ binary log ดังนี้

รันคำสั่งด้านล่างเพื่อแสดงข้อมูล binary log บนระบบ

SHOW BINARY LOGS;

mysql> SHOW BINARY LOGS;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| mysqld-bin.002622 | 134217792 |
| mysqld-bin.002623 | 134222196 |
| mysqld-bin.002624 | 134217834 |
...
| mysqld-bin.002837 | 134217881 |
| mysqld-bin.002838 | 134217844 |
| mysqld-bin.002839 | 134217816 |
| mysqld-bin.002840 | 3008671 |
+-------------------+-----------+
219 rows in set (0.00 sec)

การคำสั่งด้านบนพบว่าจำนวน binary log ที่เก็บไว้มีจำนวนมาก ดังนั้นถ้าหากไม่ต้องการให้ binary log มีจำนวนมาก จำเป็นต้องทำการแก้ไข expire_logs_days บน MySQL ให้มีจำนวนวันน้อยกว่านี้ แต่ก่อนจะทำการแก้ไขเราควรจะทราบก่อนว่าตอนนี้ expire_logs_days กำหนดเป็นกี่วัน โดบให้รันคำสั่งด้านล่าง

mysql> show variables like "expire_logs%";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 14 |
+------------------+-------+
1 row in set (0.00 sec)

จากตัวอย่าง ผลลัพธ์แสดงให้เห็นว่า มีการเก็บ binary log ไว้นานถึง 14 วัน ดังนั้นเราสามารถลดจำนวนวันที่เก็บ binary log ได้โดยใช้คำสั่ง

mysql> SET GLOBAL expire_logs_days=xx;

xx คือจำนวนวันที่เก็บ binary log

ตัวอย่างเช่น ต้องการเก็บ binary log ไว้ 4 วัน ให้รันคำสั่งดังนี้

mysql> set global expire_logs_days=4;
Query OK, 0 rows affected (0.00 sec)

เมื่อทำการกำหนดวัน expire เรียบร้อยแล้ว ขั้นต่อมาให้ทำการ flush log เพื่อลบ binary log ที่มีอายุเกินกว่าที่กำหนดทิ้ง

mysql> flush logs;
Query OK, 0 rows affected (0.35 sec)

ทำการตรวจสอบ binary log อีกครั้ง จะพบว่าจำนวน binary log ที่มีจะมีจำนวนน้อยลง

mysql> SHOW BINARY LOGS;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| mysqld-bin.002712 | 134217773 |
| mysqld-bin.002713 | 134217864 |
| mysqld-bin.002714 | 134217840 |
| mysqld-bin.002715 | 134217819 |
| mysqld-bin.002716 | 134217941 |
...
| mysqld-bin.002837 | 134217881 |
| mysqld-bin.002838 | 134217844 |
| mysqld-bin.002839 | 134217816 |
| mysqld-bin.002840 | 8238887 |
| mysqld-bin.002841 | 244952 |
+-------------------+-----------+
130 rows in set (0.00 sec)

หลังจากแก้ไขแล้วอย่าลืมไปแก้ที่ไฟล์ my.cnf โดยให้เพิ่ม parameter ด้านล่างลงไปที่ส่วนของ [mysqld] ถ้าหากไม่แก้ไขจะทำให้ expire_logs_days มีค่าเท่าเดิมหลังจาก restart MySQL

[mysqld]
...
expire_logs_days=xx
...

xx คือจำนวนวันที่เก็บ binary log

นอกจากนี้ยังมีอีกวิธีที่สามารถลบ binary log ได้ โดยไม่ต้องไปแก้ไข parameter ของ MySQL แต่จำนวนวันหมดอายุของ binary log จะเท่าเดิม ซึ่งก็จะมีโอกาศที่ binary log จะใช้เนื้อมากเหมือนเดิม

คำสั่งที่ใช้ในการลบ binary log
ลบ binary log โดยกำหนดจากไฟล์ binary log

mysql> PURGE BINARY LOGS TO 'mysql-bin.0xx';

ลบ binary log โดยกำหนดจากวันและเวลา
[shell]
mysql> PURGE BINARY LOGS BEFORE ‘20013-04-02 22:00:00’;
[/shell]