MySQL repair deleted data – Any chance of recovery

myisamMySQLrecovery

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

Best Answer

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:

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

P.S.

InnoDB is recommended storage engine for a reason.

Related Solutions

Mysql – How to recover MySQL table structure from FRM files

If you only see the .frm files, then there is a strong likelihood that the storage engine in use was InnoDB and innodb_file_per_table must have been off by default.

If you transferred everything from datadir on the crashed server onto another disk on another machine, you may be able to startup mysql with that folder as is.

For example, suppose ServerA is your crashed server and ServerB is where you want it placed.

Install MySQL 5.5.8 on ServerB
net stop mysql on ServerB to make sure mysql is down on ServerB
md C:\MySQLData on ServerB
Drop everything from the data folder on ServerA into C:\MySQLData on ServerB
Make C:\MySQLData the new datadir

Add this to my.ini on ServerB

[mysql]
datadir=C:/MySQLData

C:\> del C:\MySQLData\ib_logfile* on ServerB
net start mysql on ServerB

Please try this and tell us what happened

UPDATE 2012-02-03 17:06 EDT

Since you were able to recover everything NOW DO THIS:

mysqldump -u... -p... -A -d --routines --triggers > C:\MySQLSchema.sql

This will give all table structures in the MySQL Instance.

Mysql – Repair with keycache in thesql runs EVERY time. Can’t even log why it’s happening

Your my.conf says the following: key_buffer_size=32M That's really small. You could afford to set it higher: Run this query please:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

This will give you a recommended size of key_buffe_size. Please you common sense when setting this value.

Here is something else: I don't see a bulk_insert_buffer_size. The default is 8MB. Try setting it to 512MB:

[mysqld]
bulk_insert_buffer_size=512M

I just noticed your myisam_sort_buffer_size is only 300M. Set it to 4G (max value)

[mysqld]
myisam_sort_buffer_size=4G

Best Answer

Related Solutions

Mysql – How to recover MySQL table structure from FRM files

UPDATE 2012-02-03 17:06 EDT

Mysql – Repair with keycache in thesql runs EVERY time. Can’t even log why it’s happening

Related Question