CODEWITHSUNDEEP
mysqldatabase-replication
Abdullah Shahin
Abdullah Shahin

Reputation: 1052

multimaster mysql replication position

I have a circular muli-master mysql setup as follows

                 DB1 ------> DB2 ------> DB3 --
                  |                            |
                   ------------<---------------

I initialized a replication on the 3 DB servers on the same data and same master position index, if this position index become non-identical would that mean for a fact that the 3 databases became inconsistent later on, considering only one master is being written to at a time ?

Upvotes: 1

Views: 35

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562891

The binary log coordinates have nothing to do with the instances having consistent data.

You could write binary logs on DB1 for weeks, then take a backup and use it to initialize DB2. Then some time later take a backup of DB2 and use it to initialize DB3. After that, all three servers would have identical data, but quite different binary log files and indexes.

You can instead use Global Transaction IDs (which are not binary log coordinates) to manage replication.

In any case, it's a risk that data will get out of sync no matter how you manage the replication. You could run queries that include non-deterministic expressions. You could turn off binary logging temporarily. Or logs could become corrupted as they are transferred to replicas.

If you want to test that the data is consistent, use pt-table-checksum.

Upvotes: 1

Related Questions