Alex Rivera | Logout

MySQL InnoDB database restore

Asked 2008-09-22T15:49:22.957
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?

mysql backup innodb restore
Edit
Report

1 Answer

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

answered 2012-06-17T05:44:36.893

Your Answer