Restore lost data in MySQL using InnoDB engine without file ibdata1
If by any chance you moved your MySQL files by copy pasting or you lost you ibdata1 file and your MySQL database is now not working, you may encounter this problem that when you go to phpMyAdmin you are abel to see the database and all the tables, but after you click on the table name you get the following error:
MySQL error - #1932 - Table 'table_name' doesn't exist in engine.
In this case, you can follow these steps to help you restore your database from you .FRM and .IBD files.
In this example, I'm assuming you are using your localhost and XAMPP installed on a Windows machine.
1- Enable innodb_file_per_table
Go to C:\xampp\mysql\bin and open my.ini. Add
# The MySQL server
Save and close my.ini.
2- Install MySQL Utilities
You can download that from https://dev.mysql.com/downloads/utilities/
3- Open MySQL Command
Open your Command Prompt and open MySQL command inside MySQL bin. To do so, type in the following in the Command Prompt:
Hit enter. Then type:
mysql -u root -p
Hit enter. It will ask for your password. Enter your password and then hit enter.
4- Create and select new database
In command prompt, type the following lines and then hit enter after each line (test is the name for the new database. You can use anything you like.):
create database test;
5- Create a MySQL file
You can use the MySQL utilities to create a TXT file from the FRM. But I've noticed that people are having problem and getting some different types of error with this method. Likely there're is a website that will do this for you for free. Here is what you need to do:
A- Go to https://recovery.twindb.com/
B- In left column, click on Recover Structure. Then click on From .frm file
C- Click Browse and find the .frm file for the broken table. If you have multiple tables, you can upload all of them as a zip file.
D- Click Upload.
E- A pop up screen with generate your SQL command for creating the table structure. Copy everything in that screen and paste in the command prompt, then hit enter. This will create one .FRM and one .IBD file for each table.
6- Discard IBD files
Back in Command Prompt, type:
ALTER TABLE filename DISCARD TABLESPACE
This will delete the IBD file associated with that specific table. If you have multiple tables, then you need to repeat this step for all tables.
7- Get the IBD files
Go to the original (old) database directory and copy the .IBD file for each table and paste them in the new database (test) directory. You find this directory in C:\xampp\mysql\data\test
8- Import data in to the tables
In Command Prompt, now enter the following:
ALTER TABLE finename IMPORT TABLESPACE;
Congratulations. You are done. You just restored your broken table. You can now to to phpMyAdmin and navigate to your new table with all the data intact.