1

I have set a Master-master replication with the second Master running as read-only. This master-master setup is on the same location. Now I will have to setup an DR for this production at a different location. What are the solutions I have? Is it possible to set another master-master setup at DR site where in the Master in DR acts as the slave of the active master of DC? Will that work? Any other inputs for DR solution would be appreciated.

Thanks!!

Improve this question

2 Answers 2

Reset to default
1

Active-Passive master-master is a good setup but I have seen SUPER (humans)users writing on slave without setting sql_log_bin. (Though super_read_only in 5.7 will change things around this.)

Anyways, following is possible and works.

ProdActiveMaster<---->ProdPassiveMaster
|
|
|
\/
DRmaster----->DRSlave

So you know now that another master-master setup is not possible as DRMaster cannot replicate from two masters. I doubt multi-source replication is a solution here as it will create more trouble than help.

You need to make sure to keep your DRSlave, "master-ready"... setting auto_increment% and binlog + logslaveupdates settings.

Improve this answer
13
  • Okay but why do you say DR master cannot replicate from two masters? trying to understand the reason here. Is Master-master not possible in DR also?
    – tesla747
    Commented Aug 27, 2015 at 5:48
  • There can only be one source. If DRMaster replicates from ProductionMaster, it cannot replicate from DRSlave!
    – mysql_user
    Commented Aug 27, 2015 at 5:50
  • Newer versions working towards multi-source replication but it will switch between maters. In our scneario, if DRMaster switches from ProdMaster (which is actually the only source of data) to DRSlave, we will be lagging. Thus increase risk a possible data loss in case disaster. Percona, MariaDB and MySQL 5.7 all provides multi-source replication. You may google, here is one link to refer: mysqlhighavailability.com/…
    – mysql_user
    Commented Aug 27, 2015 at 5:54
  • Alright got it. So this is the possible setup that I can have right as DR solution? do you think any other DR features that can be setup?
    – tesla747
    Commented Aug 27, 2015 at 5:55
  • But we stick to Percona's 5.6 and not 5.7 so we cannot go for multi-source.
    – tesla747
    Commented Aug 27, 2015 at 5:57
1

Master Master should be possible in DR too. Basically what you really have is a Master - Slave with Slave ready to become a master upon failover. So in Location 1: Master Active <--> Macter Passive Location 2: Master Active <--> Macter Passive Master Active in Location 1 should replicate to Master Active in Location 2. Make sure they all have bin logs turned on, and update slave. I've done similar set up with Percona Cluster.

Improve this answer
2
  • Im using percona server but not the cluster. Is it possible do the way you mentioned? ProdMaster to DRMaster?
    – tesla747
    Commented Aug 27, 2015 at 7:12
  • Yes, it is possible. Just make sure Master passive is ready only. Technically you don't have to ; read only is to guarantee no writes can occur and break replication. But you could have a database on Master Passive that does not exist anywhere else and write to it without affecting replication. I hope this makes sense.
    – greenlitmysql
    Commented Aug 28, 2015 at 14:44

Your Answer

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

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