#!/bin/bash

# Do non-blocking MySQL DB backups. For InnoDB, the backup user does not need the LOCK_TABLES privilege.

excludeDB="'information_schema', 'performance_schema'" # Which DBs NOT to backup

# User needs the SELECT privilege, optionally EVENTS to avoid a warning
# introduced in MySQL 5.5 when dumping the DB "mysql" (Warning: Skipping the data of table mysql.event.)
# CREATE USER 'backup'@'localhost' IDENTIFIED BY 'PASSWORD';
# GRANT LOCK TABLES, SELECT, EVENT ON *.* TO 'backup'@'localhost' IDENTIFIED BY 'PASSWORD';

user="backup"
pass="PASSWORD"             # Well..
path="/backups/mysql"       # Where to write the backups?
keep="30"                   # How long to keep old backups around, in days.
optParam="--events"         # Optional parameters for mysqldump. Hint: Specify --events if the user has the privilege

# Get database names, exclude those defined earlier.
databases=`mysql -u $user -p$pass -B -N -e "select schema_name from information_schema.schemata where schema_name not in ($excludeDB)"`

# Backup every database to a separate file.
for curDB in $databases; do
        mysqldump --single-transaction --opt $optParam --databases "$curDB" -u$user -p$pass \
                | gzip -3 --rsyncable > "$path/$curDB-$(date +%FT%H%M%S).sql.gz"
done

if [ ${PIPESTATUS[0]} = "0" ];then
    :
else
    echo "Error, could not backup MySQL databases"
    exit 1
fi

# Delete old backups
find $path -mtime +$keep -exec rm {} \;