将 .frm 和 .opt 文件导入 MySQL

Import .frm and .opt files to MySQL

提问by JJunior

I am using MySQL 5.5.1.1 downloaded and installed from : http://dev.mysql.com/downloads/

我使用的下载和安装从MySQL 5.5.1.1：http://dev.mysql.com/downloads/

I want to import .frm and .opt database files from my local machine to my mysql database.

我想将 .frm 和 .opt 数据库文件从我的本地机器导入到我的 mysql 数据库中。

I created a new Databbase connection to help me import the files from my machine. But I am unable to import .frm files as I keep getting error message: Format not supported.

我创建了一个新的数据库连接来帮助我从我的机器导入文件。但是我无法导入 .frm 文件，因为我不断收到错误消息：不支持格式。

Can someone please help me on how I could import files to MySQL client??

有人可以帮助我如何将文件导入 MySQL 客户端吗？

Thank you, J

谢谢你，J

回答by RolandoMySQLDBA

As far importing any MySQL data, three(3) things need to considered.

至于导入任何 MySQL 数据，需要考虑三 (3) 件事。

MyISAM

我的ISAM

Importing a MyISAM table is a simple as moving three files with the extensions .frm, .MYD, and .MYIfiles for the given table into a MySQL folder. For example, if the table name is mydata, then the three(3) files

导入MyISAM表是一个简单的移动三个文件与扩展.frm，.MYD以及.MYI对于给定的表文件到MySQL的文件夹。例如，如果表名是 mydata，那么三（3）个文件

• mydata.frm
• mydata.MYD
• mydata.MYI

• mydata.frm
• mydata.MYD
• mydata.MYI

The following could be the nightmare of nightmares. Importing InnoDB depends entirely on many factors that fall into one of two categories:

以下可能是噩梦的噩梦。导入 InnoDB 完全取决于属于以下两类之一的许多因素：

InnoDB (innodb_file_per_tabledisabled [default])

InnoDB ( innodb_file_per_table禁用 [默认])

All InnoDB data and index pages are located in /var/lib/mysql/ibdata1. This file must be moved from your source machine (Server-S) to the target machine (Server-T) and placed in the same absolute path. In fact, here is the shocker: Server-S and Server-T must be the same. In other words, you cannot import and export InnoDB .ibdfiles to other machines. They can only be imported and exported on the same machine the .ibdwas created on.

所有 InnoDB 数据和索引页都位于/var/lib/mysql/ibdata1. 此文件必须从源计算机 (Server-S) 移动到目标计算机 (Server-T) 并放置在相同的绝对路径中。事实上，令人震惊的是：Server-S 和 Server-T 必须相同。换句话说，您不能将 InnoDB.ibd文件导入和导出到其他机器。它们只能在.ibd创建它们的同一台机器上导入和导出。

You would also have to move /var/ib/mysql/ib_logfile0 and /var/ib/mysql/ib_logfile1 from Server-S and place them in the same absolute path on Server-T.

您还必须从 Server-S 移动 /var/ib/mysql/ib_logfile0 和 /var/ib/mysql/ib_logfile1 并将它们放在 Server-T 上的相同绝对路径中。

You also must make sure that every InnoDB variable set in /etc/my.cnf from Server-S must be set in /etc/my.cnf on Server-T.

您还必须确保在 Server-S 的 /etc/my.cnf 中设置的每个 InnoDB 变量都必须在 Server-T 的 /etc/my.cnf 中设置。

InnoDB (innodb_file_per_tableenabled)

InnoDB（启用innodb_file_per_table）

For every InnoDB table, there will be two files. For example, if the InnoDB table in the mydata database is called mytable, you will have /var/lib/mysql/mydata/mytable.frmand /var/lib/mysql/mydata/mytable.ibd. The .ibdfile contains data and index pages for the table. To import the individual table you must

对于每个 InnoDB 表，将有两个文件。例如，如果 mydata 数据库中的 InnoDB 表名为 mytable，您将拥有/var/lib/mysql/mydata/mytable.frm和 /var/lib/mysql/mydata/mytable.ibd。该.ibd文件包含表的数据和索引页。要导入单个表，您必须

