How does mysql recover data from ibd files

Editor to share with you how mysql recovers data from ibd files, I believe most people don't know much about it, so share this article for your reference. I hope you can learn a lot after reading this article. Let's learn about it together.

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

How mysql recovers data from ibd files:

1. Create a table with the same structure as the original table:

CREATE TABLE...

2. Delete the newly created tablespace:

ALTER TABLE DISCARD TABLESPACE

3. Copy the .ibd file to be restored to the target database folder, and modify the file permissions:

Cp .ibd / var/lib/mysql/cd / var/lib/mysql/chown mysql:mysql .ibd

4. Import tablespaces:

ALTER TABLE IMPORT TABLESPACE

The following problems may also occur:

1. Mysql 1808 error:

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

This is due to the error caused by restoring the files of mysql 5.6to version 5.7 of mysql. You need to 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

The ibd file of copy is not authorized. Please follow step 2 to execute the permission.

The above is all the contents of the article "how mysql recovers data from ibd files". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!