I readily recommend Master/Master over Master/Slave for several reasons
- You have two DB servers configured to be Master with Latest Binary Logs
- Manual Failover to the Passive Master is just a 'ip addr add' away.
- You can configure an IP to one Active Master (using ucarp)
- You can isolate DB Writes to one machine (whichever Master has the DBVIP)
- Passive Master (who does not have the DBVIP) can be available for reads, mysqldumps, XtraBackups, tar/gzips
- Reconfiguring a Slave to be a New Master and the Old Master to a New Slave is neither fast, trivial nor seemless, especially when you have to get a DB Server back up quickly.
There are only two(2) drawbacks to using Master/Master
- You cannot perform any failover if the Passive Master is > 0 Seconds Behind. Failing over too soon present an app with data that is seconds out of date and the possibility of duplicate keys can rear its ugly head trying to write new data with gaps in the data. For low write environments, you can live with this scenario as Replication can catch up quickly before failing over.
- The caches (MyISAM Key Cache and InnoDB Buffer Pool) for both Masters are not the same. Failing over to the Passive Master can cause an inital server load spike with reads and writes to get DB caches with the right data. You can overcome that with mk-slave-prefetch, soon to be renamed pt-slave-prefetch (a la Percona Toolkit)
What seems to be in a gray area is performing DB writes on both Masters. Although the options auto_increment_increment and auto_increment_offset were invented to mitigate auto_increment values on two different server for the same table, a truly safe use of Master/Master allowing for DB Writes on both servers falls under two scenarios:
- All tables that use PRIMARY KEYS not be based on auto incrementing
- Writing to mutually exclusive databases on the different Masters. In other words, if you restrict DB writes to schema1 on one Master and never perform DB writes to schema1 on the other Master.
UPDATE 2012-10-04 11:42 EDT
Percona XtraDB Cluster (PXC) has emerged as a good Synchronous MultiMaster Database. This is great for multitenant databases.
You will have to remember the following about PXC
- Sizing memory requirements and disk configuration for InnoDB
- remembering that DDL/DML on MyISAM is not replicated in the Galera Write Set Replicator Libraries. Since GRANT commands is storage-engine neutral, MyISAM table in the mysql schema is handled with no problem. Any DML against mysql.user is not replicated.
- Weakest node makes the whole Cluster Slow, especially at COMMIT time. So, make sure all node have identical hardware/software/OS/VM/RAM/Networking settings.
- You can perform moderate Load-Balanced writes against all nodes. The more node in the Cluster. the slower the COMMITs.
- PXC differs from MySQL Cluster in that all nodes contain the same data
When it comes to failover, any node can be a Master. You spread reads across the Cluster. For multitenants DBs, you can spread writes (INSERTs, UPDATEs, DELETEs) without deadlocking worries. Deadlocks are still possible when writes to the same database are spread throuhgout the Cluster. Due diligence in coding your app to avoid deadlocking.