Tech by PC

To backup Divine!

Good thing I back up my sites every night. Yesterday with one fell swoop I wiped out every one of my MySQL databases, and had to restore from last night’s backup. Fortunately this happened early in the morning and there was little lost. My wife lost some comments and I lost some configuration changes. The comments can’t be replaced, but the configuration changes were easily redone, although I haven’t finished all of them yet.

I had to modify my backup script due to some ISP blocking and some unreliability. My ISP, Atlantic Broadband, recently expanded its top end service from 5mbps to 8mbps which wouldn’t change much except that they also increased the lower tiers as well. I have had unreliable service ever since. SSH sessions frequently disconnect and there are periods of up to a minute at a time where web browsing doesn’t even work well. Instead of using SSHFS or rsync, both of which no longer work reliably, I SCP the files home after they are created. This failure was human error with me yelling out half a second after hitting the button in phpMyAdmin, so none of the files on the server were lost. Recovery was as simple as gzip and pushing mysql dump files back into the correct databases. Fortunately my gaff didn’t allow me to remove the permissions tables or it would have been a much longer recovery process as I hunted through numerous scripts for passwords.

On an upside I feel like performance has improved. I lend server space to a friend of mine who uses it to record data from a datalogger. His database is web accessible and he probably lost several hours of data. His database was by far the largest.

What did I learn? Only log into phpMyAdmin with enough privileges to do what you need to do, and none else. I was setting up a new mediawiki installation for another friend and wanted to reinstall since we were moving from test to production. I wanted to drop his tables only but ended up dropping everything. Had I been logged in as his database user I would not have had access to do that. Nothing I didn’t already know, but now I will practice it. At least I learned my lesson in backups years ago.

Some things to look at for the future for me are restoring from transaction logs – I assume that can be done – so that I can restore to a more immediate point in time. My quick and dirty backup still works right now, but I’d hate to lose a whole post, or worse still a whole post on my wife’s blog.

This entry was posted on Sunday, October 5th, 2008 at 10:29 am and is filed under Software. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.