3

We have two datacenters with a copy of our web app (online training platform) at each. The primary datacenter receives all of the traffic at any given time and the second datacenter serves as a failover(using DNS failover). The databases are set up in master-master with different auto-increment-increment values. The server at datacenter 1 also has its own local slave server on which we do backups, that way we can pause replication, do the backup with mysqldump, then start replication again.

 SERVER 1 <=========> SERVER 2
    |
  Slave 1

This weekend we did a failover to server 2 because datacenter 1 was doing planned network maintenance. Everything went smoothly and after a few hours we brought everything back to DC1. Last night I start getting replication errors on the slave saying it failed to DELETE an entry that didn't exist. I found it odd that the Slave 1 was getting the error while Server 2 was not (since they're both acting as slaves to Server 1). After digging, I found out that we still have a user that's going to Server 2.. for whatever reason their DNS hasn't updated for over 2 days. So the new data that they are producing is being replicated to Server 1, but Slave 1 is not getting these updates because they're not in Server 1's binlogs.

I now see that this was poor planning on my part for not foreseeing this issue. My question is, how can I make this work? We want to keep the master-master and we want to allow Slave 1 to exist, not only for backups but for additional read only reporting servers and such. Those of you that run Master-Master, what have you done to remedy similar issues? Or is this just a bad setup and I'm SOL? The app was not built with redundancy in mind, so everything is having to be done system side.

Improve this question

1 Answer 1

Reset to default
4
+50

What you want is server1 to binlog not only its own updates, but also those updates he received from server2, so that slave1 will get all updates.

You just need to tell server1 to do this, which can be accomplished by setting log-slave-updates to yes.

You might want to set this option on server2 too, if the server topology should change someday (as Rolando kindly remarked in the comments below).

This does not loop endlessly as the default setting for replicate-same-server-id is 0 (and cannot be changed as long as log-slave-updates is on). This setting set to 0 means the slave will skip statements he receives with his own server-id.

Your infrastructure decision in general seems fine to me.

Improve this answer
6
  • 1
    I was thinking the same thing. +1 for typing faster than me.!!!
    – RolandoMySQLDBA
    Mar 25, 2014 at 14:30
  • I would also recommend server2 have log-slave-updates should the topology need to be reversed.
    – RolandoMySQLDBA
    Mar 25, 2014 at 14:33
  • @RolandoMySQLDBA Sounds good. I updated my answer with this hint.
    – Ulrich Thomas Gabor
    Mar 25, 2014 at 14:34
  • Wouldn't that create an infinite loop of sorts in a master-master setup? If data goes into server B and gets replicated to A, then A writes that to its binlogs as well, won't B then pick up those changes from the binlog and try to execute them to it's own dataset?
    – Safado
    Mar 25, 2014 at 14:42
  • 1
    Great. These were the answers I needed to hear. Thank you for the suggestions.
    – Safado
    Mar 25, 2014 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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