*Nix4all

Got some good news there sport fans (actually MySQL fans, but who really reads this anyway), the script got updated for the 3rd and probably final time (lie).

It can perform backups for earlier versions of Mysql (5.*, 4.*) using the mysqldump utility, it will auto-detect the backup type it performed and select the appropriate way of restore "on the fly". Of course you can use mysqldump on version 6.0 but why would you want to do that! :)
Another option was added in the config_file so be sure to check it out, when upgrading. (BTW upgrading using the install will replace your config-file)

Mysql Server 6.0 will be a great database when it reaches GA level, unlike it's earlier version (5.1, 5.0, 4.*) Mysql 6.0 has a lot of new features.
The one that stands out IMO is the possibility of performing a hot database backup ( AKA online).

You can simply connect to the server with the mysql client and: BACKUP DATABASE database TO 'path/to/backup/backup.bkp'; and that's it, you just backed up your database.
When restoring it's enough to issue this command RESTORE from 'path/to/backup/backup.bkp'; and you're done.

The mysqldump and mysqlhotcopy utilities are also available in the 6.0 (alpha version at least) but as you probably already know they perform read-locks on the tables/databases while backing them up.

Of course the backup solution provided in 6.0 disables some features during restore procedure but the backup can be performed without affecting the clients using the database.

Taken this in consideration I decided to write a backup script with a couple of interesting features and a configuration file.

#!/bin/bash

###############################
# MB ( Mysql Backup) - Online #
#    http://nix4all.com       #
###############################

# If config file wasn't specified as a command line argument default will be used

if [ -z $2 ];then
        CONFIG="/etc/mb.conf";
else
        if [ -e $2 ];then
                CONFIG=$2;
        else
                echo "Error: 3rd command line argument must be a config file";
        fi
fi

#Catch SIGINT

cleanup()
{
        if [ "$backup_file"  != "" ]; then
        echo -e "\033[31mBackup was canceled, Removing backup file....\033[0m";
        rm -rf $backup_file.bkp;
        exit 2;
        fi
        echo -en "\t*** Restore cannot be canceled ***\n";
        return;
}

control_c()
{

        echo -en "*** SIGINT Caught, somebody is getting wild on the keyboard ***\n"
        cleanup
}

trap control_c SIGINT

# Check for config file existance

if [ -e "$CONFIG" ];then
        echo -e " \033[1m*********** MB ( Mysql Backup) ************\033[0m";
else
        echo "Error: Config file does not exist";
        exit 1;
fi

# Function to read config-file

function readconf() {

    match=0

    while read line; do
        [[ ${line:0:1} == "#" ]] && continue

        [[ -z "$line" ]] && continue

        if [ $match == 0 ]; then

            if [[ ${line:$((${#line}-1))} == "{" ]]; then

                group=${line:0:$((${#line}-1))}

                group=${group// /}

                if [[ "$group" == "$1" ]]; then
                    match=1
                    continue
                fi

                continue
            fi

        elif [[ ${line:0} == "}" && $match == 1 ]]; then
            break

        else
            eval $line
        fi

    done < "$CONFIG"
}

# Check for bzip2

check_bzip2(){

        which bzip2 >> /dev/null 2>&1
        if [ "$?" != "0" ]
        then
                echo "bzip2 is not available, compression disabled";
                compression="no";
        fi
}

# Check configuration parameters

check_config(){

        if [ "$DB" == "" ];then echo "Error: Database was not specified, please check config-file";exit 1; fi
        if [ "$backup_dir" == "" ];then echo "Error: Backup dir was not specified, please check config-file";exit 1; fi
        if [ "$user" == "" ];then echo "Error: Database User was not specified, please check config-file"; exit 1; fi
        if [ "$pass" == "" ];then echo "Error: Database Password was not specified, please check config-file"; exit 1 ;fi
        if [ "$log_file" == "" ];then echo "Error: Log File was not specified, please check config-file";exit 1; fi
        if [ "$host" == "" ];then echo "Error: Host was not specifies, please check config-file";exit 1;fi
        if [ "$port" == "" ];then port=3306; echo "Port wasn't specified, using default: 3306";fi
        if [[ "$backup_type" != "dump" && "$backup_type" != "online" ]];then echo "Error: Backup type was not specified or incorrect value"; exit 1;fi
}

# Actual database backup function

backup_database(){
        starti=`date +%s`;
        if [ "$backup_type" == "dump" ];then
                echo "mysqldump -u$user -p$pass -h $host -P $port $DB > $backup_file-dump.bkp";
                backup_file=$backup_file-dump;
                mysqldump -u$user -p$pass -h $host -P $port $DB > $backup_file.bkp 2> $log_file;
        fi
        if [ $? != "0" ]; then
                echo "Please check log: $log_file";
                echo -e "\033[31mThere was an error\033[0m";
                exit 1;
        fi

        if [ "$backup_type" == "online" ];then
                echo "mysql -u$user -p$pass -h $host -P $port -Nse \"BACKUP DATABASE $DB TO \"$backup_file.bkp\"\"";
                mysql -u$user -p$pass -h $host -P $port -Nse "BACKUP DATABASE $DB TO \"$backup_file.bkp\"" > $log_file 2>&1;
        fi

        if [ $? != "0" ]; then
                echo "Please check log: $log_file";
                echo -e "\033[31mThere was an error\033[0m";
                exit 1;
        fi
    startf=`date +%s`;
# Check for compression status

        if [ "$compression" == "yes" ]; then
                echo "Backup file: $backup_file.bkp.bz2";
                echo "Compressing file...";
                bzip2 $backup_file.bkp
        else
                echo "Backup file: $backup_file.bkp";
        fi

        echo "log file: $log_file";
        echo "Mysql Backup ID: `cat $log_file`";
    echo "Backup time was: $(($startf - $starti)) sec";
}

# Restore database function

restore_database(){

        if [ "$restore_file" == "" ];then
                echo "Restore File was not specified";
                echo "Using latest backup according to file timestamp";
                echo "Are you sure ? [yes]/[no]";read ans1;
                if [ "$ans1" != yes ];then
                        echo "Restore Aborted"
                        exit 0;
                fi
                restore_file=$backup_dir/`ls -tl $backup_dir| grep bkp |grep $DB| head -n 1 | awk -F " " '{ print $8 }'`;
                if [ "${restore_file:(-1)}" == "/" ];then
                        echo "Error: There is no \"$DB\" database backup in directory $backup_dir/";
                        exit 1;
                fi
        fi

        if [ "${restore_file:(-1)}" == "/" ];then
                echo "Error: There is no \"$DB\" database backup in directory $backup_dir/";
                        exit 1;
                fi

# Decompress before restore

        if [ "${restore_file:(-4)}" == ".bz2" ];then
                echo "Restore file was compressed, Decompressing...";
                bzip2 -d $restore_file >> /dev/null 2>&1
                if [ "$?" != "0" ];then
                        echo "bzip2 is not available, check \$PATH if bzip2 is installed";
                        echo "For this backup to be restored correctly please install bzip2";
                        exit 1;
                fi
                restore_file=${restore_file/.bz2/}
        fi

        echo "Using database backup: $restore_file";

        echo $restore_file | grep dump >> /dev/null 2>&1;
        if [ $? != "0"  ];then
                echo "mysql -u$user -p$pass -h $host -P $port -Nse \"RESTORE FROM \"$restore_file\"\"";
                mysql -u$user -p$pass -h $host -P $port -Nse "RESTORE FROM \"$restore_file\"" > $log_file 2>&1;
        else
                echo "mysql -u$user -p$pass -h $host -P $port $DB < $restore_file";
                mysql -u$user -p$pass -h $host -P $port $DB < $restore_file > $log_file 2>&1;
        fi

        if [ $? != "0" ]; then
                echo "Please check log: $log_file";
        echo -e "\033[31mThere was an error\033[0m";
                exit 1;
        fi

        echo "log file: $log_file";

        echo "Mysql Restore ID: `cat $log_file`";
}

if [ "$1" == "backup"  ];then
        echo "Database backup in progress...";
        readconf "settings";
        backup_file=$backup_dir/`date +%F_%R`_$DB-$RANDOM;
        check_bzip2;
        check_config;
        backup_database;
        echo -e "\033[1m *********** DONE ***********\033[0m";
    echo " ";
        exit 0;
fi

if [ "$1" == "restore" ]; then
        echo "This will restore your database according to your settings";
        echo "Continue [yes]/[no]";read ans;
        if [ "$ans" != "yes" ];then
                echo "Database restore canceled";
                exit 0;
        fi
        echo "Database restore in progress...";
        readconf "settings";
        check_config;
        restore_database;

    echo -e "\033[1m *********** DONE ***********\033[0m";
    echo " ";
        exit 0;
fi

if [ "$1" == "" ] ; then
        echo "Default config file: /etc/mb.conf";
        echo "                     mandatory|[optional]";
        echo "Usage $0 {backup|restore|[config_file]}";
        exit 0;
else
        echo "Error: Unknown option: $1";
        echo "Usage $0 {backup|restore}";
        exit 1;
fi

And the sample configuration file:

#General Settings
settings {

# Hostname or IP address of the mysql server
host=localhost;

# Local directory where backup files will be placed, do not include the final "/"
backup_dir=/var/backup;

# user name to connect to mysql database, it should have read/write permissions
user=username;

# the above username's password
pass=password;

# database name
DB=database;

# You must specify : online (for mysql server ver 6.0+, better choice) or dump (for mysql server ver 5.1-)
# Only use online for version 6.0+ lying will not do you any good :)
backup_type=dump;

# File where to log all the information
log_file=/var/log/backup-log_`date +%F_%R`;

# This option is not mandatory, if not specified latest backup will be used for restore
# For now it is set to get the latest backup
restore_file=$backup_dir/`ls -tl $backup_dir| grep bkp |grep $DB| head -n 1 | awk -F " " '{ print $8 }'`;

# Mysql Server Port, not mandatory, if not specified default will be used
port=3306;

# Enable / Disable Compression of backup files [yes] or [no], only bzip2 is available,so check if it's in $PATH
compression=yes;

###########
# NOTES : #
###########

# If you want to use compression in the client/server protocol, open script file,
# add option -C as an agument on the mysql command in both backup_database and restore_database functions

# Encryption is a feature of the mysql client and it's better to specify ssl certificates in my.conf and not as command line arguments
# Enable it for the user performing the backup if you think it's necessary

}

It is really simple to use you just fill in the sample config with your specifics, edit the first line in the actual script to point to the config file and run it.

The script does not need to be on the same machine as the mysql server because it uses the mysql-client to perform backup/restore, of course the mysql-client must be installed on the server performing & storing the backup.

If you want to run the backup from the mysql-server itself by mounting other filesystems from your backup-server you could backup directly to it, just set the backup_dir accordingly.

The script can backup and restore the database, if you don't specify the backup-file from which the restore should be performed it will try to use the latest backup.

Also created an installer for the utility (see attachments section), it will download both the config file and the actual script, it will place the shell executable in the /usr/bin/ and the config file in /etc/.

Then just simply run it (mb.sh) after modifying /etc/mb.conf.

That's about it.
FeedBack is welcomed.

Update:
Added compression for HDD space sake.
(using only bzip2)
As requested backup duration is now displayed if backup was successful

Update II:
Scripted got pimped with "on SIGINT" behaviour.
CTRL+C will not stop the restore (so we won't end up with an inconsistent database), but it will stop and delete the backup_file during backup procedures.

Update III:
You can now perform mysqldump backups so the script can be used with earlier versions of MySQL (5.*, 4.*).
!New option in config file!

Update IV:
Config file can be specified as a command line argument, option is not mandatory (/etc/mb.conf will be used instead)
I thought this would be good to have when making backups for different servers/databases.

Known BUGs:
- restore will be canceled if you send a SIGINT because I can't find a way to to do a nohup on the mysql restore command.
It won't stop the script but it will stop the mysql client from finishing the restore.
(so be careful)

TODO:
- Add mysqlhotcopy as a backup option type
(seems weird since I'll be wrighting a wrapper for a perl script :^) )