How to restore corrupted MyISAM/InnoDB tables on MySQL 5.0

I know that it’s not our job to manage database servers. But if you don’t have a competent crew for that, you have to face the problem yourself.

Well, here is what happened to me this week. Waiting for the end of the day, in which the number of connections to our MySQL server is quite low, I started the creation of a new index on a huge MyISAM table (14 Gb), which is used to store the logs of our systems. While the index was being created, our “network administrator” received some complaints from our users that the systems were to slow. What was his solution? Shut down the server! In this process, logically our huge table was lost and, even worse, the MySQL server couldn’t be restarted at all!

Many of our InnoDB and MyISAM tables were corrupted because of this “little” mistake. So, after some hours of research I discovered a simple procedure that can restore all your data (or at least most of it). This procedure is composed of three steps:

1. Execute the myisamchk software inside your MySQL data directory. If you don’t know where this directory is, check the parameter datadir on your MySQL configuration file (my.ini on Windows, my.cfg on Linux/Unix).

You must use two parameters: –force and –verbose (which is optional) like bellow:

myisamchk --force --verbose */*.MYI

The –force parameter will instruct myisamchk to repair automatically every problem it may encounter on your MyISAM tables, while the –verbose (which is optional) will only show the status of execution of the myisamchk program on your screen.

2. Start the MySQL server with the parameter –innodb_force_recovery
Try to start your MySQL server with this parameter. The value must be a number between 1 and 6. As higher it’s value, worst are the problems on your MySQL tablespace.

Example:

mysqld --innodb_force_recovery=4

If you only got to start your MySQL server with a value bigger than four, your databases will be in read only mode. If not, your users may use the server normally.

If you wish, you may also add the command innodb_force_recovery to your MySQL configuration file as in the example bellow:

[mysqld]
innodb_force_recovery=4

3. After your MySQL server starts, backup your data and then restore them on a new MySQL installation.