mysql-backup-restore.sh

#!/bin/bash

# ================================
# 🛡️  MySQL Backup & Restore Tool 🛡️
# -------------------------------
# - Configurable via CLI or file 📄
# - Upload/download to/from S3 ☁️
# - Super cool messages 🚀
# ================================

# ========== VARIABLES ==========

# 🎨 Colors for pretty printing logs
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[0;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

# 🛠️ Default configuration
CONFIG_FILE="$HOME/.mysql-backup.cnf"
DB_HOST=""
DB_USERNAME=""
DB_PASSWORD=""
DATABASES=()
BACKUP_PREFIX="s3://your-default-bucket/db-backup"
RESTORE=false
RESTORE_DATABASE=""
S3_TOOL=""
TODAY=$(date "+%Y_%m_%d")
ANY_FAILURE=false
RETAIN_DAYS=30
DEBUG=false

# ========== FUNCTIONS ==========

# 📣 Logging functions with colors
info() { echo -e "${BLUE}$1${NC}"; }
success() { echo -e "${GREEN}$1${NC}"; }
error() { echo -e "${RED}$1${NC}"; }
debug() { if [ "$DEBUG" = true ]; then echo -e "${YELLOW}[DEBUG] $1${NC}"; fi }

# 📝 Create a default config file if missing
create_config_file() {
    cat <<EOL > "$CONFIG_FILE"
[credentials]
host=localhost
username=root
password=

[databases]
db1

[backup]
prefix=s3://your-default-bucket/db-backup
EOL
    success "⚙️ Configuration file created at $CONFIG_FILE ✅"
}

# 🔍 Parse the configuration file
parse_config() {
    debug "Parsing configuration from $CONFIG_FILE"
    local section=""
    while IFS= read -r line || [ -n "$line" ]; do
        line="$(echo "$line" | xargs)" # ✂️ Trim spaces

        # ➡️ Skip comments and empty lines
        [[ -z "$line" || "$line" == \#* ]] && continue

        # 🔥 Detect section headers like [credentials]
        if [[ "$line" =~ ^\[(.*)\]$ ]]; then
            section="${BASH_REMATCH[1]}"
            continue
        fi

        # 🛠️ Handle lines based on current section
        case "$section" in
            credentials)
                key="${line%%=*}"
                value="${line#*=}"
                key="$(echo "$key" | xargs)"
                value="$(echo "$value" | xargs)"
                case "$key" in
                    host) DB_HOST="$value" ;;
                    username) DB_USERNAME="$value" ;;
                    password) DB_PASSWORD="$value" ;;
                esac
                ;;
            backup)
                key="${line%%=*}"
                value="${line#*=}"
                key="$(echo "$key" | xargs)"
                value="$(echo "$value" | xargs)"
                if [[ "$key" == "prefix" ]]; then
                    BACKUP_PREFIX="$value"
                fi
                ;;
            databases)
                if [[ -n "$line" ]]; then
                    DATABASES+=("$line")
                fi
                ;;
        esac
    done < "$CONFIG_FILE"
}

# 📄 Create a temp MySQL config file for secure authentication
create_temp_mysql_config() {
    TEMP_MYSQL_CONFIG=$(mktemp)
    cat <<EOL > "$TEMP_MYSQL_CONFIG"
[client]
user=$DB_USERNAME
password=$DB_PASSWORD
host=$DB_HOST
EOL
    debug "Created temporary MySQL config file: $TEMP_MYSQL_CONFIG"
}

# ⚙️ Choose available S3 tool (s3cmd or awscli)
select_s3_tool() {
    if command -v s3cmd &> /dev/null; then
        S3_TOOL="s3cmd"
    elif command -v aws &> /dev/null; then
        S3_TOOL="aws"
    else
        error "🚨 Neither s3cmd nor awscli found! Please install one to proceed ❌"
        exit 1
    fi
    success "🛠️ Using ${S3_TOOL} for S3 operations ✅"
}

