การสำรองข้อมูลฐานข้อมูลเป็นหนึ่งในงานสำคัญที่สุดของผู้ดูแลระบบ เพราะข้อมูลอาจสูญหายได้จากหลายสาเหตุ ทั้งฮาร์ดแวร์เสียหาย ซอฟต์แวร์ผิดพลาด หรือแม้แต่ Human Error ที่ลบข้อมูลโดยไม่ตั้งใจ การมีระบบ Backup ที่เชื่อถือได้จึงเป็นเหมือนประกันภัยของข้อมูล

บทความนี้จะอธิบายการใช้ mysqldump ซึ่งเป็นเครื่องมือสำรองข้อมูลมาตรฐานที่มาพร้อมกับการติดตั้ง ตั้งแต่คำสั่งพื้นฐาน ตัวเลือกสำคัญ การกู้คืน ไปจนถึงการตั้ง Automated Backup ด้วย Cron Job

mysqldump คืออะไร

mysqldump เป็น Command-line Utility ที่สร้าง Logical Backup โดยแปลงโครงสร้างและข้อมูลในฐานข้อมูลเป็นชุดคำสั่ง SQL (CREATE TABLE, INSERT INTO) ที่สามารถ Import กลับเข้าไปสร้างฐานข้อมูลใหม่ได้ ข้อดีคือไฟล์ Backup อ่านได้ด้วย Text Editor แก้ไขได้ และย้ายข้ามเซิร์ฟเวอร์หรือข้ามเวอร์ชันได้ง่าย ข้อจำกัดคือเหมาะกับฐานข้อมูลขนาดเล็กถึงกลาง (ไม่เกินหลักสิบ GB) เพราะกระบวนการ Export และ Import ใช้เวลาเพิ่มขึ้นตามขนาดข้อมูล

สำรองข้อมูลทั้งฐานข้อมูล

คำสั่งพื้นฐานที่สุดสำหรับการสำรองข้อมูลฐานข้อมูลเดียว

mysqldump -u root -p myapp_db > myapp_db_backup.sql

คำสั่งนี้จะ Dump ทั้งโครงสร้าง (CREATE TABLE) และข้อมูล (INSERT INTO) ของทุกตารางในฐานข้อมูล myapp_db ลงไฟล์ myapp_db_backup.sql ระบบจะถามรหัสผ่านหลังกด Enter

หากต้องการสำรองทุกฐานข้อมูลในเซิร์ฟเวอร์พร้อมกัน

mysqldump -u root -p --all-databases > all_databases_backup.sql

ตัวเลือก –all-databases จะรวมทุกฐานข้อมูลที่ User มีสิทธิ์เข้าถึง รวมถึงฐานข้อมูลระบบที่เก็บ User และ Privileges เหมาะสำหรับ Migrate เซิร์ฟเวอร์ทั้งหมด

สำรองเฉพาะบางตาราง

ไม่จำเป็นต้อง Backup ทั้งฐานข้อมูลเสมอไป สามารถระบุเฉพาะตารางที่ต้องการได้

mysqldump -u root -p myapp_db users orders > users_orders_backup.sql

คำสั่งนี้สำรองเฉพาะตาราง users และ orders จากฐานข้อมูล myapp_db เหมาะกับกรณีที่ต้องการ Backup เฉพาะตารางสำคัญก่อนทำการเปลี่ยนแปลงข้อมูล

สำรองเฉพาะโครงสร้าง (Schema Only)

ถ้าต้องการ Backup เฉพาะโครงสร้างตารางโดยไม่รวมข้อมูล ใช้ตัวเลือก –no-data

mysqldump -u root -p --no-data myapp_db > myapp_db_schema.sql

ไฟล์ที่ได้จะมีเฉพาะ CREATE TABLE, CREATE INDEX และ CREATE VIEW เหมาะกับการเก็บโครงสร้างฐานข้อมูลไว้ใน Version Control หรือสร้างฐานข้อมูลทดสอบที่มีโครงสร้างเหมือนกันแต่ไม่มีข้อมูลจริง

ตัวเลือกสำคัญสำหรับ Production Backup

การ Backup ฐานข้อมูล Production ที่มีผู้ใช้งานอยู่ต้องคำนึงถึง Consistency ของข้อมูล ตัวเลือกที่แนะนำสำหรับ InnoDB

mysqldump -u root -p --single-transaction --routines --triggers --events myapp_db > myapp_db_backup.sql

–single-transaction — สร้าง Consistent Snapshot ของข้อมูลโดยใช้ Transaction แทนการ Lock ตาราง ทำให้สามารถ Backup ได้โดยไม่กระทบการอ่านเขียนของผู้ใช้งาน ใช้ได้เฉพาะตาราง InnoDB เท่านั้น

–routines — รวม Stored Procedures และ Functions ไว้ใน Backup ซึ่งปกติจะไม่ถูกรวมโดย Default

–triggers — รวม Triggers ไว้ใน Backup (เปิดโดย Default อยู่แล้ว แต่ระบุไว้เพื่อความชัดเจน)

