How to convert a MyISAM .sql dump into InnoDB?

Solution 1:

You can use sed to search for engine myisam and replace it with engine innodb.

sed -e 's/engine myisam/engine innodb/g' file.sql > file_innodb.sql

Solution 2:

If you really want to change the engine before importing, find/replace is the only way. Not sure if changing MyISAM to InnoDB can speedup import, but do consider couple of points:

If you have a large dump, dont use phpMyAdmin for import. With phpMyAdmin we impose php restrictions like max execution time and the import can fail. Use command line - CMD in Windows or Terminal on linux - to login to mysql and then import the dump. This works pretty well, I have done imports of 2.5GB file without problems.

Speed of import depends on the dump itself too. If the dump is created with 1 insert statement per record then its way slow. Select "insert multiple rows in every INSERT statement" during export and then import will be much faster