I would much appreciate if someone could advise me on the scenarios I am planning to setup.
I am using percona Server (mysql). Topology is circular Master to Master replication.
There are three Nodes: 1, 2 and 3. Each of them located in different regions / branches. Users at each branch updates on their server locally. Each server runs the web app which talk directly to mysql database. Each node act as Master and Slave.
Node 2 Slave pulls info from Node 1 Master
Node 3 Slave pulls info from Node 2 Master
Node 1 Slave pulls info from Node 3 Master
I would like to know what if Node 3 fails and disconnects from network:
- Node 2 will still receive updates from Node 1, right?
- Node 1 will not be able to receive updates as Node 3 is down, right?
What are the risks? data inconsistencies risks? To note on Node 3, users will keep on updating as they will be working locally. When the network connection resumes. What will happen? will the links restored and replication/updates resumed?
I know i cannot implement Percona xtraDB cluster on my scenario as it prevent split brains therefore if any node has network disruption users wont be able to perform updates locally. the percona cluster will not accept any queries or commands to perform on the mysql.
What the best alternative solution can anyone suggest me please. Should i go with Percona cluster with a load balancer/proxy which will check and connect to the live nodes and the web app is will point to the load balancer?
At present there is a production live working Master to Master replication between only two nodes in two different branches; both of them act as master and slaves. It has been working since 6 months now and no critical issues i came across. I want to know adding the third node what impact will it be.
Thanks a lot guys.