Schedule MySQL Database Backup Automatically using Cron Job

Database backup is one of the most important tasks of server maintenance. Taking database backup in a Linux server manually whenever we want a backup in not for cools users :D. Database backup should be taken periodically in fixed intervals, and we here like to automate out periodic tasks. In this tutorial we will take a look at Cron Jobs and Crontabs in Linux; and see how we can schedule periodic complete Database backups of our MySQL Databases using Cron.
Alright so let’s get into it:

MySQL Database Backup in Linux

1. Install Cron
Cron should be installed as a part of base system, but due to some issues it may not be available for you. Type crontab -a in your terminal and hit enter, if you see crontab command not found then you need to install crontab. If your system is Debian, then you can install Cron using :

sudo apt-get install cron

If you are using a Redhat based system, use:

yum install cronie

Now you should have Cron installed. Let’s go to the next step.

2. Setting up Database backup in Crontab

Now that we have installed Cron installed in out Linux system we are ready to schedule the MySQL Database backup.

Note: If you are not familiar with Crontab/Cronjobs, I recommend watching our 5 minute tutorial : Cron Job.

Usually, you should open your personal crontab file using crontab -e command, however if you are root user and decide to choose system wide crontab file, you need to edit the crontab file located in /etc/crontab. Whatever you choose to do, you should have crontab file open now. To take backup use the following command:

12 30 * * * username mysqldump -umysqlusername -pmysqluserpwd | gzip >/home/directory/to/backup/db_`date ‘+%d-%m-%Y’`.sql.gz

The above commands backs up your all databases everyday at 12:30 AM for the user that you entered after -u in the command above. The last part (db_`date ‘+%d-%m-%Y’`.sql.gz) is the database name. The `date ‘+%d-%m-%Y’` appends the date to db_ so your database bacakup won’t be overwritten, if you create a static name, your latest database back will overwrite previous database backup.

3. More Cron Job Time constraints

Some of the common time constraints that you can add in Cron are:

You can learn about the parameters of Cron job in our tutorial video: Cron Job.

Running Cron job Yearly
@yearly /scripts/script.sh

Running Cron job Monthly
@monthly /scripts/script.sh

Running Cron job Weekly
@weekly /bin/script.sh

Running Cron job Daily
@daily /scripts/script.sh

Running Cron job Hourly
@hourly /scripts/script.sh

Running Cron job on Reboot
@reboot /scripts/script.sh

Running Cron job at 5AM Daily
0 5 * * * /bin/sh backup.sh

Running Cron job at 12AM & 12PM Daily
0 12,24 * * * /scripts/script.sh

Running Cron job Every Minute
* * * * * /scripts/monitor.sh

Running Cron job Every Monday at 2AM
0 2 * * mon /scripts/script.sh

Running Cron job Every 30 Minutes
*/30 * * * * /scripts/monitor.sh

Running Cron job Every 2 Hours
0 */5 * * * /scripts/script.sh

Running Cron job Every 30 Seconds
* * * * * /scripts/script.sh
* * * * * sleep 30; /scripts/script.sh

Running Cron job on Selected Months
* * * jan, mar, april, jun * /script/mons.sh

Running Cron job on Selected Days at 3PM
0, 15 * * sun, wed /script/check.sh

Running Cron job on first Sunday of every Month
0 2 * * sun [ $(date +%d) -le 07 ] && /script/script.sh

Running Multiple Scripts in a Single cron
* * * * * /scripts/script.sh; /scripts/scrit2.sh

So, this is it for this tutorial, you should now be able to backup your MySQL Database using Crontab. If you have any problems, feel free to comment below. Cheers!

Schedule MySQL Database Backup Automatically using Cron Job – Linux Server

Don’t miss these tips!

1 thought on “Schedule MySQL Database Backup Automatically using Cron Job – Linux Server”

Leave a Comment Cancel Reply