MySQL change table's storage engine

MySQL It provides a variety of database storage engines, the storage engine responsible for MySQL storage and retrieval of data database. Different storage engines have different characteristics, and it is necessary to convert the storage engine of an existing table into another storage engine. There are many ways to complete such conversions, each of which has pros and cons. Should choose according to the actual situation. Here are three ways to convert the MySQL table engine:
ALTER TABLEThe simplest way to convert a MySQL table engine is to change the table definition directly using ALTER TABLE. The following statement converts the MyTable table engine to InnoDBmysql>alter TABLE mytable ENGINE = InnoDB;Advantages: Simple, can be used for whatever the table engine. disadvantage: It takes a very long time to run. The process of conversion is to define a new engine table and then copy the data from the original table. During replication, the system consumes a large amount of I/O capability, and a read lock is added to the original table at the same time. So it doesn't work to use this kind of transformation in a very busy system, even though it's very easy. Also, it is important to note that the conversion of the table engine with such a way would lose the attributes associated with the original table engine. For example, convert a InnoDB table into a MyISAM table and then convert it to a InnoDB table. Even though you didn't do anything. However, the foreign keys in the original table will disappear.

Import and Export To better control the conversion process, you can use the Mysqldump tool to export the data to a file, then change the engine option of the CREATE TABLE statement in the file, and note the change to the table name, because it is not possible to have two tables with the same name in a database. They use different storage engines on the fly. shell>mysqldump-u username-p database Name Table name > file name VI change the CREATE TABLE statement in the file mysql> source with path file name

Create and Query The third approach combines the first efficient and the other way of security.

You do not need to export the entire table of data. Instead, create a new storage engine table and then take advantage of the insert .... The select syntax comes to the data.

mysql> CREATE TABLE innodb_table like myisam_table; mysql>ALTER TABLE innodb_table ENGINE = InnoDB; mysql>INSERT into innodb_table SELECT * from myisam_table;

MySQL change table's storage engine