Backup MySQL/MariaDB อัตโนมัติบน VPS: mysqldump + cron

มิถุนายน 2026 · อ่าน 7 นาที

Database คือหัวใจของเว็บไซต์ส่วนใหญ่ การสูญเสีย Database อาจหมายถึงสูญเสียทุกอย่าง การตั้ง Backup อัตโนมัติให้รันทุกคืนเป็นสิ่งที่ควรทำตั้งแต่วันแรกที่เปิด VPS บทความนี้จะพาตั้งระบบ Backup MySQL และ MariaDB แบบครบ ตั้งแต่ mysqldump ธรรมดา ไปจนถึง offsite backup ที่ป้องกันได้แม้ VPS พังทั้งลูก

Backup ด้วย mysqldump พื้นฐาน

# Backup ทุก Database
mysqldump -u root -p --all-databases > all-databases.sql

# Backup เฉพาะ Database เดียว
mysqldump -u root -p mydb > mydb.sql

# Backup พร้อม compress ทันที
mysqldump -u root -p mydb | gzip > mydb-$(date +%Y%m%d).sql.gz

# Backup แบบ single-transaction (ไม่ lock table — สำหรับ InnoDB)
mysqldump -u root -p --single-transaction --quick mydb | gzip > mydb.sql.gz

สร้าง Backup User แยกต่างหาก

ไม่ควรใช้ root สำหรับ Backup เพราะถ้า credential หลุด จะให้สิทธิ์มากเกิน ควรสร้าง User ที่มีสิทธิ์แค่อ่านและ lock table:

# สร้าง Backup User
mysql -u root -p
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongP@ss123';
GRANT SELECT, RELOAD, SHOW DATABASES, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;
EXIT;

สร้างไฟล์ .my.cnf เพื่อไม่ต้องพิมพ์รหัสผ่าน

# สร้างไฟล์ /root/.my.cnf (หรือ /home/backupuser/.my.cnf)
nano /root/.my.cnf

[mysqldump]
user=backup_user
password=StrongP@ss123
single-transaction
quick
max_allowed_packet=512M

# จำกัดสิทธิ์ไฟล์ — ห้ามให้คนอื่นอ่าน
chmod 600 /root/.my.cnf

# ทดสอบ backup โดยไม่ต้องพิมพ์รหัส
mysqldump --all-databases | gzip > test.sql.gz

สร้าง Backup Script แบบครบ

# สร้างไฟล์ /usr/local/bin/mysql-backup.sh
nano /usr/local/bin/mysql-backup.sh

#!/bin/bash
# MySQL Daily Backup Script

BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7
LOG_FILE="/var/log/mysql-backup.log"

mkdir -p "$BACKUP_DIR"

log() {
  echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" >> "$LOG_FILE"
}

log "Starting MySQL backup..."

# Backup ทุก Database แยกไฟล์
mysql -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|sys)" | while read db; do
  OUTFILE="$BACKUP_DIR/${db}_${DATE}.sql.gz"
  mysqldump --single-transaction "$db" | gzip > "$OUTFILE"
  if [ $? -eq 0 ]; then
    SIZE=$(du -sh "$OUTFILE" | cut -f1)
    log "OK: $db -> $OUTFILE ($SIZE)"
  else
    log "ERROR: Failed to backup $db"
  fi
done

# ลบ Backup เก่ากว่า RETENTION_DAYS วัน
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
log "Cleaned up backups older than $RETENTION_DAYS days"
log "Backup completed."

# ให้สิทธิ์รัน
chmod +x /usr/local/bin/mysql-backup.sh

# ทดสอบรันครั้งแรก
/usr/local/bin/mysql-backup.sh
ls -lh /var/backups/mysql/

ตั้ง cron รันอัตโนมัติทุกคืน

crontab -e

# เพิ่มบรรทัดนี้: รัน Backup ทุกวันตี 3
0 3 * * * /usr/local/bin/mysql-backup.sh

