Mysql5.7 uses frm and ibd to recover data

Mysql5.7 uses frm and ibd to recover data

1. Create a new database with the same name, and create a new table with the same name in it, with arbitrary fields.

-if you have the table structure of the original table, this step can be omitted

Add my.cnf profile to innodb_force_recovery=6

2. Overwrite the backup .frm file over the .frm file of the newly created table, modify the file owner and permissions, and restart the database. At this time, the err file will prompt you that the number of fields in the table structure is inconsistent.

3. Create a table with the same name repeatedly. There are as many fields as prompted, and the field name type is arbitrary.

4. Overwrite the backup .frm file over the .frm file of the newly created table, modify the file owner and permissions, and restart the database; so that the table structure is restored.

5. Rebuild the new table according to the table structure, and specify the ROW_FORMAT attribute of the table, ROW_FORMAT=COMPACT

CREATE TABLE ty (

Id bigint (20) NOT NULL AUTO_INCREMENT COMMENT 'primary key'

Valid int (11) whether DEFAULT'1' COMMENT'is valid 1: valid 0: invalid'

Rel varchar (25) DEFAULT NULL

PRIMARY KEY (id)

) DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

In this way, the table structure is restored.

6. Restore data

Execute the discard tablespace command in the specified database

Alter table ty discard tablespace

7. Copy the original ibd file, overwrite the newly created table ibd, and modify the permissions

Chown mysql.mysql ty.ibd

8. Import tablespaces

Alter table ty import tablespace

So the whole data is restored.

-End-By-TangYun-2019.10.14-