# ☁️ Upload a file to S3
upload_to_s3() {
    FILE="$1"
    DEST="$2"
    debug "Uploading $FILE to $DEST"
    if [ "$S3_TOOL" == "s3cmd" ]; then
        s3cmd put "$FILE" "$DEST"
    else
        aws s3 cp "$FILE" "$DEST"
    fi
}

# ☁️ Download the latest backup from S3
download_from_s3() {
    DB_NAME="$1"
    BACKUP_PATH="$2"
    debug "Downloading backup for $DB_NAME from $BACKUP_PATH"
    if [ "$S3_TOOL" == "s3cmd" ]; then
        LATEST_FILE=$(s3cmd ls "$BACKUP_PATH" | grep "/${DB_NAME}-" | sort | tail -n 1 | awk '{print $4}')
        [ -n "$LATEST_FILE" ] && s3cmd get "$LATEST_FILE" .
    else
        LATEST_FILE=$(aws s3 ls "$BACKUP_PATH" | grep "${DB_NAME}-" | sort | tail -n 1 | awk '{print $4}')
        [ -n "$LATEST_FILE" ] && aws s3 cp "s3://${BACKUP_PATH}${LATEST_FILE}" .
    fi
    echo "$LATEST_FILE"
}

# 📤 Export database permissions
export_db_permissions() {
    DB_NAME="$1"
    debug "Exporting permissions for database $DB_NAME"
    mysql --defaults-extra-file="$TEMP_MYSQL_CONFIG" -B -N -e "SHOW GRANTS FOR CURRENT_USER()" "$DB_NAME" | sed 's/$/;/g' > "${DB_NAME}-permissions.sql"
}

# 📥 Restore database permissions
restore_db_permissions() {
    DB_NAME="$1"
    debug "Restoring permissions for database $DB_NAME"
    mysql --defaults-extra-file="$TEMP_MYSQL_CONFIG" < "${DB_NAME}-permissions.sql"
}

# 🧹 Clean up old backups older than retention policy
clean_old_backups() {
    info "🧹 Cleaning up backups older than $RETAIN_DAYS days..."
    debug "Running cleanup with $S3_TOOL"
    if [ "$S3_TOOL" == "s3cmd" ]; then
        s3cmd del --recursive --days-older-than="$RETAIN_DAYS" "$BACKUP_PREFIX"
    else
        aws s3 rm "$BACKUP_PREFIX" --recursive --expire "$RETAIN_DAYS"
    fi
    success "🚮 Old backups cleaned up!"
}

# ========== SCRIPT START ==========

info "🚀 Launching the Ultimate MySQL Backup & Restore Tool! 🚀"

# 📄 Check if config exists, else create one
if [ ! -f "$CONFIG_FILE" ]; then
    create_config_file
    error "✍️ Please edit the configuration file with your DB credentials before rerunning the script ❗"
    exit 1
fi

# 🏗️ Parse CLI arguments
while [[ "$1" != "" ]]; do
    case $1 in
        --host )       shift; DB_HOST="$1" ;;
        --username )   shift; DB_USERNAME="$1" ;;
        --password )   shift; DB_PASSWORD="$1" ;;
        --prefix )     shift; BACKUP_PREFIX="$1" ;;
        --database )   shift; DATABASES=("$1") ;;
        --restore )    RESTORE=true ;;
        --restore-db ) shift; RESTORE_DATABASE="$1" ;;
        --retain-days ) shift; RETAIN_DAYS="$1" ;;
        --debug )      DEBUG=true ;;
        * )            error "🚫 Unknown option: $1"; exit 1 ;;
    esac
    shift
done

# 🧩 Load config if anything missing
if [ -z "$DB_HOST" ] || [ -z "$DB_USERNAME" ] || [ -z "$DB_PASSWORD" ] || [ "${#DATABASES[@]}" -eq 0 ] || [ "$BACKUP_PREFIX" == "s3://your-default-bucket/db-backup" ]; then
    parse_config
fi

