Migrate MyISAM tables to INNODB using mysqldump

In this article, you will find information on how to convert MyISAM tables to INNODB.

The procedure here is an offline procedure, i.e, you cannot have changes on the SOURCE database, while you do this. A better way could be to setup replication between the SOURCE and the TARGET, but that is not described here.

On the source database
In this example, we dump out the database myDB into mydb_schema.sql and mydb_data.sql

mysqldump -uroot -p --no-data -R --triggers mydb > mydb_schema.sql

Then dump out the data:

mysqldump -uroot -p --no-create-info -R --triggers mydb > mydb_data.sql

Change Storage Engine

You must verify first you don’t have any MYISAM tables with FULLTEXT, since InnoDB only supports FULLTEXT from MySQL version 5.6.

If you have FULLTEXT indexes, then change manually all but the full-text tables to InnoDB (not shown here).

sed -i.bak 's#MyISAM#innodb#g' mydb_schema.sql

On the TARGET database server
Create the database on the target database server and load in the dump into the database myDB:

# create the database
mysql -uroot -p -e "create database mydb"
# create the schema
mysql -uroot -p mydb < mydb_schema.sql
# load in the data
mysql -uroot -p mydb < mydb_data.sql