Recovering MySQL Database from EXT4 Formatted Hard Disk ...

Hello,

I am trying to recover MySQL databases (which were properly shut down) from an EXT4 formatted hard disk. I loaded the SystemRescueCD distro that you can get online and when running TestDisk I can see the partitions but I cannot recover said partitions because it tells me the structure is bad (any options here, by the way?)

With PhotoRec, I can recover parts of the MySQL Database but I cannot get the important *.MYD files because I guess PhotoRec doesn't have the signatures for that type of file.

So, any options I have at this point?

Thank you for your time.

Do some research on the use of e2fsck to repair damaged ext2/ext3/ext4 file systems. You may be able to return the partition to a mountable state.

Originally Posted by bigrigdriver Do some research on the use of e2fsck to repair damaged ext2/ext3/ext4 file systems. You may be able to return the partition to a mountable state.

This has gotten me closer! Thanks so much.

So, now I used e2fsck and now everything that existed on that partition is in the "lost+found" directory on the root of the partition (at least I assume).

I am grepping in the lost and found directory but is there a better way to find stuff, especially the MySQL databases?

Thanks.

Originally Posted by ckoeber (..) from an EXT4 formatted hard disk.

OK, databases shut down properly, but what happened to the disk? Do you have any backups? If yes, how old?

Originally Posted by ckoeber I cannot recover said partitions because it tells me the structure is bad

Can't say w/o details but running testdisk with the "/debug /log" switches produces a log you can read and attach as plain text.

Originally Posted by ckoeber With PhotoRec, I can recover parts of the MySQL Database but

Which parts?

Originally Posted by ckoeber I cannot get the important *.MYD files because I guess PhotoRec doesn't have the signatures for that type of file.

That's because .MYD files contain the actual record data. If you had an old .MYD backup then in the most desperate situation you could use 'md5deep' in piecewise mode and compare hashes with those of recovered chunks in the hope of salvaging parts but the situation is complicated by write ops performed in the time between deletion and halting disks, file system direct and indirect block allocation, extfs journalling, database compression and what the bottom of this web log post explains about record deletion and reuse... Besides you do need the .frm to load the .MYD. Some more background info:
http://forge.mysql.com/wiki/MySQL_Internals_MyISAM
http://forge.mysql.com/wiki/MySQL_In...s_File_Formats
http://lists.mysql.com/internals/34752

Originally Posted by ckoeber now I used e2fsck and now everything that existed on that partition is in the "lost+found" directory on the root of the partition (at least I assume).

I'm happy to see it worked for you as I would never have suggested using e2fsck w/o creating a disk image first. Running fsck tries to reorder what was already in disarray and IMNSHO allowing write ops on a file system slotted for recovery is a mortal sin.

Originally Posted by unSpawn ... and IMNSHO allowing write ops on a file system slotted for recovery is a mortal sin.

Hard to argue.
Even Ted Ts'o (the ext[234] maintainer) has come around. It used to be you couldn't stop the (potentially bad) journal be run even on a ro mount. Now at least you have the option of not running it - although I wonder how many people know ...

Originally Posted by syg00 Now at least you have the option of not running it - although I wonder how many people know ...

Hmm. Maybe in another language the anagram of "-o ro,norecovery,noload" is "I did read the manual"? ;-p

OK, databases shut down properly, but what happened to the disk?

The disk was a virtual harddrive that was originally on a failed RAID array. I recovered the file after originally needing to get the RAID array back online.

Do you have any backups? If yes, how old?

Yes, but the backups are incomplete due to being poorly configured. I won't be able to recover everything I need from it.

Can't say w/o details but running testdisk with the "/debug /log" switches produces a log you can read and attach as plain text.

The file has been attached to this post.

With PhotoRec, I can recover parts of the MySQL Database but Which parts?

Just the "frm" and "MYI" files. NOT the MYD files...

I'm happy to see it worked for you as I would never have suggested using e2fsck w/o creating a disk image first. Running fsck tries to reorder what was already in disarray and IMNSHO allowing write ops on a file system slotted for recovery is a mortal sin.

That actually was OK since I copied the original virtual disk to a safe location and ran this operation on a copy of the virtual disk in question.

I still have the original VHD file so I can try something different if, based on what I wrote, I should do something else.

Thanks so much for the help thus far.

Also, as a addition to what I wrote, PhotoRec is recovering a lot of stuff from the old system but because it doesn't have all of the MySQL file signatures I don't believe it is recovering the MySQL database files in one block.

If anyone knows a tool that can do what PhotoRec does but recover MySQL files I would be glad to get it! I believe it would be successful if I had something like that.

Originally Posted by ckoeber the backups are incomplete due to being poorly configured.

I'm sorry for the qustions but anything that isn't clear should be explained. Incomplete in what way? Can you at least piece-wise hash the old .MYD and see what percentage of the file can be pinpointed on the disk?

Originally Posted by unSpawn I'm sorry for the qustions but anything that isn't clear should be explained. Incomplete in what way? Can you at least piece-wise hash the old .MYD and see what percentage of the file can be pinpointed on the disk?

Incomplete in that I backed up only parts of the database (certain tables) and not the whole thing. The actual complete backup I can go to is well over a few months old so that isn't an option.

Maybe there's something at http://www.linuxquestions.org/questi...8/#post4600351 you could use.