How to restore mysql database using files on data folder?

Question

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.

Answer 1

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:

\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

---

Further Resources

• Forcing InnoDB Recovery

• InnoDB Recovery Modes

• Corrupt InnoDB: Start mysqld only innodb_force_recovery=6

Answer 2

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!

Answer 3

This is what I did on Windows 10 with MySQL 5.7

Both installations have been done with MySQL Installer, so the setups were the same and a bit different from those described in the other answers.

Basically, from the original installation folder I only copied the folders named after the schemas I was interested in. Next, the fundamental settings to apply are in the Security tab of the folder properties dialog:

• assigning the ownership of the main data folder and all its subfolders (including the just copied ones) to the SYSTEM account (not to admin, administrator or whatever user has been used to do the copy);
• adding the NETWORK SERVICE to the users with full control over the folder.

I found these instructions on the MySQL forums, here are the original messages:

• https://forums.mysql.com/read.php?169,659235,659235
  • https://forums.mysql.com/read.php?169,659235,659250#msg-659250](https://forums.mysql.com/read.php?169,659235,659250#msg-659250)
    • Re: Moving database to another drive -- Windows 10

Original, complete instructions by Ray Yates:

For Posterity:

1. I right clicked on the C:...\Data\ folder and Selected Properties -> Security - Advanced and saw the Principal named NETWORK SERVICE

2. In a seperate window I right clicked on the copy E:\MYSQL\Data\ folder and found there was no such setting.

3. I Click the [Add] button, then the Select a principal link and entered the object name NETWORK SERVICE and [OK]

4. I selected NETWORK SERVICE and clicked [edit] and checked Full control [ok]

5. I then repeated all of the original steps and the server started as expected.