InnoDB type MySql restore table structure and data

Prerequisite: Save the .frm and .ibd files that need to restore the database

Condition: InnoDB type

Recover table structure

1. Create a new database - create a new table with the same table name and number of columns as the database to be restored
2. Stop the mysql server service mysql stop ,
3. Add innodb_force_recovery = 6 in /usr/local/mysql/my.cnf
4. Overwrite the .frm format file of the table to be restored with the .frm format file in the /usr/local/mysql/data/ database
5. Start the mysql server service mysql start
6. Stop the database service service mysql stop and comment out innodb_force_recovery = 6 in my.cnf
7. Start the mysql server service mysql start

Recover Data

1. Restore the table structure first
2. Execute alter table `user` discard tablespace; After execution, the user.ibd file in the database directory disappears.
3. Put your backed up ibd to the disappeared user.ibd file
4. Add permissions to this file: chown -R mysql:mysql data The owner is mysql
5. Execute alter table `user` import tablespace; After execution, the table data can be read, but some information in the system table, such as the number of table rows, will be lost.

Note: If you encounter a foreign key constraint, add SET FOREIGN_KEY_CHECKS = 0 before the statement; remember to change it to 1 after completion