0

One of my machine got crashed last day and i ended up reinstalling windows on that machine. It had a mysql server in it. Is there a way to recover mysql data from that machine now . There are couple of tables which has some important data for which backup was not there.

any help would be greatly appreciated.

Improve this question
2
  • 1
    You shouldn't have done anything after the crash. If you formatted the disk and reinstalled windows, they're most likely gone for good.
    – Kayaman
    Jul 2, 2018 at 9:03
  • If the volume where MySQL databases were stored was formatted during re-install process, all data is lost with a guarantee. If this volume was not re-formatted, there is a (extremely) minor probability to recover raw datafiles using any euristic recovery software (moreover, recovered datafiles may contain wrong data). So I think you may forget about those data.
    – Akina
    Jul 2, 2018 at 9:05

1 Answer 1

Reset to default
2

Before you do anything make sure nothing can overwrite data on the disk. stop all writes to the disk ASAP. Unmount, take it out of a server - whatever is applicable and feasible. Plug the disk in a 64bit Linux machine.

Then you need to take an image from this disk. I prefer dd for the job. It's available everywhere, stores the image as it is.

 dd if=/dev/sdb of=/path/on/sda/faulty_disk.img  conv=noerror

Where /dev/sdb is the disk with data and /path/on/sda/faulty_disk.img is a path on another disk, big enough to store the image. conv=noerror will ignore disk read errors if you get any.

If you prefer to store the image remotely - check out https://twindb.com/take-image-from-corrupted-hard-drive/ , it describes more options.

Then download and compile Undrop-for-InnoDB. Then follow steps from tutorial:

You need to find InnoDB pages on the image. The stream_parsertool does it:

./stream_parser -f /path/on/sda/faulty_disk.img

The result of the tool will be a directory pages-faulty_disk.img that contains InnoDB pages sorted by page type and index_id. InnoDB stores tables in indexes. The table itself is stored in the PRIMARY index, secondary indexes are stored separately. To recover the data we need to fetch records from PRIMARY indexes. Now, InnoDB identifies indexes by index_id while we, humans, know table names. To find index_id for each table you will need to restore InnoDB dictionary first. You can check the blog post or script recover_dictionary.sh from the undrop-for-innodb toolkit.

Now, let's say you found index_id for each table and there is a respective index file in pages-faulty_disk.img/FIL_PAGE_INDEX/<index_id>.page.

To extract records from the index you need to know the table schema, because InnoDB doesn't store the schema in the index. I hope you have the schema either from old backups, or maybe from the installation script. Worst case you can try to restore the schema from InnoDB dictionary.

When you have the schema and index_id you can generate text dumps from the index.

./c_parser -6f pages-faulty_disk.img/FIL_PAGE_INDEX/0000000000000015.page -t sakila/actor.sql > dumps/default/actor 2> dumps/default/actor_load.sql

Obviously, sakila.actor is just an example. Then review the text dump dumps/default/actor and you can load it with an SQL command stored in dumps/default/actor.sql. The tool's page also refers to some other tutorials which may be useful. Data recovery is quite unpredictable so you have to get familiar with the tool and process.

Hope this helps.

Improve this answer
2
  • Does it matters that the computer in the OP's question was running Windows?
    – RDFozz
    Jul 2, 2018 at 18:17
  • It doesn’t because Innodb tablespaces binary compatible across all platforms
    – akuzminsky
    Jul 2, 2018 at 19:34

Not the answer you're looking for? Browse other questions tagged or ask your own question.