2

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.

Improve this question

2 Answers 2

Reset to default
2

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 ;)

Improve this answer
4
  • 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, 2012 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.
    – Briquette
    Oct 24, 2012 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, 2012 at 17:02
  • 1
    His 6 other tables were obviously innodb, so this was not coincidence IMHO.
    – Ben Ashton
    Nov 9, 2012 at 21:17
1

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.

Improve this answer
1
  • 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.
    – Briquette
    Oct 21, 2012 at 14:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .