InnoDB MySql restores table structure and data, innodbmysql

InnoDB MySql restores table structure and data, innodbmysql

Prerequisites: The. frm and. ibd files for database recovery are saved.

Condition: InnoDB type

Restore table structure

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

Restore data

1. Restore the table structure first
2. Execute alter table 'user' discard tablespace. After the execution, the user. ibd file in the database directory will be gone.
3. Put the ibd you backed up to the disappearing user. ibd file.
4. Add the chown-R mysql permission to this file: the mysql data owner is mysql.
5. Execute alter table 'user' import tablespace; after the execution, the table data will be readable and some information such as the number of table row records will be lost in the system table.

Note: In case of foreign key constraints, add SET FOREIGN_KEY_CHECKS = 0 before the statement. After the statement is complete, change it to 1.