How to recover data through ibd file in Mysql

Recovery steps

1. Create a database (create at will)

2. Create a data table(remarks : The table structure must be consistent with the table structure to be restored, and row_format must be consistent with the row_format of the ibd file. Otherwise, it will prompt that the two are inconsistent. Current row_format=dynamic)

3. View table attributes

We use: show table status like ‘matlab’\G to view the table attributes

Note: The row_format when creating the table is inconsistent with the table attributes. Based on innodb, when you want to set row_format to dynamic, you need to modify the global configuration of mysql. Modify it directly in the myql command: set global innodb_file_format=Barracuda;

4. Table For error information, check show warnings

5. Description

When we restore the table, we must ensure that the row_format and ibd files are consistent. If the ibd file is compact If so, when you need to create a table, set it to row_format=compact. When restoring, solve it by yourself. Starting from step 6, focus on how to restore.

6. The first step of recovery: remove the table space

 **alter table matlab DISCARD TABLESPACE;**

7. The second step of recovery: Put the backed up ibd file into Go to mysql->data->created database name->, copy ibd to this directory, as shown in the figure below
View the specific path

-- 使用sql查询数据目录
**show global variables like '%datadir%';**

8. Restore the third Step: Re-import the table space

 **alter table matlab IMPORT TABLESPACE;**

9. When the previous step is completed, we find that the data in the database has been completely restored

10 , The structure of the data table must be consistent with the structure of the data table before recovery, otherwise the recovery will fail, causing the mysql process to restart. For details, you need to check the mysql error log for analysis. In addition, the mysql CE tool provides convenient mysql parameter configuration modifications. Function

Supplement: The difference between Innodb and MyISAM storage files

Innodb storage files are divided into: .frm, .idb
.frm: storage table Definition
.ibd: Store data and index
MyISAM storage files are divided into: .frm, .myd, .myi
.frm: Storage table definition
.myd: Store data
.myi :Storage index

The above is the detailed content of How to recover data through ibd file in Mysql. For more information, please follow other related articles on the PHP Chinese website!