Originally published May 2, 2008, made some formatting adjustments, and bumped this up.
Those who fail to plan, plan to fail. While this aphorism is very worn, it is also very true. Here are some simple things you can do with mysqldump, crontab, tar/gzip and a little contingency planning to insure you don’t lose your sanity when your server crashes upon the shoals of of virtual disaster.
Check out these recent tales of real-life virtual horror as told by a variety of news sources from around the globe:
- The outgoing Italian government posted the entire population’s tax returns on the internet causing a mad scramble which crashed the system.
- Obama supporters were in for a surprise Monday when an attacker executed code on Barack Obama’s Presidential campaign Website that redirected users to Democratic rival Hillary Clinton’s campaign site.
- According to police reports, a computer was stolen from the ADT Home Security branch on Sunbeam Center Drive sometime between April 12th and April 13th.
- Tens of thousands of people were feeling short changed last night after a massive system failure wiped out all the Northern Bank’s ATMs.
- A statewide computer problem again hobbled the state’s digital driver license system on Friday.
The point is, hardware failures, power outages, software bugs, stolen computers, cross site scripted SQL injections, and/or zombie induced denial of service attacks can all turn your church and/or charity website into a tub of techno-mush quicker than you can recurse a binary tree.
The only real defense against such failures is to plan for them – anticipating them in three ways:
- backing up your data
- moving your backed-up data off site
- having and practicing how to restore backed-up data
Here’s a very simple snippet from an oldie but goldie article entitled “How to backup your MySQL tables and data every night using a bash script and cron:”
#!/bin/sh # backup data mysqldump -uroot -ppwd --opt db1 > /sqldata/db1.sql mysqldump -uroot -ppwd --opt db2 > /sqldata/db2.sql # zip up data cd /sqldata/ tar -zcvf sqldata.tgz *.sql # email data off-site cd /scripts/ perl emailsql.cgi
The article also displays a script on how to email the data off site, not a bad deal if your data is small – such backups being just as simple to restore with this dynamic command line duo of directives:
tar -zxvf sqldata.tgz mysql -uroot -ppwd db1 < db1.sql
Things get trickier when you have tons of data, in which it may play into one’s restoration plan better to backup and restore a database by individual tables. Here is a set of articles that describes how to do this that includes some script examples you can modify to suite your needs:
- MySQL: How do I dump all tables in a database into separate files?
- MySQL: How do I import individual table dump files in to MySQL using shell script?
Either way, then it is just a manner of putting the shell script on a timer, or in the vernacular of crontab:
1 3 * * * /usr/home/mysite.com/prvt/tbak.sh > /usr/home/logs/tbak.log
If either of these shell script, bash-based approach seems to complex then perhaps one of the control panel, web-based method offered by UpStartBlogger’s post “8 MySQL Backup Strategies for WordPress Bloggers (And Others)” will do the trick.
Here are some other related articles that might help, the last two include automagic date stamping of the backup files:
- MDLog:/sysadmin – Backup your MySQL databases automatically with AutoMySQLBackup
- Automatic MySql Backup Script – Dagon Design
- Justin-Cook – Automatic cPanel backup (domain & MySQL) with cron & PHP
- Simple MySQL Backup – Holy Smoly
- DreamHost Forum – Crontab/backup
The bottom line is this: just Peter implores us to make a ready defense in 1 Peter 3:15, so I’m asking you always be ready to make a defense to anything that endangers the data that is on your system so you’re not found tearfully dissheveled cowering in a corner meek and fearful, mumbling something about how you should have planned for such failures.
You’ll be glad you did – probably at the most inopportune time possible.
