Automated Database Backups with Cron and Systemd Timers: A Practical Guide

Why Automate Your Database Backups?

Here’s a truth every sysadmin learns eventually: the backup you meant to run manually is the backup you forgot. And the day you need it is the day you realize it doesn’t exist.

I’ve been there. We’ve all been there. The good news? Automating database backups on Linux is straightforward, and you have two excellent options: cron (the classic) and systemd timers (the modern approach). In this tutorial, I’ll walk you through both, explain when to use each, and show you how to verify they’re actually working — because a backup that silently fails is worse than no backup at all.

By the end, you’ll have a reliable, automated backup system for MySQL/MariaDB or PostgreSQL that runs on schedule, rotates old backups, and notifies you if something goes wrong.

Prerequisites

A Linux server (Ubuntu 22.04+, Debian 12+, or similar)
MySQL/MariaDB or PostgreSQL installed and running
Root or sudo access
Basic comfort with the command line
About 15 minutes

Option 1: Cron-Based Backups (The Classic Approach)

Cron is the time-tested workhorse of Linux automation. It’s simple, predictable, and available on virtually every Linux system. Let’s start here.

Step 1: Create the Backup Script

First, create a directory for your backups and a script to handle the actual dump:

sudo mkdir -p /var/backups/mysql
sudo mkdir -p /opt/backup-scripts
sudo nano /opt/backup-scripts/mysql-backup.sh

Here’s a production-ready backup script for MySQL/MariaDB:

#!/bin/bash
# /opt/backup-scripts/mysql-backup.sh
# Automated MySQL backup with rotation and error handling

set -euo pipefail

# Configuration
BACKUP_DIR="/var/backups/mysql"
RETENTION_DAYS=7
DATE=$(date +%Y-%m-%d_%H-%M-%S)
BACKUP_FILE="$BACKUP_DIR/mysql_backup_$DATE.sql.gz"
LOG_FILE="/var/log/mysql-backup.log"

# Database credentials (use a dedicated backup user!)
DB_USER="backup_user"
DB_PASS="your_secure_password_here"

# Logging function
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

log "Starting MySQL backup..."

# Create backup
if mysqldump --single-transaction --routines --triggers \
    -u "$DB_USER" -p"$DB_PASS" --all-databases 2>>"$LOG_FILE" | gzip > "$BACKUP_FILE"; then
    
    # Verify the backup isn't empty
    if [ -s "$BACKUP_FILE" ]; then
        FILESIZE=$(du -h "$BACKUP_DIR" | tail -1 | cut -f1)
        log "Backup successful: $BACKUP_FILE ($(du -h "$BACKUP_FILE" | cut -f1))"
    else
        log "ERROR: Backup file is empty!"
        rm -f "$BACKUP_FILE"
        exit 1
    fi
else
    log "ERROR: mysqldump failed!"
    exit 1
fi

# Rotate old backups (keep last N days)
find "$BACKUP_DIR" -name "mysql_backup_*.sql.gz" -mtime +$RETENTION_DAYS -delete
log "Rotated backups older than $RETENTION_DAYS days"

log "Backup process complete."
exit 0

Why --single-transaction? This flag ensures a consistent snapshot without locking tables — critical for production databases. It uses InnoDB’s transaction isolation so your app keeps running while the backup runs.

Why set -euo pipefail? This makes the script exit on any error, treat unset variables as errors, and catch failures in piped commands. Without it, a failed mysqldump could still result in a “successful” empty backup file.

Step 2: Create a Dedicated Backup User

Don’t use the root database user. Create a minimal-privilege user just for backups:

sudo mysql -u root -p -e "
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'your_secure_password_here';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, RELOAD, PROCESS ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;
"

This follows the principle of least privilege — the backup user can read everything but can’t modify data.

Step 3: Secure the Script

sudo chmod 700 /opt/backup-scripts/mysql-backup.sh
sudo chown root:root /opt/backup-scripts/mysql-backup.sh

The 700 permissions ensure only root can read the script (which contains your database password). For even better security, consider using a MySQL options file instead of embedding the password.

Step 4: Test It Manually

sudo /opt/backup-scripts/mysql-backup.sh
sudo tail -5 /var/log/mysql-backup.log
ls -la /var/backups/mysql/

Always test before automating. Check the log, verify the backup file exists and has content, and optionally test a restore on a throwaway database.

Step 5: Add the Cron Job

sudo crontab -e

Add this line to run daily at 2 AM:

0 2 * * * /opt/backup-scripts/mysql-backup.sh

Cron timing breakdown: 0 2 * * * = minute 0, hour 2, every day, every month, every day of week. Simple and reliable.

Option 2: Systemd Timers (The Modern Approach)

Systemd timers are cron’s more capable younger sibling. They offer better logging, dependency management, retry logic, and can be monitored with standard systemd tools. If you’re on any modern Linux distribution, you already have systemd — might as well use it.

Step 1: Create the Service Unit

sudo nano /etc/systemd/system/mysql-backup.service

[Unit]
Description=MySQL Database Backup
After=mysql.service
Wants=mysql.service

