Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm desperate, and hoping some kind soul can rescue me! I'm dealing with restoring a completely crashed server. Everything is back up except for there's an issue with one domain which uses MySQL (no other domains on the server use MySQL). The server is Windows 2008, running Plesk 9.5.

The MySQL database has been restored (I'm not sure how exactly) but only two of the 8 tables are available (the website can't connect to the other 6, and they are not visible through PHPMyAdmin).

I'm not a server admin, I'm a front-end web developer, and my server admins are telling me that the 6 tables have been 'LOCKED' and aren't accessible. I'm just desperate to find a way to get the data accessible.

Basically, the data has been copied from the crashed server. We have access to the old C drive. On that C drive, the data files for the MySQL database have been copied to the rebuilt server. So on the rebuilt server we have this folder:

C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL\Data

In this folder are the following folders/files:

/my_database
    /tblnews.MYD
    /tblpage.MYD
    /tblhospice.frm
    /tblevent.frm
    /tblpage.frm
    /tblnews.frm
    /tblgallery.frm
    /tbleventcategory.frm
    /tblhospiceregion.frm
    /tbleventregion.frm
    /tblnews.MYI
    /tblpage.MYI
    /db.opt

/mysql
    / *loads of frm, MYD and MYI files, such as 'columns_priv.frm', 'func.frm' etc.

/ib_logfile0
/ib_logfile1
/ibdata1
/my.ini
/Server5797.err
/Server5797.pid

The two tables which ARE working are tblnews and tblpage. These are the ones which have MYI and MYD files too. The 6 tables which don't work are the ones which only have .frm files.

Is there any way I can get the data from anywhere here to restore the 6 inaccessible tables? Some way I can get a working copy on my local PC and script the data out perhaps?

Many thanks for any help anyone can give on this. I truly, truly appreciate it.

share|improve this question

3 Answers 3

up vote 2 down vote accepted

I can confirm it is indeed possible to recover data with only .frm and ibdata1 files. See here: http://www.nerdydork.com/restoring-mysql-innodb-files-on-windows.html. My solution was a little more difficult and my config settings etc were all in different places, but generally that solution worked for me... and saved my life too ;)

share|improve this answer
    
This is under the assumption that the database in question is all InnoDB tables, AND that you haven't set innodb-file-per-table in the my.cnf file. If either of those things isn't true, you are missing data. –  Joel E Salas Oct 24 '12 at 3:03
    
I'm not sure Joel. All I know is that there were only 2 tables accessible before and 6 inaccessible. I was able to recover the 6 inaccessible ones from the .frm and ibdata1 files. It definitely worked and all data is thankfully intact. –  Dan Oct 24 '12 at 7:42
    
I'm glad you got your data back, but it's essential to note that it's purely a coincidence. –  Joel E Salas Oct 24 '12 at 17:02
1  
His 6 other tables were obviously innodb, so this was not coincidence IMHO. –  Ben Ashton Nov 9 '12 at 21:17

The .frm files are only table definitions and contain no data so you can't recover data from them. You will need to get hold of the .MYD files that are missing from which you can recover the .MYI files.

Without the .MYD files or a suitable backup/dump you have no data.

share|improve this answer
    
Thanks Iain. We have the slave drives from the original machine, so every file should be on there. There are just no MYD or MYI files for the 6 'missing' tables though, but it was definitely working up until the crash, so the data must be somewhere. That prompted me to investigate more and I found lots of posts saying that sometimes data is stored in two different ways with MySQL, and it should be possible to recover the data from the ibdata1 and ib_logfile0 and ib_logfile1 files. I'm just struggling to find out how to do that exactly. –  Dan Oct 21 '12 at 14:28

MySQL Utilities: FRM Reader - http://drcharlesbell.blogspot.com/2013/04/mysql-utilities-new-frm-reader-utility.html - http://mysqlblog.fivefarmers.com/2013/04/09/mysql-utilities-meets-the-worlds-ugliest-table/

share|improve this answer
1  
Welcome to Server Fault! Whilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference. –  Mark Henderson Aug 29 '13 at 23:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.