How do you extract data from mysql frm,myd, and myi files?

Question

Level 4

3,537 points

How do you extract data from mysql frm,myd, and myi files?

I have a /Library/MySQL folder with a lot of important mysql information. MySQL does not recognize it. Is there a way that I can extract the data into the traditional CSV or XML format so that I can reinstall mysql and reimport the database/tables/data?

Please help someone! I have some important information stored in these files and would like to get my databases and tables restored.

Thank you in advance for and help!

Posted on Sep 11, 2005 8:00 PM

Reply

Answer 1

thepotter

Level 4

1,070 points

Sep 13, 2005 12:18 AM in response to mahongue

First off, make a backup of the files.

I know I've tried the following before a long time ago in Red Hat -- just can't recall if it worked or not! 😉

Anyway, first thing I would try would be to make a brand new mysql install (same version used to make the data of course) -- preferably on a different computer, but definitely in a location other than your current install. I'm not sure how scattered mysql gets in OSX but you'd want to hunt down all the parts -- I suppose renaming the old files/dirs would be easier than installing in a different location. It seems you have a couple computers though -- do the install on your other machine. Then copy over the folder that has your database with the data you want, and you probably also want to copy over the folder with the mysql table data. Hopefully, you can access your data.

At which point a plaintext backup would be in order:

mysqldump important_db > important dbbackup.sql

to read the data back into mysql:

mysql important_db < important dbbackup.sql</div>

see: http://dev.mysql.com/doc/mysql/en/mysqldump.html

Reply

Answer 2

Sep 13, 2005 4:32 AM in response to mahongue

Thats the best way to do it, however one thing to note is you must fool mysql slightly.

Before you copy the files back from your backup location into whereever the data is stored (on my machine it is /usr/local/mysql/data) you must create the database in mysql :

create database <name>;

This then creates a subfolder in the data folder and you can copy all the frm, myi and myd files into there.

Then you can use mysqldump to remove the data.

Reply

Answer 3

mahongue Author

Level 4

3,537 points

Sep 13, 2005 2:36 PM in response to Andrew Weddle

Dear Andrew,

Thank you so much for your post. Your advice pointed me in the right direction and I figured out how to restore all of my data.

Here is what I ended up doing, which was ultimately successful.

1. Reinstalled a fresh mysql installation.
2. Created a new empty database in mysql for each of my databases. This is the step that I was missing. I thought that copying the folder, whose name is the same as the database name, and which contains the .MYI, MYD, and .frm files, into the folder /Library/MySQL/var it would work. It didn't. Perhaps MySQL needs to be told about the database as well.
3. Went into Terminal and
a. changed the owner of the database folder to my own user name (a temporary necessity to get access to the files inside).
b. copied the .MYI, .MYD, and .frm files into this folder.
c. changed the permissions of these files with: chmod 660 *.
d. changed the owner and group of these files to mysql with: chown mysql:mysql *.
e. remodified the owner and group of the database folder with: chown mysql:mysql databasename.

I did this for all of my databases (to many!). But it all worked and the data is restored!

Thank you for pointing me in the right direction. I was surprised that there was no documentation for this in the MySQL manual, or no other information on the internet.

Reply

Answer 4

mahongue Author

Level 4

3,537 points

Sep 13, 2005 2:39 PM in response to thepotter

Dear thepotter,

Thank you for your post. It turns out that the mysql data from my installation is stored in the /Library/MySQL/var folder. After much tweaking of permissions, and by creating blank databases with the names of the ones I wanted to recover, I was able to restore all my data. See below for a more detailed description.

Thank you for your post. I think next time I will use mysqldump! Come to think of it, let me make a dump right now!

Reply

Answer 5

thepotter

Level 4

1,070 points

Sep 13, 2005 9:48 PM in response to mahongue

It makes sense that you'd have to make new empty databases. The mysql directory naming is a little confusing to use when talking, but the "mysql-folder" contains a database of your databases (and users, permissions, etc.). It is awfully easy to say "mysql database" -- which can mean the mysql system, or the database it keeps of all its databases and such.

mysqldump is a good friend indeed. As I'm sure you'll see, it'll dump the schemas and all the data in plain text. In a pinch, you can even open it up in a text editor and search about for things -- something I've had to do a couple times when I had the data, but not the database system.

Anyway, I'm really glad to hear you got your data back. It's such a sickening feeling to realize that important information may be lost.

Reply