How to setup crontab on dedicated server to dump WordPress MySQL database and email it to a Gmail account

Question

I know I can do a mysql dump for my WordPress databases on my dedicated server. But I want to do this automatically, and I want the dump to be emailed to my Gmail account. Is this even possible?

Answer 1

You can use the wp-db-backup plugin to backup your database to gmail or any other email address.

http://wordpress.org/extend/plugins/wp-db-backup/

Update:

This tutorial explains how to backup and email a wordpress database.

http://www.tamba2.org.uk/wordpress/cron/

Extract from the script

#Set the 4 variables
#Replace what is AFTER the = with the information from your wp-config.php file
#That's your information on the right okay ?

DBNAME=DB_NAME

DBPASS=DB_PASSWORD

DBUSER=DB_USER

#Keep the " around your address
EMAIL="you@your_email.com"

#Change the 'wp_' to match your table_prefix in the database
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 Name: Your mySQL Backup is attached' | mutt -a $DBNAME-backup-$DATE.sql.gz $EMAIL -s "MySQL Backup"
rm $DBNAME-backup-$DATE.sql.gz

A quick tutorial how to setup crontab

http://adminschoice.com/crontab-quick-reference

Answer 2

There are two ways to set up cron.

1. If you are running some sort of web interface, like cPanel, there is almost certainly going to be an easy way to set up cron jobs from there. Look for something called "cron jobs" or "scheduled tasks" or something like that. They will ask you when you want it (e.g. at 4:00am every Sunday) to run and where to email the results.

2. If you have shell access to the server, you may edit the crontab manually. Here is one tutorial of many out there on how to do so. You can edit it directly by typing crontab -e, view it with crontab -l, or save your crontab to a text file and load it in by typing crontab -r the-text-file.

In your case, for option (2), you could enter the following two lines to have it update every Sunday at 4:00 am and email you the results.

0 4 * * 0 command-you-want-to-use
MAILTO=me@example.com

The command you probably want to use is

mysqldump --user=username --password=password databasename

This will dump the database to stdout, which crontab will email to you. The dump will be in the message of the email. You can configure which email to send it to by including a line in the crontab like

If you would rather save the database to a file on the server instead of emailing it to yourself, you could use the command

mysqldump --user=username --password=password databasename > somefilename