Mysql--innodb and MyISAM Concepts and data recovery

InnoDB and MyISAM are two of the most common data storage engines for MySQL. Databases such as Oracle and SQL Server have not been researched, so the following is only for MySQL.

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.

Third, InnoDB and myisam other differences:

　　1, MyISAM multiple database and data table record data is a sub-file storage, and InnoDB all the table record data are stored in the Ibdata1 file, so when the data table records less, a single table data file is relatively small, the use of MyISAM performance will be slightly better than InnoDB. However, if the table has a lot of data records, the performance of using InnoDB is much higher than MyISAM, and by testing, the performance of InnoDB is reduced as the table records increase, and myisam performance is reduced rapidly.

2. InnoDB supports transactional operations, while MyISAM does not support transactional operations.

Transactional operations: A set of multiple SQL instructions as a whole can be concurrently manipulated, but the key point is that multiple operations within a transaction must be executed at the same time before the results are submitted. So the SQL within the transaction is either done or not.

Transaction implementations generally have the following two ways:

Way1 begin, rollback, and commit:

Begin represents the start of a transaction, rollback represents a transaction rollback, and a commit represents a transaction commit.

Way2 changes the default auto-commit mode for MySQL by using set:

Set autocommit=0 prohibit autocommit (using transactions, at which point each SQL is treated as a transaction, must show a commit result or rollback revocation result)
Set autocommit=1 turn on Auto commit (disable transaction)

Example: refer here

Mysql--innodb and MyISAM Concepts and data recovery