InnoDB Type MySql Recovery Table Structure and Data

Premise: Save the files that need to recover the database. frm and. ibd files

Condition: InnoDB type

Restore table structure

1. Create a new database-Create a new table with the same 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 under the/usr/local/mysql/data/database with the table. frm format file to be restored
5. Start the mysql server service mysql start
6. Stop database service service ES40stop and comment out innodb_force_recovery = 6 in my. cnf
7. Start the mysql server service mysql start

Recovery of data

1. Restore the table structure first
2. Implement alter table ` user ` discard tablespace; After execution, the user. ibd file in the database directory is gone
3. Put your backup ibd in the missing user. ibd file
4. Give permission to this file chown-R mysql: mysql data owner is mysql
5. Execute alter table ` user ` import tablespace; After execution, the table data is readable, and at this time, some information stored in the system table, such as the number of records in the table row, will be lost

Note: If a foreign key constraint is encountered, prefix the statement with SET FOREIGN_KEY_CHECKS = 0; Remember to change it to 1 when it is finished