–events — รวม Event Scheduler Events ไว้ใน Backup

ตัวเลือกเพิ่มเติมที่มีประโยชน์

–quick — อ่านข้อมูลทีละแถวแทนที่จะโหลดทั้งตารางเข้า Memory เหมาะกับตารางขนาดใหญ่ (เปิดโดย Default ในเวอร์ชันใหม่)

–lock-tables — Lock ทุกตารางก่อน Backup ใช้เมื่อมีตาราง MyISAM ที่ไม่รองรับ –single-transaction

–flush-logs — Flush Binary Logs หลัง Backup เหมาะกับการทำ Point-in-Time Recovery ร่วมกับ Binary Log

–set-gtid-purged=OFF — ใช้เมื่อ Backup จากเซิร์ฟเวอร์ที่เปิด GTID แล้วจะ Import เข้าเซิร์ฟเวอร์ที่ไม่เกี่ยวข้องกับ Replication

บีบอัดไฟล์ Backup

ไฟล์ SQL ที่ได้จาก Dump มักมีขนาดใหญ่ แต่บีบอัดได้ดีมากเพราะเป็น Text ล้วน สามารถ Pipe Output ผ่าน gzip ได้โดยตรง

mysqldump -u root -p --single-transaction myapp_db | gzip > myapp_db_backup.sql.gz

ไฟล์ที่ได้จะเล็กลง 5-10 เท่า ประหยัดพื้นที่จัดเก็บและเร็วขึ้นในการโอนย้ายผ่าน Network

กู้คืนข้อมูลจากไฟล์ Backup

การ Restore ข้อมูลจากไฟล์ SQL ที่ไม่ได้บีบอัด

mysql -u root -p myapp_db < myapp_db_backup.sql

หากฐานข้อมูลยังไม่มี ต้องสร้างก่อน

mysql -u root -p -e "CREATE DATABASE myapp_db;"
mysql -u root -p myapp_db < myapp_db_backup.sql

สำหรับไฟล์ที่บีบอัดด้วย gzip ให้ใช้ gunzip Pipe เข้า Client โดยตรง

gunzip < myapp_db_backup.sql.gz | mysql -u root -p myapp_db

สำหรับไฟล์ที่ Backup ด้วย –all-databases ไม่ต้องระบุชื่อฐานข้อมูล เพราะคำสั่ง CREATE DATABASE อยู่ในไฟล์แล้ว

mysql -u root -p < all_databases_backup.sql

ตั้ง Automated Backup ด้วย Cron Job

การ Backup ด้วยมือทุกวันไม่ใช่วิธีที่เชื่อถือได้ ควรตั้ง Cron Job ให้ทำงานอัตโนมัติ เริ่มจากสร้าง Script สำหรับ Backup

#!/bin/bash
BACKUP_DIR="/backup/db"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="myapp_db"
DB_USER="backup_user"
DB_PASS="YourBackupPassword123!"

mkdir -p "$BACKUP_DIR"

mysqldump -u "$DB_USER" -p"$DB_PASS" --single-transaction --routines --triggers --events "$DB_NAME" | gzip > "$BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz"

# ลบ Backup ที่เก่ากว่า 30 วัน
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +30 -delete

echo "Backup completed: ${DB_NAME}_${DATE}.sql.gz"

บันทึก Script เป็นไฟล์ เช่น /usr/local/bin/backup_db.sh และตั้งค่าสิทธิ์

chmod 700 /usr/local/bin/backup_db.sh

เพิ่ม Cron Job ให้ทำงานทุกวันตี 2

crontab -e

เพิ่มบรรทัดนี้

0 2 * * * /usr/local/bin/backup_db.sh >> /var/log/db_backup.log 2>&1

สร้าง User สำหรับ Backup โดยเฉพาะ

ไม่ควรใช้ Root User สำหรับ Automated Backup ให้สร้าง User ที่มีสิทธิ์เฉพาะที่จำเป็น

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'YourBackupPassword123!';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, PROCESS, EVENT ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

สิทธิ์เหล่านี้เพียงพอสำหรับการ Dump — SELECT อ่านข้อมูล, SHOW VIEW ดู View Definition, TRIGGER ดึง Triggers, LOCK TABLES สำหรับ Lock (กรณี MyISAM), PROCESS สำหรับ –single-transaction และ EVENT สำหรับ –events

ใช้ .my.cnf เก็บรหัสผ่านอย่างปลอดภัย

การใส่รหัสผ่านใน Script หรือ Command Line ไม่ปลอดภัย เพราะจะเห็นได้จาก Process List หรือ Shell History ให้ใช้ไฟล์ .my.cnf แทน

sudo nano ~/.my.cnf

เพิ่มเนื้อหาต่อไปนี้

[mysqldump]
user=backup_user
password=YourBackupPassword123!

ตั้งค่าสิทธิ์ให้เฉพาะเจ้าของไฟล์อ่านได้

chmod 600 ~/.my.cnf

หลังตั้งค่าแล้ว ใช้เครื่องมือ Dump ได้โดยไม่ต้องระบุ -u หรือ -p เพราะจะอ่านจากไฟล์อัตโนมัติ ปรับ Script ได้ดังนี้

