Migrate MyISAM tables to INNODB using mysqldump

0
324

In this article you will find information 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

LEAVE A REPLY

Please enter your comment!
Please enter your name here