MySQL Backup Cron Script

Do you have a MySQL server that needs a basic backup mechanism with compression and retention? Try this script on for size and see if it fits your needs.

  • Create the backup script:
nano backup_sql.sh
  • Add the following in to the script:
#!/bin/bash
# Variables, adjust as necessary
date=$(date +%Y-%m-%d)
dest="/var/backup"
ret="5" # retention in number of days
pass="password" # mysql root password
# Workin' section
find $dest -type f -mtime +$ret -exec rm {} ;
mysqldump -uroot -p$pass --all-databases |gzip -9 > $dest/backup_db_$date.sql.gz
exit 0
  • Adjust any of the variables for your own environment, particularly the $pass variable
  • Make it executable, chown it to root and lock down the permissions because it has your MySQL root password stored in plain text:
chmod +x backup_sql.sh
chmod root:root backup_sql.sh
chmod 600 backup_sql.sh
  • Move the script to cron.daily so it will run once a day:
mv backup_sql.sh /etc/cron.daily
  • Make sure that /var/backup (or whatever destination you specified in the script variables) exists and only root can access is since it will be storing all of your databases:
mkdir /var/backup
chown root:root /var/backup
chmod 600 /var/backup

This backup method doesn’t support error handling, notification, logging or differential backups so it probably isn’t appropriate a production server unless it is low-priority or there is also another backup system in place.