How to automate database backups with cron

A backup you have to remember to run is not a backup. With cron, your database saves itself every night. Here is how.

1. The backup script

Create /usr/local/bin/backup-db.sh. Example for MySQL, deleting archives older than 14 days automatically:

#!/bin/bash
DEST=/var/backups/mysql
mkdir -p "$DEST"
STAMP=$(date +%F)
mysqldump -u root -pPASSWORD app_db | gzip > "$DEST/app_db-$STAMP.sql.gz"
find "$DEST" -name '*.sql.gz' -mtime +14 -delete

For PostgreSQL, replace the dump line with:

pg_dump -U postgres app_db | gzip > "$DEST/app_db-$STAMP.sql.gz"

2. Make it executable

chmod +x /usr/local/bin/backup-db.sh

3. Schedule it with cron

Run crontab -e and add a line for every night at 3:00:

0 3 * * * /usr/local/bin/backup-db.sh

4. Send the backup off the server

A backup on the same server does not save you if the server fails. Copy the archives to a Backup Box, mounted as an extra disk via SSHFS or SMB. Add to the end of the script:

rsync -a "$DEST/" /mnt/backup/mysql/

Verify the restore

An untested backup does not count. See the MySQL and PostgreSQL guides for the restore steps.