Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
    
MySQL Utilities / FRM Reader : mysqlblog.fivefarmers.com/2013/04/09/… & drcharlesbell.blogspot.com/2013/04/… – user27566 Aug 28 '13 at 3:06

I have answered questions like this before

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

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 !!!

share|improve this answer

At TwinDB we built a web interface to mysqlfrm. It's free and easy to use.

To recover table structure from you just need to upload the .frm file.

Here are steps. 1. Open https://recovery.twindb.com/ . Click on "Recover Structure" enter image description here

  1. In next submenu click on "from .frm file" enter image description here

  2. On the next view click on "Browse" and select an .frm file on a local disk. Click on "Upload" enter image description here

  3. Wait till it's doing the magic enter image description here

  4. Get the recovered structure. enter image description here

It's also possible to upload an archive with a bunch of .frm files. The site will recover them altogether.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.