Re: Read & Exec Master Log Position on Master-Master setup

[Tim Rosine]

I've noticed the same on my setup (mysql 4.1.10a). Looking at the mysqlbinlog output, I find that in the case where M01 is "lagging", the position at which it stopped is just after the last update from M02. That is - the server id is equal to itself for all remaining statements.

In other words, the slave SQL thread is not "consuming" any updates made on the same server_id. The documentation states that the SQL thread will not execute these statements by default (although you can override with --replicate-same-server-id). In my case, i don't want --replicate-same-server-id (update a set b=b+1 would actually run twice on the same server).

In addition, I have found that restarting the sql thread updates the Exec_Master_Log_Pos all the way to the end of the binlog.

Personally, I would expect the SQL thread to consume all binlog records, skipping execution as appropriate (according to --replicate-same-server-id). That way, when the slave starts, it only needs to worry about updates made since it had stopped. At a minimum, I would expect the value of Exec_Master_Log_Pos to be the same regardless of when the slave started.

For now, I'm using Seconds_Behind_Master (which is really seconds behind slave IO thread) instead.