Offsite Backup ไป S3 (AWS/Cloudflare R2/Wasabi)

Backup บน VPS เดียวกันไม่ปลอดภัยพอ ถ้า VPS พัง disk เสีย หรือถูก ransomware Backup ก็หายไปด้วย ควร upload Backup ไปเก็บที่อื่นด้วย:

# ติดตั้ง AWS CLI หรือ rclone
apt install awscli -y
# หรือ
curl https://rclone.org/install.sh | sudo bash

# ตั้งค่า rclone (รองรับ S3, Cloudflare R2, Wasabi, SFTP, Google Drive ฯลฯ)
rclone config

# เพิ่มใน backup script หลัง backup เสร็จ
rclone copy "$BACKUP_DIR" remote:my-db-backups/$(hostname) --max-age 7d
log "Uploaded to remote storage"

Offsite Backup ผ่าน SFTP/SCP

# สร้าง SSH Key pair (ไม่ต้องใช้รหัสผ่าน)
ssh-keygen -t ed25519 -f /root/.ssh/backup_key -N ""

# Copy public key ไปยัง Backup Server
ssh-copy-id -i /root/.ssh/backup_key.pub [email protected]

# เพิ่มใน backup script
rsync -azq -e "ssh -i /root/.ssh/backup_key" \
  "$BACKUP_DIR/" [email protected]:/backups/$(hostname)/

ทดสอบ Restore ว่า Backup ใช้งานได้จริง

# ทดสอบ restore ใน Database ชั่วคราว
mysql -u root -p -e "CREATE DATABASE test_restore;"
zcat /var/backups/mysql/mydb_20260607_030001.sql.gz | mysql -u root -p test_restore

# ตรวจสอบว่าข้อมูลครบ
mysql -u root -p test_restore -e "SHOW TABLES; SELECT COUNT(*) FROM users;"

# ลบ Database ทดสอบ
mysql -u root -p -e "DROP DATABASE test_restore;"

Binary Log สำหรับ Point-in-Time Recovery

mysqldump คือ "snapshot" ณ เวลาที่ backup ถ้าต้องการ recover ได้ถึงนาทีที่ database พัง ต้องเปิด Binary Log ด้วย:

# เพิ่มใน /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
binlog_expire_logs_seconds = 604800  ; เก็บ 7 วัน
max_binlog_size = 100M

# Restart MySQL
systemctl restart mysql

ระบบ Backup ที่ดีต้องมีทั้งการรัน backup อัตโนมัติ, retention policy ที่เหมาะสม, offsite copy และการทดสอบ restore สม่ำเสมอ สี่ขั้นตอนนี้ทำให้คุณมั่นใจได้ว่าไม่ว่าจะเกิดอะไรขึ้นกับ VPS ข้อมูลของคุณจะยังอยู่ครบ

ตรวจสอบว่า Backup ทำงานถูกต้อง

# ทดสอบ backup script รันได้
/usr/local/bin/mysql-backup.sh

# ตรวจสอบไฟล์ backup ที่สร้าง
ls -lh /var/backups/mysql/
# ควรเห็นไฟล์ .sql.gz ที่สร้างเมื่อกี้

# ตรวจสอบว่า gzip ไฟล์ไม่เสีย
gzip -t /var/backups/mysql/mydb_*.sql.gz && echo "Backup file OK"

# ดู backup log
tail -20 /var/log/mysql-backup.log

แก้ปัญหาที่พบบ่อย

Error: "Access denied for user 'backup_user'" ตอน mysqldump

Backup user อาจไม่มีสิทธิ์เพียงพอ หรือ .my.cnf ตั้งค่าไม่ถูก:

# ทดสอบ backup user login
mysql -u backup_user -p -e "SHOW DATABASES;"

# ตรวจสอบสิทธิ์
mysql -u root -p -e "SHOW GRANTS FOR 'backup_user'@'localhost';"
# ต้องมี: SELECT, RELOAD, SHOW DATABASES, LOCK TABLES, REPLICATION CLIENT

