Restoring MySQL InnoDB Files on Windows

Our server died at work last week. We’re a small shop without a real IT pro, just a bunch of hackers. Therefore, we had no back-up or recovery plan. I was tasked with restoring the MySQL database files. These where critical database we use on a daily basis, as well as our customers because it contain all their license information. We were able to get all the files off the computer in the mysql data folder (in C:\Program Files\MySQL\MySQL Server 4.1 & C:\Program Files\MySQL\MySQL Server 5.0). I found that all the MyISAM type tables restored just by moving the data files to the new data directory. The InnoDB type files were a more difficult task, and that is why I am writing this post. I had a hard time finding out how to do it, but I pieced together enough information to accomplish the task. Now, in an effort to give back, I’ll explain exactly what I did to get it restored.

In our back-ups we had the following files:

  • \MySQL\MySQL Server 4.1\data\ibdata1
  • \MySQL\MySQL Server 4.1\data\ib_logfile0
  • \MySQL\MySQL Server 4.1\data\ib_logfile1

Plus, in the data folder there were was a folder with the name of the database I was restoring that contained *.frm files (table_name.frm).

I did the restore on my development machine rather than the actual server because I didn’t want to screw up what was working on the server. I already had MySQL installed from an XAMPP install. (My development box is running Windows XP SP2). XAMPP installs MySQL a little differently than the regular MySQL install, so if it helps to follow what I did here, you may want to install it.

I first stopped my MySQL service using XAMPP’s control panel.

I moved the files listed above (ib* files and the folder containing the *.frm files) to the my local mysql data folder (C:\Program Files\xampp\mysql\data).

I then edited my.cnf (located in C:\Program Files\xampp\mysql\bin) and made the following changes (starting at line 66 for me):

OLD:
skip-innodb
#innodb_data_home_dir = C:/Program Files/xampp/mysql/data/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = C:/Program Files/xampp/mysql/data/
#innodb_log_arch_dir = C:/Program Files/xampp/mysql/data/
#set-variable = innodb_buffer_pool_size=16M
#set-variable = innodb_additional_mem_pool_size=2M
#set-variable = innodb_log_file_size=5M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#set-variable = innodb_lock_wait_timeout=50

NEW:
#skip-innodb
innodb_data_home_dir = C:/Program Files/xampp/mysql/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = C:/Program Files/xampp/mysql/data/
innodb_log_arch_dir = C:/Program Files/xampp/mysql/data/
set-variable = innodb_buffer_pool_size=16M
set-variable = innodb_additional_mem_pool_size=2M
set-variable = innodb_log_file_size=170M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
set-variable = innodb_lock_wait_timeout=50

(I had to set innodb_log_file_size to the actual size of my log file)

I then edited the XAMPP batch file that starts the mysql service (C:\Program Files\xampp\mysql_start.bat). I added –innodb_force_recovery=6 to the end of the call to mysqld. So line 8 of that file now read:

mysql\bin\mysqld –defaults-file=mysql\bin\my.cnf –standalone –console –innodb_force_recovery=6

This did the trick! My databases were recovered on my machine. I used SQLyog to do a sql dump of the database to restore it on our production server.

If this helped at all, please leave a comment and let me know.

Related posts:

  1. Pidgin Crashing on Windows 7 64-bit – Fixed Ever since installing Windows 7 RC, I have not been...
  2. One Liner to Decompress tar.gz Files For some reason I can never seem to remember this...
  3. My Applications Windows 7 Release Candidate is now available. I’ve been...
  4. One Click Django Project Publishing I was reading The Joel Test and I got to...

Related posts brought to you by Yet Another Related Posts Plugin.

October 16, 2007   Posted in: Programming & Internet

