It was a WordPress site on a $5 DigitalOcean droplet. Client's small business. Nothing fancy.

I had SSH access. I had root. I had every tool I needed to set up automated backups. I just... didn't. Because it was a small site. Because I'd "get to it later." Because the database was only 40MB and what could go wrong?

What went wrong was a botched plugin update that corrupted the wp_options table. No backup. No snapshot. No dump. Just a client on the phone asking why their site shows a white screen and me trying to explain that their content might be gone.

I rebuilt it from a cached Google version and a Wayback Machine scrape. Took two days. Billed zero.

That was the last time I ran a database without automated backups.

The Script

This is what I use now. Every server, every database, no exceptions.

#!/bin/bash

CHECK="✓"
CROSS="✗"

# --- Configuration ---
DB_USER="root"
DB_PASS="your_password"          # Or use ~/.my.cnf for security
DB_NAME="your_database"          # Change to your DB name
BACKUP_DIR="/var/backups/mysql"
KEEP_DAYS=7                       # Delete backups older than this
DATE=$(date '+%Y-%m-%d_%H-%M-%S')
FILENAME="${DB_NAME}_${DATE}.sql.gz"

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

# --- Run the backup ---
echo "Backing up database: $DB_NAME..."

if mysqldump -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" | gzip > "${BACKUP_DIR}/${FILENAME}"; then
  echo "$CHECK Backup created: ${BACKUP_DIR}/${FILENAME}"
  echo "$CHECK Size: $(du -sh "${BACKUP_DIR}/${FILENAME}" | cut -f1)"
else
  echo "$CROSS Backup FAILED for $DB_NAME — check credentials and database name"
  exit 1
fi

# --- Delete old backups ---
echo "Removing backups older than $KEEP_DAYS days..."
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +"$KEEP_DAYS" -delete
echo "$CHECK Cleanup complete"

# --- Show current backups ---
echo "Current backups:"
ls -lh "$BACKUP_DIR"/*.sql.gz 2>/dev/null || echo "No backups found"

That's it. mysqldump → gzip → timestamp → auto-cleanup. Runs in under a second on most databases.

What Each Piece Does

mysqldump -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" — dumps every table, every row, every schema definition into a single SQL file. This is the standard MySQL backup tool and it's been reliable for decades.

| gzip — pipes the dump straight into compression without ever writing an uncompressed file to disk. A 200MB database compresses to maybe 15MB. On a cheap VPS with limited storage, this matters.

date '+%Y-%m-%d_%H-%M-%S' — timestamps every backup file. You get filenames like mydb_2026-05-20_03-00-01.sql.gz. When something breaks, you can see exactly which point in time each backup represents.

find ... -mtime +"$KEEP_DAYS" -delete — the line most people forget. Without this, your backup folder grows forever. -mtime +7 means "modified more than 7 days ago." Adjust KEEP_DAYS based on how much disk you have.

Stop Hardcoding Passwords

The script above works, but DB_PASS="your_password" sitting in a plain text file is a liability. In production, use a ~/.my.cnf file instead:

[client]
user=root
password=yourpassword

Then chmod 600 ~/.my.cnf and remove the -u and -p flags from the mysqldump command entirely. MySQL reads credentials from that file automatically. Nobody sees the password in ps aux output, nobody finds it in your bash history.

Schedule It

Open crontab -e and add:

0 3 * * * /home/user/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

That runs the backup every night at 3 AM and logs the output. If you want it every 6 hours instead:

0 */6 * * * /home/user/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

If you're not comfortable with cron syntax, I built a free cron job builder that generates the line for you — click the schedule you want, copy the output.

Variations I Actually Use

Back up ALL databases on the server:

mysqldump --all-databases | gzip > "${BACKUP_DIR}/all_dbs_${DATE}.sql.gz"

Copy the backup to a remote server (so it survives a full disk failure):

scp "${BACKUP_DIR}/${FILENAME}" backupuser@remote-server:/backups/

Send yourself an email when the backup runs (or fails):

Pair this with my email alert script and you'll get a notification either way.

The Part Nobody Mentions

Test your restores. A backup you've never restored is a backup that might not work. Once a month, spin up a test database and run:

gunzip < your_backup.sql.gz | mysql -u root -p test_database

If that command works, your backups are real. If it doesn't, you've been collecting dead files.

The full script with the line-by-line breakdown, the ~/.my.cnf setup walkthrough, and three more variations is at:

bashsnippets.xyz/snippets/mysql-database-backup.html

If you're running a database on a VPS without automated backups, take 90 seconds and set this up today. Future you will be grateful.