如何从 .myd、.myi、.frm 文件恢复 MySQL 数据库

How to recover MySQL database from .myd, .myi, .frm files

提问by chandrajeet

How to restore one of my MySQL databases from .myd, .myi, .frmfiles?

如何从.myd, .myi,.frm文件恢复我的 MySQL 数据库之一？

回答by derobert

If these are MyISAM tables, then plopping the .FRM, .MYD, and .MYI files into a database directory (e.g., /var/lib/mysql/dbname) will make that table available. It doesn't have to be the same database as they came from, the same server, the same MySQL version, or the same architecture. You may also need to change ownership for the folder (e.g., chown -R mysql:mysql /var/lib/mysql/dbname)

如果这些是 MyISAM 表，那么将 .FRM、.MYD 和 .MYI 文件放入数据库目录（例如，/var/lib/mysql/dbname）将使该表可用。它不必是与它们来自相同的数据库、相同的服务器、相同的 MySQL 版本或相同的体系结构。您可能还需要更改文件夹的所有权（例如，chown -R mysql:mysql /var/lib/mysql/dbname）

Note that permissions (GRANT, etc.) are part of the mysqldatabase. So they won't be restored along with the tables; you may need to run the appropriate GRANTstatements to create users, give access, etc. (Restoring the mysqldatabase is possible, but you need to be careful with MySQL versions and any needed runs of the mysql_upgradeutility.)

请注意，权限（GRANT等）是mysql数据库的一部分。所以它们不会和桌子一起恢复；您可能需要运行适当的GRANT语句来创建用户、授予访问权限等。（恢复mysql数据库是可能的，但您需要小心 MySQL 版本和任何需要的mysql_upgrade实用程序运行。）

Actually, you probably just need the .FRM (table structure) and .MYD (table data), but you'll have to repair table to rebuild the .MYI (indexes).

实际上，您可能只需要 .FRM（表结构）和 .MYD（表数据），但您必须修复表以重建 .MYI（索引）。

The only constraint is that if you're downgrading, you'd best check the release notes (and probably run repair table). Newer MySQL versions add features, of course.

唯一的限制是，如果您要降级，您最好查看发行说明（并可能运行修复表）。当然，较新的 MySQL 版本会添加功能。

