Automated Backup Scripts

Manual backups are error-prone and easily forgotten. Automating backups with shell scripts and schedulers (cron on Linux, Task Scheduler on Windows) ensures backups happen reliably every day.

A Robust Backup Script

A good backup script should: use a date-stamped filename, compress the output, keep only the last N backups to manage disk space, and log success or failure. Optionally, it uploads the backup to off-site storage (S3, Google Cloud Storage, etc.).

Scheduling with Cron

Add the script to crontab to run nightly. Use a MySQL option file (~/.my.cnf) to store credentials safely rather than embedding them in the script.

MySQL Option File (.my.cnf)

Store the database password in ~/.my.cnf with [client] section so you never have a password visible in scripts or process lists.

Example

#!/bin/bash
# backup_mysql.sh

DB_NAME="myapp_db"
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y-%m-%d_%H-%M)
FILE="$BACKUP_DIR/${DB_NAME}_$DATE.sql.gz"
KEEP_DAYS=7

mkdir -p "$BACKUP_DIR"

mysqldump --single-transaction --routines "$DB_NAME" \
    | gzip > "$FILE"

# Remove backups older than KEEP_DAYS
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$KEEP_DAYS -delete

echo "Backup complete: $FILE"

# Crontab entry (runs at 2 AM daily):
# 0 2 * * * /usr/local/bin/backup_mysql.sh >> /var/log/mysql_backup.log 2>&1

SQL

Full Editor

Pro Tip

Store MySQL credentials in ~/.my.cnf with chmod 600 rather than in backup scripts.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned