← Back to DevBytes
Database July 10, 2026 10 min read

How to Automate Database Backups with Cron and rsync

What It Is: Automating Database Backups with Cron and rsync

Automating database backups with cron and rsync is the practice of combining Linux's built-in job scheduler (cron) with the powerful incremental file transfer utility (rsync) to create a hands-free, reliable backup pipeline for your databases. At its core, the approach consists of three distinct layers: a dump utility (like mysqldump or pg_dump) that exports your database to a flat file on disk, a cron job that triggers this dump on a recurring schedule, and an rsync command that securely copies the resulting backup files to a remote machine—often over SSH—ensuring geographical redundancy.

This method is not a single monolithic tool but a composition of proven Unix philosophies: small, focused programs chained together. The dump utility handles data integrity and format, cron handles timing, and rsync handles efficient, resumable transfer with delta detection so that only changed portions of large backup files are sent over the network after the initial full sync.

Why It Matters

🚀 Deploy your AI agent in 10 minutes

Managed Hermes hosting. Zero DevOps. 100M tokens/mo included.

Try it free →

Manual backups are a ticking time bomb. A developer might remember to run mysqldump before a migration but forget during a crunch week—and that is exactly when disaster strikes. Automated backups eliminate human forgetfulness from the equation entirely. Beyond mere scheduling, the rsync component brings critical advantages that a simple scp or FTP push cannot match:

For solo developers running side projects, small teams managing their own infrastructure, or DevOps engineers building cost-effective backup systems without cloud-vendor lock-in, this stack is a lightweight, transparent, and battle-tested foundation.

How to Use It: A Step-by-Step Implementation

This section walks through building the entire pipeline from scratch. The examples assume a Linux-based server, MySQL as the database, and a remote backup host accessible via SSH. The principles apply identically to PostgreSQL, MariaDB, or MongoDB with minor tooling swaps.

1. Create a Dedicated Backup Directory Structure

Organization prevents chaos. Create a predictable path layout for dumps and logs.

sudo mkdir -p /var/backups/mysql/daily
sudo mkdir -p /var/backups/mysql/weekly
sudo mkdir -p /var/log/backups
sudo chown -R $USER:$USER /var/backups /var/log/backups

The daily directory holds rolling 7-day backups; the weekly directory stores longer-retained snapshots. Logs capture stdout/stderr from cron so you can audit what ran and what failed.

2. Write the Backup Shell Script

This script performs the database dump, compresses the output, timestamps the filename, and rotates old files. Save it as /usr/local/bin/db-backup.sh and make it executable.

#!/bin/bash
#
# db-backup.sh — Automated MySQL dump with gzip compression and retention
# Intended to be called by cron daily.

set -euo pipefail

# ---------- CONFIGURATION ----------
DB_USER="backup_user"
DB_PASS="secure_password_here"
DB_NAME="your_database"
BACKUP_DIR="/var/backups/mysql/daily"
WEEKLY_DIR="/var/backups/mysql/weekly"
LOG_FILE="/var/log/backups/db-backup.log"
DATE_STAMP=$(date +%Y-%m-%d)
DAY_OF_WEEK=$(date +%u)   # 1=Monday, 7=Sunday

# ---------- CHECK DISK SPACE ----------
AVAIL_SPACE=$(df --output=avail "$BACKUP_DIR" | tail -1)
if [ "$AVAIL_SPACE" -lt 500000 ]; then
    echo "[$(date)] ERROR: Low disk space (${AVAIL_SPACE} blocks) — aborting backup" >> "$LOG_FILE"
    exit 1
fi

# ---------- PERFORM DUMP ----------
DUMP_FILE="${BACKUP_DIR}/${DB_NAME}-${DATE_STAMP}.sql.gz"

echo "[$(date)] Starting backup of ${DB_NAME}" >> "$LOG_FILE"

# Use --single-transaction for InnoDB to avoid locking
# Use --routines and --triggers to capture stored code
mysqldump --user="$DB_USER" --password="$DB_PASS" \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    "$DB_NAME" | gzip > "$DUMP_FILE"

DUMP_EXIT_CODE=${PIPESTATUS[0]}
if [ "$DUMP_EXIT_CODE" -ne 0 ]; then
    echo "[$(date)] ERROR: mysqldump failed with exit code $DUMP_EXIT_CODE" >> "$LOG_FILE"
    exit 1
fi

echo "[$(date)] Dump completed: $(du -h "$DUMP_FILE" | cut -f1)" >> "$LOG_FILE"

# ---------- WEEKLY SNAPSHOT (every Sunday) ----------
if [ "$DAY_OF_WEEK" -eq 7 ]; then
    cp "$DUMP_FILE" "${WEEKLY_DIR}/${DB_NAME}-week-$(date +%Y-%W).sql.gz"
    echo "[$(date)] Weekly snapshot saved" >> "$LOG_FILE"
fi

# ---------- RETENTION: purge dumps older than 7 days ----------
find "$BACKUP_DIR" -name "${DB_NAME}-*.sql.gz" -mtime +7 -delete
find "$WEEKLY_DIR" -name "${DB_NAME}-*.sql.gz" -mtime +60 -delete

echo "[$(date)] Backup script finished successfully" >> "$LOG_FILE"

Key design decisions in this script:

3. Secure Database Credentials with MySQL Configuration File

Hardcoding passwords in a shell script is a security risk—any user with read access to the script or a process listing can see them. Instead, create a ~/.my.cnf file for the user that runs the backup (often root or a dedicated backup system account).

# Create credentials file in the home directory of the backup runner
cat > /root/.my.cnf << 'EOF'
[client]
user=backup_user
password=secure_password_here
host=localhost
EOF

chmod 600 /root/.my.cnf

Then simplify the mysqldump invocation—the client reads credentials automatically:

mysqldump --defaults-file=/root/.my.cnf \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    "$DB_NAME" | gzip > "$DUMP_FILE"

The chmod 600 ensures only the file owner can read the credentials. Never check this file into version control.

4. Schedule the Backup with Cron

Open the crontab for the user that will execute the backup (typically root for database access):

sudo crontab -e

Add a line that runs the script daily at 2:00 AM—a quiet period for most applications:

# Daily database backup — runs at 02:00 every day
0 2 * * * /usr/local/bin/db-backup.sh >> /var/log/backups/db-backup.log 2>&1

Cron expression breakdown:

The redirection >> … 2>&1 appends both stdout and stderr to the log file so you have a complete audit trail. Without this, cron emails output to the local mailbox (often undelivered on modern minimal servers).

5. Test the Cron Job Immediately

Before relying on the schedule, run the script manually as the cron user to verify it works:

sudo su - root
/usr/local/bin/db-backup.sh
# Check the log
tail -20 /var/log/backups/db-backup.log
# Verify the dump file exists and is non-zero
ls -lh /var/backups/mysql/daily/

Also test that cron's environment does not cause failures—cron runs with a minimal PATH. Explicitly reference full paths inside scripts (e.g., /usr/bin/mysqldump instead of just mysqldump) or set PATH at the top of your script:

PATH=/usr/local/bin:/usr/bin:/bin
export PATH

6. Transfer Backups Off-Site with rsync Over SSH

Local backups protect against accidental deletion or database corruption but not against server failure. rsync pushes the backup directory to a remote host efficiently.

First, set up SSH key-based authentication so rsync can run non-interactively:

# Generate an SSH key pair (without passphrase for automation)
ssh-keygen -t ed25519 -f /root/.ssh/backup_rsync_key -N "" -C "automated-backup"

# Copy the public key to the remote backup server
ssh-copy-id -i /root/.ssh/backup_rsync_key.pub backup_user@remote-host.example.com

Test the SSH connection once to accept the host key and confirm it works:

ssh -i /root/.ssh/backup_rsync_key backup_user@remote-host.example.com "echo connected"

Now create a companion script, /usr/local/bin/db-sync.sh, that handles the rsync transfer:

