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
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:
CREATE TABLE `actor` ( id int, ) ENGINE InnoDB
Stop MySQL to make sure all changes are written to disk.
actor.frm that MySQL has created and replace it with the original
Start MySQL with
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
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 utility from Oracle is an easier and more reliable way.
# 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 a
mysql-utilities package from the MySQL repository.
To install it on CentOS 7, you need to install the
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 an individual .frm file, we restore the structure of with
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.
Press the “Recover structure” button, wait a little bit, and get the recovered structure: