I have been tasked with Mysql Master-Master replication?

Question

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?

Answer 1

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

ASPECT #1 : PRIMARY KEY COLLISION

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

• auto_increment_increment
• auto_increment_offset

The key to using these is the following:

• set auto_increment_increment to be the same value on all nodes in Circular Replication topology.
• set auto_increment_offset to be the any unique value >= 0 and less than auto_increment_increment

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
  • This allows DB2 to to be a hot standby
  • You can perform SELECTs from DB2 to reduce read I/O on DB1
  • You can perform mysqldump backups from DB2 without disturbing DB1
  • This allows you to add additional read-only slaves (See my post Adding slave to existing master-master config throws foreign key constraint failed error)
• 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)

ASPECT #2 : DATA DRIFT

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)
  • sync-binlog
  • sync-relay-log
  • sync-relay-log-info
  • sync-master-info
• 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.

Answer 2

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