10

I have to restore a database that has been inadvertently DROPped in MySQL 5.0. From checking the backup files, I only seem to have .FRM files to hold the database data.

Can anyone advise whether this is all I need to perform a database restore/import from the backup, or are there other files I should have to hand to complete this?

Jaymie Thomas
  • 812
  • 2
  • 10
  • 22

3 Answers3

15

.frm files are not the data files, they just store the "data dictionary information" (see MySQL manual). InnoDB stores its data in ib_logfile* files. That's what you need in order to do a backup/restore. For more details see here.

tpk
  • 2,041
  • 5
  • 21
  • 29
  • InnoDB requires the ibdata, ib_logfile*, and/or *.ibd files in addition to the *.frm files. – silfreed May 17 '10 at 18:59
  • 1
    what if you only have the ib_logfile* and the .frm files (along with a months old backup)? any chance to recover in that scenario? or do you need the ibdata to recover anything at all? – eglasius Sep 03 '10 at 21:10
12

Restoring innodb: (assuming your data folder is C:\ProgramData\MySQL\MySQL Server 5.5\data)

  1. Copy the folders of the databases (named after the database name) you want to restore to C:\ProgramData\MySQL\MySQL Server 5.5\data

  2. Copy the 3 ibdata files to the data folder ex. (C:\ProgramData\MySQL\MySQL Server 5.5\data)

    _ib_logfile0
_ib_logfile1
_ibdata1

  3. Get the size of the _ib_logfile0 in MB (it should be the same as _ib_logfile1) by File Right click -> Properties

  4. Edit the mysql config file (mysql\bin\my.ini) for the innodb_log_file_size=343M to be exactly the ibdata files size

  5. Run

    mysqld --defaults-file=mysql\bin\my.ini --standalone --console --innodb_force_recovery=6

  6. Now your data should be back in your database. Export them using phpmysql or any other tool

Alexander Craggs
  • 7,874
  • 4
  • 24
  • 46
Ash
  • 789
  • 1
  • 8
  • 24
  • doesn't work for me - in phpMyAdmin I can see InnoDb tables, but can't access them – van_folmert Mar 25 '15 at 13:58
  • Step 3/4 unsure if they did anything. Had to remove the files in-order for it to actually restart mysql. Unsure what step 5 does... But step 1,2,5 and restarted mysql seemed to work! – msponagle May 27 '16 at 04:41
  • Just like @msponagle says. I couldn't complete all the steps but defenetly steps 1, 2, maybe 3 then restart the service and if it wont start try to restart the machine. That worked for me. – ingkevin Jan 09 '18 at 17:18
-2

The detailed solution you can found here:

http://www.unilogica.com/mysql-innodb-recovery/ (Article in Portuguese)

Besides the flag of innodb_force_recovery, I found another solution: innodb_file_per_table, that splits InnoDB tables in each file like MyISAM tables.

In a crash recovery you can lost less data than in single file ibdata1.

Ragen Dazs
  • 2,115
  • 3
  • 28
  • 56