#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    9
    Rep Power
    0

    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.

    Regards,

    OldBadger
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Location
    Wisconsin
    Posts
    78
    Rep Power
    3
    Is it possible to recontruct the database using only what is left? I.e. ibdata and logs?
    I dont believe so you will need a fresh mysql install

    I have the 2.5 GB databases
    by this do you mean a backup dump of the database?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    9
    Rep Power
    0
    Thanks for your reply,

    You say:
    I dont believe so you will need a fresh mysql install
    Yes I understand, but can I replace the ibdata1 with the old one?

    and then:
    by this do you mean a backup dump of the database?
    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?

    Thanks,

    OldBadger
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Location
    Wisconsin
    Posts
    78
    Rep Power
    3
    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 03:30 PM.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    9
    Rep Power
    0
    Thanks deljr,

    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.

    Regards,

    OldBadger
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    9
    Rep Power
    0

    Smile


    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 ----
    Regards,

    OldBadger

IMN logo majestic logo threadwatch logo seochat tools logo