咨询微信： dbservice1234 7 x 24 在线支持！

首页

下载 DBRECOVER

How to recover table structure from FRM files online

How to recover table structure from FRM files online

Posted by PDSERVICE on 11月 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.
 
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: