scottlinux.com | Linux Blog

logrotate can be used to rotate and auto-purge any file, including mysql backups.

This very server (scottlinux.com) backs up a mysql dump daily, keeping the previous 8 with this method. I’ll show you how:

1. Create the following file, editing as desired. This example backs up all mysql databases in one dump. Of course you will need to edit in your actual mysql password.

sudo nano /etc/logrotate.d/mysql-bkup

/var/backups/db.sql.gz {
daily
rotate 8
nocompress
create 640 root adm
postrotate
mysqldump -u root -pPassword --all-databases > /var/backups/db.sql --single-transaction
gzip -9f /var/backups/db.sql
endscript
}

2. One last thing:

sudo touch /var/backups/db.sql.gz

To test your logrotate config file, issue this command:

sudo logrotate -f /etc/logrotate.d/mysql-bkup

Done!