Automate MySQL database backups to run every Saturday

backup_mysql.sh

#!/bin/bash

# 获取脚本所在的目录
SCRIPT_DIR=$(dirname "$(readlink -f "$0")")

# 加载 .env 文件
source $SCRIPT_DIR/.env

# 检查是否加载成功
if [ -z "$DB_NAME" ]; then
    echo "Missing required environment variables."
    exit 1
fi

# 获取当前日期和时间
DATE=$(date +%Y%m%d%H%M%S)
BACKUP_FILE="$SCRIPT_DIR/${DB_NAME}_backup_$DATE.sql"
COMPRESSED_BACKUP_FILE="$BACKUP_FILE.gz"

# 进行备份
mysqldump --defaults-extra-file=$SCRIPT_DIR/.my.cnf --no-tablespaces $DB_NAME > $BACKUP_FILE

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "Backup successful: $BACKUP_FILE"

    # 压缩备份文件
    gzip $BACKUP_FILE

    # 检查压缩是否成功
    if [ $? -eq 0 ]; then
        echo "Compression successfule: $COMPRESSED_BACKUP_FILE"
    else
        echo "Compression failed"
    fi
else
    echo "Backup failed"
fi