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.