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.

MySQL logo

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
Example of what happens when you update in both masters at the same time

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

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

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.