#!/bin/sh
# A simple bash script for backup all MySQL database.
# Christian Dita Putratama <svcadm[at]ditatompel[dot]com>

HOST="localhost"
USER="<YOUR_MYSQL_USER>"
PASSWORD="<YOUR_MYSQL_PASS>"
BACKUPBASEDIR='/path/to/backup/dir/'
THEDATE=`date +%d-%m-%Y-%H-%M`

# default database backup file to keep before deleted : 3 day.
# feel free to change `mtime` value so it can be fit with your need.
find $BACKUPBASEDIR -mtime +3 -type f -delete

databases=`mysql -h $HOST -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
        FILEBACKUPSQL="$THEDATE"-"$db".sql.gz
        echo "Dumping database: $db"
        mysqldump -h $HOST -u $USER -p$PASSWORD --databases $db | gzip > $BACKUPBASEDIR$FILEBACKUPSQL
    fi
done