How to recover data from ibd file in mysql

Mysql method to restore data from ibd file: first create a table with the same table structure as the original table structure; then delete the newly created table space; then copy the [.ibd] file to be restored Go to the target database folder and modify the file permissions; finally import the table space.

The operating environment of this tutorial: windows7 system, mysql5.6&&mysql5.7 version, Dell G3 computer.

Related free learning recommendations: mysql database(Video)

##mysql Method to restore data from ibd file:

1. Create a table whose structure is consistent with the original table structure:

CREATE TABLE <table_name> ...;

2. Delete the newly created table space:

ALTER TABLE <table_name> DISCARD TABLESPACE;

3. Copy the

2d8f501bba7dbcabf64f6c8aceccc3b7.ibd file to be restored to the target database folder, and modify the file permissions:

cp <table_name>.ibd /var/lib/mysql/<database_name>
cd /var/lib/mysql/<database_name>
chown mysql:mysql <table_name>.ibd

4. Import the table space:

ALTER TABLE <table_name> IMPORT TABLESPACE;

The following problems may also occur:

1. Mysql 1808 error:

Error Code: 1808. Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, <table_name>.ibd file has ROW_TYPE_COMPACT row format.)

This is an error caused by restoring the mysql 5.6 file to the mysql 5.7 version and needs to be built Add

ROW_FORMAT=COMPACT after the table statement, as shown below:

create table test(id int, name varchar(10)) row_format=compact;

2, mysql 1812 error:

Error Code:1812. Tablespace is missing for table <table_name>

copy ibd file is not authorized, please follow the second step Step execution permission

Related free learning recommendations:

php programming(video)

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