Dodging a Bullet with InnoDB and MySQL


haloman30

So, last night the forums and main website went offline for a little while. The reason? It wasn't just some typical maintenance this time - rather, it was a frantic effort to try and restore corrupted SQL data.

 

What happened is that last night, my power flickered for just a mere second - and this would end up creating a nightmare scenario that I'd have to untangle. The webserver runs out of my house of course, and the power flicker caused it to briefly shut off. As a result of this power flicker, the SQL server used by the cPanel/WHM VM that hosts the websites themselves became corrupted - and I was initially unsure if I'd be able to get anything back.

 

Luckily, it turns out that we had an automatic backup from just a couple months ago. Along with that, I was able to drag and drop the current databases into a clean mysql folder - and most of them turned out to be undamaged. A handful of tables were refusing to load - as part of the restoration process required a file called ibdata1 from the backup, and several new tables have been created since then that didn't exist before. These tables were the ones that failed to load.

 

Chaotic United's forums didn't suffer from those tables much. Only one table was in that state, and it was just a table for basic caching - nothing important. Elaztek's website however had tables for Stratagem that were added - which is what we use over there (and in CU as well) to track various bugs, features, and overall progress for various projects.

Thankfully, I was able to start up the old, damaged mysql folder with innodb_force_recovery set to 5 - and was able to then dump the stratagem tables for Elaztek, start up the clean and good mysql folder, and reimport them - and it worked. At that point, all tables in all databases were found to be clean with no obvious errors, and I could breathe a sigh of relief.

 

We were extremely lucky to escape this situation unharmed, however it's still partially my fault. Not the power outage, of course - but the fact that the most recent backup was still two months old.

 

You see, WHM (the software under which Chaotic United and Elaztek's websites run within, and used within virtually any webhost plan out there) can (and is) configured to run automatic backups. It was previously set to run backups every other day, once a week, and once a month together. The idea is that we'd have numerous backups available, going back up to 6 months. Then, in the event that a situation like this were to happen - no sweat, just restore the backup from yesterday or the day before.

 

The issue is that there are perpetual disk space issues that I've continued to struggle with, and as such the machine that hosts the webserver is critically low on disk space just like everything else. Backups don't get created if there's less than 5% available - which, as of right now, there isn't.

 

The good news is that Michael is going to help me solve this in the short term - since my long term solution is still nearly a year away. He's going to help me get a UPS for the machine in order to avoid any future power failures, as well as get me a spare 6TB hard disk that I can use to free up some breathing room on both my main PC as well as the webserver. This ought to ensure that this wont be an issue going forward, rather than having this be a potential risk for the next 12 months or so.

 

The other good news is that, once again - we managed to come out the other side effectively unscathed. All data is intact, including the data for Elaztek's stratagem. All databases have been checked over and everything seems to be in order. But of course - in the offchance that you find something that seems as if it could be a sign of database corruption, let me know immediately.

 

Other than that, well - I'm sorry. I apologize for putting us in this situation to begin with, and I'm going to do everything I can to ensure that it never happens again.





User Feedback

Recommended Comments

There are no comments to display.



Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now