9

I have a big dump (tens of GB) and I would like to import it into a new table without respecting the ENGINE=INNODB parameter.

I tried several editors dedicated for large files to edit the dump but it always takes a lot of time to save the modifications, so it would make much more sense to just ignore the ENGINE=INNODB parameter and override it (for example with the default engine).

Importing and then changing the engine would not be an option as InnoDB is extremely slow to import the dump, so it would take days.

Erwin Mayer
  • 18,076
  • 9
  • 88
  • 126

2 Answers2

10

mysqldump --compatible=no_table_options

worked for me

Nicolás Ozimica
  • 9,481
  • 5
  • 38
  • 51
Paul van Helden
  • 101
  • 1
  • 2
6

If you are on a linux/unix system how about an in place edit with sed

sed -i 's/ENGINE=INNODB/ENGINE=MYISAM/g' filename
Adrian Cornish
  • 23,227
  • 13
  • 61
  • 77
  • 3
    It's worth mentioning that, in the unlikely event that some of the data in the database actually contains the text `ENGINE=INNODB`, this command would corrupt the data. – Asaph Oct 12 '11 at 13:38
  • 3
    use `sed -i -re 's/^(\) ENGINE=)INNODB/\1MyISAM/gi'`, it should be safe since there should be no unencoded newlines in the actual data. – Stefan Seidel Jan 13 '14 at 10:52
  • Great Halped a Lot. Thanks :) – Hoja May 27 '15 at 10:08