MySQL repair deleted data - Any chance of recovery?

Question

Well, this has been a long Saturday morning. MySQL was throwing an error when dumping backups. That error was...

mysqldump: Error 1030: Got error 5 from storage engine when dumping table mytable at row: XXXXXXXX

This is a MyISAM table with 300 million rows of data. It's become corrupt in the past, and MySQL repair would always resolve the issue. Aside from the error during mysqldump, the data appeared normally on the production site.

I assumed it was a similar issue to before, and the table needed to be repaired. The repair command ended unusually fast with the result...

database.mytable repair warning Number of rows changed from 303970410 to 22082981

database.mytable repair Error Error reading file './database/mytable.MYD' ...

database.mytable repair status OK

And just like that, 93% of the data was gone, with the MYD and MYI files replaced. Any idea what might have went wrong here? Any chance of recovering data? I'm assuming no, and I'll have to go with the most recent valid backup, but I'm scratching my head. Appreciate any help.

Edit:

MySQL 5.1.73 CentOS 6.5

Answer 1

myisamchck did its best job, so that may be the maximum what you can get out of mytable.MYD.

The structure of MyISAM record is pretty simple, integers are stored in packed format, strings are prefixed with their length(1 or 2 bytes). So you can write a C program with pre-defined pattern that would try to fetch records of out the MYD file. This approached worked for me on wp_comments table.

Two major problems here:

1. A record may be fragmented. Recollecting the fragmented record in a corrupt MYD seems to be extremely difficult if not impossible problem.
2. Table structure should allow to identify a good record in a stream of bytes.

P.S.

InnoDB is recommended storage engine for a reason.