1. Place the mytable.frmin /var/lib/mysql/mydata folderon Server-T
2. Place the mytable.ibdin /var/lib/mysql/mydata folderon Server-T
3. Run ALTER TABLE mydata.mytable IMPORT TABLESPACE;

1. 将mytable.frmin/var/lib/mysql/mydata folder放在 Server-T 上
2. 将mytable.ibdin/var/lib/mysql/mydata folder放在 Server-T 上
3. 跑 ALTER TABLE mydata.mytable IMPORT TABLESPACE;

Make sure you have the /var/lib/mysql/ibdata1in the same place it was imported from.

确保您将其/var/lib/mysql/ibdata1放在从其导入的同一位置。

Moral of the story

故事的道德启示

Please do not use IMPORT TABLESPACE techniques across different servers. Just do a mysqldump of everything and import the mysqldump. Logical dumps are always the safest way to go !!!

请不要在不同的服务器上使用 IMPORT TABLESPACE 技术。只需对所有内容进行 mysqldump 并导入 mysqldump。逻辑转储始终是最安全的方法！！！

回答by KalenGi

I did a mysql import using the files on xampp. I have very many databases so I did not want to go through the mysqldump route. The process was:

我使用 xampp 上的文件进行了 mysql 导入。我有很多数据库，所以我不想通过 mysqldump 路线。过程是：

1. Install the new xampp, but do not enable the services
2. Update my.inito include any custom settings I had on the previous install.
3. Confirm that mysql is starting successfully
4. Copy over all the database folders (inside the data folder) from the old install with the exception of mysql, phpmyadminand webauth.
5. Confirm that mysql is starting successfully
6. Copy over ib_logfile0, ib_logfile1and ibdata1. Rename the files first, do not overwrite just in case something goes wrong.
7. Confirm that mysql is starting successfully
8. Copy over the following files from the data/mysqlfolder: db.frm, db.MYD, db.MYI, user.frm, user.MYD, user.MYI. Do not overwrite the new ones just in case it doesn't work.
9. Confirm that mysql is starting successfully

1. 安装新的 xampp，但不启用服务
2. 更新my.ini以包含我在之前安装时的任何自定义设置。
3. 确认mysql启动成功
4. 从旧安装复制所有数据库文件夹（在数据文件夹内），但mysql、phpmyadmin和webauth 除外。
5. 确认mysql启动成功
6. 复制ib_logfile0、ib_logfile1和ibdata1。首先重命名文件，不要覆盖以防万一出现问题。
7. 确认mysql启动成功
8. 从data/mysql文件夹复制以下文件：db.frm、db.MYD、db.MYI、user.frm、user.MYD、user.MYI。不要覆盖新的以防万一它不起作用。
9. 确认mysql启动成功

At the end of the process I had mysql working perfectly with all my databases and users intact.

在这个过程结束时，我让 mysql 与我所有的数据库和用户完美地工作。

回答by shonhloi

Just copy desired 'data' folder to target mysql data folder, after renaming current 'data' folder, after stop mysql service. And export to sql file with phpmyadmin, and restore current data folder, and import desired data sql.

在重命名当前的“数据”文件夹后，停止 mysql 服务后，只需将所需的“数据”文件夹复制到目标 mysql 数据文件夹。并用phpmyadmin导出到sql文件，恢复当前数据文件夹，导入需要的数据sql。

回答by Chagbert

I think the great problem you may be experiencing comes from the fact that you cannot even view these .frm files in the phpmyadmin interface (because they really are incomplete sets). I'd suggest you delete these files from the current location and use phpmyadmin to re-create the these tables as SQL under "query" tab [delete IFF you do have database script of everything. if not, you may still get this from the old dump. if not gaian, do NOT delete). Thanks : chagbert.

我认为您可能遇到的最大问题是您甚至无法在 phpmyadmin 界面中查看这些 .frm 文件（因为它们确实是不完整的集合）。我建议您从当前位置删除这些文件，并使用 phpmyadmin 在“查询”选项卡下将这些表重新创建为 SQL [删除 IFF，您确实拥有所有内容的数据库脚本。如果没有，你仍然可以从旧的转储中得到这个。如果不是 gaian，请不要删除）。谢谢：查格伯特。