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.


c0r0nel said...

you talk about a circular replication. If you have 4 o 5 nodes, geographically distant each others, there is a way to setup a circular replication but more centralized?? I meen, like a star??

A <-> B
A <-> C
A <-> D
A <-> E

And so on?

I think in a master-slave relation between nodes but with slaves master capables :).
A circular replication scheme is a bit risky if the replication can't be made between nodes that are in the middle of circle..what happend in this case??

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.


marco said...

not counting, for the time being, the performance issue, I'm very curious about why you consider risky geographic replication.
I mean, security is always a problem , maybe one with no definitive solution, but being able to traverse a firewall with a table limited, encrypted symmetric replication would be a definitive cost saving solution for businesses!
All other solution is WAY too expensive as compared to effective (most of the time) risks.
as far as your article on advanced replication techniques, I've found it a very interesting reading.


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.

redman said...

is it possible to repl. data of specific tables and not the whole db??

i want to replicate data which are stored in specific tables. the info in other tables wont be repl. relevant or necessary data in other mysql servers..

thanks in advance