MySQL Incremental Backups

MySQL incremental backups can be acheived using the --log-bin option. For more information about that and for the original source of the following code, please see the mysql ref. manual.

Now to the code:
I added some logging to the script
[ad#ad-1]
#!/bin/bash
MODE=$1
MYSQLUSER=backup
FTPUSER=ftpuser
FTPPASS=ftppass
BINLOGPATH=/var/lib/mysql
BINLOGNAME=mysql-bin
ARCHIVEPATH=/var/data/backups/mysql/


setRead() {
# change properties on binlog path
sudo chmod o+rx $BINLOGPATH
sudo chmod o+r $BINLOGPATH/*
}


resetRead() {
# restore properties on binlog path
sudo chmod o-r $BINLOGPATH/*
sudo chmod o-rx $BINLOGPATH
}

copyBinlogs() {
# copy binlogs to archive dir
logger -ist backup_sql "Copying binlogs"
setRead;
for FILE in `cat $BINLOGPATH/$BINLOGNAME.index`
do
SFILE=(${FILE/*\//})
if [ ! -f $ARCHIVEPATH/$SFILE.gz ]
then
logger -ist backup_sql " - binlog $SFILE"
cp $BINLOGPATH/$FILE $ARCHIVEPATH
gzip $ARCHIVEPATH/$SFILE
fi
done
resetRead;
}

saveBinlogs() {
# move latest binlogs to WEEKLY
logger -ist backup_sql "Saving binlogs"
WEEKLY=WEEKLY/`date +%Y%m%d`
logger -ist backup_sql $WEEKLY
mkdir -p $ARCHIVEPATH/$WEEKLY
mkdir -p $ARCHIVEPATH/MONTHLY
FILECOUNT=`find $ARCHIVEPATH/WEEKLY/full* -type f | wc -l`
if [ -n "$FILECOUNT" ]; then
for LAST in `find $ARCHIVEPATH/WEEKLY/full* -mtime +28 -type f`;
do
mv $LAST $ARCHIVEPATH/MONTHLY/;
done
for OLD in `find $ARCHIVEPATH/WEEKLY/full* -mtime +8 -type f`;
do
rm $OLD;
done
fi
mv $ARCHIVEPATH/*.gz $ARCHIVEPATH/$WEEKLY
}

uploadToBackupFTP() {
logger -ist backup_sql " upload started"
lftp -u '$FTPUSER,$FTPPASS' ftp.server.tld -e "set ftp:ssl-protect-data true;
mirror --reverse $ARCHIVEPATH remote_mysql_backup_dir;
exit"
logger -ist backup_sql " upload complete"
}

# --- Main ---
case "$MODE" in

'full')
# Weekly backup
# - copy all bin logs to backup directory
# - clean path of backup directory
logger -ist backup_sql "Weekly backup"
DATE=`date +%Y%m%d`
mysqldump -u $MYSQLUSER --single-transaction --flush-logs --master-data=2 \
--all-databases --delete-master-logs | gzip > $ARCHIVEPATH/$WEEKLY/full-$DATE.sql.gz
logger -ist backup_sql " mysqldump complete "
copyBinlogs;
saveBinlogs;
uploadToBackupFTP;
;;

'incremental')
# Daily backup
# - flush logs
# - copy all bin logs to backup directory if not already done
logger -ist backup_sql "Mysql daily backup"
mysqladmin -u $MYSQLUSER flush-logs
logger -ist backup_sql "Flushed mysql logs"
copyBinlogs;
uploadToBackupFTP;
;;

*)
echo "Usage: mysql_backup [full|incremental]"
;;


esac

Similar Posts:

Tagged , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *