CODEWITHSUNDEEP
mysqlbackupinnodbrestore
Jaymie Thomas
Jaymie Thomas

Reputation: 812

MySQL InnoDB database restore

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?

Upvotes: 10

Views: 39220

Answers (3)

Ash
Ash

Reputation: 798

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

Upvotes: 12

Ragen Dazs
Ragen Dazs

Reputation: 2170

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.

Upvotes: -2

tpk
tpk

Reputation: 2131

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

Upvotes: 15

Related Questions