Can We Recover MySQL Table Structure from FRM and IBD Files Only?

Recover MySQL table structures from FRM and IBD files only

Question:

Users often encounter situations where they need to restore a database but can only access the FRM and IBD files.

Can the table structure be restored?

Yes, table structure can be restored from FRM file even without IB_LOG file.

Steps:

1. Extract SQL creation statement from FRM file:

• Install MySQL Utilities.
• Use the mysqlfrm --diagnostic command to generate SQL create statements for each FRM file.
• The output will contain statements similar to:

<code class="language-sql">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;</code>

2. Use SQL statements to create tables:

• Delete any existing tables (if necessary).
• Execute the SQL create statement obtained in step 1 to create a table with the same structure as the original table.

3. Recover data:

• Use the following command to delete new table data:

<code class="language-sql">ALTER TABLE example_table DISCARD TABLESPACE;</code>

• Delete the corresponding IBD file in the table directory.
• Copy original IBD files to directory:

<code class="language-bash">cp backup/example_table.ibd /path/to/example_table.idb</code>

• Make sure the IBD file has the correct ownership and permissions.
• Import old data:

<code class="language-sql">ALTER TABLE example_table IMPORT TABLESPACE;</code>

The above is the detailed content of Can We Recover MySQL Table Structure from FRM and IBD Files Only?. For more information, please follow other related articles on the PHP Chinese website!