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