DBA tip: Rotating MySQL dumps & binary logs

Originally Posted 28th June 2011

Especially for the binlogs, allows to keep much older backups for longer; on my tests, binlog file ca. 1G gzipped to 125M

#!/bin/bash
DAYS_TO_KEEP=4
BINLOG_DAYS_TO_KEEP=5
PATH_BACKUP=/backup
PATH_BINLOG=/var/log/mysql
userpassword=”-uxxx -pxxx”

# delete old
find ${PATH_BACKUP} -maxdepth 1 -type f -mtime +${DAYS_TO_KEEP} -exec rm {} \;

# move current set to timestamped
if [ -n “`ls ${PATH_BACKUP}/*.sql.gz 2>/dev/null`” ] ; then
echo “Rotate backups, keeping past $DAYS_TO_KEEP days …”
for fn in `ls ${PATH_BACKUP}/*.sql.gz` ; do
fn_new=`basename $fn gz`
fn_new=`date +${fn_new}%Y%m%d.gz`
mv $fn ${PATH_BACKUP}/$fn_new
done
echo “… done”
fi

echo “Rotating binlogs ..”
# gzip files of format bin.(words) (i.e. not suffix .gz), not accessed in past 30 minutes
find ${PATH_BINLOG} -regex ‘.*/bin\.[0–9]+’ -mmin +30 -exec gzip {} \;
find ${PATH_BINLOG} -maxdepth 1 -type f -mtime +${BINLOG_DAYS_TO_KEEP} -exec rm {} \;
echo “… Binlogs rotated”

echo “Purging old bin logs …”
PURGEDATE=`date -d “$DAYS_TO_KEEP days ago” +”%Y-%m-%d”`
echo “PURGE BINARY LOGS BEFORE \’$PURGEDATE 00:00:00\’”
echo PURGE BINARY LOGS BEFORE \’$PURGEDATE 00:00:00\’ | /usr/bin/mysql $userpassword
echo “… binary logs purged”