Inconsisten Backup with mysqldump

Hi,
I'm running mysql version 4.1.20, with all tables in InnoDB. I'm having
problems getting consistent backups with mysqlbindump and binary logs.
I'm running mysqlbindump with the following options:
"--quick --single-transaction --flush-logs --add-drop-table
--master-data"

I run mysqldump separately for each database, and then flush logs one
last time. The databases need to be backed up separately because I may
occasionally need to restore one database at a time.

I am trying to use these backups to build a slave.

So what I do is take all these dumps and load them into the database
and then run the binlog that flushed for that dump. Then when thats
done I can start the slave at the final binlog and things are good.

The problem I am having is that occasionally the backup is not
consistent. When restoring on the slave I will get Duplicate Key errors
running the bin log, so I look in the dump and in the log, and the row
exists in the dump and is also inserted in the bin log. I though that
the --master-data flag was supposed to make the log flush consistent
with the dump, so this sort of thing shouldn't happen. I suspect that I
am also having problems the other way, where a row is not in either the
dump or the binlog, but I this is a lot harder to verify than looking
at the same row in both locations.

So, am i doing something obviously wrong, or making an assumption I
shouldn't be?

Thanks,
magicrobotmonkey

"magicrobotmonkey" <abassett@gmail.com> wrote:

> I'm running mysql version 4.1.20, with all tables in InnoDB. I'm having
> problems getting consistent backups with mysqlbindump and binary logs.
> I'm running mysqlbindump with the following options:
> "--quick --single-transaction --flush-logs --add-drop-table
> --master-data"

So far, so good.

> I run mysqldump separately for each database

That's the problem. The binlog writer does not care about databases,
it writes all transactions into a single log. If you need consistency
between your tables and the binlog you have to backup all at once.

> The databases need to be backed up separately because I may
> occasionally need to restore one database at a time.

You can split the dumpfile later. It's just plain text.

> I am trying to use these backups to build a slave.
>
> So what I do is take all these dumps and load them into the database
> and then run the binlog that flushed for that dump. Then when thats
> done I can start the slave at the final binlog and things are good.

What you could do:

Image you have databases A, B and C. You backup those in this order.
Since you use --flush-logs, MySQL begins a new binlog right after the
point in time when a backup was taken. So a new binlog starts after
you took the backup of A, B and C. Lets call those binlogs logA, logB
and logC.

To restore the complete database (A+B+C) you have to

1. restore A
2. replay logA (ignoring errors about nonexisting B+C)
3. restore B
4. replay logB (ignoring errors about nonexisting C)
5. restore C
6. start replication at logC

To restore just A+C you have to

1. restore A
2. replay logA (ignoring errors about nonexisting B+C)
3. replay logB (ignoring errors about nonexisting B+C)
4. restore C
5. start replication at logC

I guess you got the idea now.


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Hmm... I think the way we restore it now is basically the same, as we
restore the databases in order, and after each one run all the
binlogs, but restricted to the database we just restored, so we dont
get any errors.

I thought, though, that running mysqlbinlog with those options makes
the dump atomic with the log flush, so I can't figure out why the row
would be in the dump and in the binlog. It seems like I should be able
to restore from the dump and start the binlog at the position
specified in the output when using --master-data, but that is not the
case here.

The other thing I forgot to mention that was really strange was that
in the specific case where I first figured out what was going on, the
last row in the dump was like the 300th statement in the binlog. I
thought that was strange, because it seemed like it would be closer to
the start of the binlog.

I'm not really sure why it makes a difference if I back them all up at
once or one at a time but I 'll give it a shot and see if that makes a
difference. The difficulty is that this doesnt happen everytime I run
a backup and backups take several hours to run.

Thanks for the Reply,

Magicrobotmonkey

On Jan 28, 5:19 am, Axel Schwenke <axel.schwe...@gmx.de> wrote:
> "magicrobotmonkey" <abass...@gmail.com> wrote:
> > I'm running mysql version 4.1.20, with all tables in InnoDB. I'm having
> > problems getting consistent backups with mysqlbindump and binary logs.
> > I'm running mysqlbindump with the following options:
> > "--quick --single-transaction --flush-logs --add-drop-table
> > --master-data"So far, so good.
>
> > I run mysqldump separately for each databaseThat's the problem. The binlog writer does not care about databases,
> it writes all transactions into a single log. If you need consistency
> between your tables and the binlog you have to backup all at once.
>
> > The databases need to be backed up separately because I may
> > occasionally need to restore one database at a time.You can split the dumpfile later. It's just plain text.
>
> > I am trying to use these backups to build a slave.
>
> > So what I do is take all these dumps and load them into the database
> > and then run the binlog that flushed for that dump. Then when thats
> > done I can start the slave at the final binlog and things are good.What you could do:
>
> Image you have databases A, B and C. You backup those in this order.
> Since you use --flush-logs, MySQL begins a new binlog right after the
> point in time when a backup was taken. So a new binlog starts after
> you took the backup of A, B and C. Lets call those binlogs logA, logB
> and logC.
>
> To restore the complete database (A+B+C) you have to
>
> 1. restore A
> 2. replay logA (ignoring errors about nonexisting B+C)
> 3. restore B
> 4. replay logB (ignoring errors about nonexisting C)
> 5. restore C
> 6. start replication at logC
>
> To restore just A+C you have to
>
> 1. restore A
> 2. replay logA (ignoring errors about nonexisting B+C)
> 3. replay logB (ignoring errors about nonexisting B+C)
> 4. restore C
> 5. start replication at logC
>
> I guess you got the idea now.
>
> XL
> --
> Axel Schwenke, Support Engineer, MySQL AB
>
> Online User Manual:http://dev.mysql.com/doc/refman/5.0/en/
> MySQL User Forums: http://forums.mysql.com/

I don't know if it's defaulted on your installation but the 'extended
inserts' mysqldump switch makes for much faster backups and restores,
using the insert into TABLE.

On 28 jan, 13:51, "magicrobotmonkey" <abass...@gmail.com> wrote:
> Hmm... I think the way we restore it now is basically the same, as we
> restore the databases in order, and after each one run all the
> binlogs, but restricted to the database we just restored, so we dont
> get any errors.
>
> I thought, though, that running mysqlbinlog with those options makes
> the dump atomic with the log flush, so I can't figure out why the row
> would be in the dump and in the binlog. It seems like I should be able
> to restore from the dump and start the binlog at the position
> specified in the output when using --master-data, but that is not the
> case here.
>
> The other thing I forgot to mention that was really strange was that
> in the specific case where I first figured out what was going on, the
> last row in the dump was like the 300th statement in the binlog. I
> thought that was strange, because it seemed like it would be closer to
> the start of the binlog.
>
> I'm not really sure why it makes a difference if I back them all up at
> once or one at a time but I 'll give it a shot and see if that makes a
> difference. The difficulty is that this doesnt happen everytime I run
> a backup and backups take several hours to run.
>
> Thanks for the Reply,
>
> Magicrobotmonkey
>
> On Jan 28, 5:19 am, Axel Schwenke <axel.schwe...@gmx.de> wrote:
>
> > "magicrobotmonkey" <abass...@gmail.com> wrote:
> > > I'm running mysql version 4.1.20, with all tables in InnoDB. I'm having
> > > problems getting consistent backups with mysqlbindump and binary logs.
> > > I'm running mysqlbindump with the following options:
> > > "--quick --single-transaction --flush-logs --add-drop-table
> > > --master-data"So far, so good.
>
> > > I run mysqldump separately for each databaseThat's the problem. The binlog writer does not care about databases,
> > it writes all transactions into a single log. If you need consistency
> > between your tables and the binlog you have to backup all at once.
>
> > > The databases need to be backed up separately because I may
> > > occasionally need to restore one database at a time.You can split the dumpfile later. It's just plain text.
>
> > > I am trying to use these backups to build a slave.
>
> > > So what I do is take all these dumps and load them into the database
> > > and then run the binlog that flushed for that dump. Then when thats
> > > done I can start the slave at the final binlog and things are good.What you could do:
>
> > Image you have databases A, B and C. You backup those in this order.
> > Since you use --flush-logs, MySQL begins a new binlog right after the
> > point in time when a backup was taken. So a new binlog starts after
> > you took the backup of A, B and C. Lets call those binlogs logA, logB
> > and logC.
>
> > To restore the complete database (A+B+C) you have to
>
> > 1. restore A
> > 2. replay logA (ignoring errors about nonexisting B+C)
> > 3. restore B
> > 4. replay logB (ignoring errors about nonexisting C)
> > 5. restore C
> > 6. start replication at logC
>
> > To restore just A+C you have to
>
> > 1. restore A
> > 2. replay logA (ignoring errors about nonexisting B+C)
> > 3. replay logB (ignoring errors about nonexisting B+C)
> > 4. restore C
> > 5. start replication at logC
>
> > I guess you got the idea now.
>
> > XL
> > --
> > Axel Schwenke, Support Engineer, MySQL AB
>
> > Online User Manual:http://dev.mysql.com/doc/refman/5.0/en/
> > MySQL User Forums: http://forums.mysql.com/