Automate Daily MySQL Backups with Cron

Manual backups are the ones you forget to run right before you need them. A cron job that runs mysqldump every night, unattended, is a few minutes of setup that pays for itself the first time a migration goes wrong.

The backup script

Create /opt/scripts/backup-mysql.sh:

#!/bin/bash
DB_NAME="mydatabase"
DB_USER="backupuser"
DB_PASS="a-strong-password"
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%F)
 
mkdir -p "$BACKUP_DIR"
mysqldump -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" | gzip > "$BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz"
 
# delete backups older than 7 days
find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" -mtime +7 -delete

Piping straight into gzip avoids ever writing an uncompressed dump to disk — for anything but a tiny database, that matters both for disk usage and for how long the job takes.

Make it executable:

chmod +x /opt/scripts/backup-mysql.sh

A safer way to hold the password

Putting -p"$DB_PASS" directly in the script means the password is visible to anyone who can read the file, and briefly visible in ps aux while the command runs. A cleaner approach is a dedicated MySQL option file:

# ~/.my.cnf  (chmod 600 this file!)
[mysqldump]
user=backupuser
password=a-strong-password

chmod 600 ~/.my.cnf
mysqldump mydatabase | gzip > backup.sql.gz   # no -u/-p needed at all now

mysqldump (and every other MySQL client tool) automatically reads credentials from ~/.my.cnf if it's present, so the password never appears in the script or in ps output.

Scheduling it with cron

crontab -e

Add a line to run it at 2 AM every day:

0 2 * * * /opt/scripts/backup-mysql.sh >> /var/log/mysql-backup.log 2>&1

The >> /var/log/mysql-backup.log 2>&1 part matters more than it looks — without it, cron either emails output nowhere useful or silently discards it, and the first time you find out the backup's been failing for a month is when you actually need one.

Confirm it's actually running

Don't just trust that cron fired — check the log and the backup directory the next morning:

tail -20 /var/log/mysql-backup.log
ls -lh /var/backups/mysql/