从 frm 和 ibd 文件恢复表结构

2021-11-20 00:00:00 sql mysql phpmyadmin innodb

我正在尝试在 PMA 中恢复数据库，但只能访问 frm 和 ibd 文件 - 而不是我理解您需要的 ib_log 文件.

I am trying to restore a database in PMA but only have access to frm and ibd files - not the ib_log files which I understand you need.

我知道我可能无法恢复数据库数据，但是否可以从 frm 文件中恢复表的结构?

I know I may not be able to recover the database data but is it possible to recover the structure of the tables from the frm files?

推荐答案

我仅从 .frm 和 .idb 文件恢复了表格.

I restored the table from only .frm and .idb files.

如果您已经知道表的架构，则可以跳过此步骤.

If you already know the schema of your tables, you can skip this step.

  1. 首先，安装 MySQL 实用程序.然后你可以在命令提示符(cmd)中使用 mysqlfrm 命令.

  1. First, install MySQL Utilities. Then you can use mysqlfrm command in command prompt (cmd).

其次，使用mysqlfrm命令从.frm文件中获取SQL查询:

Second, get the SQL queries from .frm files using mysqlfrm command:

mysqlfrm --diagnostic <path>/example_table.frm

然后您可以获取 SQL 查询以创建相同的结构化表.像这样:

Then you can get the SQL query to create same structured table. Like this:

CREATE TABLE `example_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(150) NOT NULL,
  `photo_url` varchar(150) NOT NULL,
  `password` varchar(600) NOT NULL,
  `active` smallint(6) NOT NULL,
  `plan` int(11) NOT NULL,
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;

创建表

使用上述 SQL 查询创建表.

Create the tables

Create the table(s) using the above SQL query.

如果旧数据仍然存在，您可能需要先删除相应的数据库和表.确保您有数据文件的备份.

If the old data still exists, you may have to drop the respective database and tables first. Make sure you have a backup of the data files.

运行此查询以删除新表数据:

Run this query to remove new table data:

ALTER TABLE example_table DISCARD TABLESPACE;

这会删除新的 .frm 文件和(新的，空的).idb 文件之间的连接.此外，删除文件夹中的 .idb 文件.

This removes connections between the new .frm file and the (new, empty) .idb file. Also, remove the .idb file in the folder.

然后，将旧的.idb文件放入新文件夹中，例如:

Then, put the old .idb file into the new folder, e.g.:

cp backup/example_table.ibd <path>/example_table.idb

确保 .ibd 文件可以被 mysql 用户读取，例如通过在文件夹中运行 chown -R mysql:mysql *.ibd.

Make sure that the .ibd files can be read by the mysql user, e.g. by running chown -R mysql:mysql *.ibd in the folder.

运行此查询以导入旧数据:

Run this query to import old data:

ALTER TABLE example_table IMPORT TABLESPACE;

这会从 .idb 文件中导入数据并将恢复数据.

This imports data from the .idb file and will restore the data.

相关文章