Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using one mySQL database for several sections of the website and one of the section is not working, when I go to the link of that section it gives the error "1146 Error @ Line 39: Table 'database_name.table_name' doesn't exist"

I logged into PHPmyAdmin and in the list of raws the "table_name" is missing, seems like someone has hacked into it and dropped it.

I have got the following files from the backup server :

  • table_name.frm
  • table_name.myd
  • table_name.myi

I opened all these files into notepad to see the data, frm and myi is understandable may be because they are structures but myd is understandable as it has data.

Now I want to restore the lost data through these 3 files but I don't know how.

share|improve this question

migrated from stackoverflow.com Feb 7 '12 at 17:47

This question came from our site for professional and enthusiast programmers.

    
Duplicate of stackoverflow.com/questions/879176/… ? Also, as stated in that question. probably belongs on serverfault. –  Brenton Alker Feb 7 '12 at 0:51

2 Answers 2

In order to restore the MyISAM called table_name into database_name you must do the following

STEP 01) Run this query : show variables like 'datadir';

This will tell you what the base directory is for data. For this example, let's use the default (/var/lib/mysql)

STEP 02) Copy table_name.frm to /var/lib/mysql/database

STEP 03) Copy table_name.MYD to /var/lib/mysql/database

STEP 04) Copy table_name.MYI to /var/lib/mysql/database

STEP 05) chown mysql:mysql /var/lib/mysql/database/table_name.*

That's it. A mysql restart is not needed because the information_schema database is very sensitive and detects folder changes very quickly.

To make sure the restored table is known to mysql, do the following:

STEP 06) Run this query

SELECT * FROM information_schema.tables
WHERE table_schema = 'database_name'
AND table_name = 'table_name'\G

You should see information about the table size and change dates

STEP 07) Run these commands

use database_name
show tables;

You should see the restored table in the listing

share|improve this answer

Myisam engine holds data in .myd index in .myi and structure in .frm files u can just paste these file in to the datatirectry inside the specific database and restart the mysql service then u can access the data from that table

If in this case some error may accore because of the version change like dy may removed some features or they may added some extra feature in such case you need to run mysql_upgrade utility which is there in bin directry to fix the problems.

Regards Deepak M

share|improve this answer

Your Answer

 
discard

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