Why Does CHANGE MASTER Trigger Traffic Spikes in MySQL GTID Replication?

Background

In a MySQL 5.7.12 environment with GTID and semi‑synchronous replication enabled, the master’s IP was changed (the instance remained the same). Shortly after, the master‑to‑slave traffic surged dramatically.

Phenomenon Analysis

Monitoring showed the master’s traffic consisted entirely of send operations, while the slave’s traffic consisted of receive operations.

The send‑traffic curve of the master overlapped almost exactly with the receive‑traffic curve of the slave.

It appeared that all of the master’s send traffic was directed to the slave.

The only operation performed on the slave at the time was a CHANGE MASTER that updated master_host and set auto_position=1.

Key Question

Did the CHANGE MASTER cause the slave to re‑receive binlog events that had already been executed on the master?

Test Procedure

Using a test setup (MySQL 5.7.12, GTID, auto‑position replication), several operations were performed while observing the contents of the slave’s relay‑log. The following steps were taken:

Run initial operations and inspect the relay‑log (see first image).

Execute CHANGE MASTER on the slave.

Observe that MySQL clears the existing relay‑log and creates a new one that contains GTID information (second image).

Restart the slave with auto_position=1 and examine the new relay‑log and slave status (third image).

Execute a new SQL statement on the master and verify that the slave receives only the binlog entries that follow the previously executed GTID, not the already‑executed ones (fourth image).

Conclusion of Initial Test

The CHANGE MASTER command with auto_position=1 does not cause the slave to re‑fetch binlog events that have already been executed on the master.

Extended Question

If the slave is already lagging when CHANGE MASTER is executed, will the portion of the binlog that has been fetched but not yet applied be re‑fetched, potentially creating a short‑term traffic peak?

Answer

Yes.

Validation with Lagging Slave

In a sysbench‑driven environment where the slave was already delayed, the slave was stopped, and its relay‑log and status were examined. The number of transaction IDs in the relay‑log far exceeded the number of transactions that had been applied, confirming significant lag.

The specific delayed transaction was located within the relay‑log (see image).

After executing CHANGE MASTER, the relay‑log was cleared again. When the IO thread was started, the missing binlog entries were pulled from the master, causing a noticeable traffic spike on the master (send) and the slave (receive), as shown in the following monitoring screenshots.

As expected, the previously unsynchronized portion of the binlog was fetched again, producing a traffic peak whose magnitude and duration depend on the size of the lagged binlog segment.

Final Remarks

The original high‑traffic incident was likely not caused solely by the CHANGE MASTER operation, because the observed traffic persisted far longer and exceeded the total size of the master’s binlog. Further investigation is required to pinpoint the primary cause.