A solution for failover in circular replication

Guilhem Bichot

Here is an answer to question on how you can handle the failure of
a single master in a circular chain of N masters using MySQL database replication. For example, if the circular chain is A -> B -> C -> D -> A and then B fails, what you can do to manually reconnect C to A as a slave.

Let's say '->' means 'is replicated by'.

A -> B
^ |
| v
D <- C

(Prior note: to set up circular replication you need to start A, B, C and D all with --log-slave-updates.) Say server ids for A,B,C,D are 1,2,3,4, respectively.

Now circular replication is running. When B fails, C will get a read error on B. C will print something like "error, will reconnect to master in 60 sec" and try to reconnect every 60 sec. Now you want to bypass dead B, i.e. make C a slave of A.

In C's binlog you can see all updates issued by clients connected to A, B, C or D. For every update in C's binlog, you can know on which machine it was originally run, thanks to 'server id'.

Example : if a client sends an UPDATE query to B,
in B's binlog there is
# server id 2 log_pos=312
UPDATE ...,
in C's binlog there is
# server id 2 log_pos=312
UPDATE ...

Example : if a client sends an UPDATE query to C,
in C's binlog there is
# server id 3 log_pos=281
UPDATE ...

Example : if a client sends an UPDATE query to D,
in A's binlog there is
# server id 4 log_pos=3145
UPDATE ...,
in B's binlog there is
# server id 4 log_pos=3145
UPDATE ...,
and in C's binlog there is
# server id 4 log_pos=3145
UPDATE ...

In other words, server ids and binlog position (unfortunately, not
binlog name) are preserved in circular replication : they are those of the originating server, the one which performed the UPDATE first, the one which received the UPDATE query from the client.

To make C a slave of A, you need to inspect C's binlog and possibly A's. As stated above, binlog names are not logged in binlogs, hence it will be easier for you if your binlogs are not rotated too often (set max_binlog_size to its default of 1 GB for example). First read C's binlog and find the latest update (INSERT, UPDATE, DELETE etc) that came from D or A (can be determined by reading 'server id'). Write down its timestamp and log_pos.

* If this latest update is from A. Determine in which of A's binlog it was (easy with the timestamp read in C's binlog).
On C do
CHANGE MASTER TO MASTER_HOST='A', MASTER_PORT='A's port',
MASTER_USER=xx, MASTER_PASSWORD=yy,
MASTER_LOG_FILE='A's_binlog_you_determined',
MASTER_LOG_POS='the_position_you_read_in_C's_binlog';

* If this latest update is from D. Read A's binlogs and determine in which of A's binlogs it was (use timestamp, server id and log_pos), write down the coordinates of this update in A's binlog. Then go back to first *.

You can see all this is entirely manual. However, it could be automated: the script would need to read C's binlog (connect to C and do SHOW BINLOG EVENTS or mysqlbinlog), get the last line whose server id is 1 or 4, then read A's or D's disk (depending on 1 or 4), determine the binlog (do 'ls' and compare with the timestamp), etc etc. This way it can be entirely automated. If I had to do it, I'd probably do it in Perl.