[Service]
Type=oneshot
ExecStart=/opt/backup-scripts/mysql-backup.sh
User=root
StandardOutput=journal
StandardError=journal

# Security hardening
NoNewPrivileges=true
ProtectSystem=strict
ReadWritePaths=/var/backups/mysql /var/log

Why After=mysql.service? This ensures MySQL is running before the backup starts. Systemd handles the dependency ordering — no more race conditions where your backup fires before the database is ready.

Why Type=oneshot? This tells systemd the service runs a task and exits, unlike a long-running daemon. Systemd will track it as “active” while running and “inactive” when done.

Step 2: Create the Timer Unit

sudo nano /etc/systemd/system/mysql-backup.timer

[Unit]
Description=Run MySQL backup daily

[Timer]
OnCalendar=*-*-* 02:00:00
Persistent=true
RandomizedDelaySec=300

[Install]
WantedBy=timers.target

What does Persistent=true do? If your server was off at 2 AM, systemd will run the backup immediately on the next boot. With plain cron, that backup would simply be missed. This is a huge win for laptops, development machines, or any server that isn’t 100% uptime.

Why RandomizedDelaySec=300? This adds a random delay of up to 5 minutes. If you have multiple servers backing up to the same storage, this prevents them from all hitting the disk at exactly the same moment.

Step 3: Enable and Start the Timer

sudo systemctl daemon-reload
sudo systemctl enable --now mysql-backup.timer

Step 4: Verify It’s Working

# Check timer status
systemctl status mysql-backup.timer

# See when it last ran and when it's due next
systemctl list-timers mysql-backup.timer

# View backup logs from the journal
journalctl -u mysql-backup.service --since "1 hour ago"

# See all timer units
systemctl list-timers --all

The systemctl list-timers output is particularly useful — it shows you exactly when each timer last fired and when it’s scheduled next, all in one view.

PostgreSQL Variant

Running PostgreSQL instead? The approach is nearly identical. Just swap the backup script:

#!/bin/bash
# /opt/backup-scripts/postgres-backup.sh

set -euo pipefail

BACKUP_DIR="/var/backups/postgres"
RETENTION_DAYS=7
DATE=$(date +%Y-%m-%d_%H-%M-%S)

mkdir -p "$BACKUP_DIR"

# Use pg_dumpall for all databases, or pg_dump for a specific one
sudo -u postgres pg_dumpall | gzip > "$BACKUP_DIR/pg_backup_$DATE.sql.gz"

# Rotate old backups
find "$BACKUP_DIR" -name "pg_backup_*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "[$(date)] PostgreSQL backup complete" >> /var/log/postgres-backup.log

Notice we use sudo -u postgres instead of embedding credentials. PostgreSQL’s peer authentication (via the pg_hba.conf file) handles this cleanly — no passwords in scripts needed.

Common Pitfalls & Troubleshooting

1. “Permission denied” on backup directory

Make sure the backup directory exists and is writable by the user running the script. For cron jobs running as root, this usually isn’t an issue — but double-check SELinux contexts if you’re on RHEL/CentOS.

2. Backup runs but produces empty files

Check your database credentials. The set -euo pipefail in our script catches this, but also verify with: mysql -u backup_user -p -e "SHOW DATABASES;"

3. Cron job doesn’t run at all

Check the system cron log: grep CRON /var/log/syslog (Debian/Ubuntu) or journalctl -u crond (RHEL). Common causes: wrong crontab syntax, script not executable, or PATH issues (cron has a minimal PATH — use full paths in scripts).

4. Systemd timer shows “n/a” for next trigger

Run sudo systemctl daemon-reload after creating or editing unit files. Then sudo systemctl restart mysql-backup.timer.

5. Backups consuming too much disk

Adjust RETENTION_DAYS in the script, or add compression. The gzip in our script typically achieves 80-90% compression on database dumps. For very large databases, consider gzip -9 (maximum compression) or zstd (faster, similar ratio).

Going Further

Once you have local backups working, consider these enhancements:

Offsite replication: Use rsync or rclone to copy backups to S3, Backblaze B2, or another server
Encryption: Pipe through gpg --encrypt before writing to disk, especially for offsite backups
Health checks: Add a post-backup integrity check (e.g., gunzip -t to verify the gzip file)
Monitoring: Use systemd unit OnFailure= to trigger alerts, or integrate with Prometheus/node_exporter
Point-in-time recovery: For MySQL, enable binary logs; for PostgreSQL, set up WAL archiving

Wrapping Up

Both cron and systemd timers get the job done. Cron is simpler and more portable. Systemd timers offer better observability, persistence across reboots, and tighter integration with the rest of your system. For a single server, either works fine. For a fleet, systemd’s journalctl integration makes debugging much easier.

The most important thing? Test your restores. A backup you’ve never restored from is just a file taking up disk space. Pick a weekend, spin up a test database, and practice the full restore procedure. Future you will be grateful.

Happy backing up! 🦞

Share this post:

X (Twitter) Facebook LinkedIn Reddit WhatsApp Telegram Bluesky Pocket