从 .frm 文件恢复 mysql 数据库

Restore the mysql database from .frm files

提问by user359187

I have dumped all my tables everyweek to got the backup. But later I understand that it is only storing the .frm file of the table. It is not showing .MYD and .MYI files of a table. So I have only my .frm file of the database with me and also mydatabase is innodb. So can I get my database with data in the database?

我每周都转储我所有的桌子以获得备份。但后来我明白它只是存储表的 .frm 文件。它不显示表的 .MYD 和 .MYI 文件。所以我只有我的数据库的 .frm 文件，而且 mydatabase 是 innodb。那么我可以使用数据库中的数据获取我的数据库吗？

回答by user359187

Yes this is possible. It is not enough you just copy the .frmfiles to the to the databse folder but you also need to copy the ib_logfilesand ibdatafile into your data folder. I have just copy the .frmfiles and copy those files and just restart the server and my database is restored.

是的，这是可能的。这是不够的，你只是拷贝.frm文件到该文件夹DATABSE但你也需要复制ib_logfiles和ibdata文件到您的数据文件夹。我刚刚复制.frm文件并复制这些文件，然后重新启动服务器，我的数据库就恢复了。

After copying the above files execute the following command -

复制上述文件后执行以下命令 -

sudo chown -R mysql:mysql /var/lib/mysql

须藤 chown -R mysql:mysql /var/lib/mysql

The above command will change the file owner under mysql and it's folder to MySql user. Which is important for mysql to read the .frmand ibdatafiles.

上面的命令会将 mysql 下的文件所有者及其文件夹更改为 MySql 用户。这对于 mysql 读取.frm和ibdata文件很重要。

回答by anybudy

Just might be useful for someone:

可能对某人有用：

I could only recover frm files after a disaster, at least I could get the table structure from FRM files by doing the following:

我只能在灾难后恢复 frm 文件，至少我可以通过执行以下操作从 FRM 文件中获取表结构：

1- create some dummy tables with at least one column and SAME NAME with frm files in a new mysql database.

1- 在新的 mysql 数据库中创建一些包含至少一列和 SAME NAME 的虚拟表，其中包含 frm 文件。

2-stop mysql service

2站mysql服务

3- copy and paste the old frm files to newly created table's frm files, it should ask you if you want to overwrite or not for each. replace all.

3- 将旧的 frm 文件复制并粘贴到新创建的表的 frm 文件中，它应该询问您是否要覆盖每个文件。全部替换。

4-start mysql service, and you have your table structure...

4-启动mysql服务，你有你的表结构......

regards. anybudy

问候。任何伙伴

回答by jordan8037310

I answered this question here, as well: https://dba.stackexchange.com/a/42932/24122

我也在这里回答了这个问题：https: //dba.stackexchange.com/a/42932/24122

I recently experienced this same issue. I'm on a Mac and so I used MAMP in order to restore the Database to a point where I could export it in a MySQL dump.

我最近遇到了同样的问题。我在 Mac 上，所以我使用 MAMP 来将数据库恢复到可以将其导出到 MySQL 转储的点。

You can read the full blog post about it here: http://www.quora.com/Jordan-Ryan/Web-Dev/How-to-Recover-innoDB-MySQL-files-using-MAMP-on-a-Mac

您可以在此处阅读有关它的完整博客文章：http: //www.quora.com/Jordan-Ryan/Web-Dev/How-to-Recover-innoDB-MySQL-files-using-MAMP-on-a-Mac

You must have:

你必须有：

-ibdata1

-ibdata1

-ib_logfile0

-ib_logfile0

-ib_logfile1

-ib_logfile1

-.FRM files from your mysql_database folder

mysql_database 文件夹中的 -.FRM 文件

-Fresh installation of MAMP / MAMP Pro that you are willing to destroy (if need be)

- 您愿意销毁的 MAMP / MAMP Pro 的全新安装（如果需要）

