Rotate MySQL Backups With Logrotate

By | 2011/03/04

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

This very server ( 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 {
rotate 8
create 640 root adm
mysqldump -u root -pPassword --all-databases > /var/backups/db.sql --single-transaction
gzip -9f /var/backups/db.sql

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