FYI.

When working with a MySQL database last year, I had to find a way to backup the database in such a way that it could be restored with reasonably low effort into any schema, any database, any engine.

As of MySQL v5.0.77 (which is what ships with RHEL/CentOS 5.5) the closest I was able to get was this rather baroque set of command-line options to mysqldump:

========== mysqldump --user=${DBUSER} --password=${DBPASS} --skip-opt --add-drop-table --add-locks --create-options --comments --complete-insert --no-create-db --quick --routines --dump-date --result-file="${HOME}/${DB}.sql" ${DB} ==========

In my case, the various values are hard-coded in root's crontab. (There's little on-host security, as it's a single-function, otherwise-untrusted system.)

Figured I'd post it here since I've discussed it with several people in the group.

Note that by using "--add-drop-table --add-locks --create-options --routines" several MySQL-specific features are introduced into the output file; these features are all easily removable using egrep/sed/vi/whatever as they are trivially regex-matchable. If you know you're going to be importing into a foreign DB, you might want to either not use those particular features, or be aware of the appropriate transform into the target SQL dialect.

Furthermore, mysqldump uses "`" (backtick) to encapsulate namespace objects; other databases use '"' (double-quote) or [] (square-brackets). This, generally, must be handled by a postprocessing step. Note that the mysqldump documentation suggests that the "-Q" or "--quote-names" option is required to produce these quotes; that is not so in 5.0.77. I haven't yet found a way to disable quoting altogether.

For that matter, "--skip-opt" is supposed to turn off several output features, but the observed behaviour does not match the documentation. The one critical feature it does correctly disable is "--extended-insert", which AFAIK is not supported by any other SQL dialect, and is non-trivial to transform in a post-processing step.

-Adam Thompson athompso@athompso.net