1. SSH into your web server (dev, production, no difference) and browse to your mysql folder (mine was at /var/lib/mysql for a Plesk installation on Linux)
2. Compress the mysql folder
3. Download an archive of mysql folder which should contain all mySQL databases, whether MyISAM or innoDB (you can scp this file, or move this to a downloadable directory, if need be)
4. Install MAMP (Mac, Apache, MySQL, PHP)
5. Browse to /Applications/MAMP/db/mysql/
6. Backup /Applications/MAMP/db/mysql to a zip archive (just in case)

7. Copy in all folders and files included in the archive of the mysql folder from the production server (mt Plesk environment in my case) EXCEPT DO NOT OVERWRITE:

   -/Applications/MAMP/db/mysql/mysql/

   -/Applications/MAMP/db/mysql/mysql_upgrade_info

   -/Applications/MAMP/db/mysql/performance_schema

8. And voila, you now should be able to access the databases from phpMyAdmin, what a relief!

1. SSH 连接到您的 Web 服务器（开发、生产、没有区别）并浏览到您的 mysql 文件夹（我的文件夹位于 /var/lib/mysql 以在 Linux 上安装 Plesk）
2. 压缩mysql文件夹
3. 下载 mysql 文件夹的存档，该文件夹应包含所有 mySQL 数据库，无论是 MyISAM 还是 innoDB（如果需要，您可以 scp 此文件，或将其移动到可下载的目录）
4. 安装 MAMP（Mac、Apache、MySQL、PHP）
5. 浏览到 /Applications/MAMP/db/mysql/
6. 将 /Applications/MAMP/db/mysql 备份到 zip 存档（以防万一）

7. 从生产服务器（在我的情况下为 mt Plesk 环境）复制 mysql 文件夹存档中包含的所有文件夹和文件，除了不要覆盖：

   -/应用程序/MAMP/db/mysql/mysql/

   -/Applications/MAMP/db/mysql/mysql_upgrade_info

   -/Applications/MAMP/db/mysql/performance_schema

8. 瞧，您现在应该可以从 phpMyAdmin 访问数据库了，真是一种解脱！

But we're not done, you now need to perform a mysqldump in order to restore these files to your production environment, and the phpmyadmin interface times out for large databases. Follow the steps here:

但是我们还没有完成，您现在需要执行 mysqldump 以将这些文件恢复到您的生产环境，并且 phpmyadmin 界面对于大型数据库会超时。请按照此处的步骤操作：

http://nickhardeman.com/308/export-import-large-database-using-mamp-with-terminal/

http://nickhardeman.com/308/export-import-large-database-using-mamp-with-terminal/

Copied below for reference. Note that on a default MAMP installation, the password is "root".

复制如下供参考。请注意，在默认 MAMP 安装中，密码为“root”。

How to run mysqldump for MAMP using Terminal

如何使用终端为 MAMP 运行 mysqldump

EXPORT DATABASE FROM MAMP[1]

从 MAMP 导出数据库 [1]

Step One:Open a new terminal window

第一步：打开一个新的终端窗口

Step Two:Navigate to the MAMP install by entering the following line in terminal cd /applications/MAMP/library/bin Hit the enter key

第二步：通过在终端 cd /applications/MAMP/library/bin 中输入以下行来导航到 MAMP 安装按回车键

Step Three:Write the dump command ./mysqldump -u [USERNAME] -p [DATA_BASENAME] > [PATH_TO_FILE] Hit the enter key

第三步：写转储命令 ./mysqldump -u [USERNAME] -p [DATA_BASENAME] > [PATH_TO_FILE] 按回车键

Example:

例子：

./mysqldump -u root -p wp_database > /Applications/MAMP/htdocs/symposium10_wp/wp_db_onezero.sql

Quick tip: to navigate to a folder quickly you can drag the folder into the terminal window and it will write the location of the folder. It was a great day when someone showed me this.

快速提示：要快速导航到文件夹，您可以将文件夹拖到终端窗口中，它将写入文件夹的位置。当有人向我展示这个时，这是美好的一天。

Step Four:This line of text should appear after you hit enter Enter password: So guess what, type your password, keep in mind that the letters will not appear, but they are there Hit the enter key