debug "Backup upload path: ${BACKUP_PREFIX}/${TODAY}/"

# ⚙️ Prepare environment
select_s3_tool
create_temp_mysql_config
BACKUP_UPLOAD_PATH="${BACKUP_PREFIX}/${TODAY}/"

# ========== MAIN LOGIC ==========

if [ "$RESTORE" = true ]; then
    # 🛠️ RESTORE MODE
    IFS=',' read -ra DB_ARRAY <<< "$RESTORE_DATABASE"

    for DB_NAME in "${DB_ARRAY[@]}"; do
        DB_NAME=$(echo "$DB_NAME" | xargs)
        [ -z "$DB_NAME" ] && continue

        info "🛢️ Starting restoration of database: ${DB_NAME}..."
        FILE_DOWNLOADED=$(download_from_s3 "$DB_NAME" "$BACKUP_UPLOAD_PATH")

        if [ -z "$FILE_DOWNLOADED" ]; then
            error "😞 No backup found for database ${DB_NAME}! Skipping..."
            continue
        fi

        BACKUP_FILE_NAME=$(basename "$FILE_DOWNLOADED")
        export_db_permissions "$DB_NAME"

        info "🧹 Dropping and recreating database ${DB_NAME}..."
        mysql --defaults-extra-file="$TEMP_MYSQL_CONFIG" -e "DROP DATABASE IF EXISTS \`$DB_NAME\`;"
        mysql --defaults-extra-file="$TEMP_MYSQL_CONFIG" -e "CREATE DATABASE \`$DB_NAME\`;"

        info "📥 Restoring backup data for ${DB_NAME}..."
        gunzip < "$BACKUP_FILE_NAME" | mysql --defaults-extra-file="$TEMP_MYSQL_CONFIG" "$DB_NAME"

        if [ $? -ne 0 ]; then
            error "🚨 Restore failed for ${DB_NAME}! Check logs ❌"
            ANY_FAILURE=true
            continue
        fi

        restore_db_permissions "$DB_NAME"
        success "🎉 Restoration completed successfully for ${DB_NAME}!"

        rm -f "$BACKUP_FILE_NAME" "${DB_NAME}-permissions.sql"
    done
else
    # 💾 BACKUP MODE
    for DB_DATABASE in "${DATABASES[@]}"; do
        BACKUP_TIME=$(date "+%d_%m_%Y_%H_%M_%S")
        BACKUP_FILE_NAME="${DB_DATABASE}-${BACKUP_TIME}.sql.gz"

        debug "Backing up database: $DB_DATABASE"
        info "🛢️ Backing up database: $DB_DATABASE..."

        mysqldump --defaults-extra-file="$TEMP_MYSQL_CONFIG" --single-transaction --set-gtid-purged=OFF --skip-tz-utc "$DB_DATABASE" | gzip > "$BACKUP_FILE_NAME"
        
        if [ $? -ne 0 ]; then
            error "🔥 Backup failed for ${DB_DATABASE}! 🚫"
            ANY_FAILURE=true
            continue
        fi

        success "✅ Backup completed: ${BACKUP_FILE_NAME}"

        info "📤 Uploading ${BACKUP_FILE_NAME} to S3..."
        upload_to_s3 "$BACKUP_FILE_NAME" "$BACKUP_UPLOAD_PATH"

        if [ $? -ne 0 ]; then
            error "🚨 Upload failed for ${DB_DATABASE}! ❗"
            ANY_FAILURE=true
            continue
        fi

        success "🚀 Upload successful for ${DB_DATABASE}!"
        rm -f "$BACKUP_FILE_NAME"
    done

    clean_old_backups
fi

# 🧹 Cleanup temp files
rm -f "$TEMP_MYSQL_CONFIG"

# 📢 Final message
if [ "$ANY_FAILURE" = true ]; then
    error "🚨 Some operations failed! Please check the logs above carefully ❌"
else
    success "🏁 All tasks finished successfully! 🎯 Backup/Restore Complete!"
fi