Safe database master-master setup

Safe database master-master setup

At first let’s remind ourselves what master-master setup means.

Master-master method belongs to Multi-master replication method, in database replications you have 2 types on nodes – master and slave.

In master-slave, needed changes identified by a group member must be submitted to the designated “master” of the node. This differs from Master-Master replication in which data can be updated by any authorized contributor of the group.

Although it is a bit unusual, it´s sometimes better to have your databases in master-master setup, for example:

  • Each node works for a different app (example)
  • Multiple machines for better scalability (loadbalancing…)
  • Geographical distribution, if nodes are far away, there might be huge delays or network connection interruption

An example of what happens when you insert into both masters at the same time

Safe database master-master setup 02

Example of what happens when you update in both masters at the same time

Safe database master-master setup 03

As you can see, we have a data inconsistency!

That’s exactly what we want to prevent from happening and the solution is pretty simple.

Each server writes to its own set of tables

Safe database master-master setup 04

As we can see above, node 1 writes only into table1 and table2. On the other side node2 writes only into table3 and table4. After that, updating between nodes happens, which means node1 sends update of table1 and table2 to node2, and node2 sends an update of table3 and table4 to node1. Nodes are fully synced without any error or data inconsistency.

Each server writes to its own rows

Safe database master-master setup 05

Or we can make each node write into its own rows, on the screen above we can see that node1 is writing only into row1 and3 and node2 writes into row2 and row4. Then again, update procedure from example above is repeated.



Junior DevOps Engineer

Do you have any questions or comments? Contact Us