How can extract the table schema from just the .frm file?

Question

I have extracted the data directory of mysql from a backup and need to get the schema from an old table, however it's a backup from a different machine.

I have read a fair number of tutorials today on how to do this, but each time it seems to fail or I end up having to reinstall mysql because mysql hangs or crashes. I have tried the following:

• Create a different database
• Create table with same name in that database
• Replace file
• Stop/Start engine
• Recover from the .frm file

I have tried in various orders and combinations.

Is there any external tool that can extract the schema from the .frm file? I can see the column names if I open up the file. I had a look but I can't seem to find anything that will enable me to do this.

Thanks in advance.

Answer 1

I have answered questions like this before

• Dec 21, 2011 : Table compression in InnoDB?
• Feb 03, 2012 : How to recover MySQL table structure from FRM files
• Apr 23, 2012 : restore table from .frm and .ibd file?

I have referred to a blog from Chris Calendar on connecting .ibd files back to the data dictionary.

If you do not have the .ibd along with .frm, chances are MySQL was running with innodb_file_per_table off. Please refer to the Feb 03, 2012 post on that. If this is Linux, make sure you run

cd /var/lib/mysql
chown -R mysql:mysql *

before starting mysql.

Give it a Try !!!

SUPPLEMENTARY INFO

• Percona's mysqlperformanceblog.com has information on this from over 4 years ago
• MySQL Utility Team has the tool mysqlfrm.

At the bottom of that last blog it says:

Ok, I'm Hooked! Where Can I Get It?

The .frm reader utility is part of the new release-1.3.0 Alpha available as a separate download at http://dev.mysql.com/downloads/tools/utilities/. Simply choose the platform repository or source repository and download it.

We welcome your comments and hope that this utility will help expand your diagnosis and recovery toolkit.

UPDATE 2013-06-11 14:05 EDT

I have good news. I installed the Windows version of the MySQL Utilities. I tested the mysqlfrm program from the command line. I tested getting the .frm info from the user.frm in the mysql schema. Here is what I got:

C:\MySQL_5.6.10\data\mysql>mysqlfrm --server=root:rootpassword@localhost mysql:user.frm --port=3307
# Source on localhost: ... connected.
# Starting the spawned server on port 3307 ... done.
# Reading .frm files
#
# Reading the user.frm file.
#
# CREATE statement for user.frm:
#

CREATE TABLE `mysql`.`user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `plugin` char(64) COLLATE utf8_bin DEFAULT '',
  `authentication_string` text COLLATE utf8_bin,
  `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'

#...done.

C:\MySQL_5.6.10\data\mysql>

It really works. The database is running on my desktop computer using port 3306. I had to give 3307 as a port to run a spawned process.

I could move the .frm from a Linux box to the Windows machine and do the same.

Give it a Try !!!