WP-Mix

Here’s a simple, effective, and practical way to keep regular backups of your database using cron. After setting up the cron job, backups of your database are sent to you via email.

Update: Although the following method works as described, I am no longer using it for my own sites. Email is just not a secure mode of transport for sensitive data. The tutorial remains for reference purposes only.

This took a lot of trial and error, but it works great and is easy to implement. Taking care of database backups via cron makes it all effortless and automatic, which saves time because I don’t have to install, configure, and maintain a plugin to ensure current, working backups — they’re sent every night via email and work great. With many sites this can be a huge time-saver. Here’s the magic bullet to make it happen:

0 1 * * * /usr/bin/mysqldump -e --user=username --password=password dbname | gzip | uuencode dbname.gz | mail -s "dbname backup" email@example.com

Before this technique, I was using a database-backup plugin. But I didn’t like having to set 777 permissions for the backups directory just so the plugin could work. Plus, the plugins I’ve seen only backup stuff that’s contained in your WordPress database. On some sites, I have other databases in use and wanted a clean, set-it-and-forget-it method of backing up everything. This cron solution meets all of these needs with the added bonus of reducing the number of plugins required for hands-free administration and maintenance.

Notes:

• In the cron command, edit the following with your own values: /usr/bin/mysqldump (path to sql dump file), username (database user), password (database password), dbname (name of database), email@example.com (your email address).
• The first five characters indicate the minute, hour, day, week, and month. Currently, the code instructs the server to email a backup once daily at 1:00 in the morning. Adjust as needed.