CODEWITHSUNDEEP
mysqlinnodbdata-recovery
Bimal Poudel
Bimal Poudel

Reputation: 1234

Recover MySQL InnoDB data/structure from .frm (no .myd and .myi, ibdada, my.ini) files

I tried to recover my old innodb databases from the folder copied out of innodb defined tables. There is no binary log file and my.ini file currently. I do not have the ibdata log file at hand. I only need to be able to re-read the table information so that I can remake those lost tables.

I will be also losing the foreign key relationships.

How can I recover the structure of such tables whose data goes into that unavailable log files?

Some helping error messages when I try to access such tables are:

1. Can't find file: 'college_users' (errno: 2 - No such file or directory)
2. Table 'college.college_batches' doesn't exist

Upvotes: 0

Views: 3760

Answers (2)

wiktor
wiktor

Reputation: 1637

There is a program called dbsake and it has a command called frmdump.

Decode a MySQL .frm file and output a CREATE VIEW or CREATE TABLE statement.

This command does not require a MySQL server and interprets a .frm file according to rules similar to the MySQL server.

For more information on how this command works see Description of the .frm format

Example:

$ dbsake frmdump --type-codes /var/lib/mysql/mysql/plugin.frm
--
-- Table structure for table `plugin`
-- Created with MySQL Version 5.5.35
--

CREATE TABLE `plugin` (
  `name` varchar(64) NOT NULL DEFAULT ''  /* MYSQL_TYPE_VARCHAR */,
  `dl` varchar(128) NOT NULL DEFAULT ''  /* MYSQL_TYPE_VARCHAR */,
  PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL plugins';

More details: https://dbsake.readthedocs.io/en/latest/commands/frmdump.html

Upvotes: 3

akuzminsky
akuzminsky

Reputation: 2257

UPDATE: Since then I launched online tool to recover the structure from .frm files. https://recovery.twindb.com/

To recover structure from the actor.frm file do following:

  1. Create dummy table actor in database test(please note on 5.6 number of fields in the fake and the original tables must match):

    mysql> use test
Database changed
mysql> create table actor(id int);
Query OK, 0 rows affected (0.01 sec)

    For 5.6 the create statement would be create table actor(id1 int, id2 int, id3 int, id4 int);. How would you know the actual fields number in your table. Check the error log for following line:

    2014-06-20 03:09:20 3741 [Warning] InnoDB: table test/actor contains 1 user defined columns in InnoDB, but 4 columns in MySQL. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how to resolve it

  2. Stop MySQL

  3. Replace /var/lib/mysql/test/actor.frm with your actor.frm file:

    cp /path/to/my/actor.frm `mysql -NBe"select @@datadir"`/test

  4. Start MySQL with innodb_force_recovery=6

    # cat /etc/my.cnf
...
[mysqld]
innodb_force_recovery=6

    On windows put "innodb_force_recovery=6" to my.ini file under [mysqld] group.

  5. Start MySQL

  6. Read the structure of table actor:

    mysql> show create table actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Upvotes: 1

Related Questions