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

Posted on 2006-06-07
Last Modified: 2010-05-18
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?
Question by:yurymb
    LVL 9

    Expert Comment

    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.

    Author Comment

    All my tables are InnoDB, so they don´t have the myd and myi.  At least not in the data folder...
    LVL 22

    Accepted Solution

    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.


    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
    Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (, A SQLite Tidbit: Quick Numbers Table Generation (…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now