Advanced MySQL Replication Techniques
Subject:   conceptually great but far from being usable
Date:   2006-09-05 01:05:12
From:   Oyku
Thanks for the great article. Unfortunately it's far from being practically usable after testing in production grade traffic and data environment.

Let's consider this case. A,B,C,D in circular replication. First of all it's crucial to have a RESET MASTER before everything else so that until a problem occurs all hosts will have the same log_pos.

When B fails, failing over to A as master for C requires
1-checking to see if the latest update came from A or D
2-If it came from A then determining the position for that event
3-determining the log file name in A
4-If the latest update came from D, based on the timestamp determining the log file name in A by examining D's logbin events.

It seems perfectly scriptable BUT
1- With B not functioning, A,C,D working in circular replication there is no good way to reintroduce B without disturbing the system
2- Even though the steps above can be scriptible it's perfectly the database server software's responsibility to provide the basic information regarding replication not the db admin's by checking the log positions and timestamps.

My conclusion is having multi master ability with managed auto increment keys is wonderful. But right now out of the box MySQL does not provide the tools to use it safely. The CHANGE MASTER TO very low level. In a multi master situation, you cannot tell your web server to stop updating just because you'll need to figure out what log name and which position to issue the command with.

Having looked at 5.1 NDB replication, I saw good signs that can be brought into plain vanilla replication. Until that day, I'd refrain from using multi master replication.

I hope I'm not missing a point here.