สคริปต์สำรอง MySQL อัตโนมัติรายวันพร้อมลบไฟล์เก่า

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

สคริปต์ออกแบบให้ปลอดภัยต่อการใช้งานจริง คือไม่ใส่รหัสผ่านในสคริปต์หรือใน Command Line ไม่ทิ้งไฟล์ที่สำรองไม่ครบไว้ให้เข้าใจผิดว่าเป็นไฟล์ที่ใช้ได้ และไม่รันซ้อนกันถ้ารอบก่อนยังไม่เสร็จ

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

• เซิร์ฟเวอร์ Linux (Ubuntu 22.04/24.04 หรือ AlmaLinux/Rocky 8-9) ที่ติดตั้ง MySQL 8 หรือ MariaDB 10.x และมีสิทธิ์ root หรือ sudo
• พื้นที่ดิสก์ว่างพอสำหรับไฟล์สำรองหลายวัน ประมาณได้จากขนาดไฟล์สำรองหนึ่งรอบคูณจำนวนวันที่จะเก็บ
• พื้นฐานคำสั่ง mysqldump ดู สำรองและกู้คืนฐานข้อมูล MySQL/MariaDB ด้วย mysqldump

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

ขั้นตอนที่ 1: สร้างผู้ใช้สำหรับสำรองข้อมูลโดยเฉพาะ

ไม่ควรใช้ root ของฐานข้อมูลในสคริปต์ ให้สร้างผู้ใช้ที่มีสิทธิ์เท่าที่จำเป็นสำหรับการอ่านและ Dump เท่านั้น เข้า mysql ในฐานะผู้ดูแล

sudo mysql

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'ตั้งรหัสผ่านยาวและสุ่ม';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS, RELOAD ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

สิทธิ์ PROCESS ใช้สำหรับ Dump ข้อมูล Tablespace บน MySQL 8 และ RELOAD ใช้ในบางกรณีร่วมกับ --single-transaction ผู้ใช้นี้แก้ไขหรือลบข้อมูลไม่ได้

ขั้นตอนที่ 2: เก็บรหัสผ่านในไฟล์ตั้งค่าที่มีสิทธิ์จำกัด

การใส่ -pรหัสผ่าน ใน Command Line จะทำให้รหัสผ่านปรากฏในรายการ Process ที่ผู้ใช้อื่นเห็นได้ ให้เก็บในไฟล์แทน

sudo nano /root/.my-backup.cnf

[client]
user=backup
password=ตั้งรหัสผ่านยาวและสุ่ม

ถ้ารหัสผ่านมีอักขระ # หรือ ; ให้ครอบด้วยเครื่องหมายคำพูด เช่น password="ab#12;cd" จากนั้นจำกัดสิทธิ์ให้ root อ่านได้เท่านั้น

sudo chmod 600 /root/.my-backup.cnf

ทดสอบว่าเชื่อมต่อได้ ตัวเลือก --defaults-extra-file ต้องเป็นตัวเลือกแรกของคำสั่งเสมอ

sudo mysql --defaults-extra-file=/root/.my-backup.cnf -e "SHOW DATABASES;"

ขั้นตอนที่ 3: สร้างสคริปต์สำรองข้อมูล

sudo nano /usr/local/sbin/mysql-backup.sh

ใส่เนื้อหาต่อไปนี้

#!/usr/bin/env bash
# สำรองทุกฐานข้อมูล MySQL/MariaDB แยกไฟล์ และลบไฟล์ที่เก่ากว่า RETENTION_DAYS
set -euo pipefail

BACKUP_DIR="/var/backups/mysql"
RETENTION_DAYS=14
CNF="/root/.my-backup.cnf"
STAMP="$(date +%F_%H%M)"

# MariaDB 11 ใช้ชื่อ mariadb / mariadb-dump ถ้าไม่มี mysql / mysqldump
MYSQL="$(command -v mysql || command -v mariadb)"
DUMP="$(command -v mysqldump || command -v mariadb-dump)"

# ป้องกันการรันซ้อน
exec 9>/run/mysql-backup.lock
flock -n 9 || { echo "$(date '+%F %T') previous backup still running, skip"; exit 1; }

umask 077
mkdir -p "$BACKUP_DIR"

DBS="$("$MYSQL" --defaults-extra-file="$CNF" -N -B -e 'SHOW DATABASES' \
  | grep -Ev '^(information_schema|performance_schema|sys|mysql)$')"

FAILED=0
for DB in $DBS; do
  OUT="$BACKUP_DIR/${DB}_${STAMP}.sql.gz"
  if "$DUMP" --defaults-extra-file="$CNF" --single-transaction --quick \
       --routines --triggers --events "$DB" | gzip > "$OUT.part"; then
    mv "$OUT.part" "$OUT"
    echo "$(date '+%F %T') OK   $DB -> $OUT ($(du -h "$OUT" | cut -f1))"
  else
    rm -f "$OUT.part"
    echo "$(date '+%F %T') FAIL $DB"
    FAILED=1
  fi
done

# ลบไฟล์สำรองที่เก่ากว่ากำหนด ทำเฉพาะเมื่อรอบนี้สำเร็จทั้งหมด
if [ "$FAILED" -eq 0 ]; then
  find "$BACKUP_DIR" -type f -name '*.sql.gz' -mtime +"$RETENTION_DAYS" -print -delete
fi

exit "$FAILED"

หลักการทำงานที่สำคัญของสคริปต์

• set -o pipefail ทำให้ถ้า mysqldump ล้มเหลว ทั้ง Pipe จะถือว่าล้มเหลว แม้ gzip จะทำงานสำเร็จ
• เขียนลงไฟล์ .part ก่อน แล้วค่อยเปลี่ยนชื่อเมื่อเสร็จ ไฟล์ .sql.gz ทุกไฟล์ในโฟลเดอร์จึงเป็นไฟล์ที่สำรองครบ
• ลบไฟล์เก่าเฉพาะเมื่อรอบนี้สำเร็จ ถ้าการสำรองเสียต่อเนื่องหลายวัน ไฟล์ดีชุดสุดท้ายจะไม่ถูกลบทิ้ง
• --single-transaction สำรองตาราง InnoDB ได้โดยไม่ล็อกตาราง แต่ตาราง MyISAM ยังอาจไม่สอดคล้องกันถ้ามีการเขียนระหว่างสำรอง
• ฐานข้อมูลระบบ mysql ถูกข้ามไว้ เพราะการกู้คืนข้ามเวอร์ชันมักมีปัญหา ผู้ใช้และสิทธิ์ควรบันทึกเป็นคำสั่ง CREATE USER และ GRANT แยกไว้

ให้สิทธิ์รันสคริปต์

sudo chmod 700 /usr/local/sbin/mysql-backup.sh

ขั้นตอนที่ 4: ทดสอบสคริปต์ด้วยมือ

ก่อนตั้งเวลา ให้ดูก่อนว่าคำสั่งลบจะเลือกไฟล์ใด โดยใช้ find แบบไม่มี -delete ซึ่งจะแสดงรายการเท่านั้น

sudo find /var/backups/mysql -type f -name '*.sql.gz' -mtime +14 -print

จากนั้นรันสคริปต์

sudo /usr/local/sbin/mysql-backup.sh

ผลลัพธ์ควรเป็นบรรทัด OK หนึ่งบรรทัดต่อหนึ่งฐานข้อมูล เช่น

2026-09-19 02:30:01 OK   shopdb -> /var/backups/mysql/shopdb_2026-09-19_0230.sql.gz (48M)

sudo nano /etc/cron.d/mysql-backup

SHELL=/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
30 2 * * * root /usr/local/sbin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

sudo tail -n 20 /var/log/mysql-backup.log
sudo ls -lh /var/backups/mysql/

sudo gzip -t /var/backups/mysql/shopdb_2026-09-19_0230.sql.gz && echo "gzip OK"
sudo zcat /var/backups/mysql/shopdb_2026-09-19_0230.sql.gz | tail -n 1