MySQL Architecture and History - High Performance MySQL - page 28

large databases that aren't suitable for Infobright might be candidates for TokuDB
instead.
Table Conversions
There are several ways to convert a table from one storage engine to another, each with
advantages and disadvantages. In the following sections, we cover three of the most
common ways.
ALTER TABLE
The easiest way to move a table from one engine to another is with an ALTER TABLE
statement. The following command converts mytable to InnoDB:
mysql> ALTER TABLE mytable ENGINE = InnoDB;
This syntax works for all storage engines, but there's a catch: it can take a lot of time.
MySQL will perform a row-by-row copy of your old table into a new table. During that
time, you'll probably be using all of the server's disk I/O capacity, and the original table
will be read-locked while the conversion runs. So, take care before trying this technique
on a busy table. Instead, you can use one of the methods discussed next, which involve
making a copy of the table first.
When you convert from one storage engine to another, any storage engine-specific
features are lost. For example, if you convert an InnoDB table to MyISAM and back
again, you will lose any foreign keys originally defined on the InnoDB table.
Dump and import
To gain more control over the conversion process, you might choose to first dump the
table to a text file using the mysqldump utility. Once you've dumped the table, you can
simply edit the dump file to adjust the CREATE TABLE statement it contains. Be sure to
change the table name as well as its type, because you can't have two tables with the
same name in the same database even if they are of different types—and mysqldump
defaults to writing a DROP TABLE command before the CREATE TABLE , so you might lose
your data if you are not careful!
CREATE and SELECT
The third conversion technique is a compromise between the first mechanism's speed
and the safety of the second. Rather than dumping the entire table or converting it all
at once, create the new table and use MySQL's INSERT ... SELECT syntax to populate
it, 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;