sudo nano /usr/local/bin/backup_mariadb.sh

#!/bin/bash

# 設定資料庫的用戶名和密碼
DB_USER="your_db_user"
DB_PASSWORD="your_db_password"
DB_HOST="10.0.0.1"

# 設定備份儲存目錄
BACKUP_DIR="/path/to/backup/dir"
mkdir -p $BACKUP_DIR

# 獲取當前日期和時間
CURRENT_DATE=$(date +%Y-%m-%d-%H-%M-%S)

# 獲取所有資料庫名稱
DATABASES=$(mysql -h$DB_HOST -u$DB_USER -p$DB_PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database)

# 備份每個資料庫
for DB in $DATABASES; do
  if [[ "$DB" != "information_schema" && "$DB" != "performance_schema" && "$DB" != "mysql" && "$DB" != "sys" ]]; then
    BACKUP_FILE="$BACKUP_DIR/$CURRENT_DATE-$DB.sql"
    mysqldump  -h$DB_HOST -u$DB_USER -p$DB_PASSWORD --databases $DB > $BACKUP_FILE
  fi
done

sudo chmod +x /usr/local/bin/backup_mariadb.sh

crontab -e

0 4 * * * /usr/local/bin/backup_mariadb.sh

# 備份每個資料庫並壓縮
for DB in $DATABASES; do
  if [[ "$DB" != "information_schema" && "$DB" != "performance_schema" && "$DB" != "mysql" && "$DB" != "sys" ]]; then
    BACKUP_FILE="$BACKUP_DIR/$CURRENT_DATE-$DB.sql"
    ZIP_FILE="$BACKUP_DIR/$CURRENT_DATE-$DB.zip"
    mysqldump -h$DB_HOST -u$DB_USER -p$DB_PASSWORD --databases $DB > $BACKUP_FILE
    zip $ZIP_FILE $BACKUP_FILE
    rm $BACKUP_FILE
  fi
done

# 刪除兩天前的備份文件
find $BACKUP_DIR -type f -name "*.zip" -mtime +2 -exec rm {} \;