[RndTbl] FYI: mysqldump to create portable SQL

[Adam Thompson]

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 at athompso.net