3

Can I convert dumped .sql MyISAM database into InnoDB engine before importing it to my MySQL server? Unfortunately, importing the current .sql file takes ages to complete.

2 Answers 2

7

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
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .