Automating MySQL Database Backups with Cron Jobs: Complete Guide
The quick answer: Automate MySQL backups by creating a shell script with `mysqldump`, saving it as a `.sh` file, and scheduling it with a cron job. This guide shows you exactly how to set this up in under 10 minutes—no manual backups needed.
Database backups are critical for developers, DevOps engineers, and anyone running production MySQL instances. Manual backups fail when you forget. Automation ensures your data is safe, recoverable, and consistent. With cron jobs, you can run backups daily, hourly, or at any custom interval.
This guide covers everything from writing the backup script to testing and restoring your data. You’ll also learn pro tips to avoid common pitfalls like corrupted dumps or disk space issues.
## Why Automate MySQL Backups?
Manual backups are unreliable. When servers crash, data gets corrupted, or someone accidentally drops a table, you need a recent, working backup. Automation removes human error.
Key benefits:
- Consistency: Backups happen on schedule, every time
- Time savings: Stop logging in to run `mysqldump` manually
- Disaster recovery: Restore from the latest backup in minutes
- Version history: Keep multiple backup generations for rollback
## Prerequisites: What You Need
Before diving in, ensure you have these basics:
| Requirement | Details |
|---|---|
| Linux/Unix server | Any distro (Ubuntu, CentOS, Debian) with cron installed |
| MySQL/MariaDB | Running database with user credentials |
| `mysqldump` utility | Included with MySQL client packages |
| Root or sudo access | To edit crontab and create scripts |
| Storage space | Enough disk for backup files (monitor usage) |
If you need pre-built DevOps scripts to speed up this setup, check out the developer tools at freefile.store.
## Step 1: Create the MySQL Backup Script
Write a shell script that uses `mysqldump` to export your database. Here’s a production-ready example:
“`bash
#!/bin/bash
# Backup configuration
DB_USER=”root”
DB_PASSWORD=”your_secure_password”
DB_NAME=”your_database”
BACKUP_DIR=”/var/backups/mysql”
DATE=$(date +%Y-%m-%d_%H-%M-%S)
BACKUP_FILE=”$BACKUP_DIR/$DB_NAME-$DATE.sql”
# Create backup directory if it doesn’t exist
mkdir -p $BACKUP_DIR
# Run mysqldump
mysqldump -u $DB_USER -p$DB_PASSWORD $DB_NAME > $BACKUP_FILE
# Compress to save space
gzip $BACKUP_FILE
# Remove backups older than 7 days
find $BACKUP_DIR -type f -name “*.sql.gz” -mtime +7 -delete
echo “Backup completed: $BACKUP_FILE.gz”
“`
Save this as `mysql_backup.sh` and make it executable:
“`bash
chmod +x /path/to/mysql_backup.sh
“`
Security tip: Never hardcode passwords in scripts. Use a `.my.cnf` file with restricted permissions (chmod 600) instead.

Photo by Jakub Zerdzicki on Pexels
## Step 2: Set Up the Cron Job
Cron is a time-based job scheduler in Unix-like systems. To edit your crontab, run:
“`bash
crontab -e
“`
Add one of these lines depending on your schedule:
### Common Backup Schedules
| Schedule | Cron Expression | Description |
|---|---|---|
| Daily at 2 AM | `0 2 * * *` | Best for most applications |
| Every 6 hours | `0 */6 * * *` | For high-traffic sites |
| Hourly | `0 * * * *` | Critical data only (watch disk space) |
| Weekly on Sunday | `0 3 * * 0` | Low-traffic or staging environments |
Example for daily backup at 2 AM:
“`bash
0 2 * * * /path/to/mysql_backup.sh
“`
### Testing Your Cron Job
Manually run the script first:
“`bash
bash /path/to/mysql_backup.sh
“`
Check the backup directory for the `.sql.gz` file. Then verify cron logs:
“`bash
grep CRON /var/log/syslog
“`
Pro tip: Redirect output to a log file for debugging:
“`bash
0 2 * * * /path/to/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1
“`
## Step 3: Automate Multiple Databases
If you have several databases, modify the script to loop through them:
“`bash
#!/bin/bash
DB_USER=”root”
DB_PASSWORD=”your_password”
BACKUP_DIR=”/var/backups/mysql”
DATE=$(date +%Y-%m-%d)
# List all databases (excluding system ones)
DATABASES=$(mysql -u $DB_USER -p$DB_PASSWORD -e “SHOW DATABASES;” | grep -Ev “(Database|information_schema|performance_schema|mysql)”)
for DB in $DATABASES; do
mysqldump -u $DB_USER -p$DB_PASSWORD $DB > $BACKUP_DIR/$DB-$DATE.sql
gzip $BACKUP_DIR/$DB-$DATE.sql
done
“`
This is ideal for multi-tenant applications or when you manage several projects.

Photo by Startup Stock Photos on Pexels
## Step 4: Verify and Restore Backups
A backup is useless if you can’t restore it. Test your restore process regularly.
### Restore a Single Database
“`bash
gunzip -c /var/backups/mysql/your_database-2024-01-01.sql.gz | mysql -u root -p your_database
“`
### Automated Verification Script
Add this to your cron job to send alerts on failure:
“`bash
# After mysqldump, verify file size
if [ -s “$BACKUP_FILE.gz” ]; then
echo “Backup size: $(du -h $BACKUP_FILE.gz | cut -f1)”
else
echo “ERROR: Backup file is empty!” | mail -s “Backup Failed” [email protected]
fi
“`
Important: Schedule a monthly manual restore test in a staging environment. This catches corruption or schema changes that break your backup.
## Pro Tips for Production Backups
### 1. Use Compression Always
Raw SQL dumps are huge. Gzip reduces size by 80-90%.
### 2. Rotate Old Backups
Set a retention policy (e.g., 7 days for daily, 30 days for weekly). The `find -mtime` command in our script handles this automatically.
### 3. Offsite Backups
Copy backups to cloud storage (S3, Dropbox) using `aws s3 cp` or `rsync`:
“`bash
aws s3 cp $BACKUP_FILE.gz s3://your-bucket/backups/
“`
### 4. Monitor Disk Space
Add a disk check before running backup:
“`bash
USAGE=$(df / | awk ‘NR==2 {print $5}’ | sed ‘s/%//’)
if [ $USAGE -gt 90 ]; then
echo “Disk full: $USAGE% used. Backup skipped.”
exit 1
fi
“`
### 5. Lock Tables for Consistency
For InnoDB, use `–single-transaction` to avoid table locks:
“`bash
mysqldump –single-transaction -u $DB_USER -p$DB_PASSWORD $DB_NAME > $BACKUP_FILE
“`

Photo by Ron Lach on Pexels
## Common Issues and Fixes
| Problem | Solution |
|---|---|
| Cron not running | Check `cron` service status: `systemctl status cron` |
| Permission denied | Ensure script is executable and backup dir is writable |
| mysqldump not found | Install MySQL client: `apt install mysql-client` |
| Backup file empty | Verify database name and credentials in script |
| Disk full | Reduce retention days or increase storage |
## Conclusion
Automating MySQL database backups with cron jobs is a must for any serious developer or DevOps engineer. You now have a complete, production-ready setup that runs on schedule, compresses files, rotates old backups, and even sends alerts on failure.
Stop risking your data. Implement this guide today and sleep better knowing your databases are safe.
For more ready-to-use DevOps scripts, automation tools, and developer resources, visit freefile.store. Download pre-built backup scripts, monitoring tools, and deployment templates that save you hours of setup time.