Backup และ Restore MySQL Database บน VPS ทีละขั้นตอน

Q: ตั้งค่า cron backup MySQL ทุกคืนได้อย่างไร

ตั้งค่า cron job ผ่านคำสั่ง crontab -e แล้วเพิ่มบรรทัด: 0 2 * * * /usr/bin/mysqldump -u root -p'YOURPASSWORD' --all-databases | gzip > /backup/db_$(date +\%Y\%m\%d).sql.gz คำสั่งนี้จะ backup ทุกคืนเวลา 02:00 น. และบีบอัดไฟล์ด้วย gzip เพื่อประหยัดพื้นที่ ควรเก็บไฟล์ backup ไว้ต่างโฟลเดอร์จาก web root และลบไฟล์เก่าที่อายุเกิน 30 วันออกด้วย find /backup -name '*.sql.gz' -mtime +30 -delete เพื่อไม่ให้ disk เต็ม

MySQL Database เป็นหัวใจของแอปพลิเคชันส่วนใหญ่ที่รันบน VPS ไม่ว่าจะเป็น WordPress, Laravel, Node.js หรือระบบ E-Commerce ข้อมูลใน Database มีมูลค่าสูงและกู้คืนได้ยากหากสูญหาย การ Backup อย่างสม่ำเสมอและรู้วิธี Restore อย่างถูกต้องจึงเป็นทักษะพื้นฐานที่ทุกคนที่ใช้ VPS ต้องมี บทความนี้อธิบายตั้งแต่การ Backup ด้วย mysqldump ไปจนถึงการตั้ง Cron Job อัตโนมัติและการ Restore อย่างปลอดภัย พร้อมตัวอย่างคำสั่งจริงที่ใช้งานได้ทันที

ทำไม Backup MySQL ถึงสำคัญบน VPS

บน VPS คุณมี Root Access เต็มรูปแบบซึ่งหมายความว่าคุณควบคุมได้ทุกอย่าง แต่ก็หมายความว่าความผิดพลาดสามารถเกิดขึ้นได้ทุกเมื่อเช่นกัน ไม่มี Hosting Provider ที่ backup Database ให้คุณโดยอัตโนมัติในระดับ Application Layer ดังนั้นความรับผิดชอบทั้งหมดจึงอยู่ที่คุณ สาเหตุหลักที่ Database อาจสูญหายบน VPS มีดังนี้

Human Error — คำสั่ง DROP TABLE หรือ DELETE โดยไม่มี WHERE เกิดขึ้นได้เสมอ แม้แต่ผู้เชี่ยวชาญ
Hardware Failure — Disk บน VPS อาจเสียหายได้ แม้จะน้อยแต่ก็เป็นไปได้
Ransomware / Cyberattack — หาก MySQL ตั้งค่าไม่ดีอาจถูก encrypt หรือลบข้อมูล
Software Bug — อัปเดต Plugin/Framework ผิดพลาดอาจ migrate schema ผิดหรือลบข้อมูล
Disk Full — เมื่อ Disk เต็ม MySQL อาจ corrupt ข้อมูลที่กำลัง write อยู่

กฎ 3-2-1 สำหรับ Backup คือ เก็บ 3 สำเนา บน 2 ตำแหน่งที่ต่างกัน และ 1 สำเนาต้องอยู่ off-site เช่น Cloud Storage สำหรับ MySQL บน VPS ขั้นต่ำคือต้องมี local backup ที่รันอัตโนมัติทุกวัน และส่ง backup ออกไปยังที่อื่นอย่างน้อยสัปดาห์ละครั้ง

เครื่องมือ Backup MySQL ที่ควรรู้จัก

MySQL มีเครื่องมือ backup หลายตัวที่มีประสิทธิภาพแตกต่างกัน การเลือกใช้ขึ้นอยู่กับขนาด database ประเภท storage engine และ downtime ที่ยอมรับได้

เครื่องมือ	ประเภท	Lock Table	เหมาะกับ
mysqldump	Logical (SQL text)	ไม่ lock (InnoDB + `--single-transaction`)	DB < 5 GB, ใช้งานง่าย
mysqlpump	Logical (parallel)	ไม่ lock (InnoDB)	DB หลายตัวพร้อมกัน, > 1 GB
Percona XtraBackup	Physical (file copy)	Hot backup ไม่ต้อง lock	Production DB ขนาดใหญ่
mysqlhotcopy	Physical (deprecated)	Lock table ระหว่าง copy	ไม่แนะนำ (เฉพาะ MyISAM เก่า)

