Friday, April 21, 2006

Advanced replication techniques in MySQL 5.x



OnLamp has just published an article I wrote about some interesting things you can do with MySQL 5.x and replication.
  • You can create an array of peer-to-peer nodes that are at the same time master and slave, without much worry about insertion conflicts, thanks to some server variables introduced in MySQL 5.0;
  • You can achieve automatic failover using a combination of federated tables (MySQL 5.0) and events (5.1)
The article is a proof of concept that using such techniques you can achieve your goals without help from external applications.

If you want to get your hands dirty, using the companion package MySQL Replication Playground you can try out the goodies described in the article without risking your valuable servers.

Comments welcome.

2 comments:

Giuseppe Maxia said...

The only reliable way I know of having a multi-master replication in MySQL is the circular topology.
There may be other ways, but I am not aware of them.
As for geografically distant servers, I am not sure that replication is the best solution. You may face security and performance problems.

When circular replication is broken, as shown in the related article, you must fill the gap. The article gives details on how to do it. The Replication Playground mentioned in the article gives proctical examples about this scenario.


Giuseppe

Anonymous said...

I was extremely intregued by the article. I'm trying to see if this would help a client problem that I am seeing. This client has a master and and six nodes in a traditional manner. They are seeing a lot of problems involving replication falling out of sync. The CPU spikes up and can only be corrected by restarting mysql. They are running version 4.1.11.