It was a WordPress site on a %%MATH_BLOCK0%%(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:
```ini
[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:
```bash
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:
```bash
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](https://bashsnippets.xyz/tools/cron-job-builder.html) 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:**
```bash
mysqldump --all-databases | gzip > "${BACKUP_DIR}/alldbs${DATE}.sql.gz"
```
**Copy the backup to a remote server (so it survives a full disk failure):**
```bash
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](https://bashsnippets.xyz/snippets/bash-send-email-alert.html) 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:
```bash
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.