To convert a non-InnoDB table to use
InnoDB use ALTER
TABLE:
ALTER TABLE table_name ENGINE=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.
To make an InnoDB table that is a clone of a MyISAM table:
Create an empty
InnoDBtable with identical definitions.Create the appropriate indexes.
Insert the rows with
INSERT INTO.innodb_tableSELECT * FROMmyisam_table
You can also create the indexes after inserting the data. Historically, creating new secondary indexes was a slow operation for InnoDB, but this is no longer the case.
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:
INSERT INTO newtable SELECT * FROM oldtable WHERE yourkey >somethingAND yourkey <=somethingelse;
After all records have been inserted, you can rename the tables.
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 13.6.7.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.