How To: Automate your MySQL backups using cron in Linux

I will try to break the myth on how hard Linux is, how complicated is to set-up crons and of course, how to save your time automating your website backups without using 3rd party software or scripts. Now, if you’re not familiar with this, the cron actually helps you automate any task or Linux command easily. For our current task, we first need a bash file, where we will write the database backup command (MySQL).

First, we need to create the file and using Linux as our dedicated server, we will simulate a remote connection. So, the file will be named backups.sh and we will use the VI editor.

vi backups.sh

Now we need to write the MySQL backup command within this file:

#!bin/sh
/usr/bin/mysqldump -A -u [Username] -p[Password] | gzip > /backups`date +%m_%d_%y`.gz

Now close the file, by pressing ESC then : x to save the file.

You need to test the script, so execute it to see if it creates a backup of the database in the /backups directory:

sh backups.sh

The final step is to add the bash file into the crontab file, so it can be launched at any given time, in our case, daily.

Open the crontab list using crontab -e then add the following line using the VI editor: 0 7 * * * /path/backups.sh

Note that /path/ is the location where you have saved the backup bash file. Also, you need to be sure backups.sh won’t give any permission errors, so change its permissions to 755:

chmod 755 backup.sh

Now you are all set, you have your own automatic MySQL backup script.