Splash of Style...Macs, Photography, Design, and other Passions

Cron Jobs & Backing Up Database Data

I have been backing up my mySQL databases manually through phpMyAdmin, and although it is an simple, straightforward way to backup, I really wanted to find a more reliable, automated backup system for my database data.

After a remind on an email list, I decided to learn a little more about Cron Jobs. First I googled “cron job backup database” and found an interesting script for backing up Wordpress.

Wordpress backup with Cron

From that page, I also found a more robust MySQL backup script:

AutoMySQLBackup from SourceForge.net

I read the basic instructions, then downloaded the .sh file. I thought the file was fairly easy to configure; make sure to scroll down the page for detailed instructions.

It looked like a simple enough process, but I did have a little trouble. My Notes:

I uploaded the .sh file in a new directory above my public web files. Important! Make sure that you do not upload this file to your public_html directory, as it is possible that the file could be accessed there.
I changed the .sh file permissions to 755.
I created a new directory where the backed up database files will be saved. Make sure that the directory is referenced correctly in the .sh file. This is where I kept having trouble, so I finally set an absolute path to the backup directory.
BACKUPDIR="/home/username/backup"
Set permissions to 755 for backup directory.
I checked in my /etc directory, and did not find a daily cron folder, so I experimented with my own cron job. In my hosting account’s cpanel, at the bottom category “Advanced Tools”, choose Cron Jobs.
Choose the “Standard” button for a wizard-like set-up.
Type in a valid email address. If there is an error in the script, it is very helpful to receive an email notice.
Type the path to the .sh file in the “Command to run” text box.
./directory/automysqlbackup.sh.2.5 - change directory to your specific name. Also, make sure to include the exact file name. If you are using a version of the script different than “2.5″ you must reference the specific file name, or rename the file to “automysqlbackup.sh”
To test the script, I suggest a temporary setting of every 5 or 10 minutes. It will keep running while you troubleshoot the errors. Once the script runs successfully, remember to change the setting back to “0″
Once you receive an email showing the completed process, open the backup directory. There should be newly created directories for daily, weekly and monthly. Inside the daily directory, look for individual database sub-folders, with compressed .sql files.
Regularly download the database compressed files. I plan on downloading my weekly files.
If it is ever needed, the .sh file also includes instructions for restoring your database.

This looks like a really useful script, and it will make my backups so much simpler and less time consuming!

Update: This morning, I received an email letting me know that the 3AM backup was successful; I checked the backup folder on my hosting account, and found a backup file for Thursday.

Permalink |

There are 2 comments

Very useful and simple. Thanks.
Comment by fiLi on September 24th, 2006
thanks it helps…….. it saves my lot of manual time in backup..
Comment by subeesh on July 2nd, 2008

Splash of Style...Macs, Photography, Design, and other Passions

Cron Jobs & Backing Up Database Data

There are 2 comments

Leave a Comment

What's New?

Archives

My Links

Favorite Links

More Favs

Subscribe