I have done some study and i came to know that Mysql master-master replication violates the ACID properties and there has been problem with primary key collision.Please Let me Know,Apart from these are there any disadvantages?

2 Answers 2


When using Master-Master Replication Setups (a.k.a Circular Replication), we tend to take things for granted. There are two aspects worth mentioning:


You mentioned primary key collision. That will definitely happen if you write to the same table on both Masters within the same window of time and that table has an auto_increment primary key. To circumvent this, MySQL added the following

The key to using these is the following:

There are two instances where you do not have to use auto_increment_increment and auto_increment_offset in a two-node setup (DB1 and DB2)

  • INSTANCE #1: All writes (INSERTs, UPDATEs, DELETEs, DDL) occur in DB1 only
  • INSTANCE #2: In the event you have multiple databases in each node
    • If you write to the mydb1 on DB1, you read from mydb1 on DB1 and never from DB2
    • If you write to the mydb2 on DB2, you read from mydb2 on DB2 and never from DB1

I have discussed before : (May 07, 2012 : Setting Circular Replication in mysql)


This is a rather murky situation when binlog events do not executed on the Slave.

The factors involved here

  • Binary Logs
  • Relay Logs
  • Storage Engine
  • Network Transmission

It is relatively easy to setup Replication. It also relative easy to forget that binary logs, relay logs, and moving parts of InnoDB and MyISAM require conscientious flushing to disk.

  • Settings for Flushing Logs (See my post MySQL Master-Master Replication not syncing)
  • Flushing Storage Engines
    • Set innodb_max_dirty_pages_pct to 0 to increase flushing activity
    • Running FLUSH TABLES tblname; on MyISAM tables after bulk INSERTs, UPDATEs, and DELETEs
    • cronjob to run SET GLOBAL innodb_buffer_pool_dump_now = 1; (MySQL 5.6 only, see innodb_buffer_pool_dump_now). This dumps a numeric map of the Buffer Pool's data and index pages to Disk. You would also configure innodb_buffer_pool_dump_at_shutdown innodb_buffer_pool_load_at_startup
    • This is not an all-inclusive list. Your application must become more cognizant of the need to flush to disk
  • Networking
    • You must make sure the network traffic is not inundated, your switches are fine, any crossover cabling to bypass the network is not defective, there are no dropped packets,.
    • In terms of MySQL, the Replication I/O thread is dependent on the network. You must make sure mysqld can regularly heartbeat its master. Perhaps semisync replication could be installed and configured for this.

Al long as you write simultaneously to one master nothing is wrong with master-master topology. I mean it has the same disadvantages as master-slave replication, but that's it

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.