InnoDB and MyISAM Data recovery

(Turn from) https://www.cnblogs.com/DwyaneTalk/p/4113829.html

Data storage structure in one or two ways:

Under MyISAM, every data table in the database has *.FRM, *. Ymi and *. Ymd three files, where *.FRM stores the table structure of a data table, *. Myi the index of the stored data table, *. Record data of myd data table;

Under InnoDB, each data table under each database has only one *.frm to store the table structure of the data table, and all table data indexes and data records for all databases are all stored in the Ibdata1 file, while IB_LOGFILE0 and ib_logfile1 are log files.

Second, data import and recovery:

Case1 Myisam = "Myisam:

Because MyISAM data table structure, index, record data and other information are stored in *.frm, *. Myi and *. MyD file, you only need to copy the corresponding three files from the source database data table to the corresponding destination database folder.

However, if only *.frm (assuming Test.frm) is available, you can copy the test.frm to the corresponding database directory (assuming the TMP database) and create a new test.myi and Test.myd file under the TMP directory. At this time through "show tables;" You can see the test table, but look at the table data, such as "desc test;", where an error cannot be viewed because only test.frm has data, and Test.myi and test.myd are new invalid files. You can then repair the data table with the repair operation "repairtable test use_frm" from MySQL, and then you can view the table's data, but it is empty (because the data is not included in test.frm).

Case2 Innodb = "Innodb:

Due to InnoDB, the table structure exists *.frm file, but the table data is stored in the Ibdata1 file, so when importing, in addition to copy *.frm, but also copy the source MySQL data directory ibdata1, replace the destination database Ibdata, If there are other databases that already exist in the destination database, you need to back up the original data of the destination database, then export the newly imported table in another way, and then import the newly exported data by restoring the original data.

If there is only *.frm (assuming test.frm), there is no valid ibdata1, then the structure of the table can only be restored. First copy the test.frm to another database (assuming TMP), and then create a new INNODB data table test under TMP (which fields are not important), and do not add any records. Then the test.frm that will be recovered is copied to the test directory instead of the test.frm generated by the new test table, and after restarting MySQL, the new test table has the same structure as the test table to be recovered

Case3 Myisam = "Innodb and Innodb =" Myisam:

If there is *.frm, *. Myi and *. MyD three files, can be imported by Case1 method, re-export SQL or other files, can also be modified to InnoDB after import, then through the Case2 way. Same InnoDB = "MyISAM operation is similar.

For database data, in addition to recovering from a. frm file, restoring from a log file is also an option if there is a log file. However, for databases, porting, or replacing the system, it is best to import the appropriate SQL statement files around the export tool.

InnoDB and MyISAM Data recovery