0

I am considering how to configure our MySQL replication. Hope I can get any advices from community.

In fact, I am using MySQL 5.7, and have

  • 01-Master
  • 02-Slave
  • 03-Slave

My Applications are using only 01-Master. They don't know 02-Slave, and 03-Slave.

Next, I want to promote 02-Slave to 02-Master. But want to keep 01-Master. So, I have:

  • 01-Master
  • 02-Master
  • 03-Slave

Then I will set 01-Master for some Apps and 02-Master for other Apps.

But, I am not sure about how to position 03-Slave? Can I do that? Please give me any advices. Thanks a lot!

  • I back with simple workaround. Set some Apps pointing to 02-Salve. Absolutely some Apps will not write any data to 01-Master. So the integrity of Master keep as well and the MySQL replication will not break. Following this workaround, I don't need to promote or demote. – tien Jun 3 at 9:36
2

M1 <-> M2 -> S is essentially the only configuration. Pros/cons...

  • Even old versions of MySQL could be configured that way. M1 and M2 are configured as slaves to each other; S is configured as a slave to M2 (and knows nothing about M1). Each server needs a distinct server_id.
  • By having dual master, you have easier failover than if you had simply M->S.
  • It is probably safer to do what you plan, namely having different apps talking to different Masters, than to have all apps talk to either Master. This is because of issues with auto_increment and duplicate keys.
  • The Slave is handy for backups, heavy reads, etc, but...
  • If M1 crashes and you failover to M2, use S to reconstruct M1, but...
  • If M2 crashes and you failover to M1, S cannot easily be connected to M1. It would probably be best to assume that you lost S at the same time and rebuild both. (There are issues of binlog positions, etc. GTIDs, if turned on, may make this "not an issue"; this is something to research.)
  • Since you seem to have 3 servers, why not go with Galera? That eliminates most of the issues mentioned -- you can write to any server; loss of any server is automatically repaired; etc. Group Replication is another smart idea.

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.