You have an error in your SQL syntax near 'ENGINE=MyISAM DEFAULT CHARSET=latin1'.This error message is hardly mentioned at all on the web, and you really have to hunt for the appropriate solution, so I've created this brief web page about what I've learned.
The best solution seems to be to use the "--compatible" option to mysqldump, to ensure that the dump file created with the later version of mysql is still readable by the earlier version. Unfortunately the "--compatible" option is not mentioned in "man mysqldump", and you have to hunt to find out about it (it is sometimes but not always listed under "mysqldump -help"). But apparently there are many optional arguments to --compatible:
ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, no_field_optionsFor my purposes -- copying a database from a computer running mysql version 14.12 to one running version 11.16 -- it seems that the "mysql40" option is the one that works.
Thus, to dump the database on the first computer, I use a (Linux) command like:
mysqldump -uroot -p --opt databasename --compatible=mysql40 > dumpfilenameThen, to restore the database on the second computer, I first copy the dumpfile to the new computer (using "scp"), and then on the new computer use a command (after creating databasename in mysql if it doesn't already exist) like:
mysql -uroot -p databasename < dumpfilenameThat seems to do the trick. Phew!
-- Jeffrey Rosenthal (contact me)