กู้ข้อมูล MySQL ย้อนเวลาด้วย Binary Log

สถานการณ์ที่เกิดขึ้นจริงบ่อยที่สุดไม่ใช่ดิสก์พัง แต่คือมีคนรัน DELETE โดยลืมใส่เงื่อนไข WHERE ตอนบ่ายสองโมง ในขณะที่ข้อมูลสำรองล่าสุดคือของตีสาม การกู้จากไฟล์สำรองอย่างเดียวหมายถึงข้อมูลที่ทำมาทั้งวันหายไปด้วย

Binary Log เก็บทุกคำสั่งที่เปลี่ยนแปลงข้อมูลไว้ตามลำดับเวลา ทำให้กู้ไฟล์สำรองกลับมาแล้วเล่นคำสั่งต่อจนถึงวินาทีก่อนเกิดเหตุได้ นี่คือความสามารถที่ทำให้การสำรองข้อมูลมีความหมายจริง

สิ่งที่ต้องเตรียม

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

ขั้นตอนที่ 1: ตรวจว่าเปิด binary log ไว้หรือไม่

mysql -e "SHOW VARIABLES LIKE 'log_bin';"
mysql -e "SHOW BINARY LOGS;"
sudo ls -lh /var/log/mysql/mysql-bin.*

หากยังไม่เปิด ให้เปิดไว้ตั้งแต่วันนี้ เพราะเป็นสิ่งที่ช่วยได้เฉพาะเหตุการณ์ในอนาคต

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

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
binlog_expire_logs_seconds = 1209600
max_binlog_size = 256M

sudo systemctl restart mysql

เก็บย้อนหลังอย่างน้อย 14 วัน ควรยาวกว่าระยะห่างระหว่างการสำรองข้อมูลเต็มชุดสองครั้งเสมอ

ขั้นตอนที่ 2: หยุดความเสียหายก่อน

ทันทีที่รู้ว่าเกิดเหตุ สิ่งแรกคือหยุดไม่ให้มีการเขียนเพิ่ม เพราะยิ่งมีข้อมูลใหม่เข้ามา การกู้ยิ่งซับซ้อน

# ปิดไม่ให้แอปเขียนได้
mysql -e "SET GLOBAL read_only = ON; SET GLOBAL super_read_only = ON;"

# หรือหยุดเว็บไปเลยหากทำได้
sudo systemctl stop nginx php8.3-fpm

คัดลอก binary log ทั้งหมดออกไปเก็บไว้อีกที่ทันที ก่อนที่จะถูกลบตามรอบ

sudo mkdir -p /root/pitr
sudo cp /var/log/mysql/mysql-bin.* /root/pitr/
sudo mysql -e "FLUSH BINARY LOGS;"

ขั้นตอนที่ 3: หาว่าเหตุเกิดที่ตำแหน่งไหน

อ่าน binary log เป็นข้อความเพื่อหาคำสั่งที่ทำให้เกิดปัญหา

# ดูเหตุการณ์ในช่วงเวลาที่สงสัย
sudo mysqlbinlog --base64-output=DECODE-ROWS -v \
  --start-datetime="2026-09-25 13:30:00" \
  --stop-datetime="2026-09-25 14:30:00" \
  /root/pitr/mysql-bin.000042 | less

ค้นหาคำสั่งที่ต้องสงสัย

sudo mysqlbinlog --base64-output=DECODE-ROWS -v /root/pitr/mysql-bin.000042 \
  | grep -n -B5 -A5 -i 'DELETE FROM orders'

ในผลลัพธ์ให้มองหาบรรทัดที่ขึ้นต้นด้วย # at ตามด้วยตัวเลข นั่นคือตำแหน่งในไฟล์ และบรรทัด #260925 14:02:11 คือเวลาที่เกิด จดทั้งสองค่าไว้

ขั้นตอนที่ 4: กู้ไฟล์สำรองเต็มชุดลงเครื่องทดสอบ

sudo mysql -e "CREATE DATABASE recovery;"
zcat /var/backups/mysql/all-2026-09-25-0300.sql.gz | sudo mysql

# หรือกู้เฉพาะฐานข้อมูลเดียวลงชื่อใหม่
zcat /var/backups/mysql/shop-2026-09-25-0300.sql.gz | sudo mysql recovery

จดตำแหน่ง binary log ที่ไฟล์สำรองนี้ถูกสร้าง ซึ่งอยู่ในส่วนหัวของไฟล์ดัมป์หากใช้ --source-data=2

zcat /var/backups/mysql/all-2026-09-25-0300.sql.gz | head -30 | grep -i 'CHANGE REPLICATION\|CHANGE MASTER'

จะเห็นบรรทัดประมาณ -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000041', MASTER_LOG_POS=194; นี่คือจุดเริ่มต้นที่ต้องเล่น binary log ต่อ

ขั้นตอนที่ 5: เล่น binary log ต่อจนถึงก่อนเกิดเหตุ

วิธีที่ 1 กำหนดด้วยเวลา อ่านง่ายกว่าแต่แม่นยำน้อยกว่าเพราะหลายคำสั่งอาจเกิดในวินาทีเดียวกัน

sudo mysqlbinlog \
  --start-position=194 \
  --stop-datetime="2026-09-25 14:02:10" \
  /root/pitr/mysql-bin.000041 /root/pitr/mysql-bin.000042 \
  | sudo mysql

วิธีที่ 2 กำหนดด้วยตำแหน่ง แม่นยำกว่าและเป็นวิธีที่ควรใช้

sudo mysqlbinlog \
  --start-position=194 \
  --stop-position=8842190 \
  /root/pitr/mysql-bin.000041 /root/pitr/mysql-bin.000042 \
  | sudo mysql

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

ขั้นตอนที่ 6: ตรวจสอบก่อนนำกลับ

mysql -e "SELECT COUNT(*) FROM recovery.orders;"
mysql -e "SELECT MAX(created_at) FROM recovery.orders;"
mysql -e "SELECT * FROM recovery.orders ORDER BY id DESC LIMIT 10;"

เทียบจำนวนแถวกับที่ควรจะเป็น และตรวจว่าข้อมูลล่าสุดมีเวลาใกล้เคียงกับตอนก่อนเกิดเหตุ อย่ารีบนำกลับจนกว่าจะมั่นใจ

ขั้นตอนที่ 7: นำข้อมูลกลับสู่ระบบจริง

หากเสียหายเฉพาะบางตาราง ให้นำกลับเฉพาะตารางนั้น ซึ่งปลอดภัยกว่าการเขียนทับทั้งฐานข้อมูล

mysqldump recovery orders > /tmp/orders-recovered.sql

# สำรองของเดิมไว้ก่อนเสมอ
mysqldump shop orders > /tmp/orders-before-restore.sql

mysql shop < /tmp/orders-recovered.sql

เปิดให้เขียนได้อีกครั้ง

mysql -e "SET GLOBAL super_read_only = OFF; SET GLOBAL read_only = OFF;"
sudo systemctl start php8.3-fpm nginx

# สร้างสถานการณ์จำลองบนเครื่องทดสอบ
mysql -e "CREATE DATABASE drill; CREATE TABLE drill.t (id INT PRIMARY KEY, v VARCHAR(50));"
mysql -e "INSERT INTO drill.t VALUES (1,'a'),(2,'b'),(3,'c');"
mysqldump drill > /tmp/drill-backup.sql
mysql -e "INSERT INTO drill.t VALUES (4,'d'),(5,'e');"
sleep 2
mysql -e "DELETE FROM drill.t;"   # จำลองอุบัติเหตุ