• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1258
  • Last Modified:

Recover a database having ibdata and .frm from different dates.

On a Monday morning my database simply stopped working.  I was checking what happened when I realized that all the files from \mysql\data\maindb had disappeared.  Then I got my backup and for my surprise the only folder on it was the \mysql\data\maindb itself.  So now I have the .frm files from Friday night and the ibdata1 from Monday morning.  When I place the frm files the database appears with all the tables with 0 records.  But I know that all the information is in the ibdata1.  There is any thing I can do to recover my data?
0
yurymb
Asked:
yurymb
1 Solution
 
pradapkumarCommented:
the .frm file gives only the table schema. To get the data you need to recover .myd files and to recover index details recover .myi files.

for your reference please visit the following link.

http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html
0
 
yurymbAuthor Commented:
All my tables are InnoDB, so they don´t have the myd and myi.  At least not in the data folder...
0
 
NovaDenizenCommented:
Um, if he's working with an ibdata file he isn't working with MyISAM.  

Do you have the contents of the database named `mysql`?  That has all the usernames, passwords, and permissions set up in it.  By default (well, in 4.1 at least) the mysql database is made up of MyISAM tables, so you need all the .frm, .MYI, and .MYD files from mysql/ to restore those tables.

How do you do your backups?  Do you just copy ibdata while mysql is running?  Do you grab the binlogs too?  

mysqld might not like the fact that the ibdata file you copied into the datadir doesn't match the logfiles or journals.  You might try shutting down mysqld, moving the binlogs & journals out of the datadir, copy the backed-up ibdata into the datadir, then restarting mysqld.

References:
http://dev.mysql.com/doc/refman/5.0/en/innodb-backup.html

You might need to use innodb_force_recovery to finangle a partial dump of your ibdata file if it turns out it is corrupt.  See http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now