(I'm not a big Redhat fan so have written this for my own memory in case I need to do this elsewhere)
First obviously we need a cron job to do the database backup. This page explains a bit about how cron is set up on RedHat but it's pretty standard. /etc/crontab shows that scripts in cron.daily get run at 4:02 AM via run-parts which has a loop like this:
for i in $1/*[^~,] ; do ...
so it should run after logrotate as long as it follows alphabetically. (I wonder why redhat doesn't use numbers to set the order of the existing scripts, like 05-logrotate for example.) I want to back up the previous day's backup file first and then write a new one.
/etc/cron.daily/wikidb:
#!/bin/sh /usr/bin/mysqldump -a wikidb > /root/backups/wikidb.sql
/etc/cron.daily contains a logrotate script, so it gets run daily. So we can just create /etc/logrotate.d/wikidb:
/root/backups/wikidb.sql { compress daily olddir /root/backups/old rotate 14 }