Mysql – Site running with thesql replication, shall we shut down the site when replication broken

MySQL

let me illustrate the situation here:
An online store is setup with two replicated server: A(Asia) and G(Germany)

The setup is aimed to improve speed for users from both location.

On apache side, file replication is setup to sync files on both site.
DB replication with MYSQL is setup to sync data.

Here comes to problem when DB replication is broken.
during replication is down
Item X , available quantity is 2

Mr G order one item from G

Mr A order two item from A.

At the point, the remain quantity should be -1

However, because of the broken replication, both users ordered successfully.

Can anybody advise if this is the case and people should shut down related application once the DB replication behind broken?

Best Answer

Replication may fail because of different factors. You don't want to shot down the site each time replication fails. Instead, you should think of a solution for this situation: when replication fails, how to keep the site up.

It seems that you have master-master setup for the replication. I suggest to let users (application) connect to the closer server when they are reading, and perform all writing operations on one master, call it "active master". This way, you gain the speed of reading, and you know that the replication is going in one direction. If the replication fails, you fix it while the site is still up.

On the other hand, if the master fails, you switch masters: pending master becomes the active master, and you fix the failed one, then resume the replication.

Having said that, there are some situations where you may want to shut down the site.

HTH

Related Question