I have two servers running mysql as dual master (each server is a master, each server is slaved to the other). While troubleshooting a possible issue during peak load, I began to wonder how mysql prevent a "loop" of commands in a relationship like this.
My specific question is:
If A is slaved to B, and B is slaved to A, what prevents an SQL command which is executed on A, and which propagates to B via their master(A)->slave(B) relationship, from being further propagated back to A via the master(B)->slave(A) relationship?
My guess is that in addition to the commands itself there's some sort of unique identified for the command passed around such that A knows it has previously executed the command (presumably using the server-id option). However, my google-fu is too weak today to find out how this works under the covers.
Here's how this pertains to my problem. Every 5 minutes I see a lag between Read_Master_Log_Pos and Exec_Master_Log_Pos. I understand the basic cause of this – I believe that the application is configured to dump a ton of data on the database at five minute intervals (in fact, in graphing the 15-second deltas between the values, I surmise that there's a constant number of runners every 5 minutes, with more set for every 15 minutes, and more still set for every 30 minutes).
My real concern though is that both slaves show this same delay. My understanding of this application's design is that the "active" (from the application perspective, not from a mysql perspective) database server would always be used unless that server was unavailable, in which case the application would try to use the "standby" database server. If this is true, why am I seeing the read/exec lag on both slaves? If it isn't true, do I either have a fundamental misunderstanding of the application architecture, or is the "active" server becoming over-utilized to the point that the application is failing itself over even though the server is never "down"? (those last two aren't SF questions, just questions I'm working toward answering)
I read that the master in a mysql master/slave relationship is fairly simple in that it sends everything to its slaves and it is up to the slaves to decide which commands, if any, to execute. If that's the case, perhaps the lag I'm seeing on the slave to the standby server is caused by all those already executed commands being pulled down and having to be evaluated as to whether those command have already been executed.
Best Answer
As far as your specific question, it's a combination of two things:
log-slave-updates' to the my.cnf), leading to: