MySQL dual master replication -- is this scenario safe?

Question

I currently have a MySQL dual master replication (A<->B) set up and everything seems to be running swimmingly. I drew on the basic ideas from here and here.

Server A is my web server (a VPS). User interaction with the application leads to updates to several fields in table X (which are replicated to server B). Server B is the heavy-lifter, where all the big calculations are done. A cron job on server B regularly adds rows to table X (which are replicated to server A).

So server A can update (but never add) rows, and server B can add rows. Server B can also update fields in X, but only after the user no longer has the ability to update that row.

What kinds of potential disasters can I expect with this scenario if I go to production with it? Or does this seem OK? I'm asking mostly because I'm ignorant about whether any simultaneous operation on the table (from either the A copy or the B copy) can cause problems or if it's just operations on the same row that get hairy.

Answer 1

Dual master replication is messy if you attempt to write to the same database on both masters.

One of the biggest points of contention (and high blood pressure) is the use of autoincrement keys.

As long as you remember to set auto_increment_increment and auto_increment_offset, you can lookup any data you want and retrieve auto_incremented ids.

You just have to remember this rule: If you read an id from serverX, you must lookup needed data from serverX using the same id.

Here is one saving grace for using dual master replication.

Suppose you have

• two databases (db1 and db2)
• two DB servers (serverA and serverB)

If you impose the following restrictions

• all writes of db1 to serverA
• all writes of db2 to serverB

then you are not required to set auto_increment_increment and auto_increment_offset.

I hope my answer clarifies the good, the bad, and the ugly of using dual master replication.

• Here is a pictorial example of 4 masters using auto increment settings
• Nice article from Percona on this subject

Answer 2

Master-master replication can be very tricky, are you sure that this is the best solution for you ? Usually it is used for load-balancing purposes (e.g. round-robin connect to your db servers) and sometimes when you want to avoid the replication lag effect. A big known issue is the auto_increment problem which is supposedly solved using different offsets and increment value.

I think you should modify your configuration to simple master-slave by making A the master and B the slave, unless I am mistaken about the requirements of your system.