How to recover table structure from FRM files online

Posted by PDSERVICE on Nov 07, 2019 In

Recently we announced MySQL Data Recovery Portal. Here we automated some data recovery routines and published web interface to them.

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

Now it’s 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 .frm file we want to recover and run SHOW CREATE TABLE.

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

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:

CREATE TABLE `actor` (

id int,

) ENGINE InnoDB

Stop MySQL to make sure all changes are written to disk

Remove actor.frm 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

mysql> SHOW CREATE TABLE actor\G

*************************** 1. row ***************************

Table: actor

Create Table: CREATE TABLE `actor` (

`actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

`first_name` varchar(45) NOT NULL,

`last_name` varchar(45) NOT NULL,

`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`actor_id`),

KEY `idx_actor_last_name` (`last_name`)

) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8

Benefits of this method is that with little scripting you can recover many thousand tables in reasonable time.

A drawback – 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 on whatever reason the structure can’t be recovered this way it’ll try mysqlfrm.

mysqlfrm utility

Easier and more reliable way is to use mysqlfrm utility from Oracle.

# mysqlfrm --basedir=/usr/ actor.frm --user=root --port=33333

# Spawning server with --user=root.

# Starting the spawned server on port 33333 ... done.

# Reading .frm files

# Reading the actor.frm file.

# CREATE statement for actor.frm:

CREATE TABLE `actor` (

`actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

`first_name` varchar(45) NOT NULL,

`last_name` varchar(45) NOT NULL,

`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`actor_id`),

KEY `idx_actor_last_name` (`last_name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

#...done.

mysqlfrm comes with package mysql-utilities from MySQL repository.

To install it on CentOS 7 you need to install package mysql-community-release.

yum install https://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm

For Debian check MySQL APT repository page.

When a user uploads 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 single .frm file or many .frm files archived in a zip or tar.gz archive.

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

You are here

How to recover table structure from FRM files online

How to recover table structure from FRM files online