#!/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

[client]
user        =   dbadmin
password    =   dbpassword
host        =   localhost

0 1 * * * /foo/bar/mysqlbackup.sh >/dev/null 2>&1