Recovering InnoDB MySQL Tables Data from ibdata and .frm Files

First Check Error Logs.

InnoDB: Error: log file ./ib_logfile0 is of different size 0 50331648 bytes

InnoDB: than specified in the .cnf file 0 5242880 bytes!

[ERROR] Plugin 'InnoDB' init function returned error.

[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

[ERROR] Unknown/unsupported storage engine: InnoDB

[ERROR] Aborting

The method explained below will work only for InnoDB database.

Note: First take a backup of all the MySQL files and database.

Contents Of Mysql Data Directory.

drwx------ 2 mysql mysql     4096 Oct 11  2012 performance_schema

drwx------ 2 mysql mysql     4096 Dec 10  2012 ndbinfo

drwx--x--x 2 mysql mysql     4096 Dec 10  2012 mysql

-rw-rw---- 1 mysql mysql       56 Dec 19  2012 auto.cnf

drwx------ 2 mysql mysql     4096 Jul 30  2013 bugs

-rw-r----- 1 mysql mysql 50331648 Mar 18 10:35 ib_logfile0

-rw-r----- 1 mysql mysql 50331648 Apr 22  2013 ib_logfile1

-rw-r----- 1 mysql mysql 35651584 Mar 18 10:35 ibdata1

..

§  Ibdata1 – This file is the InnoDB system table space, which contains multiple InnoDB tables and associated indexes.

§  *.frm – Holds metadata information for all MySQL tables. These files are located inside the folder of the corresponding MySQL database. (for example, inside “bugs” directory)

§  ib_logfile* – All data changes are written into these log files. This is similar to the archive logs concepts that we find in other RDBMS databases.

Copy the Files

 First stop the MySQL server.

# service mysqld stop

Copy the ibdata files, and the database schema folder to some other directory. We will use this to restore our Mysql database. In this case, we’ll copy it to the /tmp directory. The name of the database scheme in this example is Sakila.

cp –r ibdata* ib_logfile* /tmp

cp –r schema_name/  /tmp/schema_name/

Start the MySQL server:

# service mysqld start

Restore the Data

In the my.cnf configuration file, set the value of the following parameter to the current size of the ib_logfile0 file. In the following example, I’ve set it to 48M, as that is the size I see for the ib_logfile0 file when I did “ls -lh ib_logfile0″

innodb_log_file_size=48M

Please note that both the ib_logfile0 and ib_logfile1 file size will be the same.

Copy the previous ibdata files to respective position, inside mysql data directory.

cp –r /tmp/ibdata* /var/lib/mysql/

Create an empty folder inside data directory with the same name as the database schema name that you are trying to restore, and copy the previous .frm files inside this folder as shown below:

cp –r /tmp/ib_logfile* /var/lib/mysql/

cp –r /tmp/schema_name/*.frm /var/lib/mysql/schema_name/

Finally we done,restart the MySQL server.

service mysqld restart