3

We have some (10-) MySQL server nodes and we want them to be replicated in a way that if one goes down the rest never get affected.

I know MySQL provides Master/Slave (not good for us) and Multi-Master replication.

  1. Does MySQL Multi-Master replication work like a mesh? I mean all nodes can get updated from all other nodes?
  2. Is it true that even in Multi-Master replication (MySQL) each node can only get the data from one master???

So if the native MySQL replication features can not fulfill our requirement, do you know any tool that can do this? Our preference is the built-in MySql replication.

Charles
  • 50,943
  • 13
  • 104
  • 142
ehsun7b
  • 4,796
  • 14
  • 59
  • 98

2 Answers2

4

The answer to your problem is simple yet very technical aswell. Traditional MYSQL tools cannot help so you need help of Galera cache from codership. Some features are synchronous replication which MYSQL do not offer, and it works as a cluster if you have a galera MYSQL cluster setup, one goes down rest keeps on going.

I personally use Percona xtradb cluster for this, percona xtradb cluster is patched version of MYSQL and galera embeded together. All servers can take reads and writes.

I have written an article on installing this but cannot post here as stack overflow may consider as link building.

DO some research on Galera, as you use normal MYSQL in a cluster environemnt, FULL HA and HS.

If you need some more info feel free to discuss it with me.

Thanks...

Masood Alam
  • 415
  • 2
  • 6
  • +1 for Percona XtraDB cluster, I've been using it for quite a while on a rather busy application, it never failed. It's definitely the right thing to use for such a purpose as described in the question. – N.B. May 08 '13 at 08:49
1

I've been researching this as well. MySQL seems to offer multi-master replication. It's explained here: http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html and a more complex example with masters and slaves here: http://capttofu.livejournal.com/1752.html.

There are 3rd party tools as well and the one that seems to come up often is Tungsten: http://code.google.com/p/tungsten-replicator/

And here's an excellent article on multi-master replication with MySQL: http://scale-out-blog.blogspot.com/2012/04/if-you-must-deploy-multi-master.html

naveed
  • 1,395
  • 2
  • 15
  • 29
  • by Multi-master I mean the slave can get updates from more than one master and this is what MySQL does not support. Tungsten is one of the tools that supports this but after some study I chose another tool called SymmetricDS which has open source and commercial versions. – ehsun7b May 24 '13 at 11:08
  • I'm glad you found the solution you were looking for, but multi-master replication is more of a peer to peer solution than a slave having multiple masters. – naveed May 24 '13 at 17:03
  • Yes, but MySQL peer to peer replication is only for 2 peers. A mesh topology is impossible to implement using MySQL replication features. – ehsun7b May 25 '13 at 05:15