September 5th, 2012, 03:42 AM
Reconstruct MySQL having just ibdata and logs?
Since a backup of data was incomplete, and installing a new version destroyed the complete setup of MySQL, I need to recreate the environment using only ibdata1 and logs et c.
This runs under Vista Ultimate 32-bit with everything on C:.
I have had trouble installing new versions of MySQL, since I started with 5.0.x. I needed more and more of the new functionality incl stored procedures, and used 5.1.45 for a couple of years, but needed to go to the 5.5 platform.
I ended up with two services but neither apparently worked. Tested command lines et c. In that process I probably also messed up the .ini file(s).
Is there a way to get everything back to 5.1.45 - I have the 2.5 GB databases (flat databases) containing only date/time data and numeric/decimal values. I know the password of root and all the users. Is it possible to recontruct the database using only what is left? I.e. ibdata and logs?
Also, is there a good (extensive) guide on how to upgrade from one version to another - Everything I have seen so far - including the docs in the original manuals - but all these are indeed awkward to follow, and understand.
September 5th, 2012, 07:42 AM
I dont believe so you will need a fresh mysql install
by this do you mean a backup dump of the database?
September 5th, 2012, 01:45 PM
Thanks for your reply,
Yes I understand, but can I replace the ibdata1 with the old one?
Yes, I have dumps of the 3 different databases, but since there occured an error during the dump, which I noticed after the install of 5.5.x, That will not work...
So, why should it not be possible to replace the newly created ibdata1 file with the old file, I know of all the users and passwords? Isn't yhe ibdata1 just a file that the database engine opens?
September 5th, 2012, 02:18 PM
edit: found this article pertaining to your problem here
after thumbing thru the manual i would have guessed it is not possible but i was wrong
if you still have the .frm files from the mysql directory at mysql/data/databasename/tablename.frm you should be able to do this rather simply
Last edited by deljr; September 5th, 2012 at 02:30 PM.
September 5th, 2012, 03:59 PM
I have been looking for that kind of a solution since mid June, this is the first real promissing similar situation with a "fix" that sounds easy to implement. Thanks for pointing this out for me.
I will try to use this info ASAP and hopefully can add some positive info for future use.
September 9th, 2012, 10:52 AM
Again, thanks deljr,
Yuo set me on the right track, and with the new keywords to google around I found even more info. I didn't have that much time to experiment with individual files - so this is how I did it...
A) I cleaned out everything that has to do with MySQL on the laptop intended for test runs.
B) Installed MySQL 5.1.45 -32bit on my laptop (the same version I had in production on my production computer) using the default data directory (same as in prod).
C) Copied the Data directories and subdirs from the crashed prod computer, to the same dir level on the laptop. It started without protests after setting up users via the mysql commandline.
D) Updated the laptop installation to 5.1.65 (newer is better...) and then ran a dump from MySQL Workbench of everything in my own databases.
E) Cleaned everything out as in A).
F) Installed 5.5.27 -32bit and made a restore of my databases from the dump and made certain everything worked fine.
G) Cleaned out everything old conserning MySQL from the production computer - there was a real mess -.
H) Installed 5.5.27 - 32-bit on the old prod computer. And then used the Dumps again to restore. I did this because I had installed the database pointing at a another harddisk (it had been on same disk before). But, I suspect I could have copied the Data directory from the laptop to the prod computer.
---- Now everything is fine ----