Shulou(Shulou.com)05/31 Report--

Editor to share with you the InnoDB type of MySql recovery table structure and data example analysis, I hope you will learn something after reading this article, let's discuss it together!

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

Condition: InnoDB type

Restore the 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

two。 Stop the mysql server service mysql stop

3. Add innodb_force_recovery = 6 to / usr/local/mysql/my.cnf

4. Overwrite the table .frm format files that need to be restored over the .frm format files under / usr/local/mysql/data/ database

5. Start the mysql server service mysql start

6. Stop the database service service mysql stop and comment out the innodb_force_recovery = 6 in my.cnf

7. Start the mysql server service mysql start

Recover data

1. Restore the table structure first

two。 After the execution of alter table `user` discard tablespace;, the user.ibd file in the database directory is gone.

3. Put the ibd you backed up in the missing user.ibd file

4. Add permissions to this file chown-R mysql:mysql data owner is mysql

5. After the execution of alter table `user` import tablespace;, the table data can be read, and some information such as the number of table rows and records stored in the system table 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

After reading this article, I believe you have a certain understanding of "sample analysis of InnoDB type MySql recovery table structure and data". If you want to know more about it, welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.