Home >Database >Mysql Tutorial >How Can I Restore a MySQL Table Structure and Data Using Only FRM and IBD Files?

How Can I Restore a MySQL Table Structure and Data Using Only FRM and IBD Files?

Barbara Streisand
Barbara StreisandOriginal
2025-01-07 19:17:41803browse

How Can I Restore a MySQL Table Structure and Data Using Only FRM and IBD Files?

Restore MySQL table structure and data using only FRM and IBD files

If only the FRM and IBD files are available, it is possible to recover the table structure even without the IB_LOG file.

Retrieve table creation query

To get the SQL query that created the table, you can use MySQL Utilities and the mysqlfrm command:

<code>mysqlfrm --diagnostic <path>/example_table.frm</path></code>

This command will extract the SQL query used to create the table, which can be used to reconstruct the table structure.

Create table

Once you have the SQL query, you can use standard MySQL commands to create the table. If the existing table contains old data, you may need to delete the table with the same name.

Recover data

To recover data, you can perform the following steps:

  1. Use the ALTER TABLE example_table DISCARD TABLESPACE; command to truncate the newly created table.
  2. Delete the newly created IBD file and replace it with the original IBD file.
  3. Change the file permissions to allow MySQL access by running the chown -R mysql:mysql *.ibd command.
  4. Use the ALTER TABLE example_table IMPORT TABLESPACE; command to import old data.

This process will restore data from the IBD file to the table, completing the recovery of the table structure and data.

The above is the detailed content of How Can I Restore a MySQL Table Structure and Data Using Only FRM and IBD Files?. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn