Re: [gentoo-user] [OT] Incomplete mysql backup

[kashani <kashani-list@badapple.net>]

On 8/19/2010 12:03 PM, Mick wrote:
> I use mysqldump to back up a database from a development environment and
> upload it to a production environment.
>
> A couple of days ago I was surprised to see that I was getting errors as soon
> as I uploaded the backed up database to the production machine!  I repeated
> the backup (more in disbelief than anything else) but the error remained.
>
> I spent a few minutes looking around and scratching my head as to what was
> amiss with it, until eventually I noticed that the recent backup was smaller
> than the previous version (it should have been bigger due to extra data that
> has accumulated in the database).  I had another final go in running the same
> good ol' mysqldump command and this time it worked.  The backup was a
> reasonable size and the upload restored the application in the production
> environment in a good working order.
>
> Is there a right and a wrong way of backing up mysql?  Did I do something
> wrong?  How should one verify that a back up is sound?  (Imagine trying to
> restore from that incomplete backup!)

mysqldump -A --single-transaction

That's usually the best way to backup if you have a single machine. 
Without --single-transaction you may or may not get a proper backup when 
using Innodb tables on a busy server.

	However in a busy production environment it's usually best to use a 
slave to do backups. Bringing LVM snapshots into the mix is also useful, 
but you must lock and flush Mysql in order to get a correct snapshot 
which makes it only an option on the slave.

kashani