MySQL InnoDB 数据库还原
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/115681/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL InnoDB database restore
提问by Jaymie Thomas
I have to restore a database that has been inadvertently DROPped in MySQL 5.0. From checking the backup files, I only seem to have .FRM files to hold the database data.
我必须恢复在 MySQL 5.0 中无意中删除的数据库。通过检查备份文件，我似乎只有 .FRM 文件来保存数据库数据。
Can anyone advise whether this is all I need to perform a database restore/import from the backup, or are there other files I should have to hand to complete this?
任何人都可以建议这是否是我从备份中执行数据库还原/导入所需的全部内容，或者我还需要其他文件来完成此操作吗？
回答by tpk
.frm files are not the data files, they just store the "data dictionary information" (see MySQL manual). InnoDB stores its data in ib_logfile* files. That's what you need in order to do a backup/restore. For more details see here.
.frm 文件不是数据文件，它们只是存储“数据字典信息”（参见MySQL 手册）。InnoDB 将其数据存储在 ib_logfile* 文件中。这就是您进行备份/恢复所需要的。有关更多详细信息，请参阅此处。
回答by Ash
Restoring innodb: (assuming your data folder is C:\ProgramData\MySQL\MySQL Server 5.5\data)
恢复innodb：（假设你的数据文件夹是C:\ProgramData\MySQL\MySQL Server 5.5\data）
- Copy the folders of the databases (named after the database name) you want to restore to C:\ProgramData\MySQL\MySQL Server 5.5\data
Copy the 3 ibdata files to the data folder ex. (C:\ProgramData\MySQL\MySQL Server 5.5\data)
_ib_logfile0 _ib_logfile1 _ibdata1Get the size of the _ib_logfile0 in MB (it should be the same as _ib_logfile1) by File Right click -> Properties
Edit the mysql config file (mysql\bin\my.ini) for the innodb_log_file_size=343M to be exactly the ibdata files size
Run
mysqld --defaults-file=mysql\bin\my.ini --standalone --console --innodb_force_recovery=6
Now your data should be back in your database. Export them using phpmysql or any other tool
- 将要还原的数据库文件夹（以数据库名称命名）复制到C:\ProgramData\MySQL\MySQL Server 5.5\data
将3个ibdata文件复制到数据文件夹ex。(C:\ProgramData\MySQL\MySQL Server 5.5\data)
_ib_logfile0 _ib_logfile1 _ibdata1通过文件右键单击 -> 属性获取以 MB 为单位的 _ib_logfile0 的大小（它应该与 _ib_logfile1 相同）
将 innodb_log_file_size=343M 的 mysql 配置文件 (mysql\bin\my.ini) 编辑为 ibdata 文件大小
跑
mysqld --defaults-file=mysql\bin\my.ini --standalone --console --innodb_force_recovery=6
现在您的数据应该回到您的数据库中。使用 phpmysql 或任何其他工具导出它们
回答by Jaymie Thomas
Oh my... you're in trouble. Shutdown the database. Backup the innodb file. Pray that you did NOTHING after you dropped the database.
哦，天哪……你有麻烦了。关闭数据库。备份 innodb 文件。祈祷你在删除数据库后什么也没做。
The guys at Percona (includes authors of MySQL Performance Blog) should be able to help you out: Percona emergency support.
Percona 的人（包括 MySQL 性能博客的作者）应该能够帮助您：Percona 紧急支持。
If you do venture on your own, take this with you: Data Recovery Toolkit for Innodb.
如果您自己冒险，请随身携带：Data Recovery Toolkit for Innodb。
The logistics of it? You have to read every page (the computer term page... 16k block in the case of Innodb) in and rebuild your data that way. It's very low-level work (we're talking open up your hex editor and start counting bytes if the toolkit doesn't do it for you), and if you're not a strongly experienced programmer, you're going to be hurting.
物流呢？您必须阅读每一页（计算机术语页...在 Innodb 的情况下为 16k 块）并以这种方式重建数据。这是非常低级的工作（我们说的是打开你的十六进制编辑器并开始计算字节，如果工具包不适合你），如果你不是一个经验丰富的程序员，你会受到伤害.
回答by Ragen Dazs
The detailed solution you can found here:
您可以在此处找到详细的解决方案：
http://www.unilogica.com/mysql-innodb-recovery/(Article in Portuguese)
http://www.unilogica.com/mysql-innodb-recovery/（葡萄牙语文章）
Besides the flag of innodb_force_recovery, I found another solution: innodb_file_per_table, that splits InnoDB tables in each file like MyISAM tables.
除了innodb_force_recovery的标志，我找到了另一个解决方案：innodb_file_per_table，它将 InnoDB 表拆分为每个文件中的表，如 MyISAM 表。
In a crash recovery you can lost less data than in single file ibdata1.
在崩溃恢复中，与单个文件ibdata1相比，您丢失的数据更少。
