Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Suppose I have an SQL dump, how do I load this into a fresh mySQL innodb?

share|improve this question
ensure you mysqldump --order-by-primary [key] in order to save index space (InnoDB and MyISAM index very differently) and that you really want to do this… – Andy Dec 7 '09 at 10:04
up vote 2 down vote accepted
mysql <database name> -u root -p < file.sql

Then just alter the tables you want to change to innodb

> alter table <foo> set engine=innodb;
share|improve this answer

To expand on rodjek's answer, you can't decide what engine to use on the command line; your SQL dump must already have the engine type set correctly. You can edit the existing dump file, however, and change any instances of ENGINE=MyISAM (or, really, any ENGINE statement that doesn't specify InnoDB) and change it to ENGINE=InnoDB.

share|improve this answer

I believe you'll have to edit the .sql file. At the end of each CREATE TABLE stanza is an ENGINE definition. Right now, I'd guess this says ENGINE=MyISAM. Change this to InnoDB for each table, save the file, then import.

share|improve this answer
This will work if carefully carried out, but (depending on the size of the DB) it may be a large file you have to edit, with very long lines of INSERT statements. – pavium Dec 7 '09 at 2:48
True, though I've overcome this many times in the past by making judicious use of regex search/replace in vim. Any decent text editor should be able to do the same. – EEAA Dec 7 '09 at 2:49
You can simplify things by creating separate dumps for the structure and the data. Then just edit the structure file(s) and import that before importing the data. – John Gardeniers Dec 7 '09 at 3:02

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.