Automated cron backup thru email

This is a process to automate the backup of the Mambo MySQL database and send the backup data to an offsite destination for safe storage through email.

I was looking for an automated, simple and fast way to keep my database safe. Specifically, I wanted to find:

1) A fast way to backup my data.

2) Easy process to transfer the data out of my web host server to an external destination.

3) Automate the whole process.

The program is tested on CPanel on the Linux/Unix operating systems. It uses Linux commands such as gzip, tar, mutt and mysqldump. Install the program and test it out. If it does not work, ask your web host administrator if the required Linux commands are available (usually are), or he/she can install them for you.

The program may also work for other control panel mangers or Operating System, if you know the corresponding commands and replaces them accordingly. You can also use this method to automate backup of other databases, files or directories.

Updates and New Releases

1) I have released a new version (cron_backup_1.2) that allows the user to select either "uuencode + mail" or "mutt" to send the compressed files to an email address. However, "mutt" is always preferred.

2) cron_backup will work with Joomla!, Mambo, PostNuke, WordPress and etc. The backup commands and strategies are the same. This is about the lowest-level (Operating System) backup strategy around.

3) I have also listed this project at SourceForge, as the original Mambo project has now forked into two projects: Joomla! and Mambo. Read more about this here.

Program file

1) Get the program file at SourceForge or MamboForge. The "--quote-names" option for mysqldump is required to enclose table and field names with backquotes. This is because some of the names are MySQL reserved keywords and will generate errors when you try to load back the backup data. Took me some time to test and figure this out!

Instructions

1) This program works for CPanel on Linux/Unix operating systems.

2) Set the 4 variables - DBNAME, DBPASS, DBUSER, EMAIL.

3) Can use a free email for the EMAIL variable, as the file may be huge. E.g. Yahoo! Mail or GMail. Currently, the file attachment limits for GMail is 20MB and Yahoo! Mail is 10MB.

4) Upload this program file into /home/{yoursite}/etc/cron_backup.sh private dir; not in dir like /home/{yoursite}/www/cron_backup.sh where everyone can access.

5) Chmod (change the file permissions of) cron_backup.sh to 711 (-rwx--x--x).

6) Setup a cron job to auto execute this program. See http://en.wikipedia.org/wiki/Cron on how to do this, if you do not know.

Tips

1) TO TEST: Set cron schedule to "* * * * *", it will run EVERY minute. Note that your web host time may be different from your LOCAL time. Comment out "rm $STATUS" at end of the program file.

2) FOR PRODUCTION: Set cron schedule to "55 23 * * *" the suggested backup time. Daily backup at 23:55 hour, web host time. Uncomment "rm $STATUS" at end of the program file.

3) Test backup data by doing a database restore on another PARALLEL/TEST website using CPanel phpMyAdmin.

4) Login EMAIL once a while (e.g. every 6 months) to delete redundant backups to free up space.

5) Ask for help or discuss your solutions at the Mambo forum.

Setup CPanel Cron job

Your web host CPanel has many functions. Other control panel mangers include DirectAdmin, Ensim, Plesk, vDeck and others. Very often, they function in similar ways and you can map the following steps accordingly if your control panel manager is not CPanel. Note that we can also use the following steps to automate backup of other databases, files or directories.

Here, we are going to use a cron schedule to do the backup automatically daily. Why do things manually when we can program a robot to do them automatically?

In your CPanel, click on “Cron Jobs” (see Point 1). Then select “Standard”.

Setup the schedule as follows. You can also use a free email for the email address field. Click “Save Crontab”. Click “Advanced” to see your cron schedule again. You should see something like "55 23 * * *". Change it to "* * * * *" for testing. REMEMBER to reset it back after testing.

You should receive an email with the backup data as an attachment file in about 1 minute when testing. Download and save the attachment to your local drive. Always verify that the backup data is good and the process is smooth!

Click on “MySQL Databases” (see Point 2). Create an new empty database if you do not have a parallel website (install another new Mambo website) for testing the backup data.

Click on “phpMyAdmin” (see Point 3) hidden right at the end of the form. Select the new empty database OR the parallel test database. DO NOT WORK ON YOU LIVE DATABASE!

Click on the “SQL” tab. At the “Location of the text file” field, browse and select your attachment file. For “Compression”, select “gzipped”. Click “Go”. The backup data (which are actually SQL queries and insert statements) should run and load in your test database successfully.

What if things don’t work?

Don’t panic. You did work on the TEST database right? Look through the instructions here carefully and try again. You can also search at Mambo forum, most problems have already been encountered and solved by others previously.

Thank you

If you find this solution useful and wish to send a small contribution as a gesture of your appreciation, please click on the button below. You can also consider linking to our website, link instructions here (Read my suggestions on Mambo Weblinks here). Thank you and have a great day!

P.S. Thanks to Marek Dylewicz from the MamboPL.com Team, this article is now translated into Polish at http://www.mambopl.com/content/view/232/48/