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:
- Pidgin Crashing on Windows 7 64-bit – Fixed Ever since installing Windows 7 RC, I have not been...
- One Liner to Decompress tar.gz Files For some reason I can never seem to remember this...
- My Applications Windows 7 Release Candidate is now available. I’ve been...
- 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
This saved me a lot of time – thanks a million!
Thanks a lot. It worked perfectly.
Regards
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!
Thanks a million, this really saved me.
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?
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.
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.
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
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.
Can you help me please ?
Just want to say thank you
hi! i done something but not working could you guide me more.
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
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
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.
You rock! Thank you, thank you, thank you!!!
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.
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.
You’ve made my day Buddy!!
The home server with our wedding site crashed and I thought that all the messages from the guestbook were lost. Thanks for the solution…:)
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
Exactly what I was looking for + it worked perfectly !
Thank you very much!
This is fantastic and is exactly what I need. Thank you very much and what you give back is huge for me.
lz are totally awesome~~~
You’re my new best friend
After 2 days trying to reconnect my databases, your post was the response to my prays.
Thank you very much!!!
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
Great stuff it worked for me too, saved hours of work…
You REALLY saved my bacon with this post.
MANY MANY THANKS
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.
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.
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.