mysqldump --single-transaction --routines --triggers --events myapp_db | gzip > backup.sql.gz

ตรวจสอบความถูกต้องของ Backup

Backup ที่ไม่เคยทดสอบ Restore ก็เหมือนไม่มี Backup ควรตรวจสอบเป็นประจำด้วยวิธีเหล่านี้

ตรวจขนาดไฟล์ — ไฟล์ Backup ไม่ควรมีขนาด 0 Bytes หรือเล็กผิดปกติเมื่อเทียบกับครั้งก่อน

ls -lh /backup/db/

ตรวจเนื้อหาท้ายไฟล์ — ไฟล์ Backup ที่สมบูรณ์จะจบด้วยบรรทัด “– Dump completed” ถ้าไม่มีแสดงว่า Backup ถูก Interrupt

zcat myapp_db_backup.sql.gz | tail -5

ทดสอบ Restore จริง — วิธีที่เชื่อถือได้ที่สุดคือ Restore เข้าฐานข้อมูลทดสอบแล้วเปรียบเทียบจำนวนแถวกับต้นฉบับ

mysql -u root -p -e "CREATE DATABASE test_restore;"
gunzip < myapp_db_backup.sql.gz | mysql -u root -p test_restore
mysql -u root -p -e "SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema='test_restore';"
mysql -u root -p -e "DROP DATABASE test_restore;"

ส่ง Backup ไปเก็บภายนอก

Backup ที่เก็บอยู่บนเซิร์ฟเวอร์เดียวกันกับฐานข้อมูลไม่ปลอดภัย เพราะถ้าเซิร์ฟเวอร์เสียหาย Backup ก็หายไปด้วย ควรส่งสำเนาไปเก็บภายนอก เช่น ส่งไปเซิร์ฟเวอร์อื่นด้วย rsync

rsync -avz /backup/db/ user@remote-server:/backup/db/

หรือส่งไป Object Storage ด้วย CLI เช่น AWS S3

aws s3 cp /backup/db/myapp_db_20250115.sql.gz s3://my-backup-bucket/db-backup/

เพิ่มคำสั่งเหล่านี้ต่อท้าย Backup Script เพื่อให้ส่งอัตโนมัติหลัง Dump เสร็จ

ข้อจำกัดของ mysqldump และทางเลือก

เครื่องมือนี้เป็น Logical Backup ที่ Export เป็น SQL ดังนั้นทั้งกระบวนการ Dump และ Restore จะช้าลงตามขนาดข้อมูล สำหรับฐานข้อมูลขนาดใหญ่ (หลายสิบ GB ขึ้นไป) ควรพิจารณาทางเลือกอื่น

mysqlpump — เวอร์ชันปรับปรุงที่รองรับ Parallel Dump หลายตารางพร้อมกัน เร็วกว่าตัวเดิมในหลายกรณี แต่ยังเป็น Logical Backup เช่นกัน

Shell dump utility — เครื่องมือจาก Shell Client ที่รองรับ Parallel Export/Import ได้เร็วมาก เหมาะกับข้อมูลขนาดกลาง

Percona XtraBackup — ทำ Physical Backup (คัดลอกไฟล์ Data) แบบ Hot Backup ไม่ต้อง Lock ตาราง เหมาะกับฐานข้อมูลขนาดใหญ่ที่ต้องการ Backup เร็วและ Restore เร็ว

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

สรุป

เครื่องมือ Dump นี้เชื่อถือได้และใช้งานง่าย สิ่งสำคัญที่ต้องจำคือ ใช้ –single-transaction สำหรับตาราง InnoDB เพื่อไม่ให้กระทบผู้ใช้งาน รวม –routines –triggers –events เพื่อ Backup ครบถ้วน บีบอัดด้วย gzip เพื่อประหยัดพื้นที่ ตั้ง Cron Job ให้ทำงานอัตโนมัติ สร้าง User เฉพาะสำหรับ Backup ส่งสำเนาไปเก็บภายนอก และที่สำคัญที่สุดคือทดสอบ Restore เป็นประจำ เพราะ Backup ที่ไม่เคยทดสอบก็เหมือนไม่มี

แนะนำบริการ DE

หากต้องการเซิร์ฟเวอร์ที่รัน Cron Job สำรองฐานข้อมูลได้อิสระ พร้อมพื้นที่เพียงพอสำหรับเก็บ Backup หลายวัน Cloud VPS ของ DE ให้ Root Access เต็มรูปแบบพร้อม SSD Storage ที่เพิ่มขนาดได้ตามต้องการ เหมาะกับการจัดการฐานข้อมูลที่ต้องควบคุมทุกขั้นตอน

สำหรับผู้ที่ต้องการความสะดวกโดยไม่ต้องจัดการ Backup เอง Cloud Hosting ของ DE มีระบบ Backup ฐานข้อมูลอัตโนมัติผ่าน Control Panel เหมาะกับเว็บไซต์ทั่วไปที่ไม่ต้องการจัดการเซิร์ฟเวอร์