Automated Database Backup Scripts

Categories

การ Backup ฐานข้อมูลเป็นงานที่ต้องทำเป็นประจำทุกวัน แต่การ Backup ด้วยมือทุกครั้งไม่เพียงแต่เสียเวลา ยังเสี่ยงต่อการลืมหรือทำผิดขั้นตอน วิธีที่ดีที่สุดคือเขียน Script อัตโนมัติแล้วตั้ง Cron Job ให้ทำงานตามกำหนดเวลา ไม่ว่าจะใช้ MySQL, MariaDB, PostgreSQL หรือ MongoDB ก็สามารถสร้างระบบ Backup อัตโนมัติได้

บทความนี้จะแนะนำวิธีเขียน Backup Script สำหรับฐานข้อมูลยอดนิยม พร้อมการตั้งค่า Cron Job, การบีบอัดไฟล์, การลบ Backup เก่าอัตโนมัติ, การแจ้งเตือนเมื่อ Backup ล้มเหลว และแนวทางการทดสอบว่า Backup ใช้งานได้จริง

หลักการออกแบบ Backup Script

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

Backup Script สำหรับ MySQL / MariaDB

mysqldump เป็นเครื่องมือมาตรฐานสำหรับ Backup ฐานข้อมูล MySQL และ MariaDB โดยจะ Export ข้อมูลออกมาเป็นไฟล์ SQL ที่สามารถนำไป Restore ได้

ตั้งค่า Credential แยกไฟล์

# สร้างไฟล์ Credential สำหรับ MySQL
sudo nano /root/.my.cnf

# เนื้อหา:
[mysqldump]
user=backup_user
password=BackupPass123!

[mysql]
user=backup_user
password=BackupPass123!

# ตั้ง Permission ให้เฉพาะ root อ่านได้
sudo chmod 600 /root/.my.cnf

# สร้าง User สำหรับ Backup (เข้า MySQL ด้วย root ก่อน)
# CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'BackupPass123!';
# GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER, LOCK TABLES ON *.* TO 'backup_user'@'localhost';
# FLUSH PRIVILEGES;

Backup Script แบบเต็มรูปแบบ

#!/bin/bash
# mysql_backup.sh — Automated MySQL/MariaDB Backup Script

# ======== ตั้งค่า ========
BACKUP_DIR="/backup/mysql"
LOG_FILE="/var/log/mysql_backup.log"
RETENTION_DAYS=30
DATE=$(date +%Y%m%d_%H%M%S)
HOSTNAME=$(hostname)

# รายชื่อฐานข้อมูลที่ต้องการ Backup (เว้นว่างไว้ = ทุกฐานข้อมูล)
DATABASES=""

# ======== สร้างโฟลเดอร์ ========
mkdir -p "$BACKUP_DIR"

# ======== Function: เขียน Log ========
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" >> "$LOG_FILE"
    echo "$1"
}

# ======== Function: ส่ง Alert ========
send_alert() {
    local subject="[BACKUP FAILED] $HOSTNAME - MySQL Backup"
    local message="$1"
    # ส่งอีเมล (ต้องติดตั้ง mailutils)
    # echo "$message" | mail -s "$subject" [email protected]

    # หรือส่งผ่าน Webhook (Slack/Discord)
    # curl -X POST -H 'Content-type: application/json' \
    #   --data "{\"text\":\"$subject\n$message\"}" \
    #   "https://hooks.slack.com/services/YOUR/WEBHOOK/URL"

    log "ALERT: $message"
}

# ======== เริ่ม Backup ========
log "===== START MySQL Backup ====="

# หาฐานข้อมูลทั้งหมด (ถ้าไม่ได้ระบุ)
if [ -z "$DATABASES" ]; then
    DATABASES=$(mysql -e "SHOW DATABASES;" -s --skip-column-names | \
        grep -Ev "^(information_schema|performance_schema|sys)$")
fi

BACKUP_COUNT=0
ERROR_COUNT=0

for DB in $DATABASES; do
    BACKUP_FILE="$BACKUP_DIR/${DB}_${DATE}.sql.gz"
    log "Backing up: $DB"

    # Dump + Compress
    mysqldump --defaults-extra-file=/root/.my.cnf \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        "$DB" 2>> "$LOG_FILE" | gzip > "$BACKUP_FILE"

    # ตรวจสอบผลลัพธ์
    if [ ${PIPESTATUS[0]} -eq 0 ] && [ -s "$BACKUP_FILE" ]; then
        SIZE=$(du -h "$BACKUP_FILE" | cut -f1)
        log "OK: $DB -> $BACKUP_FILE ($SIZE)"
        BACKUP_COUNT=$((BACKUP_COUNT + 1))
    else
        log "FAILED: $DB"
        ERROR_COUNT=$((ERROR_COUNT + 1))
        rm -f "$BACKUP_FILE"
        send_alert "Failed to backup database: $DB"
    fi
done

# ======== ลบ Backup เก่า ========
DELETED=$(find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete -print | wc -l)
log "Deleted $DELETED old backup(s) (older than $RETENTION_DAYS days)"

# ======== สรุปผล ========
log "SUMMARY: $BACKUP_COUNT success, $ERROR_COUNT failed"
log "===== END MySQL Backup ====="

# ส่ง Alert ถ้ามี Error
if [ $ERROR_COUNT -gt 0 ]; then
    send_alert "Backup completed with $ERROR_COUNT error(s). Check $LOG_FILE"
    exit 1
fi

exit 0

Backup Script สำหรับ PostgreSQL

PostgreSQL ใช้ pg_dump สำหรับ Backup รายฐานข้อมูล และ pg_dumpall สำหรับ Backup ทั้งหมดรวม Roles และ Tablespace การตั้งค่า Credential ใช้ไฟล์ .pgpass แทนการใส่ Password ใน Script

ตั้งค่า Credential

# สร้างไฟล์ .pgpass
nano ~/.pgpass

# รูปแบบ: hostname:port:database:username:password
localhost:5432:*:backup_user:BackupPass123!

# ตั้ง Permission
chmod 600 ~/.pgpass

# สร้าง Backup User (เข้า psql ด้วย postgres ก่อน)
# CREATE ROLE backup_user WITH LOGIN PASSWORD 'BackupPass123!';
# GRANT CONNECT ON DATABASE myapp TO backup_user;
# GRANT USAGE ON SCHEMA public TO backup_user;
# GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;
# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup_user;

Backup Script แบบเต็มรูปแบบ

#!/bin/bash
# pg_backup.sh — Automated PostgreSQL Backup Script

# ======== ตั้งค่า ========
BACKUP_DIR="/backup/postgresql"
LOG_FILE="/var/log/pg_backup.log"
RETENTION_DAYS=30
DATE=$(date +%Y%m%d_%H%M%S)
PG_USER="backup_user"
HOSTNAME=$(hostname)

# ฐานข้อมูลที่ต้องการ Backup (เว้นว่าง = ทุก DB)
DATABASES=""

# ======== สร้างโฟลเดอร์ ========
mkdir -p "$BACKUP_DIR"

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

send_alert() {
    log "ALERT: $1"
}

# ======== เริ่ม Backup ========
log "===== START PostgreSQL Backup ====="

# Backup Global Objects (roles, tablespaces)
GLOBALS_FILE="$BACKUP_DIR/globals_${DATE}.sql.gz"
pg_dumpall -U "$PG_USER" --globals-only 2>> "$LOG_FILE" | gzip > "$GLOBALS_FILE"
if [ ${PIPESTATUS[0]} -eq 0 ]; then
    log "OK: Global objects -> $GLOBALS_FILE"
else
    log "FAILED: Global objects"
    send_alert "Failed to backup global objects"
fi

# หาฐานข้อมูลทั้งหมด
if [ -z "$DATABASES" ]; then
    DATABASES=$(psql -U "$PG_USER" -d postgres -t -c \
        "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres';" | \
        sed 's/^[ ]*//' | grep -v '^$')
fi

BACKUP_COUNT=0
ERROR_COUNT=0

for DB in $DATABASES; do
    BACKUP_FILE="$BACKUP_DIR/${DB}_${DATE}.custom"
    log "Backing up: $DB"

    # ใช้ Custom Format (-Fc) เพื่อรองรับ Selective Restore
    pg_dump -U "$PG_USER" -Fc -Z 6 "$DB" -f "$BACKUP_FILE" 2>> "$LOG_FILE"

    if [ $? -eq 0 ] && [ -s "$BACKUP_FILE" ]; then
        SIZE=$(du -h "$BACKUP_FILE" | cut -f1)
        log "OK: $DB -> $BACKUP_FILE ($SIZE)"
        BACKUP_COUNT=$((BACKUP_COUNT + 1))
    else
        log "FAILED: $DB"
        ERROR_COUNT=$((ERROR_COUNT + 1))
        rm -f "$BACKUP_FILE"
        send_alert "Failed to backup database: $DB"
    fi
done

# ======== ลบ Backup เก่า ========
DELETED=$(find "$BACKUP_DIR" -name "*.custom" -o -name "*.sql.gz" | \
    xargs -I {} sh -c 'find "{}" -mtime +'"$RETENTION_DAYS"' -delete -print 2>/dev/null' | wc -l)
log "Deleted $DELETED old backup(s)"

# ======== สรุปผล ========
log "SUMMARY: $BACKUP_COUNT success, $ERROR_COUNT failed"
log "===== END PostgreSQL Backup ====="

[ $ERROR_COUNT -gt 0 ] && exit 1
exit 0

Backup Script สำหรับ MongoDB

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

#!/bin/bash
# mongo_backup.sh — Automated MongoDB Backup Script

# ======== ตั้งค่า ========
BACKUP_DIR="/backup/mongodb"
LOG_FILE="/var/log/mongo_backup.log"
RETENTION_DAYS=30
DATE=$(date +%Y%m%d_%H%M%S)
HOSTNAME=$(hostname)

# MongoDB Connection
MONGO_URI="mongodb://backup_user:BackupPass123!@localhost:27017"
AUTH_DB="admin"

mkdir -p "$BACKUP_DIR"

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

send_alert() {
    log "ALERT: $1"
}

# ======== เริ่ม Backup ========
log "===== START MongoDB Backup ====="

BACKUP_FILE="$BACKUP_DIR/full_${DATE}.gz"

# Backup ทั้งหมดแบบบีบอัด
mongodump --uri="$MONGO_URI" \
    --authenticationDatabase="$AUTH_DB" \
    --gzip \
    --archive="$BACKUP_FILE" 2>> "$LOG_FILE"

if [ $? -eq 0 ] && [ -s "$BACKUP_FILE" ]; then
    SIZE=$(du -h "$BACKUP_FILE" | cut -f1)
    log "OK: Full backup -> $BACKUP_FILE ($SIZE)"
else
    log "FAILED: Full backup"
    send_alert "MongoDB full backup failed"
    rm -f "$BACKUP_FILE"
    exit 1
fi

# ======== ลบ Backup เก่า ========
DELETED=$(find "$BACKUP_DIR" -name "*.gz" -mtime +$RETENTION_DAYS -delete -print | wc -l)
log "Deleted $DELETED old backup(s)"

log "===== END MongoDB Backup ====="
exit 0

ตั้งค่า Cron Job

เมื่อเขียน Script เสร็จแล้ว ขั้นตอนถัดไปคือตั้ง Cron Job ให้ทำงานอัตโนมัติ ก่อนตั้ง Cron ต้องทำให้ Script สามารถรันได้ด้วยคำสั่ง chmod แล้วจึงเพิ่มบรรทัดใน crontab

# ทำให้ Script รันได้
chmod +x /opt/scripts/mysql_backup.sh
chmod +x /opt/scripts/pg_backup.sh
chmod +x /opt/scripts/mongo_backup.sh

# แก้ไข Cron ของ root
sudo crontab -e

# ========== ตัวอย่าง Cron Schedule ==========

# MySQL Backup ทุกวัน ตี 2
0 2 * * * /opt/scripts/mysql_backup.sh >> /var/log/cron_mysql.log 2>&1

# PostgreSQL Backup ทุกวัน ตี 3
0 3 * * * /opt/scripts/pg_backup.sh >> /var/log/cron_pg.log 2>&1

# MongoDB Backup ทุกวัน ตี 4
0 4 * * * /opt/scripts/mongo_backup.sh >> /var/log/cron_mongo.log 2>&1

# Full Backup ทุกสัปดาห์ (วันอาทิตย์ ตี 1) + Daily Incremental
0 1 * * 0 /opt/scripts/weekly_full_backup.sh >> /var/log/cron_weekly.log 2>&1
0 2 * * 1-6 /opt/scripts/daily_incremental.sh >> /var/log/cron_daily.log 2>&1

# ========== ตรวจสอบ Cron ==========
# ดู Cron Job ทั้งหมด
crontab -l

# ตรวจสอบว่า Cron Service ทำงานอยู่
systemctl status cron

การส่ง Backup ไปเก็บ Remote Storage

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

# ======== วิธีที่ 1: rsync ไปเซิร์ฟเวอร์อื่น ========
rsync -avz --delete /backup/mysql/ backup-server:/backup/mysql/

# ======== วิธีที่ 2: SCP ส่งไฟล์เฉพาะวันนี้ ========
scp /backup/mysql/*_$(date +%Y%m%d)*.sql.gz backup-server:/backup/mysql/

# ======== วิธีที่ 3: rclone ส่งไป Cloud Storage ========
# ติดตั้ง rclone แล้ว configure ก่อน
rclone sync /backup/mysql/ remote:bucket-name/mysql-backup/

# ======== วิธีที่ 4: AWS S3 ========
aws s3 sync /backup/mysql/ s3://my-backup-bucket/mysql/ \
    --storage-class STANDARD_IA \
    --delete

# ======== รวมเข้า Script ========
# เพิ่มท้าย Backup Script ก่อน exit 0
sync_to_remote() {
    log "Syncing to remote storage..."
    rsync -avz /backup/mysql/ backup-server:/backup/mysql/ 2>> "$LOG_FILE"
    if [ $? -eq 0 ]; then
        log "Remote sync: OK"
    else
        log "Remote sync: FAILED"
        send_alert "Failed to sync backup to remote storage"
    fi
}

ทดสอบ Restore จาก Backup

Backup ที่ไม่เคยทดสอบ Restore ก็ไม่ต่างจากไม่มี Backup ควรทดสอบ Restore เป็นประจำอย่างน้อยเดือนละ 1 ครั้ง โดย Restore ลงฐานข้อมูลชั่วคราวแล้วตรวจสอบว่าข้อมูลถูกต้องครบถ้วน

# ======== MySQL Restore Test ========
# สร้างฐานข้อมูลชั่วคราว
mysql -e "CREATE DATABASE restore_test;"

# Restore จาก Backup
gunzip < /backup/mysql/myapp_20260407_020000.sql.gz | mysql restore_test

# ตรวจสอบจำนวนข้อมูล
mysql -e "SELECT COUNT(*) FROM restore_test.users;"
mysql -e "SELECT COUNT(*) FROM restore_test.orders;"

# ลบฐานข้อมูลชั่วคราว
mysql -e "DROP DATABASE restore_test;"

# ======== PostgreSQL Restore Test ========
createdb -U postgres restore_test
pg_restore -U postgres -d restore_test /backup/postgresql/myapp_20260407_030000.custom
psql -U postgres -d restore_test -c "SELECT COUNT(*) FROM users;"
dropdb -U postgres restore_test

# ======== MongoDB Restore Test ========
mongorestore --uri="mongodb://adminUser:StrongPassword123!@localhost:27017" \
    --nsFrom="myapp.*" --nsTo="restore_test.*" \
    --gzip --archive=/backup/mongodb/full_20260407_040000.gz

mongosh -u adminUser -p 'StrongPassword123!' --authenticationDatabase admin \
    --eval "db.getSiblingDB('restore_test').users.countDocuments()"

mongosh -u adminUser -p 'StrongPassword123!' --authenticationDatabase admin \
    --eval "db.getSiblingDB('restore_test').dropDatabase()"

Script ตรวจสอบ Backup อัตโนมัติ

นอกจาก Backup แล้ว ควรมี Script ตรวจสอบว่า Backup ของวันนี้สร้างสำเร็จหรือไม่ ถ้าไม่พบไฟล์หรือไฟล์มีขนาดเล็กผิดปกติก็ส่ง Alert ทันที

#!/bin/bash
# check_backup.sh — ตรวจสอบ Backup ประจำวัน

BACKUP_DIR="/backup/mysql"
TODAY=$(date +%Y%m%d)
MIN_SIZE=1024  # ขนาดขั้นต่ำ (bytes) ถ้าเล็กกว่านี้ถือว่าผิดปกติ
LOG_FILE="/var/log/check_backup.log"

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

# หาไฟล์ Backup ของวันนี้
BACKUPS=$(find "$BACKUP_DIR" -name "*${TODAY}*" -type f)

if [ -z "$BACKUPS" ]; then
    log "CRITICAL: No backup files found for today ($TODAY)"
    # send alert
    exit 1
fi

ERROR=0
for FILE in $BACKUPS; do
    SIZE=$(stat -f%z "$FILE" 2>/dev/null || stat -c%s "$FILE" 2>/dev/null)
    if [ "$SIZE" -lt "$MIN_SIZE" ]; then
        log "WARNING: $FILE is too small (${SIZE} bytes)"
        ERROR=1
    else
        SIZE_H=$(du -h "$FILE" | cut -f1)
        log "OK: $FILE ($SIZE_H)"
    fi
done

if [ $ERROR -eq 0 ]; then
    log "All backups look good for $TODAY"
fi

exit $ERROR

สรุป

การสร้างระบบ Backup อัตโนมัติไม่ซับซ้อนแต่ต้องทำอย่างรอบคอบ สิ่งสำคัญคือเก็บ Credential แยกจาก Script, บีบอัดไฟล์เพื่อประหยัดพื้นที่, ลบ Backup เก่าอัตโนมัติ, ส่ง Backup ไปเก็บที่อื่นด้วย, ตรวจสอบผลลัพธ์ทุกครั้ง และที่สำคัญที่สุดคือทดสอบ Restore เป็นประจำ Backup ที่ไม่เคยทดสอบก็เหมือนไม่มี Backup

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

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

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