สำหรับผู้ใช้ VPS ทั่วไป mysqldump เป็นตัวเลือกที่ดีที่สุดเพราะติดมากับ MySQL ทุกเวอร์ชัน ไม่ต้องติดตั้งเพิ่ม และ output เป็น SQL text ที่อ่านและแก้ไขได้ง่าย

การ Backup ด้วย mysqldump

mysqldump เป็นคำสั่งที่ export โครงสร้างและข้อมูลออกมาเป็นไฟล์ SQL ซึ่งสามารถ restore ได้บน MySQL instance ใดก็ตาม

Backup Database เดี่ยว

รูปแบบพื้นฐานที่ใช้บ่อยที่สุด:

mysqldump -u root -p mydb > /backup/mydb_$(date +%Y%m%d).sql

หรือ backup พร้อมบีบอัดทันทีเพื่อประหยัดพื้นที่:

mysqldump -u root -p mydb | gzip > /backup/mydb_$(date +%Y%m%d).sql.gz

Backup แบบ Consistent สำหรับ InnoDB (ไม่ lock table)

Option --single-transaction สำคัญมากสำหรับ database ที่ใช้งานอยู่ เพราะทำให้ mysqldump ใช้ consistent snapshot โดยไม่ต้อง lock table ทำให้ web application ยังทำงานได้ระหว่าง backup:

mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  mydb | gzip > /backup/mydb_$(date +%Y%m%d_%H%M).sql.gz

option --routines (stored procedures), --triggers, และ --events ช่วยให้ backup ครบถ้วนรวมถึง database objects ทั้งหมด

Backup ทุก Database พร้อมกัน

mysqldump -u root -p \
  --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  | gzip > /backup/all_databases_$(date +%Y%m%d).sql.gz

Backup เฉพาะโครงสร้าง (ไม่มีข้อมูล)

มีประโยชน์เมื่อต้องการ clone schema ไปยัง development server:

mysqldump -u root -p --no-data mydb > /backup/mydb_schema_only.sql

การ Restore MySQL Database

การ Restore ต้องทำอย่างระมัดระวัง ควรทดสอบขั้นตอนนี้ใน environment ทดสอบก่อนเสมอ เพื่อให้แน่ใจว่าไฟล์ backup ใช้งานได้จริงก่อนเกิดเหตุฉุกเฉิน

Restore จากไฟล์ SQL ธรรมดา

# ขั้นที่ 1: สร้าง database ถ้ายังไม่มี
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

# ขั้นที่ 2: Restore
mysql -u root -p mydb < /backup/mydb_20260609.sql

Restore จากไฟล์ที่บีบอัด (.sql.gz)

gunzip -c /backup/mydb_20260609.sql.gz | mysql -u root -p mydb

หรือใช้ zcat ซึ่งเร็วกว่าเล็กน้อยในบางระบบ:

zcat /backup/mydb_20260609.sql.gz | mysql -u root -p mydb

Restore พร้อมแสดง progress (สำหรับไฟล์ขนาดใหญ่)

เมื่อไฟล์ backup มีขนาดใหญ่ การใช้ pv ช่วยแสดง progress ระหว่าง restore ได้ดี:

# ติดตั้ง pv ก่อน (ถ้ายังไม่มี)
apt install pv -y

# Restore พร้อม progress bar
pv /backup/mydb_20260609.sql.gz | gunzip | mysql -u root -p mydb

Restore เฉพาะบาง Table

บางครั้งต้องการ restore เฉพาะ table ที่เสียหาย ไม่ใช่ทั้ง database:

# ดึง table เฉพาะออกจาก dump file ก่อน
grep -n "Table structure for table .users." /backup/mydb_20260609.sql

# หรือใช้ sed ดึง table ที่ต้องการ (ต้องรู้บรรทัด start/end)
mysqldump -u root -p mydb users > /backup/users_table.sql
mysql -u root -p mydb < /backup/users_table.sql

เคล็ดลับสำคัญ: ก่อน restore ควรสร้างไฟล์ ~/.my.cnf เพื่อเก็บ password อย่างปลอดภัย แทนการพิมพ์ใน command line ซึ่งจะถูกบันทึกใน shell history:

[client] user=root password=YourPassword