33 Responses

  1. JC - November 27, 2007

    This saved me a lot of time – thanks a million!

  2. Matjaz - December 26, 2007

    Thanks a lot. It worked perfectly.
    Regards

  3. CNeeper - January 5, 2008

    Thanks! I was trying to recover an InnoDB database on Netware with no guidance. I’d already figured I needed to restore ibdata1, but didn’t know I needed the log files too. Thanks for pointing me in the right direction!

  4. BJC - January 13, 2008

    Thanks a million, this really saved me.

  5. Joshua - January 17, 2008

    So just to make sure you were able to do this without the .MYD and .MYI files? using only frm and the ibdata and logs?

  6. Dustin - January 17, 2008

    Joshua: Correct me if I’m wrong, but I think those files only exist if you have a MyISAM type database. This is for InnoDB type databases.

  7. Joshua - January 17, 2008

    That is probably ture, for some reason on all my database those others have those files, seems this one would be the same but the files are not there. I have just been reading everything I can to figure out what could have happend, thanks.

  8. Volkan - February 8, 2008

    Hi everybody.

    I was very happy when I saw this website because I have the same problem.

    You used Xampp but I don’t use Xampp. My sistems php, mysql, apache.if I install Xampp are installed separetely, Can Xammp system affect my system ?

    Xammp is set of programs isn’t it? Do you think I Should install Xammp in my computer Because There are lots of complicate configuration in my computer apache httpd.conf file, php.ini file, my.ini file I’m afraid they can change.

    My friend brought mysql files to me. He didn’t get as the export his mysql files, some files MyISAM and other files innodb.

    my mysql server version was 4.0.15 and I stopped the mysql server and then

    I moved into his mysql files to my mysql data file
    My mysql data path :”C:\PROGRAM FILES\MySQL\MySQL Server 4.1\data\”

    then I started the mysql server but it couldn’t read innodb files.

    and I deleted this verison(version 4.0.15). and then I installed mysql version 4.1.16 in my computer

    but the mysql server still couldn’t read innodb files. Afterwards I understood. the problem concerned ibdata1 files

    I searched on the internet, saw this website. I stopped the mysql server,I added these codes:

    ” innodb_data_home_dir = C:\Program Files\MySQL\MySQL Server 4.1\data\
    innodb_data_file_path = ibdata1:10M:autoextend ” in my.ini files

    and then started it couldn’t run these codes :)

    I didn’t sleep since one weeks because of this problem and I haven’t solved this problem yet Innodb’s files is very important for me.
    How can I solve this problem I don’t know.

    Can you help me please. I’m looking forward to your support

  9. volkan - February 12, 2008

    I Should install Xammp in my computer Because There are lots of complicate configuration in my computer apache httpd.conf file, php.ini file, my.ini file I’m afraid they can change.
    It is very important for me.
    I’m looking forward to your support.

  10. Volkan - February 14, 2008

    Can you help me please ?

  11. Yanping - May 7, 2008

    Just want to say thank you

  12. chandan - May 14, 2008

    hi! i done something but not working could you guide me more.

  13. David - July 1, 2008

    I am graceful to Dustin for this guide, it was my salvation! Thank you!

    Yesterday my server crashes, but was possible recover the files with this guide. My version of MySQL is 5.1 on Windows system and I want to share 2 differences to 4.0 versions that can be useful to anyone:

    - The log file is in root folder of MySQL Server and not in Bin folder and his name is my.ini
    - There is no need to change mysql_start.bat, in fact, he don’t exists

    Best regards to all,
    David

  14. Heena - July 3, 2008

    Hi… i tried to recover db from .frm files but
    mysql\bin\mysqld –defaults-file=mysql\bin\my.cnf -standalone –console –innodb_force_recovery=6
    is not working i can not start mysqld… can u please help me
    thanks

  15. Heena - July 3, 2008

    hi for more info… when i did >mysqld –verbose –help there is no option defaults-file and innodb_force_recovery … may be bcoz of that mysqld is not starting… what are other option to start with recovery?
    Thanks.

  16. Anonymous - July 8, 2008

    You rock! Thank you, thank you, thank you!!!

  17. Derek - July 23, 2008

    Thanks for this, it got me on the right track. I’m running Windows Fista 32-bit with PHP, Apache and MySQL. Had a laptop crash and could only get files off the disk. I stopped my new MySQL service on my new computer. From my old disk I grabbed the folder in the data directory that had my db’s name. Also grabbed ib_logfile0, ib_logfile1 and ibdata1 but most importantly the my.ini, (from the MySQL Server 5.0 directory), which I’m guessing is the equivalent of the my.cnf noted in this article above. I renamed all the old files _old just to be sure. Restarted MySQL service and it all works so far which is a life-saver! Before, I’d tried just the three ib files and the db data folder and MySQL service would not start, had a 1076 error I think. But with the my.ini copied over it all worked a treat! Thanks once again for pointing me in the right direction, it would’ve taken me ages to re-create my db.

  18. Brian - August 15, 2008

    Thanks my friend. I did every step on my own except innodb_log_file_size=50Mb, which I never would have figured out without this article.

  19. Topper - August 28, 2008

    You’ve made my day Buddy!! :D

  20. Peter Nanden - September 16, 2008

    The home server with our wedding site crashed and I thought that all the messages from the guestbook were lost. Thanks for the solution…:)

  21. SebaGR - November 28, 2008

    Just in case someone needs a hand for this, I was getting a weird error number 5 telling me permissions were wrong for ibdata1.

    It turned to be, actually, that ibdata1 and both log files were read only. I don’t know how they came to be RO, but once I made them R/W my servers worked again.

    Hope this helps someone :)

  22. Steve - February 7, 2009

    Exactly what I was looking for + it worked perfectly !

  23. Anonymous - February 21, 2009

    Thank you very much!

  24. Kaihua Cai - March 19, 2009

    This is fantastic and is exactly what I need. Thank you very much and what you give back is huge for me.

  25. todd - April 12, 2009

    lz are totally awesome~~~

  26. davide - June 19, 2009

    You’re my new best friend

  27. Jordi - July 22, 2009

    After 2 days trying to reconnect my databases, your post was the response to my prays.
    Thank you very much!!!

  28. Andrew - August 10, 2009

    Yay! This helped me a lot too. I also had to make sure my.ini (not using Xampp) had all the settings match the old server my.ini file. When they did not I kept getting 1067 errors when starting the MySQL service.

    Thanks for a great resource!
    Andrew

  29. Nat - September 2, 2009

    Great stuff it worked for me too, saved hours of work…

  30. Kevin Kidson - September 14, 2009

    You REALLY saved my bacon with this post.
    MANY MANY THANKS

  31. Tayson - September 24, 2009

    I´m using a program at my work that generates an ibdata1 file, but there is no ib_log*** and no *.frm files.
    I need to get the data inside it.
    Should I follow the same steps?

    Thank you.

  32. Gerry Price - September 25, 2009

    Man… you saved my behind. We had a RAID controller crash DURING a nightly backup on our web/email server. I brought the server to a ‘professional’ company that does data restoration and they said it would take over a week and cost thousands to recover the drive. No way could we afford that so I retrieved the server, found an online application that allowed me to recover my files in a couple hours (the scan took half of that).

    But I could NOT get the MySql database that had all our website articles back. I worked on it for three days until I found this article. 2hrs later the site was 100% restored. Thank you. Thank you SO much.

  33. Tofudisan - September 28, 2009

    WOOT!

    Our situation was a little different. One of our developers ran a mass update on customer data to merge records deemed to be identical. Much later we found out that the sales people were entering housing developments under the same name and only differentiating with a lot distinction in the notes. Suddenly all the housing developments were trimmed down to one record each. This caused a big problem when trying to find notes and plans on a specific house in the development. To make matters worse we didn’t have a way to go back as the problem was discovered literally months later.

    We did, however, have a copy of the data files from before the merge took place. So I built a new MySQL install on another server, created a new INNODB schema and table to find where the files were written to, stopped the service, copied the files over as you outlined above, and then edited the my.ini file in the MySQL\bin directory. Note that I don’t use XAMPP so I don’t have a my.cnf file but rather the my.ini file. Very important!

    Et voila! Suddenly I have what appears to be a copy of the data that I can now create a script to compare the old and current to find any missing records from the errant merge.

    Phew! Thanks for “giving back”

Comments are closed for this entry.