mysql: Change Replication Topology A-＞B,C to A-＞B-＞C (Doc ID 1444892.1)

Change Replication Topology A->B,C to A->B->C (Doc ID 1444892.1)To Bottom

---

In this Document

APPLIES TO:

MySQL Server - Version 5.5 and later Information in this document applies to any platform.

GOAL

Change from master A replicating to slaves B and C, to master A replicating to B which replicates to C.  

SOLUTION

1. Check that B is properly set up as a master: the binary log is enabled, log_slave_updates is set, and the server has a unique server-id.

2. Stop the replication threads on B and C.

STOP SLAVE;

3. Check if the slave has any open temporary tables:

SHOW STATUS LIKE 'Slave_open_temp_tables';

 

If the number of temporary tables is non-zero, then start and stop the slave again until it is 0.

4. Examine Relay_master_log_file and Exec_master_log_pos from B and C.

SHOW SLAVE STATUS\G

5. One of the servers will be farther behind. Advance it to match the other.

 START SLAVE UNTIL master_log_file='X', master_log_pos=Y;

Fill in X and Y with Relay_master_log_file and Exec_master_log_pos from ahead server.  

6. Use SHOW SLAVE STATUS\G until you see that it has reached the specified position and stopped.

7. Now B and C should be identical, having stopped at the same position.

8. Get the binlog position from B.

SHOW MASTER STATUS;

9. Change C to the new master. 

CHANGE MASTER TO master_host='B', master_log_file=X', master_log_pos=Y; 

Fill in X and Y with the values from step 7

10. Start replication on B and C (order doesn't matter).

START SLAVE;

REFERENCES

NOTE:1962618.1 - Change Replication Topology A->B->C to A->B,Chttps://dev.mysql.com/doc/en/replication-features-temptables.html