mysql replication master master

Question

1. is it recommended to do mysql master-slave cluster or master-master cluster?
2. do I need a 3rd party tool in order to do a master-master mysql cluster for the virtual IP? (I thought to use heartbeat but i'm not sure if it s really needed).
3. I need some documentation on how to do master-master if someone has.

Thanks, Elad.

Answer 1

If possible you want to avoid master-master setups. What's usually better is to have one master and multiple slaves and if the master fails promote one slave to be the new master. Doing this is not possible if you have a high write load. Then multi-master might make sense. Or you could look into sharding.

Answer 2

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:

1. All tables that use PRIMARY KEYS not be based on auto incrementing
2. 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.

Answer 3

A key consideration is how close to realtime you need the updates to be reflected across the cluster.

If it were me I'd lean towards master/master replication with server affinity - that way users will be guaranteed to see their updates immediately (except during a partial outage).

do I need a 3rd party tool in order to do a master-master mysql cluster for the virtual IP?

You need some smarts to handle promotion of a slave in a master/slave replication, should the master fail - have a google, there are lots of examples.

As prev, to provide load balancing and server affinity, I'd recommend running mysqlproxy (and e.g. distributing the workload based on session id or client IP). Depending on how many webservers you're running, you'll probably run an instance of mysqlproxy on each one. Implementing server affinity isn't obviously documented on the mysqlproxy web pages - but you just need to learn some lua!

There's a short howto for setting up the replication here.