Restoring mysql db from .frm, .MYD, .MYI files

Server Config
1

hi,

I did the absolutely most stupid thing yesterday, I emptied the wrong mysql table via phpmyadmin. Fortunately my host managed to grab a the table in question from a few days ago.

So I have:

auctions.frm, auctions.MYD, and auctions.MYI

My assumption, is that I can just overwrite the current files of the same name in the mysql folder, and everyone will be happy. Is there some more to this process?

2

Yep, that should work. Stop the MySQL service, drop the files in the correct location, start it up again, and the tables should be readable. Might want to run a CHECK TABLE query afterwards.

3

hmm u don’t happen to know how to shutdown mysql through WHM with root access do you?

I went to service manager, unchecked mysql, then copied the files over… re-enabled mysql… nothing… then I did a “graceful server reboot”. It seems the problem is the table contains errors:

SHOW INDEX FROM auctions ;

MySQL said: Documentation
#1105 - File ‘./auctions/auctions.MYD’ not found (Errcode: 13)

as you can see, i’m no server admin, i’m kindof clutching at straws a bit… any clues as to how I could repair this table? I know there is a REPAIR mysql command, but I have no idea what it does.

There also seems to be a lack of documentation on the matter, I guess server admins learn this kindof stuff at school…

4

That error says the file isn’t there, not that there’s an error in it. Did you put the files in the right place?

I don’t use WHM so I can’t help you with that.

5

yes, copied to: /var/lib/mysql/auctions

but it doesn’t seem to be copying over properly possibly due to the fact that I didn’t shut down mysql properly?

The local file auctions.MYD is 437kb whereas the server auctions.MYD file is 17kb. It says it copied, but obviously its not.

I do have command line access to the server (though I have only used it once before), can I turn mysql on and off using that, and is that the recommended procedure?

6

Depends on the OS and where/how MySQL is installed. For RedHat and its derivatives, this will work:

/etc/rc.d/init.d/mysqld stop
/etc/rc.d/init.d/mysqld start

I don’t know what problems you’re having copying the files. Make sure you also set the owner/permissions on the files so the mysql user can read/write them once you have the right ones in place.

7

I think I made a bit of an oversight - the file now looks ok on the server, except that I didn’t, as you said, change the file permissions, it belongs to root instead of mysql. i’m looking into how to change the owner now… i’ll edit this post if I have it solved

EDIT:

YES! YES! YES! fixed! expletive YES!

you could say i’m relieved :slight_smile:

navigated to mysql folder and ran “chown mysql auctions.MYD” to change owner!

BTW, what is the reason for shutting down mysql? is it just in case it is being accessed by someone else at the same time?

8

Stopping mySQL is just a safety thing like as you said just to avoid corruptions, in most cases it will be fine but you will find that removing directorys and working with the directorys in your data-dir without restarting mySQL will cause some “strange” activity from time to time.

You should always repair the tables after restoring the files in the data directory just to be sure.