Backup MySQL (Ubuntu)

This backup script is a more elaborate version of the one to found here, this backup script...

• Backs up all databases on a server
  • You can exclude databases if required
• Only backs up databases that have changed since the last backup
• FTP's backup's off to server (uses lftp, install with apt-get install lftp)
  • Can use FTPS for secure backup transfer

Set-up

1. Create the required folders using...
   • mkdir backup
   • mkdir backup/mysql
2. Create the file below (editing as required) as /backup/mysql.sh
3. Make the file executable
   • chmod +x /backup/mysql.sh
4. Perform a test run of the backup
   • A 1044: Access denied for user 'root'@'localhost' to database 'information_schema' error is normal and is due to a longstanding MySQL bug
5. Schedule the script to run with crontab
   • crontab -e
   • 30 1 * * * /bin/bash /backup/mysql.sh

To create a MySQL user with limited privileges to run the backup with use...

GRANT SELECT,LOCK TABLES ON *.* TO backup@'localhost' IDENTIFIED BY 'password';

The Script

#!/bin/bash

# MySQL Database backup script
#
# Simon Strutt (Apr 2012)
#
# See http://www.sandfordit.com/vwiki/index.php/Backup_MySQL_(Ubuntu)

### MySQL Server Login and local backup info ###
MUSER="backup"                          # MySQL user
MPASS="password"                        # MySQL password
MHOST="localhost"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
BAK="/backup/mysql/data"                # Where backups will go
STAMPS="/backup/mysql/stamps"           # Where last backup timestamps go
LOG="/backup/mysql.log"
GZIP="$(which gzip)"
NOW=$(date -u +%Y%m%d)
DBS2EXCLUDE="Syslog"                    # Databases to exclude (; seperated, eg db1;db2)

## FTP info
FTPDIR="/backup/server1/db"             # Folder path on FTP server
FTPUSER="backup-user"                   # FTP username
FTPPASS="backup-pass"                   # FTP password
FTPSERVER="ftp.domain.com"              # FTP server name/IP
FTPS=0                                  # Set to 1 enable FTP over SSL/TLS

## Functions
Logger()
{
        echo `date "+%a %d/%m/%y %H:%M:%S"`: $1 >> $LOG
}

## Main Script
Logger "Started backup script..."

# Create folders that don't exist
[ ! -d $BAK ] && mkdir -p $BAK
[ ! -d $STAMPS ] && mkdir -p $STAMPS
[ ! -d $BAK/prev ] && mkdir -p $BAK/prev

# Move previous backups to tmp dir
#mv $BAK/*.gz $BAK/tmp

DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do
    # Check db shouldn't be excluded from backup
    if echo "${DBS2EXCLUDE}" | grep -q ${db}
    then
        Logger "$db backup excluded, skipping"
    else
        # Is backup required?
        DBUPDATE=`mysql -u root -h localhost -psyndrome -Bse "SELECT max(update_time) FROM information_schema.tables WHERE table_schema='${db}'"`
        DBUPDATEU=`date --date "${DBUPDATE}" +%s`
        #Logger "$db last update was $DBUPDATE"
        if [ -f ${STAMPS}/${db} ]
        then
            BAKDATE=`more ${STAMPS}/${db}`
            BAKDATEU=`date --date "${BAKDATE}" +%s`
            #Logger "$db last backup was $BAKDATE"
        else
            BAKDATEU=0
            Logger "$db no last backup stamp"
        fi
        if [ "${DBUPDATEU}" -gt "${BAKDATEU}" ]
        then
            # Backup db (move previous to prev, and write stamp file first)
            rm -f $BAK/prev/$db.*.gz
            [ -f $BAK/$db.*.gz ] && mv $BAK/$db.*.gz $BAK/prev
            FILE=$BAK/$db.$NOW.gz
            Logger "$db backing up to $FILE"
            date +'%F %T' > $STAMPS/$db
            $MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS --skip-lock-tables $db | $GZIP -9 > $FILE
            RETURNVALUE=$?
            if [ ! "${RETURNVALUE}" == "0" ]; then
                Logger "$db backup experienced error (${RETURNVALUE})"
                rm $STAMPS/$db
            fi
        else
            Logger "$db backup not required (last backup $BAKDATE, last db update $DBUPDATE)"
        fi
    fi
done

Logger "Completed local backup"

## FTP to remote server
if [ ${FTPS} == 0 ]
then
    lftp -u ${FTPUSER},${FTPPASS} -e "set net:max-retries 5; cd ${FTPDIR}; mput ${BAK}/*.gz; exit" ${FTPSERVER}
else
    lftp -u ${FTPUSER},${FTPPASS} -e "set ftp:ssl-force true,ftp:ssl-protect-data true,net:max-retries 5; cd ${FTPDIR}; mput ${BAK}/*.gz; exit" ${FTPSERVER}
fi
RETURNVALUE=$?

if [ "${RETURNVALUE}" == "0" ]; then
 Logger "FTP upload completed successfully"
else
 Logger "FTP upload failed !!!"
fi

echo "----------------------------------------------" >> $LOG