#!/bin/bash
#SpikedSoftware.co.uk MySQL Backup Script

#Variables that we will need to connect to the Database
USER='root';
PASS='password';
PORT=3306;
HOST='localhost';

#List of all of the databases that we want to ignore...
DbToIgnore='information_schema mysql';

#Path to which the backups should e placed
BackupRoot='/var/backup/mysql/';

#Compression Level (9 = Best, 1 = Fast)
CompressionLevel=1

#Date Format for the FileNames
DateFormat='%d-%m-%Y_%H-%M-%S'

#Run through all of the Databases inside of the actual database
for a in $(mysql -u$USER -p$PASS -h$HOST -P$PORT -e "show databases" | grep -v "Database")
do
	#Use this variable to determine if we should ignore this databse
	IgnoreDb=0

	#Loop through all the databases, to determine if we should ignore or not
	for b in $DbToIgnore
	do
		#Does it match?
		if [ $a == $b ]
		then
			IgnoreDb=1;
			break;
		fi
	done	
	
	#Do we have to ignore the database or not?
	if [ $IgnoreDb == 0 ]		
	then
		#Check to see if the backup folder exists, if it doesn't, create it!
		if [ -d $BackupRoot ]
		then
			#Do Nothing...
			echo "Backup Folder ($BackupRoot) Exists";
		else
			mkdir -p $BackupRoot;
			echo "Backup Folder ($BackupRoot) has been created";
		fi
		
		#Now, let's see if we have a folder for this database...
		if [ -d "$BackupRoot$a" ]
		then
			echo "Sub-Backup Folder ($BackupRoot$a) Exists";
		else
			mkdir -p $BackupRoot$a;
			echo "Sub-Backup Folder ($BackupRoot$a) has been created";			
		fi
		
		#Now, we have our entire backup tree, so let's start backing up!
		echo -n "Executing backup of $a...";
		mysqldump -u$USER -p$PASS -h$HOST -P$PORT $a | gzip -$CompressionLevel \
			> "$BackupRoot$a/$(date +"$DateFormat").sql.gz";
		echo -n "Completed at ";
		echo $(date +"$DateFormat");
		
	else
		echo "Ignoring Db - $a";
	fi
done

echo "Database backup has been completed. Please drop by http://www.spikedsoftware.co.uk";