Import MySQL Table From .frm Files using mysqlfrm MySQL Utilities

Posted 12:30 pm by muni & filed under MYSQL, PHP.

Copying the MySQL data files from one MySQL instance to another MySQL instance is too difficult job. Because MyISAM table engine table will imported easily without any issues. But InnoDB engine tables create in-sync issues, because the InnoDB tables keeps their table structure in .frm file and metadata in .ibd file. So you can’t just move .ibd files to a new MySQL instance. This will create serious pain when moving InnoDB MYSQL data from one MySQL instance to another MySQL instance.

Recovering InnoDB tables from ibd and frm files Using MySQL Utilities

Here we are going to see how to recover InnoDB MySQL table structure from .frm files using MySQL Utilities (mysqlfrm).

The MySQL Utilities comes with lots of tool in it. The mysqlfrm is the one of the tool of the MySQL Utilities, which helps us to recover InnoDB .frm files.

Installing MySQL Utilities (mysqlfrm) in Windows 7/8

The mysqlfrm Utilities is not installed by default if you are using WAMP/XAMPP. So you need to install manually by downloading it from the following page.

https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysql-utils-install.html

I am using WINDOWS operating system, so click on Microsoft Windows Installation instruction and download the MySQL Utilities standalone MSI file an follow the installation instructions.

Once you successfully installed the MySQL Utilities, then add the MySQL Utilities path to Windows environment path to access mysqlfrm or all MySQL utilities commands globally.

Please refer the following tutorial to add Environment variable path in windows 7/8.

http://blog.smarttutorials.net/2016/05/addset-windows-environment-variable-path.html

For me MySQL utilities installation path is in the following path, so I am adding following path in Windows Environment variable.

C:\Program Files (x86)\MySQL\MySQL Utilities\

Now open your command prompt and type the following command in the command prompt. If you are installed the MySQL utilities successfully, then you will get message with the version like this

Now switch it to your MySQL .frm files directory. My MySQL .frm files directory resides in Desktop, so i am switching to that directory using cd command like this.

cd C:\Users\inmayothi\Desktop\smart

Next run mysqlfrm command with following syntax, it will convert the given .frm file into SQL table CREATE query.

mysqlfrm --server=root:PASSWORD@127.0.0.1 DB:FILE.frm  --port ANOTHER_PORT

Here I have just replaced localhost MySQL credentials, and also given .frm file names.

mysqlfrm --server=root:@localhost:3306 invoices.frm --port=3333

the above command will recover table structure from .frm files like this.

It is difficult to copy the above SQL create statement, So in order to directly copy above SQL CREATE statement into SQL file. Change the command like this and run again.

mysqlfrm --server=root:@localhost:3306 invoices.frm --port=3333 > invoices.sql

Now if invoices.sql file does not exists, then it will create the invoices.sql and append SQL CREATE statement in it.

 

Note : It’s too difficult to recover all table structures if number of tables increases more. So we are going to use other way of recovery using mysqlfrm diagnostic mode.

Recovering table structure from .frm files Using mysqlfrm Diagnostic Mode:

This method of table structure recovery will read all the .frm files bit by bit, and finally generates the SQL CREATE statements.

Here following command will read the all .frm files from smart directory and append SQL CREATE statements in the smart.sql file.

mysqlfrm --diagnostic C:\Users\inmayothi\Desktop\smart > smart.sql

For more info refer the following link.

https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqlfrm.html