1. How to Recover Table Structure From FRM Files Online

How to Recover Table Structure From FRM Files Online

Recently we announced MySQL Data Recovery Portal. Here, we automated some data recovery routines and published a matching web interface.

At first, it was a tool to estimate recovery chances from corrupted InnoDB tablespaces.

Now, it’s an even more exciting and useful feature – recovery table structure from FRM files online.

There are several methods to recover MySQL table structure from FRM files.

On MySQL Data Recovery Portal we utilize a combination of them.

Fake Table Method

The idea is to create a dummy InnoDB table, replace its .frm file with an .frm file that we want to recover and run SHOW CREATE TABLE.

Let’s say, we have actor.frm and we want to get the structure of the actor table.

The algorithm is following:

Create a dummy table with the same name, actor. The table must be the same type as it’s encoded in the .frm file. If grep -i innodb actor.frm returns any matches, the table is InnoDB:

Stop MySQL to make sure all changes are written to disk.

Remove the actor.frm that MySQL has created and replace it with the original actor.frm

Start MySQL with innodb_force_recovery=6

The final step is to get the structure with SHOW CREATE TABLE

The benefit of this method is that with little scripting you can recover thousands of tables in reasonable time.

Unfortunately, the hack works only with MySQL version up to 5.5. 5.6 will complain about different number of columns in the .frm file and InnoDB dictionary.

On the Data Recovery Portal tables uploaded in an archive we restore using this method. If, for whatever reason, the structure can’t be recovered this way, it’ll try mysqlfrm.

Mysqlfrm Utility

Using mysqlfrm utility from Oracle is an easier and more reliable way.

mysqlfrm comes with a mysql-utilities package from the MySQL repository.
To install it on CentOS 7, you need to install the mysql-community-release package.

For Debian, check MySQL APT repository page.
When a user uploads an individual .frm file, we restore the structure of with mysqlfrm.

Recover Table Structure From Frm Files Online

The easiest and fastest way to recover the table structure is to do it online on Data Recover Portal.

It can recover either a single .frm file or many .frm files archived in a zip or tar.gz archive.


Screenshot 2015-09-27 23.02.18

Press the “Recover structure” button, wait a little bit, and get the recovered structure:

Screenshot 2015-09-27 23.30.00

Have a question? Ask the experts!

Previous Post Next Post