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.

1 comment:

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.