#!/bin/bash
#
# db-sync.sh — rsync database backups to remote host
# Runs after db-backup.sh completes.

set -euo pipefail

SOURCE_DIR="/var/backups/mysql/"
REMOTE_USER="backup_user"
REMOTE_HOST="remote-host.example.com"
REMOTE_PATH="/backups/mysql/"
SSH_KEY="/root/.ssh/backup_rsync_key"
LOG_FILE="/var/log/backups/db-sync.log"

echo "[$(date)] Starting rsync to ${REMOTE_USER}@${REMOTE_HOST}" >> "$LOG_FILE"

rsync -avz --delete \
    --rsh="ssh -i ${SSH_KEY} -o StrictHostKeyChecking=yes" \
    "$SOURCE_DIR" \
    "${REMOTE_USER}@${REMOTE_HOST}:${REMOTE_PATH}" \
    >> "$LOG_FILE" 2>&1

RSYNC_EXIT_CODE=$?
if [ "$RSYNC_EXIT_CODE" -ne 0 ]; then
    echo "[$(date)] ERROR: rsync failed with exit code $RSYNC_EXIT_CODE" >> "$LOG_FILE"
    exit 1
fi

echo "[$(date)] Rsync completed successfully" >> "$LOG_FILE"

Important rsync flags explained:

7. Chain Backup and Sync in Cron

You can either schedule the sync script 30 minutes after the backup (giving mysqldump time to finish even on large databases) or combine both in a single wrapper script that runs sequentially:

#!/bin/bash
#
# nightly-backup.sh — wrapper that runs dump then sync

/usr/local/bin/db-backup.sh
/usr/local/bin/db-sync.sh

Then schedule the wrapper in cron:

0 2 * * * /usr/local/bin/nightly-backup.sh

The sequential approach guarantees that a failed dump aborts the sync, preventing corrupted or stale backups from overwriting good remote copies.

8. Verify Remote Backups Regularly

Automation breeds complacency. Schedule a separate cron job that performs a spot-check restore on the remote host and emails you the result. For example, a weekly verification script on the remote backup server:

#!/bin/bash
#
# verify-backup.sh — runs on the REMOTE host to test the latest dump

LATEST_DUMP=$(ls -t /backups/mysql/daily/*.sql.gz | head -1)
gunzip -t "$LATEST_DUMP" && echo "Backup integrity OK: $LATEST_DUMP" || echo "BACKUP CORRUPT: $LATEST_DUMP"

Even better, spin up a temporary MySQL container, load the dump, and run mysqlcheck to verify table consistency. This is a deeper topic but worth the investment for production data.

9. PostgreSQL Variant

The pattern is identical for PostgreSQL; swap mysqldump for pg_dump. Use a .pgpass file for credentials:

# .pgpass format: hostname:port:database:username:password
echo "localhost:5432:your_database:backup_user:secure_password" > /root/.pgpass
chmod 600 /root/.pgpass

pg_dump --host=localhost --username=backup_user --dbname=your_database \
    --format=custom --compress=9 > "/var/backups/postgres/daily/db-$(date +%Y-%m-%d).dump"

The custom format (--format=custom) supports parallel restore with pg_restore --jobs=N, which drastically reduces recovery time for large databases.

Best Practices

Conclusion

Automating database backups with cron and rsync is a deceptively simple yet profoundly robust strategy. It stitches together tools that have been refined over decades—mysqldump's consistent snapshots, cron's reliable scheduling, rsync's network-efficient synchronization, and SSH's strong encryption—into a pipeline that costs nothing beyond the storage you already provision. The real power lies not in any single component but in the disciplined layering: dump locally, verify integrity, transfer remotely, retain intelligently, and test restoration regularly. Once this system is in place and the verification cadence is established, you gain something invaluable: the ability to sleep through the night knowing that your data—and the applications that depend on it—can be rebuilt from the ground up, on any server, at any time.

🚀 Need a reliable AI agent for your project?

Deploy Hermes Agent in 10 minutes. Managed hosting, zero DevOps.

Get Started — $23.99/mo
← Back to all articles