第四步：在你按下回车键后应该会出现这行文字 Enter password: 所以猜猜怎么着，输入你的密码，记住字母不会出现，但它们在那里按回车键

Step Five:Check the location of where you stored your file, if it is there, SUCCESS Now you can import the database, which will be outlined next.

第五步：检查您存储文件的位置，如果在那里，则成功 现在您可以导入数据库，接下来将对其进行概述。

Now that you have an export of your mysql database you can import it on the production environment.

现在您已经导出了 mysql 数据库，您可以将其导入到生产环境中。

回答by Chandan Purohit

I made use of mysqlfrmwhich is a great tool which generates table creation sql code from .frm files. I was getting this nasty table not found error although tables were being listed. Thus I used this tool to regenerate the tables. In ubuntu you need to install this as:

我使用了mysqlfrm一个很好的工具，它可以从 .frm 文件生成表创建 sql 代码。尽管列出了表格，但我收到了这个讨厌的表格未找到错误。因此我使用这个工具来重新生成表格。在 ubuntu 中，您需要将其安装为：

sudo apt install mysql-utilities

then,

然后，

mysqlfrm --diagnostic mysql/db_name/ > db_name.sql

Create a new database and then you can use,

创建一个新的数据库，然后你可以使用，

mysql -u username -p < db_name.sql

However, this will give you the tables but not the data. In my case this was enough.

但是，这将为您提供表格而不是数据。就我而言，这就足够了。

回答by Muddassar Ahmad

I just copy pasted the database folders to data folder in MySQL, i.e. If you have a database called alto then find the folder alto in your MySQL -> Data folder in your backup and copy the entire alto folder and past it to newly installed MySQL -> data folder, restart the MySQL and this works perfect.

我只是将数据库文件夹复制粘贴到 MySQL 中的数据文件夹，即如果您有一个名为 alto 的数据库，则在您的 MySQL 中找到文件夹 alto -> 备份中的数据文件夹，然后复制整个 alto 文件夹并将其粘贴到新安装的 MySQL - > 数据文件夹，重新启动 MySQL，这很完美。

回答by Mohsen Beiranvand

Copy all file and replace to /var/lib/mysql , after that you must change owner of files to mysqlthis is so important if mariadb.service restart has been faild

复制所有文件并替换到 /var/lib/mysql ，之后您必须将文件的所有者更改为mysql这非常重要，如果 mariadb.service restart 失败

