[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: problem restoring from binary log
From:       Mathieu Bruneau <mathieu.bruneau () argontechnologies ! ca>
Date:       2007-04-27 23:26:36
Message-ID: 463286AC.60005 () argontechnologies ! ca
[Download RAW message or body]

Ofer Inbar a écrit :
> Mathieu Bruneau <mathieu.bruneau@argontechnologies.ca> wrote:
>> Ofer Inbar a écrit :
>>> I can repeat the problem with this procedure on the test db:
>>>  - Import a full mysqldump file from the prodution db
>>>  - flush logs
>>>  - run a full mysqldump with --flush-logs --master-data=2
>>>  - do a bunch of stuff that writes data
>>>  - drop the database, and recreate it, flushing the log
>>>  - make a copy of the binary log made since the mysqldump
>>>  - import the mysqldump I made locally
>>>   => now I have all data correctly restored up to that point
>>>
>>> Now, I attempt to restore the binary log and this happens:
>>>
>>>   mysqlbinlog [binlogfile] | mysql -u root -p[db root password] [db name]
>>>   ERROR 1062 (23000) at line 21: Duplicate entry '16021330' for key 1
> 
>>  I think your problem is in the way your trying to playback the binlog.
>> I guess this is happening when you try to play the first of your binlog
>> right ?
> 
> There is only one relevant binlog in the scenario I describe.
> So yes, it is the "first" :)
> 
>> Using mysqlbinlog in the way you put it will replay all the log but you
>> are probably somewhere in the middle of that log (Or maybe that binlog
>> is already inside your backup) Since you dumped with --master-data=2
> 
> Either I am misunderstanding something very basic, or you
> misunderstood my scenario, so let's clarify:
> 
> If I run a mysqldump with the --flush-logs option, and I see that
> mysql did indeed start a new binlog at the moment I ran mysqlbindump,
> am I not guaranteed that the the new binlog starts *after* the dump
> and that I should replay from the beginning of that log?

Indeed, I never verified that. But what's is the change master line in
your dump ?

You can correlate the position of the change master line with the query
in your binlog and see if any were run "before" that. So you'll have the
answer to your question.

Maybe the change of the binlog happens, the lock goes but some queries
may have went through between...

Anyway I think it's safer to use the position of the dump to know
exactly where the binlog should be played back :)


-- 
Mathieu Bruneau
aka ROunofF

===
GPG keys available @ http://rounoff.darktech.org

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mysql-marcsub@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]