There’s a particular kind of dread that comes from realizing your database died six months ago and nobody noticed. I’ve been there. The server kept humming along, the uptime monitor stayed green, and somewhere in the silence between one failed backup and the next, an entire season of data quietly evaporated.

Let’s make sure that never happens to you.

In this tutorial, we’ll set up automated database backups for both MySQL and PostgreSQL using nothing but cron and a shell script. No expensive SaaS tools, no complex orchestration platforms — just a few lines of bash that run while you sleep.

What We’re Building

By the end of this guide, you’ll have:

• Daily compressed database backups with automatic rotation
• Separate scripts for MySQL/MariaDB and PostgreSQL
• Old backups cleaned up automatically (keeps the last 7 days)
• Logging so you can verify backups actually ran
• Optional email alerts on failure

Prerequisites

• A Linux server with MySQL or PostgreSQL installed
• Access to a user account with sudo privileges
• Basic comfort with the command line
• At least 2x your database size available in disk space for backups

Step 1: Create the Backup Directory

First, let’s create a dedicated directory for our backups. Keeping them separate from your web root means they won’t accidentally get served to visitors.

sudo mkdir -p /var/backups/databases
sudo chown root:root /var/backups/databases
sudo chmod 700 /var/backups/databases

That chmod 700 is important — database dumps contain passwords, user data, and all sorts of sensitive information. Only root should be able to read these files.

Step 2: Write the Backup Script

Create the script at /usr/local/bin/db-backup.sh:

sudo nano /usr/local/bin/db-backup.sh

Paste in the following (I’ll walk through each section after):

#!/bin/bash
# Automated Database Backup Script
# Supports MySQL/MariaDB and PostgreSQL

set -euo pipefail

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

# Database type: "mysql" or "postgresql"
DB_TYPE="${DB_TYPE:-mysql}"

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

# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"

if [ "$DB_TYPE" = "mysql" ]; then
    log "Starting MySQL backup..."
    
    # Option A: Backup all databases (simplest)
    mysqldump --all-databases \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        --flush-logs \
        --master-data=2 \
        2>>"$LOG_FILE" | gzip > "$BACKUP_DIR/mysql_all_${DATE}.sql.gz"
    
    log "MySQL backup completed: mysql_all_${DATE}.sql.gz"

elif [ "$DB_TYPE" = "postgresql" ]; then
    log "Starting PostgreSQL backup..."
    
    # Run as postgres user for peer authentication
    sudo -u postgres pg_dumpall \
        --clean \
        --if-exists \
        2>>"$LOG_FILE" | gzip > "$BACKUP_DIR/pgsql_all_${DATE}.sql.gz"
    
    log "PostgreSQL backup completed: pgsql_all_${DATE}.sql.gz"

else
    log "ERROR: Unknown DB_TYPE '$DB_TYPE'. Use 'mysql' or 'postgresql'."
    exit 1
fi

# Rotate old backups
log "Removing backups older than ${RETENTION_DAYS} days..."
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +${RETENTION_DAYS} -delete -print | tee -a "$LOG_FILE"

log "Backup process complete."

# Optional: Show disk usage
BACKUP_SIZE=$(du -sh "$BACKUP_DIR" | cut -f1)
log "Total backup storage: $BACKUP_SIZE"

Make it executable:

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

Step 3: Understanding the Script

Let’s break down the important parts:

MySQL flags explained

• --single-transaction — Creates a consistent snapshot without locking tables. Essential for InnoDB databases that need to stay online.
• --routines --triggers --events — Includes stored procedures, triggers, and scheduled events in the dump. Without these, your backup is incomplete.
• --master-data=2 — Writes the binary log position as a comment. Critical if you ever need to do point-in-time recovery.

PostgreSQL approach

We use pg_dumpall instead of pg_dump because it captures cluster-wide objects like roles and tablespaces that pg_dump misses. The --clean --if-exists flags make the dump idempotent — you can restore it without errors if objects already exist.

Why set -euo pipefail?

This is bash’s “be strict” mode:

• -e — Exit on any error
• -u — Treat unset variables as errors
• -o pipefail — If any command in a pipeline fails, the whole pipeline fails

