MySQL InnoDB database restore

Question

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?

Answer 1

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

Answer 2

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