Using a cron job to backup your WordPress database (you can of course backup any database this way) The objective is to get a backup of the blog database emailed every night so that should the server die you will not have lost anything (or at least not much). It may look a little long but it’s a one-time piece of work and could save your blog. This was originally published here (by me, I’m copying no-one):
Part 2 of this backup plan uses this information too.

Using your ftp client (or file manager) create a directory above public_html called db-backup

Open Notepad / Textedit (not Word or anything similar) and paste the lines below into a new file.

DBNAME=your-db-name
DBPASS=your-db-password
DBUSER=your-db-username
EMAIL="your@emailaddress"
mysqldump --opt -u $DBUSER -p$DBPASS $DBNAME > backup.sql
gzip backup.sql
DATE=`date +%Y%m%d` ; mv backup.sql.gz $DBNAME-backup-$DATE.sql.gz
echo 'Blog mySQL Backup is attached' | /usr/bin/mutt -a $DBNAME-backup-$DATE.sql.gz $EMAIL -s "MySQL Backup"
rm $DBNAME-backup-$DATE.sql.gz

Edit and add your information. If you are using WordPress the first 3 items are in the wp-config.php file.
The line echo 'Blog mySQL Backup is attached' can be edited to add your blog name. Mine for example says echo '69105 mySQL Backup is attached'
Save this as cron.sh and upload it to the db-backup directory. Give it permissions of 700.
This file compresses the database, dates it, emails it to you then deletes the compressed file.
Note #1: The code needs to access the mailer your server uses. On the Ana Lucia server at A Small Orange this is correct: /usr/bin/mutt but if you get errors at ASO or another host you need to ask your host for the right line to use. This is not something I can help with.

Now you need the path.
Download this file http://romanticrobot.net/files/i/abs.zip
Unzip it, upload it to your site then go to where you uploaded it, so for example http://example.com/abs.php
This will give you the path – copy it.

Now to get it all working.
In your website control panel you need to look for cron jobs. In cPanel this is in the Advanced part. When you find it you will need the path. The path above will have said something like
/home/example/public_html/
You have already made a directory above public_html so the path you need will be
/home/example/db-backup/
and what you enter for the cron job is
/home/example/db-backup/cron.sh
Then set the frequency of the backups. Once daily should be enough. Then save the change. You might want to make the cron run a few times just to check so you could make the interval every 10 minutes for instance. But your host will get annoyed if you leave it like that so do set it back to being daily.

And that should be it. Any error messages check the permissions on the cron.sh file, check passwords, check the mailer. If you aren’t sure ask your host – they should have someone in Support who can help. This script does work and once done you can set and forget.

Note#2: Get a gmail account for the backups. They will be coming in every night and will soon start to take up a lot of space. It’s easy to use the Subject line to filter them into an archive.

Note #3: All you really need to backup are the core tables. If you have plugins for stats, geo-ips, spam logs and others that add huge amounts of data then think about just saving the core tables. In this case change this:
mysqldump --opt -u $DBUSER -p$DBPASS $DBNAME > backup.sql
to this (use your own table_prefix. It’s in the wp-config.php file):
mysqldump --opt -u $DBUSER -p$DBPASS $DBNAME wp_users wp_posts [and all the other table names] > backup.sql

Note #4: Every so often download a backup from your email and unzip it. Check it looks the right size at least. If you are able import it into a XAMPP / MAMP install to check everything is there. And if you move hosts or change passwords definitely check.