MySQL Bugs: #3314: Keep track of binary log position.

Bug #3314	Keep track of binary log position.
Submitted:	27 Mar 2004 17:01	Modified:	29 Apr 2004 5:04
Reporter:	James Sleeman	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:		OS:
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
While each logged statement has a position identifier in the binary log, there is no way (that I know, or that is documented it seems) to find out for a given data set what the last statement that was successfully comitted to the data was.  That is binary log 'a' has say 400000 statements, I have an older database that I wish to begin the restore from.  How do I know which of those 400000 statements is the next one that should be executed against the old data to begin the process.

This is particularly the case if your full backups are 'hot' copies, and you don't then really have a good way (short of locking everything for the duration of the copy) of causing your binary logs to exactly sync (through flush logs) to your full backup.  

How to repeat:
Try restoring from an old data set using binary logs that do not 'start' at the same point the old data set was copied.

Suggested fix:
Just a small addition of this transaction serial number to mysql would allow people who need to restore to drop an old data set in place, find the last transaction that was executed and start from that point in the binary logs.

Further, because you are gnerally not locking the tables when hot copying and thus you're not making a consistent copy, it would be useful if each *table* knew the ID of the last statement that was committed to it, and if through some enhancement to the communication between mysqlbinlog and mysql (client) that when a statement (from the bin log) was issued against a table that it would be checked to see if it's serial is older/less than the last serial that was comitted to that table - thus you never need worry about duplicating statements when restoring, the system just drops the already comitted ones.

Hello,

> Further, because you are gnerally not locking the tables when hot copying
> and thus you're not making a consistent copy

Please how do you do your backups? With a MySQL tool (which one with which options) or another way ?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".