Replication Topologies

So far you have looked at a simple two-server replication topology, like this:

A → B

where machine A is the master, and it replicates to machine B.

This is the simplest, easiest to set up, replicating pair.

Multislave

It's possible to set up a single master with any number of slaves. The architecture would look like this:

You would configure the master in the same way as explained previously. For the slaves, the only critical thing to ensure is that they each have a unique server-id.

In this model, any update that occurs in the master A is replicated to slaves B, C, and D.

Such a system is a good model for a load-balanced system if there are many more client read requests than writes. Client requests would pass through a load-balancing server (not shown), which refers each read request to B, C, or D in turn, whereas write requests would be referred to master server A. (Server A might also process reads.)

The setup is a high availability one, because if any slave system goes down (say, C), the load-balancer would just send requests to the remaining live systems (B and D).

The weakness in the system is that if master server A goes down, updates cannot be processed. To make the system a little more fault tolerant, the configuration needs more than one master.

Multislave, Multimaster

As well as having several slaves to share the load of read requests and provide resilience, you can have a second master (or several masters) to ensure high availability of the system to updates, as well as to reads.

You could have a setup like this:

In this topology, master M1 not only feeds updates to B, C, and D, but also to a backup master, M2. Slave servers B, C, and D know that they must look to master M1 for their updates. But they know that if M1 fails, they should elect server M2 as their new master and replicate changes from it instead.

MySQL has a command, CHANGE MASTER TO, that enables a slave to switch its attention to a new master. However, when several slaves need to be switched to a new master, things can be a little more complicated.

It is important that all slaves “agree” at the same time that they're going to elect a new master. If there's a problem with a network connection, some slaves might think master M1 is down, whereas others know it's still up. If this situation arises, not all slaves will have identical copies of the data.

Currently such master election procedures have to be implemented in application code. However, it is intended that MySQL will soon have a master election process built into its replication system so that issues of this sort are handled in MySQL rather than in your application.

See the MySQL Reference Manual for detailed information about CHANGE MASTER TO and other replication commands.

Circular Replication

It is possible to connect servers in a ring, like this:

To do this, you would need to configure each server, A through F, to do both binary logging and slave update logging. Each machine would have to be configured with both master and slave parameters, effectively a daisy-chain setup that loops back onto itself.

However, such a setup can be prone to problems if you allow clients to perform updates on all machines. If an update is made on A that creates a unique key (perhaps a value got generated in an auto-increment column), and then an update occurs on F a short time after, before replication has occurred, the same unique key value will be generated. When replication of the data from A reaches F, a unique key violation will arise, and replication will stop.

To use circular replication, you need to ensure that updates can occur only on one server. Alternatively, your applications must be written to take this situation into account and ensure that it does not arise.