Backup MySQL (Ubuntu)

From vwiki
Revision as of 07:12, 25 April 2012 by Sstrutt (talk | contribs) (→‎The Script: Updated script)
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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
  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 $MUSER -h $MHOST -p$MPASS -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=`cat ${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