从恢复的硬盘恢复 MySQL 数据库文件夹

Restore MySQL database folder from a recovered Hard Disk

提问by John Woo

I was able to recover the database folder of MySQL server 5.5 of the corrupted operating system (Windows XP) at

我能够在损坏的操作系统 (Windows XP) 中恢复 MySQL 服务器 5.5 的数据库文件夹

C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data\

C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data\

How can I manually restore it to the new MySQL Server? When I open the folder, I found out several folders and I believed those are the databases. I didn't file any SQL Files.

如何手动将其恢复到新的 MySQL 服务器？当我打开文件夹时，我发现了几个文件夹，我相信这些就是数据库。我没有归档任何 SQL 文件。

回答by John Woo

Ok, I got the answer to my own problem.

好的，我得到了自己问题的答案。

Possible Scenario

可能的情况

1. MYSQL program is corrupted and fail to start. Fresh install of MYSQL did not restore the data.
2. Computer crashed but hard disk is still working
3. Windows corrupted and fail to start up, but data is safe.

1. MYSQL 程序已损坏且无法启动。全新安装的 MYSQL 没有恢复数据。
2. 电脑死机但硬盘还在工作
3. Windows 损坏且无法启动，但数据是安全的。

For your information, in MYSQL, files with the extension MYDis the table data, FRMfiles is the table definition and MYIfile is the table indices. There is also the ibdatafile specified in the my.ini which is the InnoDB tablesapce files. You have to be an administrator, so that you can access Document and Settingsfolder or the Program Filesfolder.

供您参考，在 MYSQL 中，带有扩展名MYD的FRM文件是表数据，文件是表定义，MYI文件是表索引。还有ibdata在 my.ini 中指定的文件，它是 InnoDB 表空间文件。您必须是administrator, 才能访问Document and Settings文件夹或Program Files 文件夹。

1. Open Window Explorer (Windows Key + E) or Open My Computer and go to the data folder where your database files reside.
2. I would recommend you to copy all the folders in the data folder than just picking a few to copy. Copy the database folders and ibdata file (DO NOT COPY THE ib_logfile(x) files!) Note that the ibdata file might not be in the same location as the data files. To find out where it could be, read How to find MYSQL database files in Windows.
3. Go over to the target computer, Open System Services. (Type services.msc at the Windows menu “Search Program and files” entry box)
4. On the right panel of the Services window, scroll along the list and look for the name “MYSQL“. Select MYSQL and right mouse click and click . Once it has stopped, you will it's status become blank instead of .
5. Similarly look for the location of the data folder. Once you located it, I recommend you back up the folder or just rename it. Paste over the folders and ibdata file you have copied from the source computer to the data folder.
6. Go back to the Services window and select back MYSQL. Right mouse click on it and click to resume the MYSQL service.
7. Open MYSQL administrator and log on to your database server and you should be able to see your transferred database together with their data.

1. 打开窗口资源管理器（Windows 键 + E）或打开我的电脑，然后转到数据库文件所在的数据文件夹。
2. 我建议您复制数据文件夹中的所有文件夹，而不仅仅是选择几个进行复制。复制数据库文件夹和 ibdata 文件（不要复制 ib_logfile(x) 文件！）请注意，ibdata 文件可能与数据文件不在同一位置。要找出它的位置，请阅读如何在 Windows 中查找 MYSQL 数据库文件。
3. 转到目标计算机，打开系统服务。（在 Windows 菜单“搜索程序和文件”输入框中键入 services.msc）
4. 在“服务”窗口的右侧面板上，沿列表滚动并查找名称“MYSQL”。选择 MYSQL 并单击鼠标右键，然后单击 。一旦它停止，您的状态将变为空白而不是 。
5. 同样查找数据文件夹的位置。找到它后，我建议您备份该文件夹或重命名它。将您从源计算机复制的文件夹和 ibdata 文件粘贴到数据文件夹中。
6. 返回到服务窗口并选择返回 MYSQL。鼠标右键单击它，然后单击以恢复 MYSQL 服务。
7. 打开 MYSQL 管理员并登录到您的数据库服务器，您应该能够看到您传输的数据库及其数据。

回答by Deval Khandelwal

@JohnWoo's answer is only for MYSIAMdatabase engine. My answer is strictly for InnoDbstorage engine,i.e., FRMand IBDfiles

@JohnWoo 的答案仅适用于MYSIAM数据库引擎。我的回答严格适用于InnoDb存储引擎，即FRM和IBD文件

---

---

为了恢复InnoDbInnoDb数据库，我成功地遵循了这些步骤。在指出步骤之前，我想告诉您，我从 Wndows XP 的已恢复硬盘中进行了恢复，并将其转移到 Windows 7。因此，这对 Windows XP 和 Windows 7 用户都有帮助。所以，我遵循的步骤是：-

• The MySQL server at both the machines MUSTbe stopped. Of course if the OS/hard disk crashed, the server at that machine is already stopped.
• Now, MySQL installation is in 2 folders (for me at least) ->
  1.Program Files / Program Files (x86), and
  2.C:\Program Data\MySQL in windows 7 and C:\Users\All users\Application Data\MySQL in Windows XP. Note that Program Data(win7) as well as Application Data(WinXP) are hidden folders.
