On 16/08/12 13:20, Arjen Lentz wrote:
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...
Also:
* Test that you can recover from a backup onto a different machine. It
won't catch all the things which go wrong only occasionally, but
probably will catch things like character set issues in a mysql dump
file, and will make sure that if things go wrong you know your recovery
procedure before you are under time pressure.
* Monitor your backups in some fashion. Periodic testing is good, but
time consuming. At least have some sort of automated check to see that
new backups
* are coming through on the expected schedule.
* have a size within an expected range.
* contain some bit of data which you write each day (or appropriate
period) to the system that will be backed up.
* verify completion. In the case of mysqlbackup, verify the last line
of the dump file, which should say something like "-- Dump completed on
2012-08-16 01:08:08".
Andrew