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 (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!

5 thoughts on “Rotate MySQL Backups With Logrotate

  1. Leonel

    Hey!!! thanks a lot!!!

    Cheers from argentina!!!

    Reply
  2. Leonel

    P.S:: the last command should be sudo “logrotate -f /etc/logrotate.d/mysql-bkup”

    Reply
  3. Jon

    Thanks for this blog article. Finally set up logrotate on my own VPS – all I need to do now is to push the rotated logs offsite…

    Reply
  4. Stuart Cardall

    It is better to setup a limited user for backups rather than leaving the root password in plaintext on the server:

    GRANT LOCK TABLES, SELECT ON *.* TO ‘backupuser’@’localhost’ IDENTIFIED BY ‘secret-password’;

    Reply

Leave a Reply

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

Notify me of followup comments via e-mail. You can also subscribe without commenting.