MySQL innodb recovery from datafiles

innodbMySQL

Last night, my MySQL server crashed and now I have several databases, which data I'm unable to recover.

According to errorlog, the coruptions in tablespaces happend quite a while ago (few months), but no one notticed that and yesterday, when MySQL crashed (for completely different reason), it just failed to start with following error:

2015-06-09 23:09:23 14642 [Note] InnoDB: Starting crash recovery.
2015-06-09 23:09:23 14642 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-06-09 23:09:24 14642 [ERROR] InnoDB: Tried to read 16384 bytes at offset 0. Was only able to read 0.
2015-06-09 23:09:24 7f648369a7e0  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2015-06-09 23:09:24 14642 [ERROR] InnoDB: File (unknown): 'read' returned OS error 71. Cannot continue operation

When the db was started with innodb-force-recovery set to 6, server started, with tons of those messages in error log:

2015-06-09 23:16:50 16659 [ERROR] InnoDB: Failed to find tablespace for table '"db1234"."tbl456"' in the cache. Attempting to load the tablespace with space id 275772.

I managed to dump majority of databases and import them to clean data directory. But some dbs have corrupted tablespace and I can't dump them (or even open any table in them).

So now i have several (about a hundred) databases, which can't be loaded into live MySQL server. I have their data files (both .frm and .ibd files) but they are somehow corrupted and MySQL can't open them:

2015-06-10 18:37:18 3965 [Warning] InnoDB: Cannot open table db123/tbl456 from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.

I tried to copy data files into another MySQL instance, but with no success, i even tried to create fresh .frm files (but i don't know the structure of the tables), stop the server, replace them with the right ones, start the server and open them – again with no success.

Sadly, i don't have backup of those databases, because they were corrupted long time ago and the backup dumps failed to save any data.

So now i have the data in innodb data files but can't load them into live server. Is there any way how to save them?

I found this toolkit: https://www.percona.com/software/mysql-innodb-data-recovery-tools, but i'm not sure if it is possible for me to use it, its quite complicated and i dont see how to recover a hundred of databases, which structure is unknown to me..

Server uses MySQL 5.6.17, compiled from source.

Best Answer

As the author of Percona data recovery toolkit I recommend you to use TwinDB data recovery toolkit. There is no development in Percona's toolkit last two years and all new features go to TwinDB's toolkit. You case is described in a post Recover Corrupt MySQL Database

To recover table structure you can use mysqlfrm tool or recover the structure from InnoDB dictionary

Related Topic