14.6.5.4 Converting Tables from MyISAM to InnoDB

Do not convert MySQL system tables in the mysql
database (such as user or
host) to the InnoDB type.
This is an unsupported operation. The system tables must always be
of the MyISAM type.

InnoDB does not have a special optimization for
separate index creation the way the MyISAM
storage engine does. Therefore, it does not pay to export and import
the table and create indexes afterward. The fastest way to alter a
table to InnoDB is to do the inserts directly to
an InnoDB table. That is, use ALTER
TABLE ... ENGINE=INNODB, or create an empty
InnoDB table with identical definitions and
insert the rows with INSERT INTO ... SELECT * FROM
....

If you have UNIQUE constraints on secondary keys,
you can speed up a table import by turning off the uniqueness checks
temporarily during the import operation:

SET unique_checks=0;
... import operation ...
SET unique_checks=1;

For big tables, this saves disk I/O because
InnoDB can use its
insert buffer to write
secondary index records as a batch. Be certain that the data
contains no duplicate keys.
unique_checks permits but does not
require storage engines to ignore duplicate keys.

To get better control over the insertion process, you might insert
big tables in pieces:

During the conversion of big tables, increase the size of the
InnoDB buffer pool to reduce disk I/O, to a
maximum of 80% of physical memory. You can also increase the sizes
of the InnoDB log files.

Make sure that you do not fill up the tablespace:
InnoDB tables require a lot more disk space than
MyISAM tables. If an ALTER
TABLE operation runs out of space, it starts a rollback,
and that can take hours if it is disk-bound. For inserts,
InnoDB uses the insert buffer to merge secondary
index records to indexes in batches. That saves a lot of disk I/O.
For rollback, no such mechanism is used, and the rollback can take
30 times longer than the insertion.

In the case of a runaway rollback, if you do not have valuable data
in your database, it may be advisable to kill the database process
rather than wait for millions of disk I/O operations to complete.
For the complete procedure, see
Section 14.6.12.2, “Forcing InnoDB Recovery”.

If you want all new user-created tables to use the
InnoDB storage engine, add the line
default-storage-engine=innodb to the
[mysqld] section of your server option file.