I need help!
Okay, here's the background. I had a MySQL 4.1.20 instance running on RedHat Linux that suddenly went kaputz recently and so I had to bring up a new server in short order.
The server available to me was a Windows Server 2003 Standard box, upon which I installed the latest and greatest MySQL (5.0.37-community for Win32 on ia32 (MySQL Community Edition (GPL)). I used the MySQL Migration Toolkit to copy the data and it worked pretty well.
So, on April 5th I got that server up and running without too many problems. Sure, there were a few smaller issues I had to take care of (default dates of 0000-00-00 00:00:00, for example) that weren't too difficult to take care of.
Today I ran into another "quirk" when running "load data infile" statements for an overnight process. It kept complaining about data in a decimal column that couldn't be inserted. I read up on what to do in those circumstances, and the answer seemed to be to turn off strict mode. So I commented out strict mode in my.ini as suggested and attempted to restart the service. Here's where the problems began.
It complained "Default storage engine (InnoDB) is not available". So, I quickly decided to rollback to my previous my.ini while I investigated this error. Restared services - same error. Huh?
So I read that the datadir= and innodb_data_home_dir= should match.
I noticed datadir was: C:/Program Files/MySQL/MySQL Server 5.0/data/
and innodb_data_home_dir was: C:/MySQL Datafiles/ (chosen at installation time)
I saw that all my old familiar database directories were under the C:/Program Files/MySQL/MySQL Server 5.0/data/ directory with all the .frm, .myd and .myi files, which I was quite used to under the 4.1 schema. I figured that this is where my data still resides. What I didn't notice at the time was that none of those files in that directory structure had newer dates on them. They were all dated back at the time of the migration.
So, I changed both datadir and innodb_data_home_dir to C:/Program Files/MySQL/MySQL Server 5.0/data/
Now I got: "Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist"
Ugh. Do some more reading on forums. This is where I think I may have killed myself... I read this post:
http://forums.mysql.com/read.php?22,58270,77005#msg-77005
which seemed to state I should delete the ib_logfile0 and ib_logfile1 files. Now, since I figured all my .frm, .myd and .myi files were still intact below this directory, it couldn't hurt. I naively thought they were simply "log" files, not database log transaction files.
Double Ugh! Do some more reading and come to the terrible realization that I may have just hosed my database. I ran an undelete program, but it was too late - those files had been overwritten.
I still have my ibdata1 file and when I do a dump on it, I can actually SEE a lot of the data I'm missing! But now, anytime I start the server, I see only "information_schema" and none of my other tables. Gulp.
Is there ANY way on earth that I can recover or extract the table data out of just the ibdata1 file? It seems crazy to me that I can actually SEE data in those tables inside that file, but not be able to somehow extract or repair it back to a healthy database. At least I haven't found anything that mentions it, as of yet.
So I decided to post this on the off chance anyone will read it in the next few hours and be able to save my sorry butt. :-)
Yes, yes, I know I should have had recent backups. I do, but they're now 5 days old. Triple Ugh.
Any help/information is GREATLY appreciated.
Thanks,
Dan