Hi Roger
SQL dumps are not by definition good either, the appropriate command line options depend on things like which storage engine(s) are used and what character sets. We regularly encounter dumps that cannot be sanely loaded, primarily due to character set issues. People are exceedingly slack in this regard and only "start caring" when there's a drama which is obviously rather late.
Is there a generic set of commands that should be applied to a mysqldump to guard against such things?
If you have any utf8 data, settings on server/database/table/column/connection level should be appropriate. For connection this means doing SET NAMES=utf8 after creating a connection. mysqldump does this when you specify --default-character-set=utf8 But I mention the whole range because people sometimes stuff utf8 into fields that are not, or where the connection is not. Then data can still get mangled, and MySQL has no way of knowing what you meant. So it's the developer's responsibility. If you have an InnoDB-only environment, passing the --single-transaction option to mysqldump will get you a consistent snapshot (without any locking during the dump!) But as I said, there are many things to consider and these are only a few examples. Also mind the I/O and CPU time mysqldump eats when you do a dump, you may have to nice it to not have it interfere with production work - obviously InnoDB's lock-free consistent reads come in very nicely there, regardless of how long the dump takes it won't have your app wait... Regards, Arjen. -- Exec.Director @ Open Query (http://openquery.com) MySQL services Sane business strategy explorations at http://Upstarta.biz Personal blog at http://lentz.com.au/blog/