Suppose I have an SQL dump, how do I load this into a fresh mySQL installation...as innodb?
-
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/…– AndyDec 7, 2009 at 10:04
Add a comment
|
3 Answers
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;
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.
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
-
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.– paviumDec 7, 2009 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.– EEAADec 7, 2009 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. Dec 7, 2009 at 3:02