How to recover table structure from .frm files with MySQL Utilities

MySQL table structures are stored in .frm files and in the InnoDB Data Dictionary. Sometimes, usually in data recovery issues, we need to recover those structures to be able to find the lost data or just to recreate the tables.

There are different ways to do it and we’ve already written about it in this blog. For example, we can use the data recovery tools to recover table structures from InnoDB Dictionary or from the .frm files using a MySQL Server. This blog post will be an update of that last one. I will show you how to easily recover the structure from a .frm file and in some cases even without using a MySQL server. This will make the process faster and easily scriptable.

mysqlfrm and MySQL Utilities

MySQL Utilities is a set of scripts released by Oracle that helps us to do some usual DBA tasks in an easier way. It is written in Python and it’s only dependency is the Python Connector. From the large list of utilities, we are going to use mysqlfrm, the tool that will help us to recover the structure.

As usual, an image worth a thousand words. Let’s recover some table structures:

This is the table we have:

So, let’s try to recover that info from the .frm file and let’s see what we can get:

Pretty good result 🙂

It is important to mention that this tool has two different ways to do the recovery.

  • First one is spawning a new MySQL instance and run there the structure recovery, pretty similar to the one PeterZ explained in his blog post. You would need to use –server or –basedir directory along with –port. It will shut the spawned instance down after the recovery is complete.
  • The second one used with –diagnostic reads the .frm file byte by byte to recover all the information possible but without the requirement of a MySQL instance. Therefore, this method can be used to recover all the information possible from damaged .frm files that even MySQL can’t read.

As we can see in the warning of the last example, not all information can be recovered with the second method. For example, character set or collation can’t be recovered without the –server option (first method). Let’s see how to use a spawned server to recover the .frm info:

The tool connects to MySQL server, get all the info it needs (basedir and so on) and it spawns a new instance on port 3307. Then it uses that new instance to recover the info from the .frm file. Fast and easy 🙂

It is worth to mention that not all the information we could need is stored in those .frm files. There are some things that we won’t be able to recover, for example, FK constraints and AI number sequences.


MySQL Utilities is a very useful set of tools. In this particular case, mysqlfrm can be used to recover a large list of table structures from their .frm files, making the process fast and easy to script.

Share this post

Comments (6)

  • Aurimas Mikalauskas

    Blimey! This is really neat tool I did not know about. Something I might be using more often these days.

    Couple of notes:

    1. Peter’s method you linked does not work with some more recent MySQL versions unless you enable innodb-force-recovery.

    2. While the –server method could seem attractive to get things like character set, one has to be aware that if you use the trick that Peter suggested, you are actually going to get a character set of a fake table you have created as this metadata is not going to be coming from the .frm file anyway.

    January 7, 2014 at 5:09 am
  • crokusek

    Nice tip! Didn’t know about mysqlfrom. Saved me some time and was able to recover some lost some tables due to a hardware reconfigure. The dependency on python was not even noticeable using the msi install.

    September 27, 2014 at 2:39 am
  • satheesh


    Thanks for the info.. I recovered the structure… but how can i recovered the data from ibdata file…


    October 31, 2014 at 3:17 am
  • Bimo Seto Prakoso

    Hi Migule,
    I’ve tried your way, but the mysqlfrm shows an error message

    This is what i type:
    mysqlfrm –server=root@ –port 3307 /home/bimo/Documents/FRM/tblbarcode.frm

    # Source on … connected.
    # Starting the spawned server on port 3307 … ERROR: Cannot find location of mysqld.

    Do you happen to know to fix this?


    April 12, 2017 at 11:20 am
  • Dimas Indra

    C:\Users>ERROR: The process “23224” not found.
    mysqlfrm –server=root:root@localhost:3306 e:\xampp\mysql\data\ngacir\media.frm –port=3310
    WARNING: Using a password on the command line interface can be insecure.
    # Source on localhost: … connected.
    # Starting the spawned server on port 3310 …ERROR: The process “9612” not found.

    April 26, 2017 at 12:01 pm
  • Steve Smith

    Xtrabackup needs something like this to restore individual databases/tables since you have to “drop/recreate/discard tablespace” before each table restore. But this solution is still incomplete since it doesn’t preserve FKs! Xtrabackup really needs something built into it to capture the true DDL of the objects it’s backing up. Currently we’ve had to write our own “DDL capture” script that runs after each Xtrabackup backup.

    June 29, 2018 at 11:00 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.