Oct 16
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.
November 27th, 2007 at 8:45 am
This saved me a lot of time - thanks a million!
December 26th, 2007 at 12:35 pm
Thanks a lot. It worked perfectly.
Regards
January 5th, 2008 at 3:45 pm
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!
January 13th, 2008 at 9:11 am
Thanks a million, this really saved me.
January 17th, 2008 at 5:29 am
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?
January 17th, 2008 at 6:03 am
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.
January 17th, 2008 at 10:55 am
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.
February 8th, 2008 at 10:44 am
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
February 12th, 2008 at 9:11 am
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.
February 14th, 2008 at 4:10 am
Can you help me please ?
May 7th, 2008 at 2:19 pm
Just want to say thank you
May 14th, 2008 at 1:40 am
hi! i done something but not working could you guide me more.
July 1st, 2008 at 9:32 am
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
July 3rd, 2008 at 5:16 pm
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
July 3rd, 2008 at 5:21 pm
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.
July 8th, 2008 at 1:07 pm
You rock! Thank you, thank you, thank you!!!