Backing up the WordPress database is an essential part of keeping your blog up and running smoothly. I was looking for a solution that would automatically do a nightly backup for me. I looked at several plugins, some third-party free and paid solutions, and a few manual ones as well.

I eventually settled on just implementing this awesome shell script as a cron job. What’s even more awesome is that the one dependency, mutt, is already installed in the DreamHost environment I’m using to run this blog. Sa-weet! One slight gotcha was that DreamHost also doesn’t run MySQL servers on the same machine as you get ssh access to, so I added one command line parameter, ‘-h myhostname’, to the mysqldump command. Substitute this with whatever hostname you chose for your MySQL database in the Panel. Fill in the rest of the variables like the shell script site says.

Now all I had to do was put this script in my home directory, a little scp foo (set up password-less authentication for ssh if haven’t already done so):

scp ~/Downloads/cronjob2.sh user@host

And lasty, set up a cron job for this bad boy.

ssh user@host
chmod u+x cronjob2.sh
crontab -e

Add the following line to the file (and fill in the mail address if it prompts you, its a good idea):

@daily /home/user/cronjob2.sh

Save, close, exit. Now every night you should get an email with a sql dump of your whole WordPress database. If you don’t want this sitting in your personal email, just set up a new Gmail account and let it accumulate there, free backup storage!