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

January 2, 2014

Author

Miguel Angel Nieto

Insight for DBAs

MySQL

Share this Post:

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:

CREATE TABLE `new_table` (<br>  `id` int(11) NOT NULL AUTO_INCREMENT,<br>  `name` varchar(45) DEFAULT NULL,<br>  `age` tinyint(4) NOT NULL,<br>  PRIMARY KEY (`id`),<br>  KEY `name_idx` (`name`)<br>) ENGINE=InnoDB DEFAULT CHARSET=latin1

1	CREATE TABLE `new_table` (<br> `id` int(11) NOT NULL AUTO_INCREMENT,<br> `name` varchar(45) DEFAULT NULL,<br> `age` tinyint(4) NOT NULL,<br> PRIMARY KEY (`id`),<br> KEY `name_idx` (`name`)<br>) ENGINE=InnoDB DEFAULT CHARSET=latin1

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

$ mysqlfrm --diagnostic /usr/local/mysql/data/test/new_table.frm<br># WARNING: Cannot generate character set or collation names without the --server option.<br>[...]<br><br>CREATE TABLE `test`.`new_table` (<br>  `id` int(11) NOT NULL AUTO_INCREMENT,<br>  `name` varchar(45) DEFAULT NULL,<br>  `age` tinyint(4) NOT NULL,<br>PRIMARY KEY `PRIMARY` (`id`),<br>KEY `name_idx` (`name`)<br>) ENGINE=InnoDB;

1

$ mysqlfrm --diagnostic /usr/local/mysql/data/test/new_table.frm # WARNING: Cannot generate character set or collation names without the --server option. [...] CREATE TABLE `test`.`new_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `age` tinyint(4) NOT NULL, PRIMARY KEY `PRIMARY` (`id`), KEY `name_idx` (`name`) ) ENGINE=InnoDB;

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:

$ mysqlfrm --server=root@127.0.0.1 --port 3307 ./new_table.frm<br># Source on 127.0.0.1: ... connected.<br># Starting the spawned server on port 3307 ... done.<br># Reading .frm files<br>#<br># Reading the new_table.frm file.<br>#<br># CREATE statement for ./new_table.frm:<br>#<br><br>CREATE TABLE `new_table` (<br>  `id` int(11) NOT NULL AUTO_INCREMENT,<br>  `name` varchar(45) DEFAULT NULL,<br>  `age` tinyint(4) NOT NULL,<br>  PRIMARY KEY (`id`),<br>  KEY `name_idx` (`name`)<br>) ENGINE=InnoDB DEFAULT CHARSET=latin1

1

$ mysqlfrm --server=root@127.0.0.1 --port 3307 ./new_table.frm # Source on 127.0.0.1: ... connected. # Starting the spawned server on port 3307 ... done. # Reading .frm files # # Reading the new_table.frm file. # # CREATE statement for ./new_table.frm: # CREATE TABLE `new_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `age` tinyint(4) NOT NULL, PRIMARY KEY (`id`), KEY `name_idx` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

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.