#!/bin/bash

usage()
{
	cat << EOF

   usage: $0 options
   Author: Tommy Frössman aka. Styrbjorn 
   Web Site: http://styrbjorn.kladhest.se

          Changelog
          2011-06-06 First version of the script with the following functions.
                * Taking automatic backup of all databases on the server
                * Taking backup of a specific database only with the -d flag
                * List all available databases in the MySQL Server with the lsdb mode in the -m flag.
                * Restore database a specific dump with the -r flag along with a timestamp of the 
                  backup you want with the -t flag.
                * List all available backups for a specific database in the backup directory using 
                  -m restore -t lstimestamp along with a database supplied for the -r flag.
                * Optional -e flag for selecting a specific character encoding when doing a restore of a database.
		
		This script is built for automatic backup of MySQL databases.
		
		OPTIONS:
		-h      Show this message
		-m      Specificy what should be done (-m backup/restore/lsdb) (required)
		-d      Backup this database only (-d mydatabase) (optional)
		-r      Restore database (-r mydatabase) (required when using -m restore mode)
		-t      Show or choose timestamp to restore database from (-t lstimestamp/timestamp) (required when using -m restore mode)
		-e      Database encoding when restoring (eg. -t utf8) (optional)

EOF
}


MODE=
BACKUP_DB=
RESTORE_DB=
TAKEN_AT=
ENCODING=

ARG_FOUND=


BWLIMIT=250
TIMESTAMP=$(date +"%Y%m%d")

# PATH TO SAVE BACKUPS
BACKUPDIR="/foo/bar/MySQL_backup_backupdir"

# PATH TO SAVE BACKUP-LOGS
BACKUPLOGDIR="/foo/bar/MySQL_backup_logdir"

# HOSTNAME OF THE SERVER TO BACKUP
HOSTNAME="myfoohostbar"

# MYSQL MY.CNF FILE PATH
MYSQL_CNF="/foo/bar.my.cnf"

# AVAILBALE DATABASES
LSDB=`/usr/bin/mysql --defaults-file=${MYSQL_CNF} -Bse "show databases;" | awk '{print $1}' | grep -iv ^Database$|grep -v mysql|grep -v information_schema`

# email subject
SUBJECT="${TODAY} Mysql Dump Failed"

# Email To ?
EMAIL="foo@bar.com"

# Email text/message
EMAILMESSAGE="/tmp/emailmessage.txt"
echo "${TODAY} Mysql Dump Failed ${BACKUPLOGDIR}/${HOSTNAME}.log"> $EMAILMESSAGE

if [[ -z $LSDB ]]
then
	echo "Connection to the MySQL Server failed, aborting script!"
	echo "${TIMESTAMP} Connection to the MySQL Server failed, aborting script!"> $EMAILMESSAGE
	echo "${TIMESTAMP} Connection to the MySQL Server failed, aborting script!" >> ${BACKUPLOGDIR}/${HOSTNAME}.log
	/usr/bin/mail -s "${SUBJECT}" "${EMAIL}" < ${EMAILMESSAGE}
	exit 0
