MySQL Bugs: #8394: Knowing the exact position of the master when it is down

Bug #8394	Knowing the exact position of the master when it is down
Submitted:	9 Feb 2005 14:37	Modified:	10 Oct 2008 10:37
Reporter:	KEvin	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S4 (Feature request)
Version:	6.0	OS:	Any
Assigned to:	Luis Soares	CPU Architecture:	Any

Description:
It could be useful to know the replication position of the master when it is down.

You can know the position of the io and slq threads on the slave with the files master.info and relay-log.info but all you know about the master is the log files currently used with the binary_log-bin.index file and so the actual log file.

How to repeat:
There is several case where this could be useful, one of them being when the mysql master (let's call it A) is down and you want to change this role to a new server (B for example) : before doing "stop slave; reset master" on B and "reset master; change master; start slave" on A, you must be sure that B is up to date.
Indeed, there could have been updates to A just before it went down that did not get to B (network failure or just busy servers ...).
As you cannot do on B :
mysqlbinlog --read-from-remote-server -h A --start-position=last_position_of_B --offset=1 last_log_of_B
because it doesn't work when the mysql server is down you must copy the remote log(s) locally to do the mysqlbinlog which could be heavy with a big log file especially if there is no need in the first place !

hope all this is comprehensible !

Thank you for your attention.

How is any service supposed to give information when it is infact "down"?

Write a script that shells to the master and performs your mysqlbinlog command on the binlog file.

>  How is any service supposed to give information when it is infact "down"?

by having a status file (like the mysql slave service for example), or a program (like mysqlbinlog) which finds the last position in the binlog more easily/cleanly than with any shell scripts.

> Write a script that shells to the master and performs your mysqlbinlog 
> command on the binlog file.

my command was supposed to execute the last events of the master on the slave. If i shell to the master i wont be able to do that : i must copy the binlog file(s) to the slave first which can be a heavy task if the log is big.
So i would find it useful to know if there was events to execute before doing any big copy. To do so i could shell to the master to read the binlog files but there is no clean way to know the last position of the master.
I thought that maybe an option in mysqlbinlog could do the trick, like specifying a negative offset to start from the end and a limit to the number of events to get (a bit like what the "limit" clause for "show binlog events" do).

Many thanks for writing a feature request. We will discuss this.