ตั้ง permission ให้อ่านได้เฉพาะเจ้าของ: chmod 600 ~/.my.cnf แล้วรัน mysqldump/mysql โดยไม่ต้องใส่ -p

ตั้งค่า Cron Job Backup อัตโนมัติ

การ backup อัตโนมัติผ่าน Cron Job เป็นแนวทางที่ดีที่สุดสำหรับ Production เพราะไม่ต้องจำทำเอง และทำงานสม่ำเสมอทุกวัน วิธีตั้งค่ามีขั้นตอนดังนี้

สร้าง Backup Script

สร้างไฟล์ /usr/local/bin/mysql-backup.sh ด้วยเนื้อหาดังนี้:

#!/bin/bash
# MySQL Backup Script — AsiaGB VPS Guide
# วันที่สร้าง: 2026-06-09

BACKUP_DIR="/backup/mysql"
MYSQL_USER="root"
MYSQL_PASS="YourSecurePassword"
KEEP_DAYS=30
DATE=$(date +%Y%m%d_%H%M%S)

# สร้างโฟลเดอร์ถ้ายังไม่มี
mkdir -p "$BACKUP_DIR"

# Backup ทุก database
mysqldump -u "$MYSQL_USER" -p"$MYSQL_PASS" \
  --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  | gzip > "$BACKUP_DIR/all_db_${DATE}.sql.gz"

# ตรวจสอบว่า backup สำเร็จหรือไม่
if [ $? -eq 0 ]; then
    echo "[$(date)] Backup สำเร็จ: all_db_${DATE}.sql.gz" >> /var/log/mysql-backup.log
else
    echo "[$(date)] ERROR: Backup ล้มเหลว!" >> /var/log/mysql-backup.log
fi

# ลบ backup เก่าที่เกิน KEEP_DAYS วัน
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$KEEP_DAYS -delete
echo "[$(date)] ลบ backup เก่า (เกิน $KEEP_DAYS วัน) เสร็จแล้ว" >> /var/log/mysql-backup.log

ตั้ง permission ให้ execute ได้:

chmod +x /usr/local/bin/mysql-backup.sh

เพิ่ม Cron Job

เปิด crontab editor:

crontab -e

เพิ่มบรรทัดนี้เพื่อ backup ทุกวันเวลา 02:00 น.:

# Backup MySQL ทุกคืนเวลา 02:00 น.
0 2 * * * /usr/local/bin/mysql-backup.sh

หรือถ้าต้องการ backup ทุก 6 ชั่วโมงเพื่อความปลอดภัยสูงขึ้น:

0 */6 * * * /usr/local/bin/mysql-backup.sh

ตรวจสอบว่า Cron ทำงาน

# ดู log ของ backup
tail -f /var/log/mysql-backup.log

# ดูไฟล์ backup ที่สร้าง
ls -lh /backup/mysql/

# ทดสอบ script รันตรงๆ
/usr/local/bin/mysql-backup.sh

เทคนิคเพิ่มเติม: ใช้ mysqlpump สำหรับ Database ขนาดใหญ่

mysqlpump (สังเกตว่ามี p) เป็นเครื่องมือที่ MySQL เพิ่มเข้ามาตั้งแต่ version 5.7 รองรับ parallel export และมี progress indicator ในตัว เหมาะกับ database ขนาดตั้งแต่ 1 GB ขึ้นไป

# Backup ด้วย mysqlpump (parallel 4 threads)
mysqlpump -u root -p \
  --default-parallelism=4 \
  --compress-output=LZ4 \
  --all-databases \
  > /backup/all_db_pump_$(date +%Y%m%d).sql

# หรือ backup เฉพาะ database ที่เลือก
mysqlpump -u root -p \
  --default-parallelism=4 \
  --include-databases=mydb,mydb2 \
  > /backup/selected_db_$(date +%Y%m%d).sql

ข้อดีของ mysqlpump เหนือ mysqldump ที่ชัดเจน:

Parallel export ลดเวลา backup ได้มากบน multi-core server
มี progress indicator แสดง percentage ระหว่าง backup
รองรับ compression หลายรูปแบบในตัว (LZ4, ZLIB)
สามารถ include/exclude database หรือ table ได้ยืดหยุ่นกว่า
Output file มีขนาดเล็กกว่าด้วย compression ที่ดีกว่า

การจัดการไฟล์ Backup และ Security

