Automatic MySQL Database Backups Using CRONTAB

Here is how to set up a cronjob that automatically backs up a MySQL database to a file every hour.

Okay, so first we need to create a new script that will be executed via cron:

YEAR=`date +%Y`
MONTH=`date +%m`
DAY=`date +%d`
HOUR=`date +%H`
mkdir -p $YEAR/$MONTH/$DAY/$HOUR
mysqldump -u root -pPASSWORD-HERE DB_NAME > $YEAR/$MONTH/$DAY/$HOUR/backup_db.sql

Save it as /root/ and give execute permissions with:

chmod +x /root/

Now to schedule it as a cron job open crontab:

crontab -e

and to run it every hour:

1 * * * * root /root/

Just save and exit.
Now an automatic backup of the selected database will be saved /root directory every hour.

Stefan Pejcic
Join the discussion

I enjoy constructive responses and professional comments to my posts, and invite anyone to comment or link to my site.