Advanced MySQL Replication Techniques

http://onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.htm...

In this broader schema, water is the master of air and slave of earth, which is slave of fire; and this last is in turn slave of air, thus completing the circle. The boxed numbers next to each server indicate the server ID, which must be different for each node; the auto_increment_increment, the same for all the nodes; and the auto_increment_offset, which guarantees the uniqueness of self-generated keys.

Here is the complete setup for all nodes:

# node A - water
[mysqld]
server-id                       = 10
log-bin                         = mysql-bin
log-slave-updates
replicate-same-server-id        = 0
auto_increment_increment        = 10
auto_increment_offset           = 1
master-host                     = earth.stardata.it
master-user                     = nodeAuser
master-password                 = nodeApass
report-host                     = nodeA
 
# Node B - air
[mysqld]
server-id                       = 20
log-bin                         = mysql-bin
log-slave-updates
replicate-same-server-id        = 0
auto_increment_increment        = 10
auto_increment_offset           = 2
master-host                     = water.stardata.it
master-user                     = nodeBuser
master-password                 = nodeBpass
report-host                     = nodeB
 
# Node C - fire
[mysqld]
server-id                       = 30
log-bin                         = mysql-bin
log-slave-updates
replicate-same-server-id        = 0
auto_increment_increment        = 10
auto_increment_offset           = 3
master-host                     = air.stardata.it
master-user                     = nodeCuser
master-password                 = nodeCpass
report-host                     = nodeC
 
# Node D - earth
[mysqld]
server-id                       = 40
log-bin                         = mysql-bin
log-slave-updates
replicate-same-server-id        = 0
auto_increment_increment        = 10
auto_increment_offset           = 4
master-host                     = fire.stardata.it
master-user                     = nodeDuser
master-password                 = nodeDpass
report-host                     = nodeD

A few variables are worth noting in these configuration files. The first is log-slave-updates. This option tells each server to write the changes that it receives from its master through the relay binary log to its own binary log. Without it, cascade replication doesn't work. The option replicate-same-server-id has the purpose of avoiding infinite replication loops, effectively telling each node to ignore from its master's binary log any statement that originated with its own server ID.

auto_increment_increment and auto_increment_offset have the appropriate values, as explained earlier. The rest is normal replication administration.