Mysql – Need help in restore some rows into theSQL database frm thed thei

myisamMySQLrestore

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.

Best Answer

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

Related Question