October 25, 2014

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

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.

MySQL Utilities and mysqlfrm

MySQL Utilities are 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 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.

Conclusion

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.

About Miguel Angel Nieto

Miguel joined Percona in October 2011. He has worked as a System Administrator for a Free Software consultant and in the supporting area of the biggest hosting company in Spain. His current focus is improving MySQL and helping the community of Free Software to grow. Miguel's roles inside Percona are Senior Support Engineer and Manager of EMEA Support Team.

Comments

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

  2. crokusek says:

    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.

Speak Your Mind

*