Daily MySQL off-site backups using logrotate and rsync

For daily MySQL off-site backups I was looking for a simple approach. Scott Miller’s minimal, but concise post on MySQL Backups With Logrotate  lead me to the unusual choice of logrotate to backup MySQL. What Scott was not doing, and I did need, was off-site backup. For this I had set up rsync access to the machine with public key authentication.

After setting up the daily rsync with some help from this post of Uwe Hermann I found out that the logrotate files were all transfered every time rsync ran, so not only the new ones. This turned out to be due to the default naming scheme of logrotate (appending sequential numbers). Fortunately I found a post by Eric Thern titled logrotate tweaks to enhance rsync backups that explains that one can use the “dateext” option to make the logrotate files “rsync friendly”.

The last thing I had to find out is when the logrotate task was fired to choose a good time for my cronjob that fired the rsync. I found a post on serverfault that showed me how to find out when the daily cron tasks were started. It turned out they were started at 6:25 AM on my Debian based machine. I scheduled the cronjob on the remote server to rsync at 9:00 AM to be sure the backup was finished.


I will go through all the files I modified on the production server and the (off-site) backup server. On the production server I created the file “/etc/logrotate.d/mysql-bkup”:

/home/maurits/bak/project.sql.gz {
rotate 8
mysqldump -uproject -pproject project > /home/maurits/bak/project.sql --single-transaction
gzip -9f /home/maurits/bak/project.sql
chown maurits:maurits /home/maurits/bak/project.sql.gz
chmod 640 /home/maurits/bak/project.sql.gz

As you can see the logrotate is run daily, with the dateext option for better rsync efficiency. It keeps 8 backups (7 days) and does not apply compression (we do that manually). The create option will make sure new files are created with the same permissions and owner as the previous ones. We finish with the “postrotate” script that does a mysqldump, gzip and then sets a proper owner and permissions. To test run the script you can execute:

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

After running it we can check the created files:

maurits@nuc:~$ ls bak -al
total 18192
drwxr-xr-x  2 maurits maurits    4096 Jun 22 15:17 .
drwxr-xr-x 10 maurits maurits    4096 Jun 22 14:54 ..
-rw-r-----  1 maurits maurits 9291115 Jun 22 15:17 project.sql.gz
-rw-r-----  1 maurits maurits 9291114 Jun 22 15:06 project.sql.gz-20130622

Now we login to the (off-site) backup server. Here we need to configure the cronjob to retrieve the logrotated MySQL backups using rsync. We add the cronjob by editing the crontab with:

maurits@nuc:~$ crontab -e

Then we add the rsync command to the crontab. Mine looks like this:

# m h  dom mon dow   command
0 9 * * * rsync -a --delete-during www.project.nl:bak ~

As you can see we configured that at minute 0 of hour 9 on every day of the month, of every month, on any day of the week we run the rsync command. This is all there is to it. If you have any questions, remarks or improvements, please use the comments section below.

One thought on “Daily MySQL off-site backups using logrotate and rsync”

  1. Darryl,Thanks for the response, and let me know how it works for you. You sound like me and want to trfesanr a bunch of photos videos at once, and PhanSync will work great for that. Additionally, if you get behind and want to bulk upload later, it will be good for that, too.-Dan

Leave a Reply

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