David W David W - 6 months ago 19
SQL Question

Removing old MySQL / MariaDB backups in Bash Backup Script

I've written a bash script, initiated on cron, that backups all databases on a particular machine nightly and weekly. The script correctly removes old databases, except for those cases when there's been a change in month.

As an example, let's say is November 2nd. The script runs at 11:00pm, and correctly removes the backup made from November 1st. But come December 1st, the script gets confused, and does not correctly remove the backup made from November 30th.

How can I fix this script to correctly remove the old backups in this case?

DATABASES=$(echo 'show databases;' | mysql -u backup --password='(password)' | grep -v ^Database$)
LIST=$(echo $DATABASES | sed -e "s/\s/\n/g")
DATE=$(date +%Y%m%d)
DAYOLD=$(($DATE-1))
SUNDAY=$(date +%a)
WEEKOLD=$(($DATE-7))

for i in $LIST; do
if [[ $i != "mysql" ]]; then
mysqldump --single-transaction $i > /mnt/backups/mariadb/daily/$i.$DATE.sql
if [ -f /mnt/backups/mariadb/daily/$i.$DAYOLD.sql ]; then
rm -f /mnt/backups/mariadb/daily/$i.$DAYOLD.sql
fi
if [[ $SUNDAY == "Sun" ]]; then
cp /mnt/backups/mariadb/daily/$i.$DATE.sql /mnt/backups/mariadb/weekly/$i.$DATE.sql
rm -f /mnt/backups/mariadb/weekly/$i.$WEEKOLD.sql
fi
fi
done

Answer

I was making this too complicated. Instead of using the date at all, I'm just searching for the age of the file backup with:

find /mnt/backups/mariadb/weekly/* -type f -mtime +8 -exec rm -f {} \;

So the entire script becomes:

DATABASES=$(echo 'show databases;' | mysql -u backup --password='foo' | grep -v ^Database$)
LIST=$(echo $DATABASES | sed -e "s/\s/\n/g")
DATE=$(date +%Y%m%d)
SUNDAY=$(date +%a)

for i in $LIST; do
if [[ $i != "mysql" ]]; then
        /bin/nice mysqldump --single-transaction $i > /mnt/backups/mariadb/daily/$i.$DATE.sql
        find /mnt/backups/mariadb/daily/* -type f -mtime +1 -exec rm -f {} \;

        if [[ $SUNDAY == "Sun" ]]; then
                cp /mnt/backups/mariadb/daily/$i.$DATE.sql /mnt/backups/mariadb/weekly/$i.$DATE.sql
                find /mnt/backups/mariadb/weekly/* -type f -mtime +8 -exec rm -f {} \;
        fi
fi
chown -R backup.backup /mnt/backups
done
Comments