Csdrhrt

I cannot run my mysql since yesterday, and I cannot access it, so I decided to reinstall it. I've backed up the data folder, which I presume contains all I need to restore the database after I reinstall mysql.

I've copy pasted and overwrite the old data folder with the backup, but it throws error that will prevent the mysql from loading up.

I've copy pasted only the folder with the same name as my database (let's say mydatabase), and it runs just fine, but the database itself could not be loaded. When I try to access the database with SQLyog, every table throws "Can't open file tablename.ibd"

How can I restore my database properly?

I'm using MySQL 4.1 and Windows 7.

Restoring MySQL InnoDB Files on Windows

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:

MySQLMySQL Server 4.1dataibdata1

MySQLMySQL Server 4.1dataib_logfile0

MySQLMySQL Server 4.1dataib_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 Filesxamppmysqldata).

I then edited my.cnf (located in C:Program Filesxamppmysqlbin)
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 Filesxamppmysql_start.bat). I added
–innodb_force_recovery=6 to the end of the call to mysqld. So line 8
of that file now read:

mysqlbinmysqld –defaults-file=mysqlbinmy.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

Further Resources

• Forcing InnoDB Recovery




• InnoDB Recovery Modes




• Corrupt InnoDB: Start mysqld only innodb_force_recovery=6

A vote up and thanks to Pimp Juice IT for a great answer. I did resolve a similar issue with assistance from his answer but a little bit differently so I thought I'd share.

I updated to a more recent version of XAMPP. I don't use the installer I just download it as a new zip and when doing so I run into issues.

1. First I stopped mysql (I'm running it locally using XAMPP)


2. Next I opened up the my.ini file located in /xampp/mysql/bin/ file -- for me my changes started at #skip-innoodb (line 136). Your line number may vary.

Here is the what I initially found:

#... omitted lines above ...

#skip-innodb

innodb_data_home_dir = "/xampp/mysql/data"

innodb_data_file_path = ibdata1:10M:autoextend

innodb_log_group_home_dir = "/xampp/mysql/data"

#innodb_log_arch_dir = "/xampp/mysql/data"

#... omitted lines below ...

The issue here seems to be the relative paths; note the missing C: on the directories above. Making the paths absolute was my first step.

#skip-innodb

innodb_data_home_dir = "C:/xampp/mysql/data"

innodb_data_file_path = ibdata1:10M:autoextend

innodb_log_group_home_dir = "C:/xampp/mysql/data"

#innodb_log_arch_dir = "C:/xampp/mysql/data"

3. While in the new version of XAMPP I want to start using:
   
   
   
   
   • In /xampp/mysql/ I renamed data to data_old
     
   
   
   • Then still in the same directory /xampp/mysql/ I copied the data directory from my old XAMPP.
   
   
   
   


4. Then I fired up my mysql server and all was good to go

Hope this helps someone!