MySQL conversion table Engine

MySQL conversion table Engine

There are many ways to convert the table storage engine into another engine. Each method has its own advantages and disadvantages. Here we will introduce four methods:

Select the priority (pt-online-schema-change> Create and query> Export and Import> alter table ).
 
• ALTER TABLE

The easiest way to change a TABLE from one engine to another is to use the alter table statement. The following statement modifies the engine of the TB table to InnoDB:

Mysql> alter table tb engine = InnoDB;

PS: This method applies to any storage engine. But there is a problem: it takes a long time to execute.

MySQL will copy the database from the original table to a new table by row. During the replication, all the I/O capabilities of the system may be consumed, and the read lock will be added to the original table.
 
• Export and Import (mysqldump)

To better control the conversion process, you can use mysqldump to export data to a file, modify the storage engine option of the create table statement in the file, and change the TABLE name at the same time, because the same database cannot have the same table name, even if they use different storage engines.

PS: mysqldump will automatically add the drop table statement before the create table statement by default. If you do not pay attention to this, data may be lost.

1. The following statement exports the TB table data in the database to the tb. SQL file:

[Root @ desktop] # mysqldump-u root-p db tb> tb. SQL

2. Modify the tb. SQL file (if you need to back up the TB table, change the table name ):

Create table 'tb '(
'Name' char (1) DEFAULT NULL
) ENGINE = InnoDB default charset = utf8;

3. The following statement imports the tb. SQL file into the database:

[Root @ desktop] # mysql-u root-p DB <tb. SQL

 
• CREATE and SELECT)

This method combines the efficiency of the first method and the security of the second method. You do not need to export the data of the entire table. Instead, you need to create a new storage engine table and then use the INSERT... SELECT syntax to export the data:

If the data volume is small, use the following statement:

Mysql> create table TB2 like TB1;
Mysql> alter table TB2 ENGINE = InnoDB;
Mysql> insert into TB2 SELECT * FROM TB1;

Use the following statement for large data volumes:

Mysql> create table TB2 like TB1;
Mysql> alter table TB2 ENGINE = InnoDB;
Mysql> start transaction;
Mysql> insert into TB2 SELECT * FROM TB1 WHERE id BETWEEN x AND y;
Mysql> COMMIT;

PS: after this operation is completed, the new table is a full copy of the original table, the original table is still there, you can delete the original table as needed.

If necessary, you can lock the original table during execution to ensure that the data of the new table is consistent with that of the original table.

• Use the pt-online-schema-change tool provided by Percona Toolkit:

Run the following command to convert the TB1 table in the database to InnoDB:

[Root @ desktop] # pt-online-schema-change-u root-h 127.0.0.1-p 123456 -- alter = 'Engine = innodb' -- execute A = utf8, D = DB, t = TB1

After successful execution, the following message is displayed:

Successfully altered 'db'. 'tb1 '.

PS: The principle is to create a temporary table-> modify the structure-> record data-> Delete the original table-> rename the temporary table