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:
1 2 3 4 5 6 7 | 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 |
So, let’s try to recover that info from the .frm file and let’s see what we can get:
1 2 3 4 5 6 7 8 9 10 11 | $ 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | $ 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.
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.
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.
Hi,
Thanks for the info.. I recovered the structure… but how can i recovered the data from ibdata file…
–Satheesh
Hi Migule,
I’ve tried your way, but the mysqlfrm shows an error message
This is what i type:
mysqlfrm –[email protected] –port 3307 /home/bimo/Documents/FRM/tblbarcode.frm
Result:
# Source on 127.0.0.1: … connected.
# Starting the spawned server on port 3307 … ERROR: Cannot find location of mysqld.
Do you happen to know to fix this?
Thanks!!
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.
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.