It sure has been awhile since I’ve updated, things have been pretty busy and I just haven’t had time. Anyway, the most important and often overlooked part of running a dynamic MySQL website is backing up your data often. Losing your file system often doesn’t hurt as much as losing all of your content, especially when running a script that’s easily replaceable like vBulletin, Wordpress, etc. Backing up your data can be a chore, so this is the simple method I use for an automated backup of my databases.

1) SSH in your box

2) Open up your crontab, to do this type:
crontab -e

3) Add the job to your crontab, this is what I use:
30 0 * * * date=`date -I` ; mysqldump -a -uuser -ppassword dbname > /path/to/dump_$date.sql

I’ll break down what’s going on in the above line and what you need to edit

• 30 0 * * * - This specifies the interval in which it will backup your data. Minutes, hours, days of the month, months, days of the week, respectively. In my case, I’m going to be running this every day at 12:30 AM. Asterisk out values which you do not need to limit.
• user - Enter your mysql username here
• password - Enter your mysql password here
• It’s important to note that -uuser is not a typo, you need to prefix -u on your username, so if it’s jsmith, you will enter -ujsmith. Same goes for your password.
• dbname - Enter the mysql database name which you want to backup
• /path/to/dump_$date.sql - Enter the directory you wish to back up your data to, include $date if you want a datestamp on your backup names. Don’t back this up to a web accessible directory as anyone would be able to access your database information and view potentially sensitive data.

Once your cron job is up and running you can then use a 3rd party backup service to automatically pull those backups across onto secure networks at set intervals (ie: every day at 12:40 AM). Talk to your hosting provider as many already provide backup services like this. You can also choose to manually download them onto your hard drive if you prefer a most cost effective approach. Just remember to go in weekly or monthly to delete older backups if necessary - those with large databases may eventually max out their hard drive space if left unattended.