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.

  • Unrelated to your question: You really need to look up Maatkit. It'll explain some of the internals. Also, I found MySQL-proxy to be helpful in getting this kind of setup profiled properly. Aug 17 '09 at 19:07

As far as your specific question, it's a combination of two things:

  1. By default, if a server receives a statement via replication, it will not send that same statement out to its slaves, preventing any kind of loop. This setting however, can be changed (by adding 'log-slave-updates' to the my.cnf), leading to:
  2. In replication, it sends the server-id of the origination server along with the statement. If a server receives a statement via replication with the same server id, it will not execute it, thus preventing it from executing the same statement twice.
  • Thanks. My servers did have log-slave-updates. I opened a ticket with our app vendor and the basically said "that's wierd".
    – jj33
    Aug 17 '09 at 20:55
  • thanks a lot! I know it's a long time a go but it's there any reference of 2 in the mysql documention. I am looking for it with no luck for now...
    – Rodo
    Aug 16 '13 at 10:31

Using mysql-master-master is only really safe in the general case if

  • You only ever write to one server at a time
  • When you switch from writing to one server to the other, you wait for replication to finish catching up beforehand

If you can be sure you'll do that, then fine.

If your application is VERY carefully written to take into account the fact that it's running on mysql-master-master (For example, be very careful with unique indexes - make sure you don't have a key violation on the other server) then you're probably ok. I could not easily write such an application.

Of course mysql-master-master is not useful for improving write performance, as both servers need to do all the writes too.

  • 2
    Using master-master replication, provided it has been properly configured and you are using transactions where appropriate, it is perfectly safe to write to each master at the same time. If you do experience a problem it is entirely due to a misconfiguration or poor database design. Oct 15 '09 at 20:46
  • 1
    "Poor database design" may mean that you haven't thought about master-master in every single query you throw at it. It's easy - really easy - to make an app which breaks master-master. Any application not SPECIFICALLY| written to tolerate it will succeed quite soon (provided there is a reasonable level of traffic). MMM does NOT do synchronous replication, so transactions don't necessarily happen in the same order on the two servers - which means that they may fail or leave data out of sync in the general case.
    – MarkR
    Oct 16 '09 at 6:46
  • I disagree completely. What will break master-master real fast is the failure to use transactions when they are called for. Any app written an the assumption that it is the only thing that will use or make changes to a database, even its own, is just poorly written and will result in a broken database, even without master-master replication. Oct 17 '09 at 9:24
  • 1
    In order to tolerate master-master, the application needs to work on the basis that its transactions will have the same (or equivalent) results regardless of the order they are applied AND not fail regardless of the order they are applied, in practice this is very hard to achieve - there are some special cases where it's possible.
    – MarkR
    Oct 17 '09 at 21:25
  • 1
    Rather, the locking which assures atomicity when using (e.g.) REPEATABLE_READ txn isolation, does not extend to the other node of a master-master - it does not do transactional replication - therefore if both nodes are written to, then situations which would lock or deadlock on a single node, will fail spectacularly and cause either out-of-sync data or a replication error (which mysql does not attempt to recover from automatically)
    – MarkR
    Oct 17 '09 at 21:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.