Category Archive Linux

Byphunsanit

Linux: auto backup MySQL / MariaDB

ข้อมูลอาจจะเป็นส่วนที่สำคัญที่สุดใน server เพราะเป็นสิ่งที่อาจจะเอากลับมาไม่ได้อีกเลย ถ้าไม่ได้ backup เอาไว้เพื่อป้องกันข้อมูลสูญหายจากเหตุการณ์ไม่คาดคิด เช่น server ล่ม, เผลอใส่คำสั่งผิด หรือโดนเฮ็ก แต่ต้องทำบ่อย ๆ เป็นประจำสม่ำเสมอ เลยทำสคริปต์สำรองข้อมูล MySQL แบบอัตโนมัติเก็บไว้ที่ /usr/local/sbin/ สำหรับงานของผู้ดูแลระบบโดยเฉพาะ

สร้างสคริปต์สำรองข้อมูล MySQL
สร้างไฟล์โดยคำสั่ง
sudo nano /usr/local/sbin/backup_mysql.sh
สาเหตุที่ต้องวางไว้ใน /usr/local/sbin เพราะเป็นที่เก็บสคริปต์ของ root ที่ user ทั่วไปจะสั่ง run ไม่ได้
ใส่เนื้อหา

#!/bin/bash

# --- Set Variables ---
# MySQL user with backup privileges
DB_USER="<username>"

# MySQL user's password (recommended to use a .my.cnf file instead)
DB_PASS="<password>"

# Date for the directory name in YYYYMMDD format
DATE=$(date +"%Y%m%d")

# Directory to store backup files
BACKUP_DIR="/var/backups/mysql/$DATE"

# --- Check and Create Backup Directory ---
if [ ! -d "$BACKUP_DIR" ]; then
    mkdir -p "$BACKUP_DIR"
    echo "Creating directory: $BACKUP_DIR"
fi

# --- Start Database Backup Process ---
echo "--- Starting Database Backup ---"

# Get a list of all non-system databases
DATABASES=$(mysql -u$DB_USER -p$DB_PASS -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql|sys)")

# Loop through each database and back it up
for DB in $DATABASES; do
    echo "  > Backing up database: $DB"
    mysqldump --user=$DB_USER --password=$DB_PASS \
        --single-transaction \
        --events \
        --routines \
        --triggers \
        --add-drop-database "$DB" | gzip > "$BACKUP_DIR/$DB.sql.gz"

    if [ $? -eq 0 ]; then
        echo "    - Success: $BACKUP_DIR/$DB.sql.gz"
    else
        echo "    - Error backing up database: $DB"
    fi
done

echo "--- Database Backup Complete ---"

---

### Backup Users and Permissions

echo "--- Starting User and Permissions Backup ---"

# Backup user accounts and their associated permissions from the `mysql` system database
echo "  > Backing up user accounts, tables, and permissions..."
mysqldump -u$DB_USER -p$DB_PASS --single-transaction --skip-triggers --compact mysql user db tables_priv columns_priv procs_priv > "$BACKUP_DIR/users_and_grants.sql"
if [ $? -eq 0 ]; then
    echo "    - Success: $BACKUP_DIR/users_and_grants.sql"
else
    echo "    - Error backing up user accounts and grants"
fi

# Backup the "SHOW GRANTS" statements which are crucial for recovering permissions
echo "  > Backing up SHOW GRANTS statements..."
mysql -u$DB_USER -p$DB_PASS -NBe "SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') FROM mysql.user" | mysql -u$DB_USER -p$DB_PASS -N | sed 's/$/;/' > "$BACKUP_DIR/grants.sql"
if [ $? -eq 0 ]; then
    echo "    - Success: $BACKUP_DIR/grants.sql"
else
    echo "    - Error backing up grants"
fi

echo "--- User and Permissions Backup Complete ---"

---

### Delete Old Backup Files

echo "--- Deleting backup directories older than 90 days ---"
find /var/backups/mysql/ -maxdepth 1 -type d -name "????????*" -mtime +90 -exec rm -rf {} \;

echo "--- All backup processes finished successfully ---"

เปลี่ยน <username> และ <password> เป็น MySQL user / password ที่สามารถ backup ได้ทุก database
บันทึกและปิดไฟล์ (Ctrl + O, Enter, Ctrl + X)
ให้ excute ได้โดย
sudo chmod +x /usr/local/sbin/backup_mysql.sh

ทดลองทำงาน
sudo /usr/local/sbin/backup_mysql.sh
ถ้ามีการสร้างไฟล์ใน /var/backups/mysql/YYYYMMDD และ SQL ที่สร้างไว้ สามารถ backup ได้จริง ๆ มั๋ย

ทำ cron job
หรือตั้งเวลาให้ run /usr/local/sbin/backup_mysql.sh เอง โดยคำสั่ง
sudo crontab -e
เพิ่มในบรรทัดสุดท้าย
0 0 * * 4 /usr/local/sbin/backup_mysql.sh >/dev/null 2>&1
บันทึกและปิดไฟล์ (Ctrl + O, Enter, Ctrl + X)
จะทำงานเองทุก ๆ วันพฤหัสบดีเที่ยงคืน

จำกัดสิทธิ์
เพราะมี username password อยู่ด้วย จึงควรไม่ให้ user ทั่ว ๆ ไปเข้าถึง backup ได้ (หรือมาลบมันออกไป) ใช้คำสั่ง
# กำหนดเจ้าของไดเรกทอรีเป็น root
sudo chown root:root /var/backups/mysql/
# กำหนดสิทธิ์ให้เฉพาะเจ้าของเท่านั้นที่สามารถอ่าน, เขียน, และรันได้
sudo chmod 700 /var/backups/mysql/

เท่านี้เราจะมี backup ไว้สำรองข้อมูลถึง 3 เดือนไว้กันเหนียวแล้ว

อ่านเพิ่มเติม