Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Suppose I have an SQL dump, how do I load this into a fresh mySQL installation...as 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 mysqlperformanceblog.com/2009/01/12/… –  Andy Dec 7 '09 at 10:04
add comment

3 Answers 3

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

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

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.

CREATE TABLE `foo` (
…
) ENGINE=InnoDB
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
add comment

Your Answer

 
discard

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.