• Home
  • Restore lost data in MySQL using InnoDB engine without file ibdata1

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 innodb_file_per_table=1 after  

# The MySQL server
[mysqld]

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: 

cd c:\xampp\mysql\bin

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;
use 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. 

Comments (0)

There are no comments posted here yet

Leave your comments

Posting comment as a guest.
0 Characters
Attachments (0 / 3)
Share Your Location