#!/bin/bash
################################################
#
# Backup all MySQL databases in separate files and compress those.
# The script will also create a folder with the current timestamp for each run
# @author: Per Lasse Baasch
# @Version: 2016-03-02
# NOTES:
# - MySQL and gzip must be installed on the system
# - Requires write permission in the destination folder
# - Excludes MySQL admin tables ('mysql',information_schema','performance_schema')
#
################################################
# MySQL User
USER='root'
# MySQL Password
PASSWORD='ssss'
# Backup Directory - WITH TAILING SLASH IF PATH OTHER THEN '.'!
OUTPUT="/root/mysql_backup"

##### Execute the backup
TIMESTAMP=`date +%Y%m%d_%H%M%S`;
mkdir $OUTPUT/$TIMESTAMP;
OUTPUTDEST=$OUTPUT/$TIMESTAMP;
echo "Starting MySQL Backup";
echo `date`;
databases=`mysql --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
for db in $databases; do
if [[ "$db" != "information_schema" ]] && [[ "$db" != _* ]] && [[ "$db" != "mysql" ]] && [[ "$db" != "performance_schema" ]] ; then
echo "Dumping database: $db"
mysqldump --force --opt --user=$USER --password=$PASSWORD --databases $db > $OUTPUTDEST/dbbackup-$TIMESTAMP-$db.sql
gzip $OUTPUTDEST/dbbackup-$TIMESTAMP-$db.sql
fi
done
echo "Finished MySQL Backup";
echo `date`