I have a database which runs in two remote locations. (A & B) The server is available locally on one location (A). However, the second location (B) accesess the database over the internet.
Incase the internet at location A is down, Location B looses connection to the database.
Is it somehow possible to put another server at Location B with continous replication and following conditions:- If internet is working at Location A, then users access Location A servers only. Users can access the server available at Location B till the time internet is down at Location A. Once the connectivity is restored, both the databases can replicate. Incase there is any mismatch/duplicate, then Location A will be considered master and the changes would be overwritten?
I would like to know if such a solution is possible and the problems I can face with this setup?
MariaDB 10+ offers Galera Cluster that has Multiple Master settings. Basically all servers (in your case 3) will be masters and data to 1 server goes to the others. If one goes down, the other servers continue. Once the server comes back up, the it syncs back the missing data.
That's the database level
For the connectivity level, you need a "load balancer" type of device which distributes the load between up machines or detects a down machine and directs to the others. They are hard to find for mysql type servers. But .. feasible if that's your question