How to recover data through frm & IBD when MySQL ibdata is lost or damaged

MySQL is stored on disks, and various disasters may cause data loss. Big companies often need to do a good job of cold and hot data backup. for small companies, it is unrealistic for many companies to do a good job of data backup, which requires a lot of costs. What if the backup is not completed, the data is deleted by mistake, or the ibdata is damaged? Don't worry, as long as some frm and IBD exist, some data can be restored.

Note:
1. InnoDB data recovery. MyISAM does not need to be so troublesome, as long as the data file is directly copied.
2. All MySQL databases must store data by table. The default value is not. However, production must be set by table sharding. If not, sorry, this method cannot restore your data. Set my. ini 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, go to the next step.

A. Create a database first. The database must have no tables or any operations.

 

B. Create a table structure with the same name as the table to be restored. The fields in the table do not matter. It must be the InnoDB engine. Create Table 'weibo _ qq0' ('weiboid' bigint (20) engine = InnoDB default charset = utf8;

 

C. Disable MySQL and service mysqld stop;

 

D. overwrite the newly created frm file with the frm file to be restored;

 

E. Modify innodb_force_recovery = 1 in my. ini. If it cannot be changed to 2, 3, 4, 5, 6.

 

F. Start MySQL, service mysqld start; show create table weibo_qq0 to Li to the table structure information.

2. Retrieve data. Remember that innodb_force_recovery has been removed and needs to be commented out. Otherwise, the recovery mode is not easy to operate. The key issue here is that any data operation in InnoDB is a log record point. That is, if we need to recover data, we must add the log records of the previous table to the same.

A. Create a database and execute the SQL statement to create a table based on the table created in the preceding export.

 

B. Find the Record Point. First, discard the tablespace of the current database to separate the data file of the current IBD from frm. Alter table weibo_qq0 discard tablespace;

 

C. Copy the previously recovered. IBD file to the new table structure folder. Associate the current IBD with frm. Alter table weibo_qq0 import tablespace; an error is certainly returned If no result is returned. As we said at the beginning of data development, the data record points are inconsistent. Let's see where the IBD record was located. Run import tablespace and report error 1030 (hy000): Got error-1 from storage engine. Find the MySQL Error Log, InnoDB: Error: tablespace ID in file '. \ test \ weibo_qq0.ibd 'is 112, but in the InnoDB: data dictionary it 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. How to record from 1 to 112. For ($1 = 1; $ I <= 111; $1 ++) {CREATE TABLE T # (ID INT) engine = InnoDB;} may be strange, why is loop 111, not 112. This is because the record has been added once when a executes table structure creation.

 

E. Modify the table structure alter table weibo_qq0 discard tablespace to disassociate the current table structure from IBD. Copy. IBD to the current directory structure.

 

F. Establish the IBD of the original data with the current frm. Alter table Product Import tablespace; there is no error at this time, indicating that the table has been created. However, the data cannot be queried.

 

G. You already know why. This mode does not mean that the database can be used in the production environment after it is changed. Change innodb_force_recovery = 1, if not, to 2, 3, 4, 5, 6. Until the data can be queried, and then dump it out. The data is backed up.

 

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

The expansion problem is that we often use the table sharding structure to Perform Batch operations to increase the speed. Use loop! Loop to discard the table space.
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 (20) not null auto_increment, primary Key (ID) engine = InnoDB "; done
Alter table inv_crawl_weibo_qq0 discard tablespace;
Copy IBD to the DD database from the backup data. Pay attention to the copied File Permission.
Cyclically import tablespaces.
Alter table inv_crawl_weibo_qq0 import tablespace;
If no error is reported, the import is successful.

Original article, reprinted Please note:Reprinted from lanceyan

Link:How to recover data through frm & IBD when MySQL ibdata is lost or damaged