[thelist] Converting old mySQL database

[John Griffith]

Hi John,

I'm pretty sure the .frm files only store the layout of the tables (as
created by your CREATE statement) and not a lot else. The .myi and .myd (I
think) files contain the actual data. However this is only true of the
ISAM/MyISAM table types. If you were using INNODB for example, the layout of
the tables would be contained in .frm files, but there would be no .myi/.myd
files, instead in the root of the mysql data directory there would be one
very big binary file containing the actual data (it would be called
something like idbdata1). As a result, and whichever way you look at it, I
think all you have is the structure of the tables and not actually any data.

Regards,
John Griffith


-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On Behalf Of
John.Brooking at sappi.com
Sent: 27 October 2004 19:22
To: thelist at lists.evolt.org
Subject: [thelist] Converting old mySQL database

Hi, all,

I did probably a very stoopid thing. To make a long story short, I have a
bunch of *.FRM files from a mySQL database, but not any of the other files.
This was an older version, I don't remember which one, but probably 3.*.
Today I installed 4.0.21 (Windows binary), and am trying to get the database
to recognize these old files. (As you can probably tell, I'm not strong on
mySQL. I installed the prior version two years ago and have since forgotten
most of what I knew about administering it. I know now from the last hour's
reading that I should have used mysqldump, but I didn't.)

I'm pretty sure that the *.FRM files were all there were to save from the
old version; I don't remember seeing any others when I backed those up. I've
also learned from the last hour's reading that the DB engine mySQL uses has
changed several times in the last few versions, so it seems clear that my
old files are from the myIsam or maybe even the ISAM engine. What's less
clear is if I can restore my database from just those files, and if so, how.
I've done some googling, but can't seem to find the right search phrase to
tell me how to do this, so I'm hoping someone here knows. (I have also
posted this on the dev.mysql.com forums.)

I went ahead and created the database using the CREATE DATABASE command,
then copied all the *.FRM files into that directory and stopped and
restarted the server. When I get back in, I can issue the USE command on
that database, but when I tried a SELECT command against one of the tables,
I got "ERROR 1017: Can't find file: 'people.MYI' (errno: 2)". So obviously I
can't use them in their present form. I see in the documentation that I can
specify a TYPE on the CREATE TABLE command, but unfortunately, I don't have
the structure of the tables written down anywhere from which to construct a
CREATE TABLE  statement from scratch.

Can anyone point me in a productive direction?

- John
-- 

This message may contain information which is private, privileged or
confidential and is intended solely for the use of the individual or entity
named in the message. If you are not the intended recipient of this message,
please notify the sender thereof and destroy / delete the message. Neither
the sender nor Sappi Limited (including its subsidiaries and associated
companies) shall incur any liability resulting directly or indirectly from
accessing any of the attached files which may contain a virus or the like. 
-- 

* * Please support the community that supports you.  * *
http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester 
and archives of thelist go to: http://lists.evolt.org 
Workers of the Web, evolt !