[debian-mysql] Question about special MySQL Replication

[Clint Byrum]

Excerpts from Denny Bortfeldt's message of 2013-07-26 15:10:09 -0700:
> Hello all,
> 
> I hope you can answer my following question:
> 
> I've had 2 Servers with Master-Master Replication. Now I increase my server
> size to 3.
> I asked myself if it is possible to replication like this:
> 
> Update/Insert on Server  ------------- Replicate to
> 
> Server A ----> Server B & Server C
> Server B ----> Server A & Server C
> Server C ----> Server A & Server B
> 
> So if one update statement happen on one server, it will replicate to
> TWO-Server and not only one.
> I read about a lot of circulate replication - but what happen if one Server
> will shut down? Then only 1 Server is "okay" and the other server won't get
> the updates anymore - right?
> 
> Let's assume Server A is my Master Server where all updates happen:
> Update on Server A --> Replicate to B --> B will Replicate to C
> 
> If B is now shutdown ('cause of hardware failure), then A can't Replicate
> to B and therefore C won't get the Updates, right?
> 
> I really hope it's possible that 1 Server can replicate to more than 1
> Master Server.
> 

In MySQL, you can only have one master. So to achieve a 3 master setup,
you need to use a replication ring. So your scenario becomes

A --> B --> C --> A

MySQL knows to drop its own replication records, so when A gets back
its own log, it ignores it.

Failures in a replication ring are _painful_, As you have to either shrink
the ring temporarily (non-trivial) or bring up a new master quickly to
repair the ring.

I'd recommend Galera before I'd recommend multi-master MySQL for 3+
masters. This gives you synchronous multi-master writes so you don't
have to figure out how to handle replication lag in your app and so
that a failure just means less replication targets, not a total loss
of replication.

http://codership.com/content/using-galera-cluster

It is also built into Percona's cluster product:

http://www.percona.com/software/percona-xtradb-cluster

And MariaDB's too:

https://downloads.mariadb.org/mariadb-galera/