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:

#!/bin/bash
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/backup-cron.sh and give execute permissions with:

chmod +x /root/backup-cron.sh

Now to schedule it as a cron job open crontab:

crontab -e

and to run it every hour:

1 * * * * root /root/backup-cron.sh

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