mysql_backup.bash

#!/usr/bin/env bash

set -e

DATE_FORMAT="%Y-%m-%d"
DATE_CREATION=$(date "+${DATE_FORMAT}")
DATE_REMOVAL=$(date -d "20 day ago" "+${DATE_FORMAT}")

DIRECTORY_BASE="/backup/mysql"
DIRECTORY_CREATION="${DIRECTORY_BASE}/${DATE_CREATION}"
DIRECTORY_REMOVAL="${DIRECTORY_BASE}/${DATE_REMOVAL}"

MYSQL_USER="root"
MYSQL_PASSWORD="root"
MYSQL_HOST="127.0.0.1"

echo "Creating '${DIRECTORY_CREATION}' backup directory"
mkdir -p "${DIRECTORY_CREATION}"
echo

mysql -u $MYSQL_USER -h $MYSQL_HOST -p$MYSQL_PASSWORD -Bse 'SHOW DATABASES' |
    egrep -v '^(information_schema|mysql|performance_schema)$' |
    while read database; do
        echo "- Dumping '${database}' database"
        filename="${DIRECTORY_CREATION}/${database}.${DATE_CREATION}.gz"
        mysqldump --single-transaction -u $MYSQL_USER -h ${MYSQL_HOST} -p${MYSQL_PASSWORD} ${database} |
            gzip -9 > "${filename}"
    done
echo

if [ -d "${DIRECTORY_REMOVAL}" ]; then
    echo "Removing backup from ${DATE_REMOVAL}"
    rm -rf "${DIRECTORY_REMOVAL}"
    echo
fi

echo "Finished!"