MySQL Cluster over WAN: Best Architecture Options?

Question

My company is looking at a multi-data-centre solution for high-availability, fun, and profit.

We have businesses across the planet, with the need for MySQL database clustering solutions that can keep up with high-traffic over high-latency. Bandwidth isn't really an issue for us.

Could you please recommend a MySQL Database Cluster Architecture that is able to cope with high-latency networks. For instance, we have one Master at Sydney, AU, and another Master at New York, US - the latency between these two points can be up to a second or more.

We are currently using Percona Server 5.1, and we would like to stay this way, so I will place more value on answers that take this factor into consideration.

Answer 1

For high-latency the main choice is between Percona XtraDB Cluster (Galera+InnoDB-based) or the new WAN replication in MySQL Cluster 7.2 (NDB-based). The downside of NDB is greatly increased complexity and application changes; it is NOT a drop-in replacement for your existing system. Percona XtraDB Cluster has only a few small differences, e.g. locking is pessimistic on the local node (as currently) but optimistic cluster-wide, so if there are conflicting changes on a remote node you won't find out until you commit. (If there are conflicting changes, the commit will fail and roll back; you can't break the cluster as you can with standard MySQL replication.)