0

I have two MySQL servers set up with replication - lets say A and B for ease.

Currently B is a slave of A

To allow me to switch over to A in event of failure, I want to make this replication circular, so A is also a slave of B.

I know how to set this up using "change master to", but having read the documentation I am not clear on how to make sure that A starts replicating from B at the very end of B's binlog so it doesn't start to try and apply a bunch of SQL queries to the master that I don't want it to?

Am I making any sense?

  • What version of MySQL are you using? And are using GTID's or binlog file and position for your replication? (from your question I assume the latter, but it's best to check). – IGGt Mar 4 '16 at 17:05
0

(-sorry if I have misunderstood)

(-assuming you are using binlog file and pos).

The key things to make sure of are that your servers have unique server-ids (if you have replication running already then this should be ok).

You also probably want to set:

auto_increment_increment
auto_increment_offset

values so that the two Databases don't generate overlapping primary keys.

http://dev.mysql.com/doc/refman/5.7/en/replication-options-master.html

After that on your Slave DB (B), simply run SHOW MASTER STATUS.

Use those details to issue the CHANGE MASTER statement on DB A.

What you should find is that anything executed on A is then replicated to B and vice versa.

However anything executed on A shouldn't be replicated back from B to A.

And as such, the only queries written back to A will be those that are executed directly on B.

Finally, you will want to set B to read_only to prevent anyone accidentally writing to it, until such time as you want them to.

| improve this answer | |

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.