A tutorial on restoring Mysql ibdata lost or corrupted data via FRM&IBD

MySQL is stored in disk, all kinds of natural and man-made disasters will result in data loss. Large companies when we often need to do good data hot and cold preparation, for small companies to do all the data backup needs to spend a lot of costs, many companies are not realistic. In case the backup has not been done, the data has been mistakenly deleted, or ibdata damaged how to do? Do not worry, as long as some of the frm, IBD exists can recover some of the data.

Attention:

First, this is the InnoDB data recovery. MyISAM does not need such trouble, as long as the data file exists directly copy the past can be.

Second, everyone's MySQL database must be stored according to the table, the default is not, but the production must be set according to 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 step

A, first create a database, this database must be no table and any operations.
b, create a table structure, and the table name you want to recover is the same. The fields in the table don't matter. It's gotta be InnoDB engine. CREATE TABLE ' weibo_qq0 ' (' weiboid ' bigint) engine=innodb DEFAULT Charset=utf8;
c, close MySQL, service mysqld stop;
D, with the need to restore the frm file cover just new frm file;
E, modify My.ini Innodb_force_recovery=1, if not modified for 2,3,4,5,6.
F, start Mysql,service mysqld start;show create table weibo_qq0 can li to table structure information.

2, retrieve the data. Remember above the innodb_force_recovery changed, need to comment out, otherwise the recovery mode is not good operation. The key problem here is that any data operation in the InnoDB is a log point. That is, if we need data recovery, we must add the log points of the previous table's data to the same.

A, set up a database to create a table based on the SQL execution of the created table exported above.
B, find the record point. First the table space of the current database is 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. Make the current IBD and frm have sex. ALTER TABLE weibo_qq0 IMPORT tablespace; The result will be no accident. As we started with the data, the data record points are inconsistent. Let's look at where the point of the IBD record is. Start executing import tablespace, error 1030 (HY000): Got error-1 from storage engine. Find the MySQL error log, InnoDB:Error:tablespace ID in file '. Testweibo_qq0.ibd ' is 112, but in the InnoDB innodb:data I T is 1. Because the record point before Weibo_qq0 is 112, the current table is only created once, so the record point is 1.
D, that how to record from 1 to 112. For ($1=1 $i <=111; $1++) {CREATE TABLE t# (id int) Engine=innodb;} It may be strange why loop 111, not 112. Because it was added once when the table structure was created.
E, MODIFY table structure ALTER TABLE weibo_qq0 discard tablespace, so that the current table structure and IBD out of relationship. Replication. ibd to the current directory structure.
F, to make the original data of IBD and the current frm to establish relations. ALTER TABLE product IMPORT tablespace; There is no mistake at this time, the explanation has been established. But the query data still can't find out.
G, compared to the people here already know why, this model is not said to change the database can be used in the production environment. Change Innodb_force_recovery=1, if not modified to 2,3,4,5,6. Until you can query the data and dump it. The data is backed up.
h, all data are exported after the new database is imported. All the data is generated.

Extended problem, many times we are table structure how to bulk operation, improve speed. Use the Loop! 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 () 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, and pay attention to the copied file permissions.

Loops Import Table space.

ALTER TABLE inv_crawl_weibo_qq0 IMPORT tablespace;

The import was successful without an error.