ลบไฟล์ binlog ใน MySQL อย่างปลอดภัย

ดิสก์บนเซิร์ฟเวอร์เต็มเร็วผิดปกติ พอไล่ดูแล้วเจอไฟล์ชื่อ binlog.000043, binlog.000044 … เรียงกันเป็นสิบ ๆ ไฟล์ ไฟล์ละ 1 GB นี่คือ binary log ของ MySQL ครับ ลบทิ้งได้ แต่ต้องลบผ่านคำสั่งของ MySQL และต้องรู้ก่อนว่ากำลังทิ้งอะไรไป

binlog คือบันทึกทุกครั้งที่ข้อมูลเปลี่ยน#

Binary log เก็บ "เหตุการณ์" ที่แก้ข้อมูลในฐานข้อมูล เช่น INSERT, UPDATE, DELETE หรือการแก้โครงสร้างตาราง เรียงตามเวลา ไฟล์แบ่งเป็นสองส่วน คือไฟล์ log ที่มีเลขต่อท้าย กับไฟล์ index (binlog.index) ที่จดว่ามีไฟล์ log อะไรบ้าง

MySQL ใช้ binlog สองงาน

1. Replication เซิร์ฟเวอร์หลัก (source) ส่ง binlog ให้เซิร์ฟเวอร์สำรอง (replica) เอาไปเล่นซ้ำ ข้อมูลทั้งสองฝั่งจึงตรงกัน
2. กู้ข้อมูลย้อนเวลา (point-in-time recovery) กู้จาก backup ล่าสุดก่อน แล้วเล่น binlog ต่อจากเวลาที่ backup ไว้ ข้อมูลก็กลับมาถึงจุดที่เราต้องการ ไม่ใช่แค่ถึงตอน backup (อ่านเพิ่ม)

ตั้งแต่ MySQL 8.0 binary log เปิดอยู่ตั้งแต่ติดตั้ง ถึงจะไม่ได้ทำ replication เลยก็ตาม ฐานข้อมูลที่เขียนบ่อยจึงมี binlog งอกขึ้นทุกวัน

เช็คก่อนว่า binlog กินพื้นที่ไปเท่าไหร่#

ดูรายชื่อไฟล์และขนาดของแต่ละไฟล์ได้ด้วย SHOW BINARY LOGS

SHOW BINARY LOGS;

+---------------+------------+-----------+
| Log_name      | File_size  | Encrypted |
+---------------+------------+-----------+
| binlog.000043 | 1073742848 | No        |
| binlog.000044 | 1074095104 | No        |
| ...           |            |           |
| binlog.000060 |  499667968 | No        |
+---------------+------------+-----------+

File_size เป็นไบต์ ถ้าจะดูจากฝั่งดิสก์ ไฟล์อยู่ในโฟลเดอร์ data ของ MySQL (บน Ubuntu ปกติคือ /var/lib/mysql) วิธีหาว่าโฟลเดอร์ไหนกินพื้นที่บนเซิร์ฟเวอร์ อ่านได้ใน ดูขนาดโฟลเดอร์บน Linux

ตัวอย่างนี้เป็นฐานข้อมูลของเว็บหนึ่งที่ผมดูแลอยู่ ระบบเก็บ log ของ API และ insert / update ข้อมูล transaction ทั้งวัน binlog เลยรวมกันได้ 17.4 GB 😫

binlog ย้อนหลัง 30 วัน รวมกัน 17.4 GB

สังเกตวันที่ของไฟล์ดู ไฟล์เก่าสุดคือ 2 มี.ค. ไฟล์ใหม่สุดคือ 31 มี.ค. ห่างกันราว 30 วัน ซึ่งตรงกับค่าเริ่มต้นที่ MySQL เก็บ binlog ไว้ ไฟล์ที่เก่ากว่านั้น MySQL ลบให้เองอยู่แล้ว แต่ 30 วันของเว็บที่เขียนข้อมูลเยอะก็มากพอจะทำดิสก์เต็มได้

ลบ binlog เก่าทันทีด้วย PURGE BINARY LOGS#

ถ้าดิสก์ใกล้เต็มแล้ว สั่ง PURGE BINARY LOGS ได้เลย คำสั่งนี้ลบไฟล์ที่เก่ากว่าเวลาที่เรากำหนด และเอาชื่อไฟล์ออกจาก binlog.index ให้ด้วย

PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;

คำสั่งด้านบนลบ binlog ทุกไฟล์ที่เก่ากว่า 3 วัน ถ้าอยากระบุเป็นชื่อไฟล์ ใช้ TO แทน คำสั่งนี้ลบทุกไฟล์ที่อยู่ก่อน binlog.000057 แต่เก็บ binlog.000057 ไว้

PURGE BINARY LOGS TO 'binlog.000057';

ผู้ใช้ที่สั่งต้องมีสิทธิ์ BINLOG_ADMIN ถ้าใช้ user root ก็สั่งได้เลย

หลัง PURGE เหลือ binlog แค่ 3 วันล่าสุด รวม 3.46 GB

ตั้งให้ MySQL ลบ binlog เก่าเอง#

PURGE แก้ได้แค่ตอนนี้ อีกเดือนดิสก์ก็เต็มอีก ทางที่ยั่งยืนกว่าคือลดระยะเวลาที่ MySQL เก็บ binlog ด้วยตัวแปร binlog_expire_logs_seconds ค่าเริ่มต้นคือ 2592000 วินาที หรือ 30 วัน

ตั้งใน my.cnf (บน Linux) หรือ my.ini (บน Windows) แล้ว restart MySQL

[mysqld]
# เก็บ binlog ไว้ 7 วัน (7 × 24 × 60 × 60)
binlog_expire_logs_seconds = 604800

หรือถ้ายังไม่อยาก restart ก็ตั้งจาก SQL ได้ SET PERSIST เขียนค่าลงไฟล์ให้ด้วย ค่าจึงยังอยู่หลัง restart

SET PERSIST binlog_expire_logs_seconds = 604800;

ตั้งค่าแล้ว MySQL ยังไม่ลบไฟล์ทันที จะรอลบตอนเปิดเซิร์ฟเวอร์และตอนขึ้นไฟล์ binlog ใหม่ ถ้าอยากให้ลบเลยก็สั่ง FLUSH BINARY LOGS; ให้ขึ้นไฟล์ใหม่ไปหนึ่งครั้ง

ถ้าใช้ replication อย่าตั้งให้สั้นกว่าเวลาที่ replica อาจตามหลังได้นานที่สุด เช่น replica เคยดับไปทั้งเสาร์อาทิตย์ ก็ควรเก็บไว้เกินสองวัน

หาว่าตารางไหนทำให้ binlog โต#

ถ้า binlog ขึ้นไฟล์ใหม่วันละหลายสิบไฟล์ ทั้งที่ฐานข้อมูลทั้งก้อนมีไม่กี่ GB แปลว่ามีงานที่เขียนแถวเดิมซ้ำ ๆ อยู่ ดูเวลาของไฟล์ binlog ด้วย ls -l ก่อน ถ้าไฟล์ขึ้นใหม่ถี่เฉพาะบางช่วงของวัน ช่วงนั้นมักตรงกับ job ที่ตั้งเวลาไว้

จากนั้นเปิดไฟล์ด้วย mysqlbinlog ซึ่งมากับ MySQL ตัวเลือก --base64-output=DECODE-ROWS -v แปลง row event ให้อ่านได้ ทุกแถวที่แก้จะขึ้นต้นด้วย ### INSERT INTO, ### UPDATE หรือ ### DELETE FROM ตามด้วยชื่อตาราง นับแยกตามตารางได้แบบนี้

$ sudo mysqlbinlog --base64-output=DECODE-ROWS -v /var/lib/mysql/binlog.000123 \
  | grep -oE '^### (INSERT INTO|UPDATE|DELETE FROM) `[a-z0-9_]+`\.`[a-z0-9_]+`' \
  | sort | uniq -c | sort -rn | head
 211034 ### UPDATE `app`.`order_lines`
   9120 ### UPDATE `app`.`items`
    850 ### INSERT INTO `app`.`jobs`

