This guide walks you through creating a simple Bash script that dumps and compresses a MySQL database nightly, then automatically removes older archives so your backup folder never overflows. It explains how to make the script executable, add a cron line that runs at 02:30 AM, log its output, and manually test the dump by checking both the file’s presence and the log for errors. Common pitfalls such as wrong credentials, insufficient disk space, and cron’s limited environment are highlighted, along with quick fixes like using a dedicated backup user or adjusting ownership of the backup directory. Finally it shows how to restore a compressed backup in one command so you can confirm that your recovery plan works before an actual data loss occurs.

Automate backup MySQL via Cronjob: Keep Your Data Safe Without a Daily Phone Call

When the database hiccups, you’re not just losing data—you’re losing time and trust. This guide shows how to set up a cron‑driven script that dumps your MySQL tables every night, compresses them, and stores them in a safe folder. No more manual mysqldump runs or forgotten backups.

1. Write a Simple Dump Script

#!/bin/bash
# /usr/local/bin/mysql_backup.sh

DB_USER="root"
DB_PASS="your_password_here"
DB_NAME="mydatabase"
BACKUP_DIR="/var/backups/mydatabase"
DATE="$(date +%Y-%m-%d_%H%M)"
FILE="$BACKUP_DIR/${DB_NAME}_$DATE.sql.gz"

# Make sure the backup directory exists
mkdir -p "$BACKUP_DIR"

# Dump, compress, and store the file
mysqldump -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" | gzip > "$FILE"

# Optional: keep only the last 7 backups
find "$BACKUP_DIR" -type f -mtime +7 -delete

Why this matters:

• The script is plain Bash, so you can tweak it without learning a new language.
• Compressing on the fly saves disk space—especially handy if your tables grow fast (I once had a 1‑GB database that doubled each month).
• The find … -delete line prevents the backup folder from filling up; after all, an endless archive is less useful than a tidy set of recent files.

2. Make the Script Executable

chmod +x /usr/local/bin/mysql_backup.sh

If you forget this step, cron will silently skip your job and you’ll discover nothing worked when you finally look for the backup file.

3. Add a Cron Entry

Run crontab -e and insert:

30 2   * /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backups.log 2>&1

What’s happening?

• The job runs at 02:30 AM, a time when most users are offline.
• Output is appended to /var/log/mysql_backups.log; this log will show success messages or errors so you can spot problems early.

4. Verify That It Works

1. Run the script manually once: ./mysql_backup.sh.

2. Check that a file appears in /var/backups/mydatabase.

3. Look at /var/log/mysql_backups.log for any warnings about permissions or MySQL errors.

If the log shows “mysqldump: Got error: 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)”, you’ve probably got a typo in $DB_PASS. Fix it and re‑test.

5. Common Pitfalls & Fixes

• Wrong MySQL credentials – Use a dedicated backup user with only SELECT and LOCK TABLES privileges to avoid accidental schema changes.
• Disk full – The cron job will silently stop when the partition is out of space. Keep an eye on /var/backups/mydatabase or set up alerts for low‑disk thresholds.
• Cron environment differences – Variables like $HOME aren’t set in cron, so always use absolute paths (as shown above).
• File permissions – If the backup directory is owned by root but you run MySQL as another user, the script may fail silently. Set chown root:root /var/backups/mydatabase or adjust ownership accordingly.

6. Restore a Backup

To test your recovery plan:

gunzip -c /var/backups/mydatabase/mydatabase_2023-09-15_0200.sql.gz | mysql -u"$DB_USER" -p"$DB_PASS" "$DB_NAME"

If the restore finishes without errors, you’ve proven that the backup is usable. If it hangs or complains about table locks, tweak your script to use --single-transaction with InnoDB tables.

There’s nothing more reassuring than a cron job that reliably backs up MySQL on autopilot. Now you’re set—just hit save, monitor the log occasionally, and enjoy that extra peace of mind while you focus on building features instead of chasing data loss. Cheers!