Mysql Ibdata lost or corrupted how to recover data through 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.

Reference http://blog.chinaunix.net/uid-24111901-id-2627876.html

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 ' weibo_qq0 ' (' 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 weibo_qq0 will be able to Li to 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 weibo_qq0 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 weibo_qq0 IMPORT tablespace; The result will be a definite error. The data record points are inconsistent, as we started with the data. Let's take a look at where the previous IBD record points are. Start execution Import tablespace error 1030 (HY000): Got error-1 from storage engine. Find the error log for MySQL, InnoDB:Error:tablespace ID in file '. \test\weibo_qq0.ibd ' was, but in the InnoDB Innodb:data dictionary It is 1. Because the record point before Weibo_qq0 is 112, the current table is created only once, so the record point is 1.

D, that how from 1 records to 112. for ($1=1; $i <=111; $1++) {CREATE TABLE t# (id int) Engine=innodb;} Maybe it's weird why it's Loop 111, not 112. Because a record has been added once when a is executed to create the table structure.

e, modify the table structure of ALTER TABLE weibo_qq0 discard tablespace; make the current table structure and IBD out of the relationship. Copy. IBD to the current directory structure.

F. Establish a relationship between the IBD of the original data and the current frm. ALTER TABLE product IMPORT tablespace; There are no errors at this time and the instructions have been established. But the query data still can't find out.

g, 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.

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

Extension problem, many times we are the table structure how to bulk operation, improve the speed of it. With loops! The loop discards the space of the table.
For i in ' seq 0 111 '; Do mysql-uroot-p33061-h127.0.0.1-dtestdd-e "CREATE TABLE inv_crawl_weibo_qq$i (id bigint (a) not NULL auto_increment, PRIMARY KEY (ID)) Engine=innodb "; Done
ALTER TABLE inv_crawl_weibo_qq0 DISCARD tablespace;
Copy the IBD from the backup data to the DD database, paying attention to the file permissions copied over.
Loop through the table space.
ALTER TABLE inv_crawl_weibo_qq0 IMPORT tablespace;
The import was successful without an error.

Mysql Ibdata lost or corrupted how to recover data through FRM&IBD