Software Web Development

MySQL bustage

So my dev instance of MySQL crashed with some InnoDB corruption (as in service won’t even start). Still no idea exactly what caused it, but I don’t really care. The only database using InnoDB wasn’t anything I actually used, just junk that should have been cleaned up anyway. The downside is that I couldn’t even get MySQL running. So here was my repair technique once I realized there wasn’t an ‘easy button’:

First always have a backup of your data. Just in case you screw up your repair attempt.

I deleted the directory containing the tables for the database I knew to be fubar‘d. And restarted MySQL not as a service but as a app using:

mysqld-nt.exe --verbose

Yes! It’s alive. Kinda. Still giving some errors, due to it’s inability to find tables (due to me deleting the corrupt database), and other related garbage. I don’t really care, I just want to fix the ones I do care about.

So next I make a dump of the entire server:

mysqldump --all-databases -u root -p > database.sql

Enter root password at the prompt. Sit back, relax, and hope things go well. It’s slow.

Now I decided to completely uninstall MySQL (and delete the data directory) and reinstall the latest version. Why not get the latest patched version? Lean, mean and up to date.

Now to get my data back

mysql --u root -p < database.sql

Again enter root password, sit back and relax. A few minutes later the process is done. Restart MySQL and all looks good.

This isn’t the only way around things, and may not even be the best, but it’s worked for me.