Preventing MySQL Database Crashes

May 5, 2005 | Posted by Sapphire

After a recent database crash nightmare, caused by a former host’s configuration errors, I would love to find a way to test all my hosts. From the research I’ve been doing the past couple of days, it appears that databases are corrupted by server settings and issues, not end users like me, or even the software applications using the database. I’m sure there are exceptions, but every page I’ve found relates error causes relating to libraries on the server, shell commands, etc. Also, overheated servers that need to be restarted, or have the databases on them flushed.

In other words, most of the causes and solutions are out of your hands. That means you’re relying on your host to keep things working properly. And you have no way of knowing about a mistake on your host’s end until something breaks on your end. By then, it might be too late to fix it.

So how do you know your host’s got things running properly? Obviously, if they’re letting their servers overheat or something like that, you can’t know. But as for basic host configurations, there is one thing you can try. It’s not simple – if I find one that is, I’ll pass it on – but if you’re at all concerned about a host’s ability to hold your MySQL databases intact, here’s what I did that should have given me a big, big clue early on.

When my blog database crashed the first time, as soon as I was able to repair it, I copied it over to a spare domain I keep for trying out new apps and stuff. I installed the blogging software there, and hooked it up to the newly repaired database. It didn’t work. I assumed I’d done something wrong.

For once, I was wrong to assume I was wrong. It didn’t work because the old host had something funky going on – something that would eventually go on to corrupt my database in an even bigger way.

If you have the exact same php-driven software app on two servers, and a database created by one of them won’t run on the other, there is something wrong with one of your servers. In my case, I know the spare domain host is good – I’ve had many databases running flawlessly on it for a long time. And the database had already behaved suspiciously on the old host. That should have told me right there.

It may sound like a lot of work to do something like this, but I highly recommend it it you have any suspicious behavior on a server. Just a few simple mistakes on your host’s part can destroy a database beyond repair, and that’s something you want to avoid at all costs.

Related posts:

Category Categories: Development & Design | Tag Tags: | No Comments »