Without this, a failed mysqldump could silently produce an empty gzip file that looks like a successful backup. That’s worse than no backup at all.

Step 4: Test It Manually

Before trusting cron with this, run it yourself:

# For MySQL
sudo DB_TYPE=mysql /usr/local/bin/db-backup.sh

# For PostgreSQL
sudo DB_TYPE=postgresql /usr/local/bin/db-backup.sh

# Check the output
ls -lh /var/backups/databases/
cat /var/log/db-backup.log

You should see a timestamped .sql.gz file and a success message in the log.

Step 5: Set Up the Cron Job

Open the root crontab:

sudo crontab -e

Add one of these lines depending on your database:

# MySQL backup at 2:30 AM daily
30 2 * * * DB_TYPE=mysql /usr/local/bin/db-backup.sh

# PostgreSQL backup at 2:30 AM daily
30 2 * * * DB_TYPE=postgresql /usr/local/bin/db-backup.sh

# Both (if you run both databases)
30 2 * * * DB_TYPE=mysql /usr/local/bin/db-backup.sh && DB_TYPE=postgresql /usr/local/bin/db-backup.sh

I like 2:30 AM because it’s late enough that most batch jobs have finished but early enough that if something goes wrong, you’ll see it when you check logs in the morning.

Step 6: Verify Cron Is Working

After the first scheduled run, check:

# Check the log
tail -20 /var/log/db-backup.log

# Verify the backup file exists and has content
ls -lh /var/backups/databases/

# Test that the gzip file is valid
gzip -t /var/backups/databases/mysql_all_*.sql.gz && echo "Backup is valid"

That gzip -t test is something I’d recommend adding to your monitoring. A corrupted gzip file is just as useless as a missing one.

Common Pitfalls

1. “mysqldump: Got error: 2002: Can’t connect to local MySQL server”

This usually means the MySQL socket path differs between your interactive shell and cron. Fix it by using --protocol=tcp or specifying the socket explicitly:

mysqldump --socket=/var/run/mysqld/mysqld.sock --all-databases ...

2. PostgreSQL peer authentication fails

Cron runs as root, but PostgreSQL’s default pg_hba.conf uses peer authentication (which checks the OS user matches the database user). Our script handles this with sudo -u postgres, but if you prefer password auth, create a .pgpass file:

# As postgres user:
echo "localhost:5432:*:postgres:yourpassword" > ~/.pgpass
chmod 600 ~/.pgpass

3. Disk space exhaustion

Database backups can grow surprisingly large. Add a disk space check to your script before the dump:

# Add near the top of the script
FREE_SPACE=$(df /var/backups/databases --output=avail -BG | tail -1 | tr -d ' G')
if [ "$FREE_SPACE" -lt 5 ]; then
    log "ERROR: Only ${FREE_SPACE}GB free. Aborting backup."
    exit 1
fi

4. Forgetting to test restores

A backup you’ve never tested restoring is a backup you don’t have. Once a month, restore to a throwaway instance:

# MySQL restore
zcat /var/backups/databases/mysql_all_20260601_023000.sql.gz | mysql -u root -p

# PostgreSQL restore
zcat /var/backups/databases/pgsql_all_20260601_023000.sql.gz | sudo -u postgres psql

Going Further

This setup handles the basics, but here are some enhancements worth considering:

• Remote backups: Add rsync or rclone after the dump to copy backups to S3, Backblaze B2, or another server entirely. Local backups protect against database corruption; remote backups protect against the server catching fire.
• Encryption: Pipe through gpg --encrypt if your database contains sensitive data and your backup server isn’t fully trusted.
• Monitoring: Have your script send a health check ping to a service like healthchecks.io. If the cron job fails silently, you’ll know.
• Per-database backups: If you have many databases with different backup schedules, loop through mysql -e "SHOW DATABASES" and dump each one separately.

Wrapping Up

Database backups are the kind of thing you only think about when you need them — and by then it’s too late. Twenty minutes of setup now can save you from a very bad day later.

The script we built is intentionally simple. It doesn’t need a Kubernetes operator or a managed service. It just needs to run, and to work when you need it. That’s the beauty of cron: it’s been doing this job since 1975 and it’s still the most reliable scheduler we have.

Set it up tonight. Sleep well.