ไฟล์ backup MySQL มีข้อมูลสำคัญทั้งหมดของ database ดังนั้นต้องเก็บอย่างปลอดภัย

ตั้ง Permission ที่เหมาะสม

# โฟลเดอร์ backup ให้ root อ่าน-เขียนได้เท่านั้น
chmod 700 /backup/mysql
chown root:root /backup/mysql

# ไฟล์ backup ให้ root อ่านได้เท่านั้น
chmod 600 /backup/mysql/*.sql.gz

ตรวจสอบความสมบูรณ์ของไฟล์ Backup

หลัง backup ควรตรวจสอบว่าไฟล์ไม่เสียหายและ restore ได้จริง:

# ตรวจสอบไฟล์ gz ว่าไม่ corrupt
gunzip -t /backup/mysql/all_db_20260609.sql.gz && echo "ไฟล์ OK"

# ดูบรรทัดสุดท้ายของ dump ว่ามี "Dump completed" หรือไม่
gunzip -c /backup/mysql/all_db_20260609.sql.gz | tail -5

ส่ง Backup ไปยัง Remote Storage

ควรส่ง backup ออกไปยัง off-site storage อย่างน้อยสัปดาห์ละครั้ง เพื่อป้องกันกรณี VPS มีปัญหา สามารถใช้ rclone ส่งไปยัง Google Drive, S3, หรือ Backblaze B2 ได้อย่างง่ายดาย

# ตัวอย่างใช้ rclone ส่งไปยัง S3
rclone copy /backup/mysql/ s3:mybucket/mysql-backup/ --min-age 1h

# เพิ่มใน cron ทุกวันหลัง backup เสร็จ
30 2 * * * rclone copy /backup/mysql/ s3:mybucket/mysql-backup/ --min-age 1h

Troubleshooting: ปัญหาที่พบบ่อยและวิธีแก้

แม้ขั้นตอน backup และ restore จะดูตรงไปตรงมา แต่ในทางปฏิบัติมักเจอปัญหาบางอย่างที่แก้ได้หากรู้สาเหตุ

Error: Access denied for user 'root'@'localhost'

เกิดเมื่อ password ผิดหรือ user ไม่มีสิทธิ์ แก้โดย:

# ตรวจสอบ user และ privilege
mysql -u root -p -e "SHOW GRANTS FOR 'root'@'localhost';"

# ถ้าต้องการใช้ user เฉพาะสำหรับ backup (แนะนำ)
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongPassword';
GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, EVENT ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

Error: Table 'xxx' doesn't exist หลัง Restore

มักเกิดเมื่อ charset ไม่ตรงกันหรือ dump ไม่ครบ ตรวจสอบด้วย:

# ตรวจว่า dump ครบไหม
grep "CREATE TABLE" /backup/mydb_20260609.sql | wc -l

# เปรียบเทียบกับ database จริง
mysql -u root -p -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='mydb';"

Restore ช้ามากบน Database ขนาดใหญ่

เพิ่ม parameter เหล่านี้ใน /etc/mysql/my.cnf ชั่วคราวระหว่าง restore:

[mysqld]
innodb_buffer_pool_size = 2G      # ปรับตาม RAM
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
sync_binlog = 0                   # ปิดชั่วคราวระหว่าง restore

และใช้ flag --disable-keys เพื่อปิด index rebuild ระหว่าง insert ซึ่งช่วยให้ restore เร็วขึ้นมาก:

mysql -u root -p mydb <<EOF
SET FOREIGN_KEY_CHECKS=0;
SET UNIQUE_CHECKS=0;
SET SESSION sql_log_bin=0;
SOURCE /backup/mydb_20260609.sql;
SET FOREIGN_KEY_CHECKS=1;
SET UNIQUE_CHECKS=1;
EOF

แนวปฏิบัติที่ดี: Backup Policy สำหรับ Production

นอกจากการตั้ง Cron Job แล้ว ควรมีนโยบาย backup ที่ครอบคลุมมากขึ้นสำหรับ Production server เพื่อให้สามารถกู้คืนได้ในทุกสถานการณ์

Backup ก่อนทุกการ Deploy — ก่อน deploy code ใหม่หรือรัน migration ควร backup database ทันที แม้จะมี cron อยู่แล้ว
ทดสอบ Restore อย่างน้อยเดือนละครั้ง — backup ที่ไม่เคย restore ทดสอบไม่มีความหมาย สร้าง staging server และ restore ไป verify ว่าข้อมูลครบ
Monitor ขนาดไฟล์ Backup — ถ้าไฟล์ backup วันใดมีขนาดน้อยกว่าปกติมาก อาจหมายความว่า dump ไม่สมบูรณ์
เก็บ Binary Log — MySQL Binary Log ช่วย Point-in-Time Recovery ได้ ทำให้ restore ไปยังเวลาที่ต้องการได้แม่นยำกว่า full backup
ตั้ง Alert เมื่อ Backup ล้มเหลว — ส่ง email หรือ notification เมื่อ backup script return error code ไม่ใช่ 0

คำถามที่พบบ่อย (FAQ)

mysqldump กับ mysqlpump ต่างกันอย่างไร

mysqldump เป็นเครื่องมือดั้งเดิมที่ทำงานแบบ single-thread ส่งออก SQL เป็น plain text เหมาะกับ database ขนาดเล็กถึงกลาง ส่วน mysqlpump เป็นเครื่องมือที่พัฒนาขึ้นมาใหม่รองรับ parallel export หลาย database พร้อมกัน มี progress indicator และบีบอัดได้ในตัว เหมาะกับ database ขนาดใหญ่ที่ต้องการความเร็วในการ backup แนะนำให้ใช้ mysqlpump เมื่อ database มีขนาดใหญ่กว่า 1 GB หรือต้องการ backup หลาย database พร้อมกัน

restore MySQL database แล้วข้อมูลหายบางส่วน ต้องทำอย่างไร

หากข้อมูลหายบางส่วนหลัง restore ให้ตรวจสอบ 3 ประเด็นหลัก ได้แก่ 1) Charset ไม่ตรงกัน เช่น dump มา utf8mb4 แต่ restore เข้า database ที่เป็น utf8 แก้โดยตรวจสอบ CHARACTER SET ใน .sql ไฟล์ 2) Transaction ถูก rollback ระหว่าง import เพราะ foreign key constraint ให้เพิ่ม SET FOREIGN_KEY_CHECKS=0 ก่อน import แล้วเปิดกลับ 3) ไฟล์ dump ไม่ครบ ให้ตรวจสอบว่า dump สำเร็จโดยดูบรรทัดสุดท้ายของ .sql ว่ามี "Dump completed on" หรือไม่

ตั้งค่า cron backup MySQL ทุกคืนได้อย่างไร

ตั้งค่า cron job ผ่านคำสั่ง crontab -e แล้วเพิ่มบรรทัด: 0 2 * * * /usr/bin/mysqldump -u root -p'YOURPASSWORD' --all-databases | gzip > /backup/db_$(date +\%Y\%m\%d).sql.gz คำสั่งนี้จะ backup ทุกคืนเวลา 02:00 น. และบีบอัดไฟล์ด้วย gzip เพื่อประหยัดพื้นที่ ควรเก็บไฟล์ backup ไว้ต่างโฟลเดอร์จาก web root และลบไฟล์เก่าที่อายุเกิน 30 วันออกด้วย find /backup -name '*.sql.gz' -mtime +30 -delete เพื่อไม่ให้ disk เต็ม

backup MySQL database ขนาดใหญ่ให้เร็วขึ้นมีวิธีใดบ้าง

มีหลายวิธีที่ช่วยให้ backup database ขนาดใหญ่เร็วขึ้น ได้แก่ 1) ใช้ mysqlpump แทน mysqldump เพราะรองรับ parallel threads ด้วย option --default-parallelism=4 2) ใช้ Percona XtraBackup สำหรับ InnoDB เป็น hot backup ที่ไม่ต้อง lock table 3) เพิ่ม option --single-transaction ใน mysqldump เพื่อ backup แบบ consistent โดยไม่ lock table (เฉพาะ InnoDB) 4) บีบอัดโดยตรงด้วย --compress หรือ pipe ผ่าน gzip/zstd เพื่อลดเวลา I/O 5) แบ่ง backup ทีละ database หรือทีละ table แทนการ dump ทีเดียวทั้งหมด

VPS KVM ประสิทธิภาพสูงของ AsiaGB

AsiaGB VPS Root Access เต็มรูปแบบ Docker MySQL Python Node.js เริ่มต้น 500 บาท/เดือน

ดูแพ็กเกจ VPS