MySQL conversion/modification table Storage Engine

MySQL conversion/modification table Storage Engine

MySQL table conversion (convert/modify table storage engine ):

There are several ways to transfer a table from one engine to another, all of which have their own advantages and disadvantages. The following describes three common methods.

I. ALTER TABLE

The easiest and quickest way to transfer a TABLE from one engine to another is to use the alter table statement:

Mysql> alter table mytablename ENGINE = ENGINE type

This syntax is suitable for all storage engines, but there is a "trap": This conversion process consumes a lot of time. For this reason, MySQL needs to execute Row-By-Row Copy from an old table to a new table ). During this period, the conversion operation may take up all the I/O processing capabilities of the server, and the source table will be read lock during conversion. Therefore, pay attention to this operation on a busy table. As an alternative to manual disconnection, you can use the following method to copy a table first.

If you convert a table from one engine to another, all the dedicated features of the original engine will be lost. For example, if you convert an innoDB table to a MyISAM table, all foreign keys originally defined in the original InnoDB table will be lost.

2. Dump and Import)

If you want to perform more control over the table conversion process, you can use the mysqldump tool to dump the table into a text file and edit the dump file ), modify the create table statement. Be sure to modify the table name and engine type, because even if the engine type is different, two tables with the same table name are not allowed in the unified database. In addition, before the create table statement, mysqldump will add the drop table command by default. If you do not pay attention, the original data may be lost.

3. CREATE and SELECT

This method achieves a balance between the speed of the first method and the security of the second method. Instead of dumping the entire table or converting all the data at a time, it creates a new table and transfers the data using the INSERT... SELECT Syntax of MySQL. As follows:

Mysql> create table innodb_table LIKE myisam_table;

Mysql> alter table innodb_table ENGINE = innoDB;

Mysql> insert into innodb_table SELECT * FROM myisam_table;

This method works well if the data volume is large. However, the more efficient way is to incrementally fill the table and commit transactions when filling each incremental data block. This will not cause the Undo Log to become too large. If the id is the primary key, you can run the following query repeatedly (increasing the values of x and y each time) 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;

After the transfer operation is complete, the source table will still be retained. You can delete it after the operation is complete. At this time, the new table has been filled. NOTE: If necessary, lock the source table during conversion to prevent data inconsistency during transfer replication.