ตารางที่อยู่บนสุดคือตัวที่เขียนเยอะที่สุด ถ้าจำนวน UPDATE ในไฟล์เดียวมากกว่าจำนวนแถวทั้งตาราง ก็แทบแน่ใจได้ว่ามี job อัปเดตแถวเดิมซ้ำ ใน output เดียวกัน แต่ละ UPDATE มีส่วน ### WHERE (ค่าก่อนแก้) กับ ### SET (ค่าหลังแก้) คอลัมน์แสดงเป็น @1, @2 ตามลำดับในตาราง เทียบสองส่วนนี้ดูว่าคอลัมน์ไหนเปลี่ยน ถ้าเปลี่ยนแค่ updated_at ต้นเหตุมักเป็น upsert() ของ Laravel ที่รันซ้ำทุกนาที วิธีแก้อยู่ในโพสต์ งาน sync ใน Laravel ที่เขียนเฉพาะแถวที่เปลี่ยน

ปิด binary log ได้ แต่จะกู้ข้อมูลย้อนเวลาไม่ได้#

ถ้าฐานข้อมูลนี้ไม่ได้ทำ replication และไม่ได้ใช้ binlog กู้ข้อมูลเลย ปิด binary log ไปก็ได้ เพิ่ม skip-log-bin หรือ disable_log_bin ซึ่งได้ผลเหมือนกัน ใน my.cnf หรือ my.ini แล้ว restart MySQL

[mysqld]
skip-log-bin

บน Ubuntu ไม่ต้องแก้ mysqld.cnf ของแพ็กเกจ สร้างไฟล์ใหม่ใน /etc/mysql/mysql.conf.d/ เช่น zz-disable-binlog.cnf แล้วใส่สองบรรทัดเดียวกัน ลำดับที่ปลอดภัยคือ

1. PURGE BINARY LOGS TO ไฟล์ล่าสุดก่อน ตอน binlog ยังเปิดอยู่ ไฟล์เก่าทั้งหมดจะหายไปทันที
2. restart MySQL แล้วเช็กว่า SELECT @@log_bin; ได้ 0
3. ไฟล์ binlog ไฟล์สุดท้ายกับ binlog.index ที่ค้างอยู่ ตอนนี้ลบด้วย rm ได้แล้ว เพราะ MySQL ไม่ได้ใช้ไฟล์พวกนี้อีก

ค่าอื่นที่ควรตั้งบนเครื่อง production ไปพร้อมกัน เช่น buffer pool และ slow query log อ่านต่อได้ที่ วิธีตั้งค่า MySQL บนเซิร์ฟเวอร์ production

แล้วถ้าใช้ MariaDB ล่ะ#

XAMPP และหลาย distro ของ Linux ติดตั้ง MariaDB มาแทน MySQL ซึ่งตั้งค่าเรื่องนี้ต่างกันนิดหน่อย (อ้างอิง)

• MariaDB ปิด binary log ไว้เป็นค่าเริ่มต้น จะเปิดก็ต่อเมื่อตั้ง log-bin เอง ถ้า SHOW BINARY LOGS บอกว่าไม่ได้เปิด binlog ก็ไม่ต้องทำอะไรต่อ
• expire_logs_days ยังใช้ได้ และ MariaDB 10.6.1 ขึ้นไปมี binlog_expire_logs_seconds ด้วย ทั้งสองตัวผูกกัน ตั้งตัวหนึ่งอีกตัวเปลี่ยนตาม
• ค่าเริ่มต้นของทั้งคู่คือ 0 หมายถึงไม่ลบเองเลย ถ้าเปิด binlog ไว้แต่ไม่ได้ตั้งค่านี้ binlog จะโตไปเรื่อย ๆ
• ตั้งแต่ MariaDB 11.4 การลบอัตโนมัติจะรอจนมี replica อ่านไฟล์นั้นไปแล้วอย่างน้อยหนึ่งตัว (slave_connections_needed_for_purge ค่าเริ่มต้นคือ 1) เครื่องที่เปิด binlog โดยไม่มี replica จึงไม่ลบเอง ต้องตั้งค่านี้เป็น 0 (อ่านเพิ่ม)

PURGE BINARY LOGS กับ SHOW BINARY LOGS ใช้ได้เหมือนกันทั้งสองตัว

อ้างอิง#

• MySQL 8.4: The Binary Log
• MySQL 8.4: PURGE BINARY LOGS Statement
• MySQL 8.4: Binary Logging Options and Variables
• MariaDB: Using and Maintaining the Binary Log