• We are ONLY CONCERNED with the MySQL Server ** folderin the Application Data or Program Data folders. The Mysql folder in Program Files is of no use as it does not have your data.
• Now Copy the my.inifile in MySQL Server ** folder.
• Open the data folder and copy these files
  1. All the ib_logfile* files
  2. All the ibdata* files
  3. The database folders (that you want) containing .frm and .ibd files
• Now, copy the files mentioned in the above two points in the destination machine. It is highly recommended to take a backup of the replaced files in the destination machine.
• Now, restart the server. If you went right, you would restore your databases without any error.

• 必须停止两台机器上的 MySQL 服务器。当然，如果操作系统/硬盘崩溃，那台机器上的服务器就已经停止了。
• 现在，MySQL 安装在 2 个文件夹中（至少对我而言）->
  1.Program Files / Program Files (x86) 和
  2.C:\Program Data\MySQL in Windows 7 和 C:\Users\All users\Application Windows XP 中的数据\MySQL。请注意，Program Data(win7) 和 Application Data(WinXP) 是隐藏文件夹。
• 我们只关心Application Data 或 Program Data 文件夹中的MySQL Server ** 文件夹。Program Files 中的 Mysql 文件夹没有用，因为它没有您的数据。
• 现在将my.ini文件复制到MySQL Server ** 文件夹中。
• 打开数据文件夹并复制这些文件
  1. 所有 ib_logfile* 文件
  2. 所有 ibdata* 文件
  3. 包含 .frm 和 .ibd 文件的数据库文件夹（您想要的）
• 现在，将上述两点中提到的文件复制到目标机器中。强烈建议对目标计算机中的替换文件进行备份。
• 现在，重新启动服务器。如果你做对了，你就可以毫无错误地恢复你的数据库。

I would recommend you to have a look at the cold backup method for InnoDB at the mysql docs at dev.mysql.com/doc/mysql-backup-excerpt/5.5/en/innodb-backup.html

我建议您在dev.mysql.com/doc/mysql-backup-excerpt/5.5/en/innodb-backup.html的 mysql 文档中查看 InnoDB 的冷备份方法

回答by wallyk

I think MySQL's file management is pretty simple by default. Create a simple new database, and copy the recovered files into the mysql subfolder which is created in response, replacing any created database files. If that doesn't work, create a table in the new database to get a hint of what else might be needed.

我认为默认情况下 MySQL 的文件管理非常简单。创建一个简单的新数据库，并将恢复的文件复制到响应创建的 mysql 子文件夹中，替换任何创建的数据库文件。如果这不起作用，请在新数据库中创建一个表以了解可能还需要什么。

回答by Allu Saiprudhvi

I too faced the same problem and spent almost two to three days in solving the issue. I was fed up with all the other confusing answers I have seen. Finally, I have retrieved all my data from my old drive and can now successfully access it in my new Microsoft Server installation.

我也遇到了同样的问题，花了差不多两到三天的时间来解决这个问题。我厌倦了我看到的所有其他令人困惑的答案。最后，我从旧驱动器中检索了所有数据，现在可以在我的新 Microsoft Server 安装中成功访问它。

This might help someone else.

这可能会帮助其他人。

Problems faced:

面临的问题：

1. Finding database files in the old hard disk:
   Answer:Follow the instructions given in this link:
   https://www.quora.com/Where-is-the-database-stored-in-the-Microsoft-SQL-server/answer/Allu-Saiprudhvi?prompt_topic_bio=1

2. If you can't open the folders in the old hard disk("The request could not be performed because of an I/O device error message"):
   Answer:Change the corresponding disk name. follow the instructions given in this link "follow the instructions given in this link"

3. After getting database folders, first download the same version of Mysql server as in the old hard drive , you can check the version mentioned with the folder name itself.

4. After downloading the same version, paste the database folders in the data folder as shown in the instruction 1 above.

5. Stop and start the Mysql server in services:
   Answer:To know how to restart a mysql server follow the instruction given in the link:
   https://www.quora.com/How-do-I-start-the-MySQL-server-in-Windows-10/answer/Allu-Saiprudhvi?prompt_topic_bio=1

6. Now you can check it, this should successfully make the data inside database accessible using SQL server.

1. 在旧硬盘中查找数据库文件：
   答：按照此链接中给出的说明进行操作：https:
   //www.quora.com/Where-is-the-database-stored-in-the-Microsoft-SQL-server/answer /Allu-Saiprudhvi?prompt_topic_bio=1

2. 如果无法打开旧硬盘中的文件夹（“由于 I/O 设备错误消息，请求无法执行”）：
   答：更改相应的磁盘名称。按照此链接中给出的说明“按照此链接中给出的说明”

3. 获取数据库文件夹后，先下载与旧硬盘中相同版本的Mysql服务器，您可以查看文件夹名称本身提到的版本。

4. 下载相同版本后，将数据库文件夹粘贴到数据文件夹中，如上面的说明 1 所示。

5. 在服务中停止和启动 Mysql 服务器：
   回答：要了解如何重新启动 mysql 服务器，请按照链接中的说明进行操作：https:
   //www.quora.com/How-do-I-start-the-MySQL-server -in-Windows-10/answer/Allu-Saiprudhvi?prompt_topic_bio=1

6. 现在您可以检查它，这应该可以成功地使用 SQL 服务器访问数据库中的数据。

THANK YOU

谢谢你