# ตรวจสอบ .my.cnf
cat /root/.my.cnf

Error: ไฟล์ backup ขนาด 0 bytes หรือเล็กผิดปกติ

อาจเกิดจาก disk เต็ม หรือ mysqldump ล้มเหลวแต่ไม่มี error message ชัดเจน:

# ตรวจ disk space
df -h /var/backups/

# รัน mysqldump แสดง error โดยตรง (ไม่ผ่าน script)
mysqldump --single-transaction mydb 2>&1 | head -5
# ถ้ามี error จะเห็นตรงนี้

# ตรวจสอบ script ว่า redirect stderr ถูกต้อง
# ควรมี: 2>&1 ใน cron command

Error: "ERROR 2013 (HY000): Lost connection to MySQL server during query"

Backup ใช้เวลานานจน connection timeout โดยเฉพาะ database ขนาดใหญ่:

# เพิ่ม timeout ใน mysqldump
mysqldump --single-transaction --net-read-timeout=300 --net-write-timeout=300 mydb | gzip > backup.sql.gz

# หรือเพิ่มใน /root/.my.cnf
[mysqldump]
net_read_timeout=300
net_write_timeout=300

Error: cron backup ไม่รัน ทั้งที่รันมือได้

cron ไม่มี PATH environment เหมือน shell ต้องใช้ full path ทุกคำสั่ง:

# แก้ crontab ให้ใช้ full path
0 3 * * * /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

# ตรวจสอบว่า cron service รันอยู่
systemctl status cron

# ดู cron log
grep "mysql-backup" /var/log/syslog | tail -10

Error: ต้องการ restore ข้อมูลเฉพาะ table เดียว ไม่อยากทับทั้ง database

สามารถ restore เฉพาะ table ได้โดย grep เอา table นั้นออกจาก dump ก่อน:

# ดึงเฉพาะ create+insert ของ table ที่ต้องการ
zcat backup.sql.gz | sed -n '/CREATE TABLE `users`/,/CREATE TABLE `/{ /CREATE TABLE `users`/,/UNLOCK TABLES/p }' > users_only.sql

mysql -u root -p mydb < users_only.sql

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

--single-transaction จำเป็นไหม? ใช้กับทุก database ได้ไหม?

--single-transaction ทำงานได้ดีกับ InnoDB ซึ่งเป็น storage engine หลักของ MySQL/MariaDB ช่วยให้ backup ทำงานได้โดยไม่ lock table ทำให้เว็บยังให้บริการได้ระหว่าง backup ถ้าใช้ MyISAM (เก่ามาก) จะต้องใช้ --lock-tables แทน สำหรับ WordPress และ app ทั่วไปใช้ --single-transaction ได้เลย

ต้องการ offsite backup จริงๆ ไหม ถ้ามี snapshot จาก VPS provider แล้ว?

Snapshot ของ provider เป็นการ backup ระดับ disk ที่ดีมาก แต่มีข้อจำกัด: snapshot บางเจ้า restore ทั้ง disk เท่านั้น (ไม่สามารถ restore แค่บาง database), ราคาแพงกว่า, และถ้า provider มีปัญหา snapshot ก็อาจเสียด้วย การมี mysqldump offsite เป็น backup อีกชั้นช่วยให้ยืดหยุ่นในการ restore ได้มากกว่า

Backup database ขนาด 50GB+ ทำอย่างไรให้เร็ว?

สำหรับ database ขนาดใหญ่ให้ใช้ mydumper (parallel dump) แทน mysqldump ซึ่งทำงาน single thread: apt install mydumper แล้ว mydumper -u root -B mydb -o /backup/ mydumper dump หลาย table พร้อมกันทำให้เร็วกว่า mysqldump 5-10 เท่า และ restore ด้วย myloader ได้เช่นกัน