I'm planning to go for a 3-node active-active MySQL cluster which will not have more 100 databases. Many experts here have already told there are issues with MySQL 5.6 and 5.7 where circular replication happens which is very vulnerable due to increase in failures, in what way Percona XtraDB provides edge to MySQL cluster? Percona doesn't do the circular way? Just trying to understand. The application which i'm going to use is not very much resource intenseful. What is the best option for 3 node active-active cluster?
You appear to be missing the distinction between "Percona XtraDB" and "Percona XtraDB Cluster," also known as PXC. XtraDB is Percona's compatible drop-in replacement rewrite of the InnoDB storage engine, which is included in Percona Server and MariaDB.
Percona XtraDB Cluster and MariaDB Galera Cluster both use XtraDB as their storage engine, but the important difference from standard MySQL replication is that they both use the Galera Replication Provider, which provides true synchronous replication among all the nodes. You can also use Galera with MySQL. Oracle may not mention that, since it wasn't their idea.
MySQL's built-in asynchronous replication can be configured for circular replication (and is included in Percona and MariaDB) but it has no mechanism for handling conflicts. If queries make conflicting changes on different servers, your data will be inconsistent and replication will stop. Galera resolves this by requiring all nodes to concur on each commit. It's replication mechanism is fundamentally different than what is built in.
There are no issues with circular replication that are specific to MySQL 5.6 and 5.7 as your question implies. These issues apply to all versions of MySQL, Percona, and MariaDB when standard replication is used in a circular configuration, if you allow writes to be done to more than one of the masters.
Brilliant Michael, some more info would clear my doubts. 1. Only Galera provides true sync replication? Isn't there 'synchronous' replication option with native MySQL NDB cluster? 2. And I would be going for 3 node active-active cluster what are the cons in MySQL Cluster (whichever version that is) that would want me to go for PXC? Jul 19, 2015 at 5:09
1NDB Cluster is synchronous, but it's its own different product, it's not "Standard MySQL behavior + Synchronous Replication" which is what PXC and MariaDB essentially provide, with Galera. There are arguments on both sides of the MariaDB/Percona preference debate, which are not on topic here, but I suggest that of those two is more likely to fit what you want than NDB, because unlike them, NDB is not a drop-in replacement for MySQL Server, and has a smaller set of applications and environments to which it is suited. Jul 19, 2015 at 14:21
Got it, so if I'm using InnoDB as the storage engine for my application, then I cannot go for NDB cluster right? Correct me if I'm wrong. Jul 19, 2015 at 14:52
@tesla747 you are correct. Jul 19, 2015 at 17:57
what is the difference between master-master replication and multi-master replication? Jul 26, 2015 at 8:13