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 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:

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

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 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.

 

Screenshot 2015-09-27 23.02.18

Press “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
  • David Borrink

    Alexsandr, I have 11 .frm files and 11 .ibd files that I want to restore into a usable database in a WAMP setup that I uninstalled and reinstalled. There is also a “db.opt” file as well. WAMP people informed me that I would need to restore my database before I could use it again.

    If I try to create a table called ‘wordpress’, how would it have the 11 .frm files that I need to replace? Your instructions don’t explain how the newly created tables would be in there. I’ve never used the MySQL utilities, so this is all new to me.

    I ran all 11 .frm files through your portal and saved them as new .frm files with the same name, so I assume I’ve done that right. I’m not understanding the procedure for how to do all this. Or which method above will restore my database.

    • David,

      Do you need to recover tables structure one or structure + data?

      • David Borrink

        To help you understand my situation, I have a MySQL 5.6.12 folder with a “data” folder inside that contains a folder called “wordpress” which is the name of the database. In that folder are 11 .frm files and 11 .ibd files of all the WordPress tables, plus a “db.opt” file. In the “data” folder are also 34 “mysql-bin.0000xx” files plus a “mysql-bin.index”, an “auto.cnf” file and a “Sallie.pid” file (my wife’s name). That’s what was left by an uninstall of WAMP on my wife’s PC. The Aestan tray menu failed, and I thought an uninstall/reinstall would solve the problem, but it left my data in a state that isn’t connecting with WAMP again, and WAMP is on 5.6.17 now. But if I can restore the 5.6.12 database, then WAMP will recognize it again.

        I know all the data is there. The .idb files are sometimes over 10 mb.

        You’ve talked about this on Stack Exchange, so I’m trying to see if I can follow all the tips you’ve left on a few posts over the last few years. I’m seeing information in three or four topics over there.

        Someone pointed me to this post today. I thought I was getting close, but I’m confused as to …

        1) how I would create the new ‘wordpress’ database – needing to know the commands to use and which MySQL Utility to use

        2) how the 11 .frm files would be in this new database so I can replace them with the originals, or would that be the .ibd files?

        3) and would the MySQL utilities put everything in it’s proper place in my ‘wordpress’ folder so that WAMP will recognize it?

        So that’s my situation. I’ve never dealt with database restore before. I’m not afraid to try this, as long as I know I’m getting the right directions. Everything I’ve read so far is like a jigsaw puzzle and I have a few pieces missing. I know it works, but I’m not there yet with my understanding.

        I have backup copies of my files, by the way.