We have 3 DB servers, two of them i.e. db1, db2, are located in the same data center. The other one i.e. drdb, is located in a different site. Currently, the replication setup for these servers is of the master/multiple-slaves kind i.e. db1 as the master, and db2 and drdb as the slaves. drdb is activated only when both db1 and db2 are down i.e. disaster recovery, business cont. sort of thing. Our application also has a rudimentary failover 'feature' in which it automatically connects to the next available db server in case the default active db server i.e. db1-to-db2-drdb, is inaccessible.
I am currently considering the idea of applying a master-master replication between db1 and db2, and making drdb as a slave of db1. In this setup, I'l make db1 as the default active db server (performs all writes and read operations) and db2 the passive one (backup db server). When db1 goes down, db2 becomes the active db server, and dynamically change the master-host of drdb to db2. And so on.
Is this a good idea? What is a better replication setup for this kind of environment? Please give some suggestions.
I also would like to add that these databases are already existent, and I've read somewhere the possibility of key-collisions/error in this kind of setup, what modifications in our current tables/databases configuration should we made before we setup this kind of replication?
Thank you very much!