Automate your MySQL backups

backup-mysql.sh

#!/bin/bash

# -------------------------------------
# Databases backup with X days storage history
#
# User executing this script must have FULL permissions
# to mysql and mysqldump
#
# Use $HOME/.my.cnf to store MySQL auth
#
# This script is freely distributed under the GPL
# -------------------------------------

echo ""
echo "START: "$(date "+%Y-%m-%d %H:%M:%S")

# Configuration

BACKUPDIR="/var/backups/mysql" # Backups Storage Folder
DAYS=7                         # Store Backups X Days
DATE=$(date "+%Y%m%d")         # Backup File Prefix

# Script execution

DATABASES="$(mysql -Bse "SHOW DATABASES" | grep -vwE "(information_schema|performance_schema)")"

for db in $DATABASES; do
    dir="$BACKUPDIR/$db"
    file="$dir/$DATE.sql.gz"

    if [ ! -d "$dir" ]; then
        install -d "$dir"
    fi

    echo "Processing $db into $file"

    mysqldump \
        --single-transaction \
        --quick \
        --skip-events \
        --ignore-table=mysql.event \
        --routines \
        --triggers \
        --force \
        --add-drop-table \
        $db | gzip -9 > "$file"
done

find "$BACKUPDIR" -maxdepth 1 -mindepth 1 -type d | while read directory; do
    for file in $(ls -t "$directory" | tail -n +$DAYS); do
        echo -e "\n\nDeleted history backup $directory/$file"

        rm -f "$directory/$file"
    done
done

echo "END: "$(date "+%Y-%m-%d %H:%M:%S")

exit 0

Thank you. What is your case use the script? Cron, yeah?

Thank you. What is your case use the script? Cron, yeah?

Yes, it's executed as a cron job every day :)