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.
Top comments (2)
“A backup you've never restored is a backup that might not work” is probably the most important line in the entire post.
Also painfully relatable 😭
Every small VPS starts with:
“it’s temporary, I’ll automate it later”
and somehow later never arrives until production achieves spiritual enlightenment and disappears.
That line came directly from pain 😂
Nothing humbles you faster than realizing your “backup strategy” was basically:
good intentions
vibes
a folder named backup-final-FORREAL.tar.gz
And you’re absolutely right — every tiny VPS starts as “temporary.” Then six months later it’s somehow hosting production, cron jobs, three side projects, and your remaining sanity.