Methods of the MySQL conversion engine

There are many ways to convert a table's storage engine to another engine, each with its pros and cons:

A:alter table

The simplest way to change a table directly from one engine to another engine

ALTER TABLE Tb_name ENGINE=INNODB;

Note: This method takes a long time, and MySQL will copy data from the original table to a new table, which may consume all of the system's IO capability during replication, while the original table will be read-locked, so you need to be very careful when doing this on a busy table. An alternative approach is to use the method discussed behind it. In addition, the conversion table of the storage engine, will lose all the features related to the original engine, such as: Convert InnoDB to MyISAM, and then back to InnoDB, the original INNODB table all foreign keys will be lost.

B: Import and Export

You can use the Mysqldump tool to export data to a file, and then modify the storage engine options for the CREATE TABLE statement in the file, and note that the table name is also modified because the same table name cannot exist in the same database, even if the same name is not used with a different storage engine, and it is important to note that Mysqldump default automatically adds a drop TABLE statement before the CREATE TABLE statement, which may result in data loss.

C: Create and query

Combining the advantages of the previous two methods, you do not need to export the data for the entire table, but instead create a new table and use the Insert...select syntax to export the data to the new table:

CREATE table tb_name_new like Tb_name_old;

ALTER TABLE tb_name_new ENGINE=INNODB;

INSERT INTO Tb_name_new select * from Tb_name_old;

Note: This is good if the data is small, and if the data is large, you might consider batching (select plus a where condition, or using a stored procedure with the limit condition) to perform a transaction commit operation on each piece of data to avoid excessive undo for large transactions. After execution, delete the original table and rename the new table.

D:percona-tookit provides a pt-online-schema-change tool that makes it easy and easy to perform the above process, avoiding the mistakes and tedious work that can be caused by manual manipulation. And, this tool is not locked table.

Methods of the MySQL conversion engine