Can I Recover MySQL Table Structure Using Only frm and ibd Files?

Restore MySQL table structure using only frm and ibd files

If the ib_log file is not available, is it possible to restore the table structure using only the frm and ibd files?

Answer:

Yes. In the absence of the ib_log file, the table structure can be recovered from the frm file.

Steps to restore table structure:

1. Extract SQL statements from .frm files:

   • Install MySQL Utilities and use the mysqlfrm command to extract SQL statements from the .frm file.
   • For example: mysqlfrm --diagnostic /path/to/example_table.frm

2. Create a table using SQL statements:

   • Execute the SQL statement obtained in step 1 to create a table with the same structure.

3. Recover data:

   • Use ALTER TABLE example_table DISCARD TABLESPACE to delete any existing data in the newly created table.
   • Replace the newly created .ibd file with the old .ibd file from the backup.
   • Make sure the .ibd file is accessible to MySQL users.
   • Use ALTER TABLE example_table IMPORT TABLESPACE to import data from old .ibd files.

The above is the detailed content of Can I Recover MySQL Table Structure Using Only frm and ibd Files?. For more information, please follow other related articles on the PHP Chinese website!