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(a)athompso.net