MySqlDump Version Fix (August 2006)

One way to transfer a mysql database from one computer to another is via "mysqldump". That mostly works great, with one potential problem: If the two computers are running different versions of mysql, then a dump file created by one computer might not be readable by the other, leading to an error message like:

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_options

For 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 > dumpfilename

Then, 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 < dumpfilename

That seems to do the trick. Phew!

-- Jeffrey Rosenthal (contact me)