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:
Have a question? Ask the experts!
The post How to recover table structure from FRM files online appeared first on Backup and Data Recovery for MySQL.