Need help in restore some rows into mySQL database frm myd myi

Question

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.

Answer 1

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

Answer 2

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