Backup MySQL (Ubuntu)
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 withapt-get install lftp
)- Can use FTPS for secure backup transfer
Set-up
- Create the required folders using...
mkdir backup
mkdir backup/mysql
- Create the file below (editing as required) as
/backup/mysql.sh
- Make the file executable
chmod +x /backup/mysql.sh
- Perform a test run of the backup
- 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