[gentoo-user] [OT] Incomplete mysql backup

[gentoo-user] [OT] Incomplete mysql backup

[Mick]

[-- Attachment #1: Type: Text/Plain, Size: 1068 bytes --]

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!)
-- 
Regards,
Mick

[-- Attachment #2: This is a digitally signed message part. --]
[-- Type: application/pgp-signature, Size: 198 bytes --]

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

[kashani]

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

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

[Mick]

[-- Attachment #1: Type: Text/Plain, Size: 2485 bytes --]

On Friday 20 August 2010 05:58:49 kashani wrote:
> 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.

Yes, it is a single machine (the one with the dev't environment) but it has a 
dozen databases on it, so the -A option is not appropriate.  

The engine is the default MyISAM and this made me think if it is the reason 
that two backups in a row were incomplete.  Should I be converting all tables 
to Innodb?

The production server is separate.

> 	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.

Thanks kashani, I'll try the --single-transaction and see what I get.  I 
hadn't had such a hiccup for years now, so it came as a surprise to me.  I was 
thinking that I should perhaps use --lock-tables, because the --single-
transaction states: 

"This option issues a BEGIN SQL statement before dumping data from the server"

and I don't really understand how this will affect the backup ... ?
-- 
Regards,
Mick

[-- Attachment #2: This is a digitally signed message part. --]
[-- Type: application/pgp-signature, Size: 198 bytes --]