CODEWITHSUNDEEP
mysql
Tower
Tower

Reputation: 102945

How to restore data from MySQL .frm?

I have all data in MySQL's .frm files. How can I restore the data? I do not want to restore all of them, just some records and tables so I need to make dumps out of them.

From what I can see, there are only .frm files, no .myd files. There is, however, ibdata1 file. How am I supposed to restore?

Upvotes: 2

Views: 13240

Answers (2)

RAAJA KING
RAAJA KING

Reputation: 189

Very Simple Steps.

  1. In Xampp Control Panel Click on config of MySql then select "my.ini" file.

  2. In this file add a line innodb_file_per_table=1 after [mysqld] line.

  3. Now, I created a same database which you want to recovery like name as exam_table

  4. Create one by one table which you want to restore like name as exam_time.

  5. Run SQL query/queries ALTER TABLE `exam_time` DISCARD TABLESPACE;

    Caution: This deletes the current .ibd file.

  6. Copy the backup exam_time.frm & exam_time.ibd file to the appropriate database directory.

  7. Run SQL query/queries ALTER TABLE tbl_name IMPORT TABLESPACE;

Check Your exam_time table its recovered .....Hurrahhh

Upvotes: 1

Tower
Tower

Reputation: 102945

I got it working.

1) I created an empty database named after what the real database was on the server in my local installation.

2) I killed "mysqld"

3) I copied the three ib* files to my local MySQL data directory (on Windows it was a hidden folder in root drive). Make sure you copied to InnoDB data file directory, depending on your my.cnf InnoDB and MyISAM data may be stored in different folders. I also copied the .frm files.

4) I ran "C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin\mysqld" --defaults-file="C:\Program Files (x86)\MySQL\MySQL Server 5.1\my.ini" --innodb-force-recovery=6

5) I ran mysql -uroot -pmypass to confirm use mydb; select * from mytable; returned results.

6) I used mysqldump mydb mytable --compact > file.sql

That's it!

Upvotes: 6

Related Questions