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=5
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.
Source