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.