Shulou(Shulou.com)06/01 Report--

MySQL is stored on disk, and all kinds of natural disasters and man-made disasters will lead to data loss. Big companies often need to do a good job of data cold and hot backup, for small companies to do all the data backup needs to spend a lot of cost, many companies are unrealistic. What if the backup isn't done yet, the data gets deleted by mistake, or the ibdata gets corrupted? Don't worry, as long as some frm and ibd exist, you can recover some data.

Note:

This is a data recovery for innodb. Myisam doesn't need to be so troublesome, as long as the data file exists, it can be copied directly.

Second, everyone's mysql database must be stored according to the table, the default is not, but everyone must be set according to the table, if not, sorry, this method can not restore your data.

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

First, create a database, which must have no tables and no operations.

b. Create a table structure with the same name as the table to be restored. The fields in the table don't matter.

It has to be an innodb engine. CREATE TABLE `test_table`( `jingfeng` char(20)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

c. close mysql, service mysqld stop;

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

e. Modify innodb_force_recovery=1 in my.cnf, if not, modify it to 2, 3, 4, 5, 6.

f. Start mysql, service mysqld start;show create table test_table\G to see the table structure information.

2) Recovery of data. Remember to change innodb_force_recovery above, you need to comment it out, otherwise recovery mode is not easy to operate.

The key problem here is that any data manipulation in innodb is a log entry point.

That is, if we need data recovery, we must add logging points to the previous table's data to consistency.

a. Establish a database and create tables according to the sql execution of creating tables exported above.

b. Find the recording point. First, we need to discard the current database table space and separate the current ibd data file from frm. ALTER TABLE test_table DISCARD TABLESPACE;

c. Copy the.ibd file to the new table structure folder. Relates the current ibd to frm. ALTER TABLE test_table IMPORT TABLESPACE;

There are no errors at this time, which means that it has been established. If you can find the data, this is OK, if not, please perform the following operations.

d. Compared with here, we already know why, this model does not mean that the database can be used in the production environment. Change innodb_force_recovery=1, if not 2, 3, 4, 5, 6. Until you can find the data, then dump it. The data is backed up.

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

Quoted from: www.cnblogs.com/logo-fox/p/6210533.html

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.