1. Introduction
Generally, under the InnoDB storage engine, a table consists of three parts: a data dictionary (.frm file), a tablespace (.ibd file), and a log file. Among them, the .frm file stores the table definition, the .ibd file stores the table data and index, and the log file records the modification operations to the table.
2. Operation process
Prerequisite: Have an ibd file with the old table
- Create a new table first (the table structure is the restored table, you need to know the table structure of the restored table);
- discard the tablespace of the current table;
- Close the database service;
- Replace the ibd file of the new table in the old table (the ibd file of the new table may have been deleted during the discarded tablespace operation, just put the ibd file of the old table in the specified directory);
- Turn on the database service;
- Check the original table for any physical or logical errors;
- Import a new tablespace.
Therefore, the command to correctly execute the above process should be:
Discard existing tablespace:
ALTER TABLE table_name DISCARD TABLESPACE;
Close the database service:
service mysql stop
Replace the new .ibd file
Use the cp command or mv command
Turn on the database
service mysql start
Check the original table for any physical or logical errors
Use the mysqlcheck or CHECK TABLE command to check for any physical or logical errors in the original table.
CHECK TABLE
Import a new tablespace:
ALTER TABLE table_name IMPORT TABLESPACE;
Note that this operation should be done with the database closed or no other transactions being accessed to this table to avoid data inconsistency or errors. At the same time, the IMPORT TABLESPACE function does not check the consistency of data, such as foreign key constraints, so you must ensure the integrity of the backup data before importing.
3. Error record
- Execute ALTER TABLE table_name IMPORT TABLESPACE
Prompt Tablespace is missing for table table_name.
Or prompt Internal error: Cannot reset LSNs in table table_name : Tablespace not found
reason:
- Importing a new tablespace without checking the original table for any physical or logical errors will prompt: Table table_name is missing tablespace.
- Files are missing or corrupted:.ibd file may be deleted, moved, or corrupted accidentally. Check if there is a file named table_name.ibd in the file system and make sure it is not corrupted.
- File path error: If the InnoDB storage engine is configured with a non-default data directory, the .ibd file may not be in the expected location. Check the MySQL configuration file (such as or ) to confirm that the data directory settings are correct.
- Permissions issues: Ensure that the MySQL service has sufficient permissions to access the .ibd file. You can try using the ls -l command to check the ownership and permissions of the file.
- The tablespace has not been discarded: Before importing a new .ibd file, you need to use ALTER TABLE st_company_info DISCARD TABLESPACE; to discard the existing tablespace.
- Version incompatible: If you are recovering a .ibd file backed up from a different version of MySQL, there may be a version incompatibility issue. Make sure the backup file is created from the same or compatible version as the currently running MySQL server.
- Other questions: It may also be caused by database corruption or some other rare situations. In this case, you may need to seek professional help to solve the problem.
To further diagnose the problem, you can try the following steps:
- Check whether the correct .ibd file exists in the data directory.
- Use the mysqlcheck or CHECK TABLE command to check for any physical or logical errors in the original table.
- If necessary, try recreating the .ibd file, such as by backup recovery or using third-party tools.
- Start the MySQL server in safe mode to see if it can solve this problem.
The above is the detailed content of MySQL's operation process of recovering data through ibd files. For more information about MySQL's ibd data recovery, please pay attention to my other related articles!