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

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

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

11 个解决方案

#1

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)

如果这些是ismyam表，那么将. 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 mysql database. So they won't be restored along with the tables; you may need to run the appropriate GRANT statements to create users, give access, etc. (Restoring the mysql database is possible, but you need to be careful with MySQL versions and any needed runs of the mysql_upgrade utility.)

注意，权限(授予等)是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

#2

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

注意，如果您想重新构建MYI文件，那么修复表的正确用法是:

REPAIR TABLE sometable USE_FRM;

维修表sometable USE_FRM;

Otherwise you will probably just get another error.

否则你可能会得到另一个错误。

#3

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 .frm file and ibdata1 from the old file which was located on "C:\Program Data\MySQL\MSQLServer5.1\Data"
2. 从位于“C:\程序数据\MySQL\MSQLServer5.1\Data”的旧文件中复制.frm文件和ibdata1
3. Stop the SQL server instance in the current SQL instance
4. 在当前的SQL实例中停止SQL server实例。
5. Go to the datafolder located at "C:\Program Data\MySQL\MSQLServer5.1\Data"
6. 转到位于“C:\程序数据\MySQL\MSQLServer5.1\数据”的datafolder
7. Paste the ibdata1 and the folder of your database which contains the .frm file from the file you want to recover.
8. 粘贴ibdata1和数据库的文件夹，其中包含要恢复的文件中的.frm文件。
9. Start the MySQL instance.
10. 启动MySQL实例。

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

这个恢复不需要定位. myi和. myd文件。

#4

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 TABLE like the other answers say.

myi文件是特定的，但是可以像其他答案说的那样用修复表删除并重新生成。

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。我发现这在移动大型数据库时非常有用。

#5

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，找到你的数据库。

#6

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:Database的错误消息未能执行查询(查询)1016:不能打开文件:'sometable.MYI'。(errno: 145)错误Msg: 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 |+------------------+-------+----------+----------+

#7

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版本，并且您在该目录中保留了所有相关的文件。如果你没有所有的文件，我很确定你会有问题。

#8

I found a solution for converting the files to a .sql file (you can then import the .sql file to a server and recover the database), without needing to access the /var directory, 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
• 安装XAMPP之后，导航到安装目录(通常是C:\XAMPP)，以及子目录mysql\data。完整的路径应该是C:\XAMPP\mysql\数据

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

  在里面你会看到你创建的任何其他数据库的文件夹。复制并粘贴满myd、.myi和.frm文件的文件夹。该文件夹的路径应该是

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

  C:\ XAMPP \ mysql \ \ foldername \ .mydfiles数据

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

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

And that is it! You (should) now have a .sql file containing the database that was originally .myd, .myi and .frm files. 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和.frm文件。然后，您可以通过phpMyAdmin将它导入到另一个服务器，方法是创建一个新的数据库并在导航条中按“导入”，然后按照以下步骤导入它

#9

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

http://forums.devshed.com/mysql -帮助- 4/mysql -安装-问题- 197509. - html

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

它说要重命名ib_*文件。我做了，它给了我db。

#10

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/shfor D in `find . -type d`do        echo $D;        chown -R mysql:mysql $D;        chmod -R 660 $D;        chown mysql:mysql $D;        chmod 700 $D;doneecho Dont forget to restart mysql: /etc/init.d/mysqld restart;

#11

For those that have Windows XP and have MySQL server 5.5 installed - the location for the database is C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data, unless you changed the location within the MySql Workbench installation GUI.

对于那些安装了Windows XP和MySQL服务器5.5 -数据库的位置是C:\Documents and Settings\All用户、应用程序数据、MySQL 5.5 \ \ MySQL服务器数据,除非你改变了位置在MySQL工作台安装GUI。