how to restore mysql db content?

[abcz800]

 

 

Could someone please let me know how the db content under mysql on a server that is not bootable could be retrieved!? I have access to the files on the hard drive but don't know which files to copy over and how to make the new installation read the old content.

 

Any help would be greatly appreciated.

 

Was running mysql 4.0.14 on Apple Darwin - would be reformatting the hard drive and upgrading to Mac 10.5 (leopard)

 

Thanks in advance.

 

 

 

[fenway]

InnoDB or MyISAM tables?

[abcz800]

 

Thanks so much for the reply

 

I'm 99% certain it was MyISAM!

 

looking forward to ur reply...

 

 

[fenway]

If so, you can simply grab everything in the /data subdirectory -- each folder inside corresponds to an entire db, and within each of those folders are the MYD (data), MYI (index) and FRM (structure) files for each table.  Stop your current server, copy them over into your new /data directory, and re-start.

[abcz800]

 

Thanks very much for your help;

 

the problem is that we can't seem to be able to get hold of the files under the root directory on the Mac server; we have access and have copied over everything that we could using firewire and the old drive as the target (and through a laptop), but the /var, /usr, lib mysql directories are not there!!! any idea on this? it's easy to switch to root through command line and I could easily access them before through  terminal window, but now that it shows a directory listing, all we have access to at the upper most level are Applications, Developer, Groups, Library, System, and Users folders. I was assuming it would be under one of these, but no luck. Any ideas !?

 

your help is greatly appreciated.

 

 

[fenway]

Usually it's in /var/lib/mysql... not sure how you could be missing those.... then again, it's a Mac, so I have no idea.

[abcz800]

 

It has finally worked; I have copied over the files into the mysql data folder on local machine - win xp - mysql 5.0.27 installed through easyphp, and luckily everything has worked fine and I have access to all databases, tables and data; the only strange thing is that the fields that were of type datetime do not show up properly. for instance "2007-03-21 12:35:51" shows up as "?/,(-*--)'' 95:86:56"

 

any idea what the reason is and how it may be fixed !!?

 

Thanks in advance.

 

 

p.s. here is the show create table on the new installation:

 

CREATE TABLE `video_comments` (\n  `comment_id` int(10) NOT NULL auto_increment,\n  `clipId` int(9) NOT NULL default '0',\n  `comment` text NOT NULL,\n  `userId` int(9) NOT NULL default '0',\n  `date_posted` datetime NOT NULL default '0000-00-00 00:00:00',\n  PRIMARY KEY  (`comment_id`)\n) ENGINE=MyISAM AUTO_INCREMENT=563 DEFAULT CHARSET=latin1

 

 

 

[fenway]

Did you change versions?  There's no way that any DATETIME field will show the value you indicate.

[Barand]

I'm locking this thread as you have double posted the problem in another at

 

http://www.phpfreaks.com/forums/index.php/topic,201843.msg913404.html#msg913404

 

(Unlocked again as someone else has deleted the double post)

[abcz800]

 

not sure if u received my email or not; yes the version is different and so is also the os. Previous was mysql 4.0.14 on Darwin (Mac/Unix); current is mysql 5.0.27 on windows.

 

I'm suspecting datetime could have been using the unix timestamp!

 

Any ideas?

 

 

[fenway]

I'm suspecting datetime could have been using the unix timestamp!

Yes, there were some incomptabile changes w.r.t timestamp....