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.

peterh
  • 4,953
  • 13
  • 30
  • 44
Jakub Pastuszuk
  • 153
  • 1
  • 7

2 Answers2

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
Leon Kura
  • 3
  • 2
Tamar
  • 186
  • 2
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

Sudhir
  • 21
  • 3