Comments on: Resolving the MySQL Active-Active Replication Dilemma https://www.percona.com/blog/resolving-the-mysql-active-active-replication-dilemma/ Tue, 06 Feb 2024 00:26:30 +0000 hourly 1 https://wordpress.org/?v=6.5.2 By: Maciek Brzeziński https://www.percona.com/blog/resolving-the-mysql-active-active-replication-dilemma/#comment-10973177 Tue, 11 May 2021 07:19:35 +0000 https://www.percona.com/blog/?p=75666#comment-10973177 I was recently involved in a project, where there was a need for active-active setup and eventually we’ve used replication with binlog. With correct setup of auto increments on both machines the setup works reliably under heavy load (we performance tested the solution for write load exciding several times the load we observed on production before introducing the active-active architecture).

It’s by no means “internet-scale” architecture, but in our situation it reliably does what it’s supposed to and (according to our tests) will perform correctly for years to come (in our project however there is no chance that the load will increase a 1000 times).

]]> By: Eero Teerikorpi https://www.percona.com/blog/resolving-the-mysql-active-active-replication-dilemma/#comment-10973176 Mon, 10 May 2021 15:58:31 +0000 https://www.percona.com/blog/?p=75666#comment-10973176 Przemysław, I understand and appreciate your position. Tungsten solutions are well adopted, by some of the largest geo-distributed web properties (for example powering Riot Games League of Legends geo-distributed across five continents). Also, it is honorable to push open source only solutions, but our position is to enable open source databases (MySQL, MariaDB and Percona Server) to serve business-critical apps that otherwise are not really feasible to do with open source only tools. We have the same cause, how to help the overall adoption of MySQL, after all. You offer this via paid services, we offer that with paid software.

]]> By: Przemysław Malkowski https://www.percona.com/blog/resolving-the-mysql-active-active-replication-dilemma/#comment-10973175 Mon, 10 May 2021 10:35:45 +0000 https://www.percona.com/blog/?p=75666#comment-10973175 Good point, I agree that having different schemas between the nodes may result in pretty unexpected results, including also silently loosing data in special cases. That’s one of the the reasons why DDLs are executed in isolation and basically blocking the whole cluster in Galera/PXC.

]]> By: Przemysław Malkowski https://www.percona.com/blog/resolving-the-mysql-active-active-replication-dilemma/#comment-10973174 Mon, 10 May 2021 10:27:23 +0000 https://www.percona.com/blog/?p=75666#comment-10973174 Eero, I was actually considering here only solutions which are free of licence fee and open source in the same time, but also well adopted in MySQL ecosystem. And I believe only Galera/PXC and Group Replication fall into that criteria, but please correct me if I am wrong.

]]> By: Eric Rasmussen https://www.percona.com/blog/resolving-the-mysql-active-active-replication-dilemma/#comment-10973169 Mon, 03 May 2021 19:22:30 +0000 https://www.percona.com/blog/?p=75666#comment-10973169 Another great danger of active-active replication is schema changes, which can either break replication or, worse, store data into the WRONG COLUMNS. See https://bugs.mysql.com/bug.php?id=88595. To the best of my knowledge, this affects all versions of MySQL using row-based replication, and would require a change to the protocol to fix it.

]]> By: Matthew Lang https://www.percona.com/blog/resolving-the-mysql-active-active-replication-dilemma/#comment-10973168 Sun, 02 May 2021 21:03:06 +0000 https://www.percona.com/blog/?p=75666#comment-10973168 You’ve correctly stated that asynchronous replication is necessary for any deployment at geo-scale or simply replication over a WAN. Marco has presented the physics of this several times in Percona Live! presentations. As for the remedies (Lesson learned), you CAN have active/active clusters at geo-scale. While using a tool to check data consistency is helpful, it’s not a remedy when using Active/Active deployments because it’s too late to identify data drift and decide which version of the data is correct. Data drift needs to be prevented by having intelligence built into the application about the active/active topology, such as having site affinity.

Also mixing of technologies (one tool for local clustering, another to connect sites via asynchronous replication, and yet another for MySQL proxying) adds complexity to the overall deployment without solving orchestration issues like site failover and failback. I’ve done a comparison between this kind of deployment and Tungsten Clustering (a complete geo-scale clustering solution for MySQL) here:

https://www.continuent.com/resources/blog/galera-high-noon-tungsten-clustering-mysql-mariadb-perconaxtradb

]]> By: Eero Teerikorpi https://www.percona.com/blog/resolving-the-mysql-active-active-replication-dilemma/#comment-10973165 Sat, 01 May 2021 19:46:55 +0000 https://www.percona.com/blog/?p=75666#comment-10973165 Przemyslaw, you may a bit young to remember that the first two synchronous, MySQL multi-primary solutions were m/cluster (2004, a predecessor of Galera) and uni/cluster (2007) by Continuent. We abandoned from those solutions due the limitations of the synchronous replication for the geo-distributed needs, both active/passive and active/active (multi-primary). The best available (complete, proven, fully-tested) MySQL clustering solution for these geo-distributed needs is Tungsten Cluster (https://www.continuent.com/products/tungsten-clustering/topologies). Please take a look!

]]>