If you use MySQL DBMS (DataBase Management System), it could happen to lose or accidentally delete the ibdata1 file located in MySQL data folder.

This file is very importante, since it holds the system tablespace of all the INNODB tables. By enabling innodb_file_per_table option, you can store data and index pages outside ibdata1 file, creating a single .ibd file for each table, also recommended since ibdata1 filesize keeps growing over time.

So, if we’re lucky and we enabled such option in the past, we could recover our data.

Thus, you can restore everything if you are in the following scenario:

• ibdata1 file is missing
• you have all the *.frm e *.ibd database files in a backup folder, plus db.opt file

Let’s see what to do. Assume your database is named nullalo and you have a backup copy in the /backup/data/nullalo folder: the first thing you need to do is creating a new database with the same name in MySQL. This database will be used as target for tables recovery.

After creating the empty database, you need to recreate damaged DB tables. To do this we must first extract the tables schema from the *.frm files, by using an Oracle tool, MySQL Utilities (current version is 1.5.6) that can be downloaded here:

http://dev.mysql.com/downloads/utilities/

Once download and installed this tool, available for all platforms, we wil be able to use mysqlfrm command, that needs to be launched from the folder containing *.frm files (/backup/data/nullalo in the example) with the following syntax:

mysqlfrm --server=root:rootpassword@localhost mydb:mytable.frm --port=3307

Let’s explain how to build the command: rootpassword is your root user’s password to access DBMS with all privileges, mydb is the database name to which the tables to be recovered belong,  mytable.frm is the name of the specific table you wish to extract the schema and 3307 is a port for the spawned server started by the command – this port must be different from the one used by your MySQL instance (usually 3306).

At this point, the command will return the CREATE TABLE statement, needed to recreate the table, that must be executed in MySQL on the empty nullalo database previously created (I recommend using a client like MySQL Workbench, SQirreL o HeidiSQL). A command output sample is the following:

C:\backup\data\nullalo>mysqlfrm --server=root:rootpassword@localhost nullalo:user.frm --port=3307
# Source on localhost: ... connected.
# Starting the spawned server on port 3307 ... done.
# Reading .frm files
#
# Reading the user.frm file.
#
# CREATE statement for user.frm:
#

CREATE TABLE `nullalo`.`user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
`ssl_cipher` blob NOT NULL,
`x509_issuer` blob NOT NULL,
`x509_subject` blob NOT NULL,
`max_questions` int(11) unsigned NOT NULL DEFAULT '0',
`max_updates` int(11) unsigned NOT NULL DEFAULT '0',
`max_connections` int(11) unsigned NOT NULL DEFAULT '0',
`max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
`plugin` char(64) COLLATE utf8_bin DEFAULT '',
`authentication_string` text COLLATE utf8_bin,
`password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'

#...done.

Now you have your table back, but still no data, since it’s located in the .ibd file. To restore table data we will execute the following steps:

1. execute command

   ALTER TABLE mydb.mytable DISCARD TABLESPACE;

2. copy mytable.ibd file (mytable must be obviously replaced with the name of your table) from backup folder to database folder (ie. C:\MySQL_5.6.10\data\nullalo)
3. execute command

   ALTER TABLE mydb.mytable IMPORT TABLESPACE;

Now your table is fully recovered! Repeat the steps for all the INNODB tables and you’re done, as you can check by executing a query on one of the restored tables.