Monday, September 2, 2013

MySQL - changing table engine in very big table dump

Changing table type from MyISAM to InnoDB or some other engine could be very painful if your table has a lot of rows.
If you have a lot of time you can do it using standard ALTER TABLE syntax.
However If you have more than one drive in you box, a lot faster would be dumping and restoring dump from command line.
After dump use sed to change table type:

sed -e "s/Engine=MyISAM/Engine=InnoDB/gi" input.sql > output.sql

Then import your output.sql.