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
Comments
2 comments
The -R and --triggers options are not needed on the second mysqldump (data) as the functions, stored procedures, and triggers will be migrated in the first (schema) dump. Recent changes in MySQL may make this even more problematic if your schema phase requires updates to the stored procedures and functions (such as ERROR 1418 validations)
Explain sed-i.bak.
Is that a typo?
Please sign in to leave a comment.