If you don’t understand the title of the post, then there’s no need to read further, just to store this code in my blog for future reference.

This guide is to be used to create a cron job to perform automatic backups of MySQL database which is gzip-ed automatically and stored in dates.

1. First a *.sh file is to be created by any text editor, in this example I’ll name it “backup.sh”.
2. Create a folder on your site (should be outside of public_html so that it’s safer) to store the backups, in this example the folder is “backup”
3. Here are the codes, modify the /path/ into the corresponding paths (it might differ, for me it’s /home/domainname/.. for it to reach my home directory, check with your hosting company if you are not sure with the path name), save the following codes into *.sh file, i.e. backup.sh

#! /bin/bash
# Automated database datestamp backup

# Initialise directory, make sure it’s the full path to the location you wish to create the backup, e.g. /home/domainname/backup/
cd /path/

# MySQL dump the db, gzip, then rename into dates
mysqldump –opt -Q -u Username -pPassword dbname >> ./dump.sql
gzip -f ./dump.sql
mv ./dump.sql.gz ./`date –iso-8601`.gz

# Clean up gz files more than 2 days in the same directory, modify/delete this according to your needs
find . -mtime +2 -type f \( -iname “*.gz” ) -exec rm -rf {} \;

4. Now upload your backup.sh file into the backup folder (not necessarily into the same folder as where the backups are going to be made, but to simplify matters we’ll upload backup.sh file into the same folder)
5. Login to your cPanel and enter “Cron jobs”.
6. Create a new cron job, setup the intervals (should be easy) and in the command tab, enter:

sh /path/backup/backup.sh

If this command line doesn’t work, please contact your host provider for the proper command.

7. Try the cron job by setting the interval at 5 minutes to see whether any error is generated.

Figured this out from this site. I’m no linux user, please do give comment on the codes if you find anything wrong.