If you've got Drush installed—and you really should—you can use the following recipe to setup a backup system that will maintain daily backups for the last two weeks. Most of the logrotate configuration is based on a Wikibooks book that I found.

Find the pieces

Make sure logrotate is installed:

Which should print something like:

So for this site we'll use the full path /usr/sbin/logrotate to run the program.

If you don't know where drush is installed you'll probably want to repeat the process to determine its location. The site I'm working on right now is hosted by May First, a very Drupal friendly ISP (and an amazing progressive group), so they've installed drush at /usr/bin/drush.

drush needs to be able to find the correct settings.php file to connect to your database. Specify the root of your Drupal site using the -r switch. You can test that it's able to locate your settings using the following command:

If it works you'll see an array with your database connection information.

Hook 'em up

Create the state and configuration files:

Edit ~/.logrotate.config insert the following text:

logrotate expects that the file will already exist so we need to use drush to create the first one:

Test that logrotate will work correctly:

If everything is working correctly you'll see something like:

Schedule it

Edit your crontab:

And add the following line which will run logrotate at midnight:

Sleep a little better

That's it, you should now have two weeks of daily backups. You'll want to check back on it tomorrow and make sure that the backups are actually occurring and that the old ones are being renamed to .sql.gz.1, .sql.gz.2, etc.