Home / server / Questions / 823621
Accepted
Frank Martin
Asked: 2017-01-02 13:26:06 +0800 CST

Master-Slave semi circle replication not working in MySQL

  • 772

I didn't know how to describe it so I am calling it semi circle. Here's what I am doing.

Server A
Server B = This is slave of Server A
Server C = This is slave of Server B

When I update something on Server A then it is reflected on Server B. But the same change is not reflected on Server C.

Only when I update something on Server B then change is reflected on Server C.

How do I make it so any changes done on Server A will come to Server B (which is already working) and then it goes to Server C?

EDIT
Upon investigation I found out that when I make some change on Server A then its log file position changes. But when those changes are reflected on Server B then Server B log file position doesn't change. It is because of this reason Server C doesn't know if there has been any change on Server B unless I explicitly change something (insert,update,delete) on Server B.

So is there a way to tell MySQL to increment log file position when Slave is receiving updates from Master?

mysql mysql-replication master-slave

1 Answers

  1. Best Answer

    FYI—this is typically called a replication relay chain. In order to turn this on, you will have to make sure the following settings are set on server B:

    log_bin
log_slave_updates

    This will tell server B to update its log position when updates from A are received. The documentation for this can be found here (emphasis added):

    Normally, a slave does not write any updates that are received from a master server to its own binary log. This option causes the slave to write the updates performed by its SQL thread to its own binary log. For this option to have any effect, the slave must also be started with the --log-bin option to enable binary logging. --log-slave-updates is used when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement

    A -> B -> C

    Here, A serves as the master for the slave B, and B serves as the master for the slave C. For this to work, B must be both a master and a slave. You must start both A and B with --log-bin to enable binary logging, and B with the --log-slave-updates option so that updates received from A are logged by B to its binary log.

    • 3