Can MySQL Table Structure Be Recovered Using Only .frm and .ibd Files?

Restore MySQL table structure using only .frm and .ibd files

Question:

If there is no ib_log file, can I restore the structure of the MySQL table using only the .frm and .ibd files?

Answer:

Table structure can be restored from *.frm files. Please follow these steps:

Step 1: Retrieve SQL Create Query

• Install MySQL Utilities and use the mysqlfrm command to extract the SQL query that creates the table.
• Execute command: mysqlfrm --diagnostic <path>/example_table.frm</path> Get SQL query. (please replace <path></path> with your *.frm file path)

Step 2: Create table

• Create a new table using the retrieved SQL query.

Step 3: Prepare for data recovery

• If old data exists, delete the database and affected tables.
• Remove the connection between the new .frm and .ibd files using the following query: ALTER TABLE example_table DISCARD TABLESPACE;
• Delete existing *.ibd files.

Step 4: Recover Data

• Copy the old *.ibd file into the folder.
• Change its ownership to the mysql user using the following command: chown -R mysql:mysql *.ibd
• Import old data using the following query: ALTER TABLE example_table IMPORT TABLESPACE;

This process will recover table structure and data from .frm and .ibd files.

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