How to remove/purge MySQL binary logs
MySQL ถ้าหากมีการเปิดใช้งาน binary log จะทำให้มีการเสียเนื้อที่บางส่วนไป ซึ่งถ้าหากไม่มีการกำหนดวันและเวลาในการ expire ของ binary log ดีๆแล้วจะทำให้มีโอกาสเกิดปัญหาเรื่องเนื้อที่ไม่พอใช้งานเกิดขึ้นอย่างแน่นอน
ถ้าหากเกิดปัญหา binary log ใช้เนื้อที่ในการเก็บข้อมูลเยอะมาก และต้องการลบออกไปบางส่วนเพื่อให้มีเนื้อที่พอใช้งานจำเป็นต้องรันคำสั่งบน MySQL เท่านั้น ไม่สามารถรันคำสั่งลบจาก OS ได้เหมือนการลบไฟล์ทั่วไป ดังนั้นควรทำตามขั้นตอนการลบ binary log ดังนี้
รันคำสั่งด้านล่างเพื่อแสดงข้อมูล binary log บนระบบ
SHOW BINARY LOGS;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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 กำหนดเป็นกี่วัน โดบให้รันคำสั่งด้านล่าง
|
1 2 3 4 5 6 7 8 9 |
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 ได้โดยใช้คำสั่ง
|
1 2 3 |
mysql> SET GLOBAL expire_logs_days=xx; |
xx คือจำนวนวันที่เก็บ binary log
ตัวอย่างเช่น ต้องการเก็บ binary log ไว้ 4 วัน ให้รันคำสั่งดังนี้
|
1 2 3 4 |
mysql> set global expire_logs_days=4; Query OK, 0 rows affected (0.00 sec) |
เมื่อทำการกำหนดวัน expire เรียบร้อยแล้ว ขั้นต่อมาให้ทำการ flush log เพื่อลบ binary log ที่มีอายุเกินกว่าที่กำหนดทิ้ง
|
1 2 3 4 |
mysql> flush logs; Query OK, 0 rows affected (0.35 sec) |
ทำการตรวจสอบ binary log อีกครั้ง จะพบว่าจำนวน binary log ที่มีจะมีจำนวนน้อยลง
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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
|
1 2 3 4 5 6 |
[mysqld] ... expire_logs_days=xx ... |
xx คือจำนวนวันที่เก็บ binary log
นอกจากนี้ยังมีอีกวิธีที่สามารถลบ binary log ได้ โดยไม่ต้องไปแก้ไข parameter ของ MySQL แต่จำนวนวันหมดอายุของ binary log จะเท่าเดิม ซึ่งก็จะมีโอกาศที่ binary log จะใช้เนื้อมากเหมือนเดิม
คำสั่งที่ใช้ในการลบ binary log
ลบ binary log โดยกำหนดจากไฟล์ binary log
|
1 2 3 |
mysql> PURGE BINARY LOGS TO 'mysql-bin.0xx'; |
ลบ binary log โดยกำหนดจากวันและเวลา
|
1 2 3 |
mysql> PURGE BINARY LOGS BEFORE '20013-04-02 22:00:00'; |