[Although it should be obvious, if you mix and match tables, the integrity of relationships between those tables is your problem; MySQL won't care, but your application and your users may. Also, this method does not work at all for InnoDB tables. Only MyISAM, but considering the files you have, you have MyISAM]

[虽然应该很明显，如果你混合和匹配表，这些表之间关系的完整性是你的问题；MySQL 不会关心，但您的应用程序和您的用户可能会关心。此外，此方法对 InnoDB 表根本不起作用。只有 MyISAM，但考虑到您拥有的文件，您拥有 MyISAM]

回答by mcardellg

Note that if you want to rebuild the MYI file then the correct use of REPAIR TABLE is:

请注意，如果要重建 MYI 文件，则正确使用 REPAIR TABLE 是：

REPAIR TABLE sometable USE_FRM;

修理表 USE_FRM;

Otherwise you will probably just get another error.

否则，您可能只会收到另一个错误。

回答by alnel

I just discovered to solution for this. I am using MySQL 5.1 or 5.6 on Windows 7.

我刚刚发现解决这个问题。我在 Windows 7 上使用 MySQL 5.1 或 5.6。

1. Copy the .frmfile and ibdata1from the old file which was located on "C:\Program Data\MySQL\MSQLServer5.1\Data"
2. Stop the SQL server instance in the current SQL instance
3. Go to the datafolder located at "C:\Program Data\MySQL\MSQLServer5.1\Data"
4. Paste the ibdata1and the folder of your database which contains the .frmfile from the file you want to recover.
5. Start the MySQL instance.

1. 从位于“C:\Program Data\MySQL\MSQLServer5.1\Data”的旧文件中复制.frm文件和ibdata1
2. 停止当前 SQL 实例中的 SQL 服务器实例
3. 转到位于“C:\Program Data\MySQL\MSQLServer5.1\Data”的数据文件夹
4. 粘贴ibdata1和包含要恢复的文件中的 .frm文件的数据库文件夹。
5. 启动 MySQL 实例。

No need to locate the .MYI and .MYD file for this recovery.

无需为此恢复查找 .MYI 和 .MYD 文件。

回答by Brent

One thing to note:

需要注意的一件事：

The .FRM file has your table structure in it, and is specific to your MySQL version.

.FRM 文件中包含您的表结构，并且特定于您的 MySQL 版本。

The .MYD file is NOT specific to version, at least not minor versions.

.MYD 文件不是特定于版本的，至少不是次要版本。

The .MYI file is specific, but can be left out and regenerated with REPAIR TABLElike the other answers say.

.MYI 文件是特定的，但可以REPAIR TABLE像其他答案所说的那样省略并重新生成。

The point of this answer is to let you know that if you have a schema dump of your tables, then you can use that to generate the table structure, then replace those .MYD files with your backups, delete the MYI files, and repair them all. This way you can restore your backups to another MySQL version, or move your database altogether without using mysqldump. I've found this super helpful when moving large databases.

这个答案的重点是让您知道，如果您有表的模式转储，那么您可以使用它来生成表结构，然后用您的备份替换那些 .MYD 文件，删除 MYI 文件并修复它们全部。通过这种方式，您可以将备份恢复到另一个 MySQL 版本，或者在不使用mysqldump. 我发现这在移动大型数据库时非常有用。

回答by Vishal

Simple! Create a dummy database (say abc)

简单的！创建一个虚拟数据库（比如 abc）

Copy all these .myd, .myi, .frm files to mysql\data\abc wherein mysql\data\ is the place where .myd, .myi, .frm for all databases are stored.

将所有这些 .myd、.myi、.frm 文件复制到 mysql\data\abc，其中 mysql\data\ 是存储所有数据库的 .myd、.myi、.frm 的地方。

Then go to phpMyadmin, go to db abc and you find your database.

然后转到 phpMyadmin，转到 db abc 并找到您的数据库。

回答by Elzo Valugi

I think .myi you can repair from inside mysql.

我认为 .myi 您可以从 mysql 内部修复。

If you see these type of error messages from MySQL: Database failed to execute query (query) 1016: Can't open file: 'sometable.MYI'. (errno: 145) Error Msg: 1034: Incorrect key file for table: 'sometable'. Try to repair it thenb you probably have a crashed or corrupt table.

如果您从 MySQL 中看到这些类型的错误消息：数据库无法执行查询（查询）1016：无法打开文件：'sometable.MYI'。(errno: 145) 错误消息：1034：表的密钥文件不正确：'sometable'。尝试修复它然后你可能有一个崩溃或损坏的表。

You can check and repair the table from a mysql prompt like this:

您可以像这样从 mysql 提示符检查和修复表：

check table sometable;
+------------------+-------+----------+----------------------------+
| Table | Op | Msg_type | Msg_text | 
+------------------+-------+----------+----------------------------+ 
| yourdb.sometable | check | warning | Table is marked as crashed | 
| yourdb.sometable | check | status | OK | 
+------------------+-------+----------+----------------------------+ 

repair table sometable;
+------------------+--------+----------+----------+ 
| Table | Op | Msg_type | Msg_text | 
+------------------+--------+----------+----------+ 
| yourdb.sometable | repair | status | OK | 
+------------------+--------+----------+----------+

and now your table should be fine:

现在你的桌子应该没问题了：

check table sometable;
+------------------+-------+----------+----------+ 
| Table | Op | Msg_type | Msg_text |
+------------------+-------+----------+----------+ 
| yourdb.sometable | check | status | OK |
+------------------+-------+----------+----------+

回答by cgp

You can copy the files into an appropriately named subdirectory directory of the data folder as long as it is the EXACT same version of mySQL and you have retained all of the associated files in that directory. If you don't have all the files, I'm pretty sure you're going to have issues.

您可以将文件复制到数据文件夹的适当命名的子目录中，只要它与 mySQL 的版本完全相同，并且您已将所有相关文件保留在该目录中。如果您没有所有文件，我很确定您会遇到问题。

回答by Kaspar Lee

I found a solution for converting the files to a .sqlfile (you can then import the .sqlfile to a server and recover the database), without needing to access the /vardirectory, therefore you do not need to be a server admin to do this either.

我找到了将文件转换为文件的解决方案.sql（然后您可以将.sql文件导入服务器并恢复数据库），而无需访问/var目录，因此您也不需要成为服务器管理员来执行此操作。

It does require XAMPP or MAMP installed on your computer.

它确实需要在您的计算机上安装 XAMPP 或 MAMP。

• After you have installed XAMPP, navigate to the install directory (Usually C:\XAMPP), and the the sub-directory mysql\data. The full path should be C:\XAMPP\mysql\data

• Inside you will see folders of any other databases you have created. Copy & Paste the folder full of .myd, .myiand .frmfiles into there. The path to that folder should be

  C:\XAMPP\mysql\data\foldername\.mydfiles

• Then visit localhost/phpmyadminin a browser. Select the database you have just pasted into the mysql\datafolder, and click on Export in the navigation bar. Chooses the export it as a .sqlfile. It will then pop up asking where the save the file

• 安装 XAMPP 后，导航到安装目录（通常为C:\XAMPP）和子目录mysql\data. 完整路径应该是C:\XAMPP\mysql\data

• 在里面，您将看到您创建的任何其他数据库的文件夹。将充满.myd,.myi和.frm文件的文件夹复制并粘贴到那里。该文件夹的路径应该是

  C:\XAMPP\mysql\data\foldername\.mydfiles

• 然后localhost/phpmyadmin在浏览器中访问。选择刚刚粘贴到mysql\data文件夹中的数据库，然后单击导航栏中的导出。选择将其导出为.sql文件。然后它会弹出询问保存文件的位置

And that is it! You (should) now have a .sqlfile containing the database that was originally .myd, .myiand .frmfiles. You can then import it to another server through phpMyAdmin by creating a new database and pressing 'Import' in the navigation bar, then following the steps to import it

就是这样！您（应该）现在有一个.sql包含数据库的文件，该数据库最初是.myd,.myi和.frmfiles. 然后，您可以通过 phpMyAdmin 创建一个新数据库并按导航栏中的“导入”将其导入到另一台服务器，然后按照步骤导入它

回答by dny238

The above description wasn't sufficient to get things working for me (probably dense or lazy) so I created this script once I found the answer to help me in the future. Hope it helps others

上面的描述不足以让事情为我工作（可能是密集的或懒惰的），所以一旦我找到了将来可以帮助我的答案，我就创建了这个脚本。希望它能帮助别人

vim fixperms.sh 

#!/bin/sh
for D in `find . -type d`
do
        echo $D;
        chown -R mysql:mysql $D;
        chmod -R 660 $D;
        chown mysql:mysql $D;
        chmod 700 $D;
done
echo Dont forget to restart mysql: /etc/init.d/mysqld restart;

回答by James

http://forums.devshed.com/mysql-help-4/mysql-installation-problems-197509.html

http://forums.devshed.com/mysql-help-4/mysql-installation-problems-197509.html

It says to rename the ib_* files. I have done it and it gave me back the db.

它说要重命名 ib_* 文件。我已经做到了，它把数据库还给了我。