fi
	
	# Checking which arguments that is passed to the backupscript.
	while getopts ":h:m:d:r:t:e:" OPTION
	do
		ARG_FOUND=1
		
		case $OPTION in
		h)
			usage
			exit 1
			;;
		m)
			MODE=$OPTARG
			;;
		d)
			BACKUP_DB=$OPTARG
			;;
		r)
			RESTORE_DB=$OPTARG
			;;
		t)
			TAKEN_AT=$OPTARG
			;;
		e)
			ENCODING=$OPTARG
			;;
		?)
			usage
			exit 1
			;;
		esac
	done
	
	if [[ -z $ARG_FOUND ]]
	then
			usage
	fi
	
	
	if [[ $MODE == 'backup' ]]
	then
		# The -m flag has been supplied with backup mode.
		
			
		if [[ -n $BACKUP_DB ]]
		then
			# Checking if the specified database supplied with the -d flag exists in the MySQL Server.
			for db in $LSDB; do
				if [ "$db" = "$BACKUP_DB" ]
				then
						DB_EXISTS=1
				fi
			done
	
			if [[ $DB_EXISTS == 1 ]]
			then
				# The specified database supplied with the -d flag was found in the MySQL Server, continuing with backup.
				# Specific database choosen so only that database will be backed up.
				echo "Only taking backup of database ${BACKUP_DB}"
				echo ${BACKUPDIR}/${TIMESTAMP}/${BACKUP_DB}.sql
				mkdir -p ${BACKUPDIR}/${TIMESTAMP}/	
				mysqldump --defaults-file=${MYSQL_CNF} ${BACKUP_DB} > ${BACKUPDIR}/${TIMESTAMP}/${BACKUP_DB}.sql
				
				if [ "$?" = 0 ];
				then
					echo "${TIMESTAMP} Mysql Dump for database ${BACKUP_DB} was Successful" >> ${BACKUPLOGDIR}/${HOSTNAME}.log
				else
					echo "${TIMESTAMP} Mysql Dump for database ${BACKUP_DB}"> $EMAILMESSAGE
					echo "${TIMESTAMP} Mysql Dump for database ${BACKUP_DB} Failed" >> ${BACKUPLOGDIR}/${HOSTNAME}.log
					/usr/bin/mail -s "${SUBJECT}" "${EMAIL}" < ${EMAILMESSAGE}
				fi
			else
				# The specified database supplied with the -d flag was not found in the MySQL Server, no backup will be made.
				echo "Backup of database failed ${BACKUP_DB} !"
				echo "The specified database could not be found on the server."
				echo "${TIMESTAMP} Mysql Dump for database ${BACKUP_DB} Failed - The specified database could not be found on the server."> $EMAILMESSAGE
				echo "${TIMESTAMP} Mysql Dump for database ${BACKUP_DB} Failed - The specified database could not be found on the server." >> ${BACKUPLOGDIR}/${HOSTNAME}.log
				/usr/bin/mail -s "${SUBJECT}" "${EMAIL}" < ${EMAILMESSAGE}
		fi
		else
			# No database has been specified in the -d argument so everything will be backed up exept the internal MySQL databases.
			echo "Taking backup of all databases on the server"
			for db in $LSDB; do
				echo ${BACKUPDIR}/${TIMESTAMP}/${db}.sql
				mkdir -p ${BACKUPDIR}/${TIMESTAMP}/	
				mysqldump --defaults-file=${MYSQL_CNF} ${db} > ${BACKUPDIR}/${TIMESTAMP}/${db}.sql
	
				if [ "$?" = 0 ];
				then
					echo "${TIMESTAMP} Mysql Dump for database ${db} was Successful" >> ${BACKUPLOGDIR}/${HOSTNAME}.log
				else
					echo "${TIMESTAMP} Mysql Dump for database ${db} Failed"> $EMAILMESSAGE
					echo "${TIMESTAMP} Mysql Dump for database ${db} Failed" >> ${BACKUPLOGDIR}/${HOSTNAME}.log
					/usr/bin/mail -s "${SUBJECT}" "${EMAIL}" < ${EMAILMESSAGE}
				fi
	
			done
		fi	
	fi
	
	if [[ $MODE == 'restore' ]]
	then
		# The -m flag has been supplied with restore mode.
		
		if [[ -z $RESTORE_DB ]]
		then
			# No database was choosen with the -r argument when restore mode was enabled, no restore will be made.
			echo "database to backup has not been specified"
		else
			if [[ -z $TAKEN_AT ]]
			then
				# A timestamp has not been supplied with the -t argument when trying to restore the database, no restore will be made.
				echo "Timestamp must be specified"
			else
				# Checking if the specified database supplied with the -r flag exists in the MySQL Server.
				DB_EXISTS=0
				for db in $LSDB; do
					if [ "$db" = "$RESTORE_DB" ]
						then
							DB_EXISTS=1
						fi
				done

				if [[ $DB_EXISTS == 1 ]]
				then
					# The database specified in the -d flag has been found in MySQL, continuing with database restore.
					if [[ $TAKEN_AT == "lstimestamp" ]]
					then
						TIMESTAMP_CHECK=0
							
						# Listing all available backup timestamps for the database specified with the -r flag using the lstimesstamp mode.
						for directory in ${BACKUPDIR}/*; do
							if [[ -f "${directory}/${RESTORE_DB}.sql" ]]
							then
								TIMESTAMP_CHECK=1
								echo "${directory: -8}"
							fi
						done
						
						if [[ ${TIMESTAMP_CHECK} == 0 ]]
						then
							# The database was found in MySQL but no timestamps could be found in the backup directory, database has probably never been backed up.
							echo "Could not find any timestamps for database ${RESTORE_DB}!"
							echo "This Database has probably never been backed up before."
						fi	
						
					else
						if [[ ! -f "${BACKUPDIR}/${TAKEN_AT}/${RESTORE_DB}.sql" ]]
						then
								# The database was found in the MySQL Server but the timestamp supplied with the -t was invalid, no restore will be made.
								echo "The backup of database ${RESTORE_DB} with timestamp ${TAKEN_AT} could not be found!"
						else			
							if [[ -n $ENCODING ]]
							then
								# We have supplied the -e argument with a specific encoding so we will restore the database with --default-character-set options.	
								
								# Dropping the database that is going to be restored.
								echo "* Dropping database"
								mysqladmin --defaults-file=${MYSQL_CNF} --force drop ${RESTORE_DB};
								
								# Creating a new empty database to restore the backup to.
								echo "* Creating empty database for restore"			
								mysqladmin --defaults-file=${MYSQL_CNF} --force create ${RESTORE_DB};
								
								# Restoring the database backup to the new database.
								echo "Restoring database ${RESTORE_DB} with timestamp ${TAKEN_AT} using ${ENCODING} encoding"
								mysql --defaults-file=${MYSQL_CNF} --default-character-set=$ENCODING ${RESTORE_DB} < ${BACKUPDIR}/${TAKEN_AT}/${RESTORE_DB}.sql
							
							else
								# No encoding has been supplied with the -e flag we are using the default MySQL encoding.
								
								# Dropping the database that is going to be restored.
								echo "* Dropping database"
								mysqladmin --defaults-file=${MYSQL_CNF} --force drop ${RESTORE_DB};
								
								# Creating a new empty database to restore the backup to.
								echo "* Creating empty database for restore"			
								mysqladmin --defaults-file=${MYSQL_CNF} --force create ${RESTORE_DB};
								
								# Restoring the database backup to the new database.
								echo "Restoring database ${RESTORE_DB} with timestamp ${TAKEN_AT} using standard encoding"
								mysql --defaults-file=${MYSQL_CNF} ${RESTORE_DB} < ${BACKUPDIR}/${TAKEN_AT}/${RESTORE_DB}.sql
							fi
						fi
					fi
				else
						# If the restore process fails this generic error message will be displayed.
						echo "The database $RESTORE_DB cannot be restored on this database server!"
						echo "There can be several reasons why the restore process failed"
						echo "* You are trying to restore an internal service database for MySQL server."
						echo "* You are trying to restore a database that does not exist on your system, if you want to migrate"
						echo "  the database to this server please create an empty database first then try to restore it again."
						echo "  To create the database in MySQL just type \"mysqladmin -u root -p create $RESTORE_DB\""
				fi
			fi
		fi	
		
	fi
	
	
	if [[ $MODE == 'lsdb' ]]
		# The -m flag has been supplied with lsdb mode.
	then
		echo "Available MySQL databases on host ${HOSTNAME}"
		echo "****************************************************"
		# Listing all available databases on the MySQL server.
		echo $LSDB|tr ' ' '\n'
	fi

exit 0