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

时间:2022-09-24 11:08:47

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

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

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

C:\Documents and Settings\All用户、应用程序数据、MySQL 5.5 \ Data \ \ MySQL服务器

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文件。

3 个解决方案

#1


22  

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. MYSQL程序损坏,无法启动。MYSQL的新安装没有恢复数据。
  3. Computer crashed but hard disk is still working
  4. 电脑死机了,但硬盘还在工作
  5. Windows corrupted and fail to start up, but data is safe.
  6. Windows损坏并无法启动,但数据是安全的。

For your information, in MYSQL, files with the extension MYD is the table data, FRM files is the table definition and MYI file is the table indices. There is also the ibdata file specified in the my.ini which is the InnoDB tablesapce files. You have to be an administrator, so that you can access Document and Settings folder or the Program Files folder.

关于您的信息,在MYSQL中,扩展名MYD的文件是表数据,FRM文件是表定义,MYI文件是表索引。还有在my中指定的ibdata文件。ini文件是InnoDB汤匙文件。您必须是一个管理员,以便您可以访问文档和设置文件夹或程序文件文件夹。

  1. Open Window Explorer (Windows Key + E) or Open My Computer and go to the data folder where your database files reside.
  2. 打开Windows资源管理器(Windows Key + E)或打开我的电脑,进入数据库文件所在的数据文件夹。
  3. 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.
  4. 我建议您复制数据文件夹中的所有文件夹,而不是只选择要复制的几个文件夹。复制数据库文件夹和ibdata文件(不要复制ib_logfile(x)文件!)注意,ibdata文件可能与数据文件不在同一位置。要找到它的位置,请阅读如何在Windows中找到MYSQL数据库文件。
  5. Go over to the target computer, Open System Services. (Type services.msc at the Windows menu “Search Program and files” entry box)
  6. 转到目标计算机,打开系统服务。(服务类型。msc在Windows菜单“搜索程序和文件”输入框)
  7. 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 .
  8. 在服务窗口的右侧面板上,沿着列表滚动,查找“MYSQL”的名称。选择MYSQL,右键单击并单击。一旦它停止,你将它的状态变成空白,而不是。
  9. 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.
  10. 类似地查找数据文件夹的位置。一旦找到它,我建议您备份该文件夹或只是重命名它。粘贴从源计算机复制到数据文件夹的文件夹和ibdata文件。
  11. Go back to the Services window and select back MYSQL. Right mouse click on it and click to resume the MYSQL service.
  12. 回到服务窗口,选择MYSQL。鼠标右键单击它并单击以恢复MYSQL服务。
  13. Open MYSQL administrator and log on to your database server and you should be able to see your transferred database together with their data.
  14. 打开MYSQL管理员并登录到数据库服务器,您应该能够看到您传输的数据库及其数据。

#2


6  

@JohnWoo's answer is only for MYSIAM database engine. My answer is strictly for InnoDb storage engine,i.e., FRM and IBD files

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


To recover InnoDb database, I followed exactly these steps successfully. Before pointing out the steps, I would like to tell you that I performed the recovery from recovered hard disk of Wndows XP and transferred it to Windows 7. So, this will help both windows xp and windows 7 users. So, the steps which I followed are :-

  • The MySQL server at both the machines MUST be stopped. Of course if the OS/hard disk crashed, the server at that machine is already stopped.
  • 必须停止两台机器上的MySQL服务器。当然,如果操作系统/硬盘崩溃,机器上的服务器已经停止。
  • 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.
  • 现在,MySQL安装在两个文件夹中(至少对我而言)——> 1。程序文件/程序文件(x86)和2。C:\程序数据\ MySQL在windows 7和C:\Users\All用户、应用程序数据、MySQL在windows XP。注意,程序数据(win7)和应用程序数据(WinXP)都是隐藏的文件夹。
  • We are ONLY CONCERNED with the MySQL Server ** folder in the Application Data or Program Data folders. The Mysql folder in Program Files is of no use as it does not have your data.
  • 我们只关心应用程序数据或程序数据文件夹中的MySQL服务器*文件夹。程序文件中的Mysql文件夹不起作用,因为它没有您的数据。
  • Now Copy the my.ini file in MySQL Server ** folder.
  • 现在复制我的。在MySQL服务器**文件夹中的ini文件。
  • 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
  • 打开数据文件夹并复制这些文件1。所有ib_logfile*文件2。所有ibdata*文件3。包含.frm和.ibd文件的数据库文件夹
  • 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.
  • 现在,重新启动服务器。如果操作正确,您将恢复数据库,不会出现任何错误。

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-摘录/5.5/en/innodb-backup.html的mysql文档中查看InnoDB的冷备份方法

#3


4  

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子文件夹中,替换任何创建的数据库文件。如果不行,在新的数据库中创建一个表,以获得可能需要的其他信息的提示。

#1


22  

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. MYSQL程序损坏,无法启动。MYSQL的新安装没有恢复数据。
  3. Computer crashed but hard disk is still working
  4. 电脑死机了,但硬盘还在工作
  5. Windows corrupted and fail to start up, but data is safe.
  6. Windows损坏并无法启动,但数据是安全的。

For your information, in MYSQL, files with the extension MYD is the table data, FRM files is the table definition and MYI file is the table indices. There is also the ibdata file specified in the my.ini which is the InnoDB tablesapce files. You have to be an administrator, so that you can access Document and Settings folder or the Program Files folder.

关于您的信息,在MYSQL中,扩展名MYD的文件是表数据,FRM文件是表定义,MYI文件是表索引。还有在my中指定的ibdata文件。ini文件是InnoDB汤匙文件。您必须是一个管理员,以便您可以访问文档和设置文件夹或程序文件文件夹。

  1. Open Window Explorer (Windows Key + E) or Open My Computer and go to the data folder where your database files reside.
  2. 打开Windows资源管理器(Windows Key + E)或打开我的电脑,进入数据库文件所在的数据文件夹。
  3. 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.
  4. 我建议您复制数据文件夹中的所有文件夹,而不是只选择要复制的几个文件夹。复制数据库文件夹和ibdata文件(不要复制ib_logfile(x)文件!)注意,ibdata文件可能与数据文件不在同一位置。要找到它的位置,请阅读如何在Windows中找到MYSQL数据库文件。
  5. Go over to the target computer, Open System Services. (Type services.msc at the Windows menu “Search Program and files” entry box)
  6. 转到目标计算机,打开系统服务。(服务类型。msc在Windows菜单“搜索程序和文件”输入框)
  7. 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 .
  8. 在服务窗口的右侧面板上,沿着列表滚动,查找“MYSQL”的名称。选择MYSQL,右键单击并单击。一旦它停止,你将它的状态变成空白,而不是。
  9. 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.
  10. 类似地查找数据文件夹的位置。一旦找到它,我建议您备份该文件夹或只是重命名它。粘贴从源计算机复制到数据文件夹的文件夹和ibdata文件。
  11. Go back to the Services window and select back MYSQL. Right mouse click on it and click to resume the MYSQL service.
  12. 回到服务窗口,选择MYSQL。鼠标右键单击它并单击以恢复MYSQL服务。
  13. Open MYSQL administrator and log on to your database server and you should be able to see your transferred database together with their data.
  14. 打开MYSQL管理员并登录到数据库服务器,您应该能够看到您传输的数据库及其数据。

#2


6  

@JohnWoo's answer is only for MYSIAM database engine. My answer is strictly for InnoDb storage engine,i.e., FRM and IBD files

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


To recover InnoDb database, I followed exactly these steps successfully. Before pointing out the steps, I would like to tell you that I performed the recovery from recovered hard disk of Wndows XP and transferred it to Windows 7. So, this will help both windows xp and windows 7 users. So, the steps which I followed are :-

  • The MySQL server at both the machines MUST be stopped. Of course if the OS/hard disk crashed, the server at that machine is already stopped.
  • 必须停止两台机器上的MySQL服务器。当然,如果操作系统/硬盘崩溃,机器上的服务器已经停止。
  • 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.
  • 现在,MySQL安装在两个文件夹中(至少对我而言)——> 1。程序文件/程序文件(x86)和2。C:\程序数据\ MySQL在windows 7和C:\Users\All用户、应用程序数据、MySQL在windows XP。注意,程序数据(win7)和应用程序数据(WinXP)都是隐藏的文件夹。
  • We are ONLY CONCERNED with the MySQL Server ** folder in the Application Data or Program Data folders. The Mysql folder in Program Files is of no use as it does not have your data.
  • 我们只关心应用程序数据或程序数据文件夹中的MySQL服务器*文件夹。程序文件中的Mysql文件夹不起作用,因为它没有您的数据。
  • Now Copy the my.ini file in MySQL Server ** folder.
  • 现在复制我的。在MySQL服务器**文件夹中的ini文件。
  • 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
  • 打开数据文件夹并复制这些文件1。所有ib_logfile*文件2。所有ibdata*文件3。包含.frm和.ibd文件的数据库文件夹
  • 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.
  • 现在,重新启动服务器。如果操作正确,您将恢复数据库,不会出现任何错误。

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-摘录/5.5/en/innodb-backup.html的mysql文档中查看InnoDB的冷备份方法

#3


4  

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子文件夹中,替换任何创建的数据库文件。如果不行,在新的数据库中创建一个表,以获得可能需要的其他信息的提示。