A happy-end-horror-story the making of.. prerequisites for my recovery plan assuming that you had/have the following InnoDB setting:
inno_db_file_per_table = 1
- A recovery (development/virtual) machine
- MySQL Utilities for schema (CREATE TABLE) recovery
- XAMPPÂ for rebuilding your databases
Create a recovery database
Start XAMPP and create a new database in my case ‘mydatabase’ got moved and stopped working and I created a ‘mydatabase_recovery’ database.
CREATE DATABASE mydatabase_recovery;
Install the MySQL Utilities and navigate to the installation location and open a command prompt:
C:\Program Files (x86)\MySQL\MySQL Utilities\mysqlfrm --server root@localhost --port 3307 D:\var_lib_mysql_from_server\mydatabase\*.frm > D:\create_mydatabase.sql
This creates the create_mydatabase.sql file which contains the original structure CREATE TABLE code for your database. You can now change the file’s content occurences of ‘mydatabase’ to ‘mydatabase_recovery’ and execute it in your new database.
Once you’ve got your tables back do the following for each new table in ‘mydatabase_recovery’, this will delete the (.idb) data file for your new tables.
ALTER TABLE `each_table_name_here` DISCARD TABLESPACE;
Now copy your .ibd files from your copied files to the following directory:
Make sure it has the same user rights and reassociate/import the data files (.idb) with the following code:
ALTER TABLE `each_table_name_here` IMPORT TABLESPACE;
And check your table, if all went well you got your data back!
If you have MyISAM files as well just copy the .MYD (MyISAM data) files and execute the following for those tables:
REPAIR TABLE `each_myisam_table_name`;
And MySQL will fix your .MYI (MyISAM indexes) files, fortunately MyISAM is easier/more forgiving with restoring.
Hopefully you now no longer see the following errors:
Error No. 1033 Incorrect information in file: 'filename' [ERROR] InnoDB: Cannot delete tablespace 91 because it is not found in the tablespace memory cache. [ERROR] Got error 155 when reading table TABLENAME InnoDB: Error: table TABLENAME InnoDB: cannot calculate statistics for table TABLENAME because the .ibd file is missing.