从frm和ibd文件还原表结构 [英] Restore table structure from frm and ibd files

问题描述

我正在尝试在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

请确保mysql用户可以读取.ibd文件，例如通过运行文件夹中的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.

这篇关于从frm和ibd文件还原表结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！