Mysql recovers data via FRM&IBD

MySQL is stored on disk, and all kinds of natural disasters can result in data loss. Large companies when we often need to do a good job of data hot and cold, for small companies to do all the data backup needs to spend a lot of costs, many companies are unrealistic. In case the backup is not done, the data has been mistakenly deleted, or ibdata damaged what to do? Don't worry, you can recover some of the data as long as some of the frm and IBD exist.

Attention:

First, this is the data recovery of InnoDB. MyISAM does not need to be so troublesome, as long as the data files exist directly copied in the past.

Second, everyone's MySQL database must be stored by the table data, the default is not, but everyone production is definitely set by the table, if not, sorry, this method can not restore your data.

The My.ini is set to Innodb_file_per_table = 1.

1, retrieve the table structure, if the table structure is not lost directly to the next

A, first create a database, this database must be no table and any operations.

B. Create a table structure that is the same as the name of the table to be restored. The fields in the table don't matter.

It must be innodb the engine. CREATE TABLE 'ax_table' (' Weiboid ' bigint ()) Engine=innodb DEFAULT Charset=utf8;

C, turn off MySQL, service mysqld stop;

D. Overwrite the newly created frm file with the frm file that needs to be restored;

E, modify My.ini Innodb_force_recovery=1, if not modified to 2,3,4,5,6.

F, start Mysql,service mysqld start;show CREATE table ax_table to see the table structure information.

2, retrieve data. Remember the above to change the innodb_force_recovery, need to comment out, otherwise the recovery mode is not good operation.

　　One of the key questions here is that any data operation in InnoDB is a log record point.

　　That is, if we need data recovery, we must add the log record points of the data from the previous table to the same.

A. Set up a database to create a table based on the SQL execution of the created table above.

b, locate the record point. The current database table space should be discarded, so that the current IBD data files and frm separation. ALTER TABLE ax_table DISCARD tablespace;

C. Copy the. ibd file that you want to restore to the new table structure folder. Causes the current IBD and frm to have sex. ALTER TABLE ax_table IMPORT tablespace;

There are no errors at this time and the instructions have been established. If you can find the data, this is OK, if not, please do the following.

D, compared to people here already know why, this model is not to say that the database can be used in the production environment. Change the Innodb_force_recovery=1 if it is not modified to 2,3,4,5,6. Until the data can be queried, then dump it out. The data is backed up.

E. After exporting all the data, import it in the new database. All the data is generated.

Mysql recovers data via FRM&IBD