chown -R mysql:mysql /var/lib/mysql/*

chown -R mysql:mysql /var/lib/mysql/*

and

和

chmod -R 700 /var/lib/mysql/*

chmod -R 700 /var/lib/mysql/*

回答by swanand keskar

create a new database with same name copy the .frm .ibd files into xampp/mysql/data/[databasename]/

创建一个同名的新数据库将 .frm .ibd 文件复制到xampp/mysql/data/[databasename]/

you will need ibdata file as well which is found inside

您还需要在里面找到 ibdata 文件

xampp/mysql/data/copy the previous ibdata1 file paste in the paste the file and replace it with the existing ibdata file

xampp/mysql/data/将之前的ibdata1文件复制粘贴到粘贴文件中，替换为现有的ibdata文件

[caution: you may loose the contents of the database which are newly created in the new ibdata file]

[注意：您可能会丢失在新 ibdata 文件中新创建的数据库内容]

回答by Dexter

Yes! It is possible

是的！有可能的

Long approach but you can get all the data's using just .frmfiles. Of course you need other files in the mysql/datadirectory.

很长的方法，但您可以仅使用.frm文件来获取所有数据。当然，您还需要目录中的其他文件mysql/data。

My Problem

我的问题

One day my harddisk crashed and got the booting blue screen error. I try connecting with multiple machine and it didn't work. Since it is a booting error i was concered about the files. and i tryed with the secondary harddisk and try to recover the folders and files. I also backed up the full xampp folder c:/xamppjust in case, Because I had no back of the recent databases i got really worried how to retrieve the database. we have lot of clients project management and personal doc in the database.

有一天，我的硬盘崩溃并出现启动蓝屏错误。我尝试连接多台机器，但没有用。由于这是一个引导错误，我很担心这些文件。我尝试使用辅助硬盘并尝试恢复文件夹和文件。我还备份了完整的 xampp 文件夹c:/xampp以防万一，因为我没有最近的数据库，我真的很担心如何检索数据库。我们在数据库中有很多客户项目管理和个人文档。

None of the method listed on the stackoverflow comment works, at-least for me. It took me 2 full days googling for the answer to get the data's from the .frmfiles. Came across multiple approaches from many people but everything was frustration and getting some error or another when implementing. If most of them get it working (based on their comment) then what am i missing.

至少对我来说，stackoverflow 评论中列出的方法都不起作用。我花了整整 2 天的时间在谷歌上搜索答案以从.frm文件中获取数据。遇到了许多人的多种方法，但一切都令人沮丧，并且在实施时遇到了一些错误。如果他们中的大多数人都能正常工作（根据他们的评论），那么我错过了什么。

Because i was so desperate I even reinstall windows which result in loosing all my softwares and tried again. But still the same error

因为我太绝望了，我什至重新安装了 Windows，这导致我所有的软件都丢失并再次尝试。但还是同样的错误

THANKS to Dustin Davis

感谢达斯汀戴维斯

i found the solution in his blog and i managed to get it working exactly the same way he did. Let me give the credit to this guy, Dustin Davis (https://dustindavis.me/restoring-mysql-innodb-files-on-windows/). You could jump from here to his blog and try his method, pretty clear and easy to follow.

我在他的博客中找到了解决方案，我设法让它以与他完全相同的方式工作。让我把功劳归功于达斯汀·戴维斯（Dustin Davis）（https://dustindavis.me/restoring-mysql-innodb-files-on-windows/）。你可以从这里跳到他的博客并尝试他的方法，非常清晰易懂。

But there are something i discovered when trying his approach which he hasn't explained in his blog and i will try my best to explain how i did and what you need to look for.

但是我在尝试他的方法时发现了一些他没有在他的博客中解释的东西，我会尽力解释我是怎么做的以及你需要寻找什么。

Follow this exactly

完全按照这个

IMPORTANT: Make sure you to install the same version of XAMPP. You cannot copy paste from older XAMPP to a new version. This will result in __configor __trackingerrors.

重要提示：确保安装相同版本的 XAMPP。您不能将旧 XAMPP 中的粘贴复制到新版本。这将导致__config或__tracking错误。

How to check your XAMPP version

如何检查您的 XAMPP 版本

1. Go to your xampp folder (your backed up xampp).
2. Open the readme_en.txtfile. which is in the root directory of the xampp.
3. You should see the version on top. ###### ApacheFriends XAMPP Version X.X.XX ######

1. 转到您的 xampp 文件夹（您备份的 xampp）。
2. 打开readme_en.txt文件。这是在 xampp 的根目录中。
3. 您应该会在顶部看到版本。 ###### ApacheFriends XAMPP Version X.X.XX ######

Files require to restore

文件需要恢复

xampp(old folder)/mysql/data/

xampp(old folder)/mysql/data/

ibdata1
ib_logfile0
ib_logfile1
<databasename>/*.frm
<databasename>/*.ibd

Step 1

第1步

1. After installed the same version of xampp.
2. Do not start the apache or myql

1. 安装相同版本的xampp后。
2. 不要启动apache或myql

Step 2

第2步

• Go to the mysql/datafolder and replacethe ibdata1, ib_logfile0, and ib_logfile1
• Now copy paste your databasefolder from your old xampp backup to the newly installed xampp folder c:/xampp/mysql/data/that contain .frmand .ibdfiles, If you are not sure try with one database.

• 转至mysql/data文件夹，并更换了ibdata1，ib_logfile0和ib_logfile1
• 现在，复制粘贴您database从旧XAMPP备份文件夹到新安装的XAMPP文件夹中c:/xampp/mysql/data/包含.frm和.ibd文件，如果你确信尝试用一个数据库没有。

Step 3

第 3 步

• Go to c:/xampp/mysql/binand look for my.cn.
• Open the my.cnfile and look for #skip-innodband under that look for the line that says innodb_log_file_size=5Mchange it to 170M. innodb_log_file_size=170M. This is your log file size and if you are not sure just set it to 170

• 去c:/xampp/mysql/bin寻找my.cn。
• 打开my.cn文件并查找#skip-innodb并在该文件下查找表示innodb_log_file_size=5M将其更改为170M. innodb_log_file_size=170M. 这是您的日志文件大小，如果您不确定，请将其设置为170

Step 4

第四步

• Now open the file mysql_start.bat(Windows Batch file) that is in the c:/xampp/directory.

• Add –innodb_force_recovery=6after the ... --console

  ....
  
  mysql\bin\mysqld --defaults-file=mysql\bin\my.ini --standalone --console
  –innodb_force_recovery=6
  if errorlevel 1 goto error
  goto finish
  

• 现在打开目录中的文件mysql_start.bat（Windows 批处理文件）c:/xampp/。

• –innodb_force_recovery=6在之后添加... --console

  ....
  
  mysql\bin\mysqld --defaults-file=mysql\bin\my.ini --standalone --console
  –innodb_force_recovery=6
  if errorlevel 1 goto error
  goto finish
  

Step 5

第 5 步

• Now Start your Apache and Mysql.
• Go to your phpmyadminand check for your database and its tables. if you do not get any errors you are on the right track.
• Stop the Apache and Mysqland copy paste the rest of the databases.

• 现在启动您的 Apache 和 Mysql。
• 转到您的phpmyadmin并检查您的数据库及其表。如果您没有收到任何错误，那么您就在正确的轨道上。
• 停止 Apache 和 Mysql并复制粘贴其余的数据库。

回答by Azam Alvi

Before starting you should stop the WAMP services, or at least restart the services when prompted to start them.

在开始之前，您应该停止 WAMP 服务，或者至少在提示启动时重新启动这些服务。

On the old server instance navigate to the MySQL data folder by default this should look something similar to C:\wamp\bin\mysql\mysql5.1.53\data\where mysql5.1.53will be the version number of the previously installed MySQL database.

在旧服务器实例导航到MySQL的数据文件夹默认情况下，这个应该类似的东西C:\wamp\bin\mysql\mysql5.1.53\data\在那里mysql5.1.53将是以前安装的MySQL数据库的版本号。

Inside this folder you should see a few files and folders. The folders are the actual MySQL databases, and contain a bunch of .frm files which we will require. You should recognise the folder names as the database names. These folder and all their contents can be copied directly to your MySQL data folder, you can neglect the default databases mysql, performance_schema, test.

在此文件夹中，您应该会看到一些文件和文件夹。这些文件夹是实际的 MySQL 数据库，包含一堆我们需要的 .frm 文件。您应该将文件夹名称识别为数据库名称。这些文件夹及其所有内容可以直接复制到你的 MySQL 数据文件夹中，你可以忽略默认数据库 mysql、performance_schema、test。

If you started the server now you will see the databases are picked up, however the databases will contain none of the tables which were copied across. In order for the contents of the database to be picked up, back in the data folder you should see a file ibdata1, this is the data file for tables, copy this directly into the data folder, you should already have a file in your new data folder called “ibdata1″ so you may wish to rename this to ibdata1.bakbefore copying across the ibdata1from the old MySQL data folder.

如果您现在启动服务器，您将看到数据库被拾取，但是数据库将不包含任何复制的表。为了获取数据库的内容，回到数据文件夹中，您应该看到一个文件ibdata1，这是表的数据文件，将其直接复制到数据文件夹中，您的新数据中应该已经有一个文件文件夹名为“ibdata1”，因此您可能希望ibdata1.bak在ibdata1从旧 MySQL 数据文件夹复制之前将其重命名为。

Once this has been done Restart all the WAMP services. You can use PhpMyAdmin to check if your databases have been successfully restored.

完成此操作后，重新启动所有 WAMP 服务。您可以使用 PhpMyAdmin 检查您的数据库是否已成功恢复。