MySQL Conversion/Modification Table Storage Engine detailed description

MySQL table conversion (conversion/modification Table storage engine):

There are several ways to move the table from one engine to another, all with its own pros and cons. 3 common methods are described below.

One: ALTER TABLE

The simplest and quickest way to move a table from one engine to another is to use the ALTER TABLE statement:

Mysql>alter TABLE mytablename engine = engines type

This usage is explained in detail: This syntax is suitable for all storage engines, but there is a "trap": This conversion process consumes a lot of time. MySQL performs a progressive copy of the old table to the new table for this purpose (Row-by-row copy). During this time, the conversion operation may take up all of the server's I/O processing power, and the source table will be read-locked at the time of the conversion. Therefore, do this on a busy table and be aware of it. As an alternative hand break, you can use the following method, which will make a table copy first.

If you do table conversions from one engine to another, all the proprietary features belonging to the original engine are lost, for example, if you convert a InnoDB table to a MyISAM table and turn back, all foreign keys originally defined on the original InnoDB table will be lost.

Two: Dump (dump) and imports (import)

If you want more control over the process of table conversion, you can choose to use the Mysqldump tool to dump the table first (dump) into a text file, and in the edit dump file (dump), modify the CREATE TABLE statement in it. It is important to note that table names and engine types are modified because even if the engine type is different, there are two tables in the same table name that are not allowed in the consolidated database species. In addition, Mysqldump will default to the drop TABLE command before the CREATE TABLE statement, and may lose the original data if you are not aware of it.

Three: CREATE and SELECT

This method of conversion, the speed of the first method and the security of the second method is a balance between. It does not dump the entire table, or convert all the data at once, but instead creates a new table, using the MySQL insert ... Select syntax to transfer data. As follows:

Mysql>create TABLE innodb_table like myisam_table;

Mysql>alter TABLE innodb_table Engine=innodb;

Mysql>insert to Innodb_table SELECT * from myisam_table;

This approach works well if the amount of data data is large. However, a more efficient approach is to populate the table incrementally, committing transactions when populating each incremental block of data, so that the undo log does not become too large. Assuming that the ID is a primary key, you can run the following query repeatedly (each time you increment the values of x and y) until all the data is copied to the new table.

Mysql>start TRANSACTION;

Mysql>insert into innodb_table SELECT * from myisam_table WHERE ID between x and y;

mysql>commit;

When the transfer operation is complete, the source table remains, and you can delete (drop) it after the operation completes, and the new table is already populated. Note: If necessary, lock the source table during conversion to prevent the transfer of replication data inconsistencies

MySQL Conversion/Modification Table Storage Engine detailed description