To switch MySQL replication roles safely, stop writes, verify the candidate replica has applied every relay-log event, promote it with STOP REPLICA and RESET MASTER, then repoint each remaining replica with CHANGE REPLICATION SOURCE TO. Finally, redirect applications to the promoted server and rebuild the old source as a replica before considering another promotion.
Need a server to try this on? Antihost offers Linux VPS plans with NVMe storage, full root access and a 14-day free trial.
What a MySQL role switch actually changes
MySQL’s current documentation calls the writer the source and its followers replicas. “Master” and “slave” still appear in older runbooks and commands, but the operational task is the same: one server becomes the source of binary-log events, and the other servers receive and apply those events.
As an Amazon Associate I earn from qualifying purchases.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchA controlled switchover is different from an emergency failover. In a switchover, the current source is available and you can pause application writes, drain replication, and redirect clients in an orderly sequence. In a failover, the source may be unavailable, so you promote the best-known replica and accept that the old source may contain transactions that were never delivered elsewhere.
The procedure is safe only when you know which transactions the candidate has applied. Replicas sharing one source can be at different positions; being connected to the same source does not prove that they are synchronized with each other.
#1 Best Overall
Before you start: choose the operation and collect facts
Planned switchover
Use a planned switchover for maintenance, a server move, or a deliberate change of writer. Quiesce application writes first, or coordinate a maintenance mode that prevents new transactions while you drain replication. Because the old source remains available, you can inspect its status and make the transition with a known endpoint.
Emergency failover
Use emergency failover when the source cannot serve writes. Select the replica with the most complete and current data, promote it, and then reconfigure surviving replicas. You cannot verify transactions that were still only on the failed source, so record the failure window and reconcile data when the old server returns.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →Information to record
- The hostname or IP address clients currently use for writes.
- Each replica’s replication status, including whether its receiver and applier threads are running.
- Whether the topology uses GTIDs and auto-positioning or file-and-position coordinates.
- The replication account, required privileges, port, TLS certificates, and connection settings for the new source.
- Which replicas must continue downstream replication after the promotion.
- The exact maintenance or failover time, so you can identify transactions written before and after the role change.
Test the runbook in the same MySQL version, authentication configuration, and network policy as production. A syntactically correct statement can still fail because the account lacks privileges, the certificate is not trusted, binary logging is disabled, or a firewall blocks the new connection.
Version-aware command names
MySQL 8.0.23 and later use CHANGE REPLICATION SOURCE TO. The older CHANGE MASTER TO statement is deprecated, although legacy scripts may still contain it. Likewise, older procedures use START SLAVE and STOP SLAVE; use START REPLICA and STOP REPLICA for current versions.
| Older terminology or command | Current terminology or command | When it matters |
|---|---|---|
| master | source | Documentation and status output in current MySQL releases use source terminology. |
| slave | replica | Use replica in runbooks and operational notes. |
CHANGE MASTER TO |
CHANGE REPLICATION SOURCE TO |
Use the current statement on MySQL 8.0.23 and later. |
START SLAVE |
START REPLICA |
Starts the replica’s replication threads. |
STOP SLAVE |
STOP REPLICA |
Stops replication before promotion or reconfiguration. |
Changing a replica’s connection parameters also updates its replication metadata repositories. The statement requires the REPLICATION_SLAVE_ADMIN privilege; the deprecated SUPER privilege can satisfy this requirement on configurations that still permit it.
Step 1: quiesce writes and identify the promotion candidate
Stop or fence application writes
Put the application into a mode that rejects writes, or route all write traffic through a gate you can close. Reads may continue if your consistency requirements allow them, but do not let clients write to the old source while you are changing the topology. If you cannot quiesce writes during an emergency, treat the operation as a failover and document the possibility of lost or divergent transactions.
Free tools Windows power users keep installed
One-click scans. No signup required.
Rank #2
Check that the candidate is usable
On every candidate, inspect replication status and confirm that the receiver and applier are healthy. Check the source host, I/O and SQL thread state, retrieved and executed coordinates, and any reported error. For a GTID topology, compare the transactions received and executed rather than relying on a replica’s age or network latency alone. For file-and-position replication, record the current binary-log file and position.
Choose the replica that has applied the greatest safe portion of the old source’s history and that has the capacity, storage, network access, and credentials required to serve as the new source. Do not assume the replica with the lowest reported delay is complete unless its relay log has also been processed.
Step 2: drain relay logs on replicas that will remain downstream
For each replica that will continue following the promoted server, stop its replication I/O thread so it stops fetching new events from the old source while the events already received can be applied. Then watch the process list until the relay log has been fully read. Oracle MySQL’s failover procedure specifically requires that all replicas process statements in their relay logs before the source is changed.
- On a downstream replica, stop only the receiver:
STOP REPLICA IO_THREAD; - Run
SHOW PROCESSLIST;repeatedly and verify that the applier has finished reading the relay log. Also inspect the replica status output for remaining errors or lag. - Do not proceed while an SQL/applier thread is stopped unexpectedly, reporting an error, or still processing relay-log events.
This drain is important because a replica can have events already downloaded from the old source that have not yet been applied. Repointing it before those events are processed can leave the database behind the candidate you are promoting.
Step 3: promote the candidate replica
Once writes are quiesced and the candidate is caught up, run the promotion commands on that server:
STOP REPLICA;
RESET MASTER;STOP REPLICA prevents the promoted server from continuing to pull or apply events from the old source. The documented promotion sequence then uses RESET MASTER before other replicas are made to follow the new source. Treat this as a deliberate topology boundary: preserve any logs or status information you need for incident analysis before executing a reset, and ensure your backup and binary-log retention policy accounts for the reset.
After promotion, enable the settings required for a source in your environment, including binary logging and any log-replica-update behavior needed by downstream replicas. Confirm that the server accepts local writes and that its replication threads are no longer running.
Step 4: repoint every remaining replica
On each replica that should follow the promoted server, stop replication and replace the old source connection details. A current-version pattern is:
STOP REPLICA;
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='new-source',
SOURCE_USER='replication',
SOURCE_PASSWORD='password',
SOURCE_PORT=3306;
START REPLICA;Use the actual account, port, hostname, and secret-management method for your environment. Add the TLS options required by your certificate and verification policy; do not remove encryption merely to make a failover connect. If your deployment uses a nondefault retry interval, heartbeat, compression, or other source-connection settings, carry those options into the statement.
Many options require the relevant replication thread to be stopped first. When using SOURCE_AUTO_POSITION=1, GTID_ONLY=1, or anonymous-transaction GTID assignment, stop both the receiver and applier threads before changing the setting. A typical GTID-based re-point therefore looks like:
STOP REPLICA;
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='new-source',
SOURCE_USER='replication',
SOURCE_PASSWORD='password',
SOURCE_PORT=3306,
SOURCE_AUTO_POSITION=1;
START REPLICA;Use GTID auto-positioning only when the topology is already configured for GTIDs and the new source contains the required transaction history. Its advantage is that the replica can locate the next needed transactions without manually calculating a source log file and position. In a file-and-position topology, supply the new source’s binary-log file and position according to your established coordinates instead of adding SOURCE_AUTO_POSITION=1.
Validate each re-point
- Check that both replication threads start and remain running.
- Confirm that the reported source host is the promoted server.
- Look for authentication, TLS, DNS, permission, and duplicate-key errors.
- Verify that the replica is applying new events generated by a controlled test transaction or by normal application traffic.
- Check all downstream replicas individually; one healthy replica does not prove that the others were reconfigured.
Step 5: redirect clients to the promoted source
Change the application’s write endpoint after the promoted server is ready and at least the required replicas are following it. Update a load-balancer target, service-discovery record, connection pool, or DNS record. A dynamic DNS record is one documented way to tell clients where the source is, but account for DNS caching and connection-pool reuse: clients may continue using the old address until their resolver and pool refresh.
Redirect reads that must observe the new writer as well. If read traffic can use replicas, define the acceptable replication delay and make sure the application does not send a write to the old source through a stale endpoint. Keep the old source fenced from writes; two writable sources create a split-brain condition that replication alone will not resolve.
Planned switchover versus emergency failover
| Decision point | Planned switchover | Emergency failover |
|---|---|---|
| Write handling | Pause or fence writes before promotion. | The failed source may have accepted transactions that never reached a replica. |
| Candidate selection | Verify relay-log drain and applied coordinates with the old source available. | Choose the most complete surviving replica using its last known status. |
| Replication coordinates | Use the topology’s GTID or file-position method consistently. | GTIDs reduce manual coordinate arithmetic when the required history exists. |
| Client redirection | Schedule endpoint changes and allow pools or DNS caches to refresh. | Redirect as soon as the promoted source is writable, while preventing stale clients from writing to the failed host. |
| Old source | Reconfigure it as a replica after the switch if it is no longer the writer. | Keep it isolated until its data and role are explicitly reconciled. |
Reintegrate the former source when it returns
Do not simply start the old source and let it resume serving writes. Configure it as a replica of the promoted source using the same CHANGE REPLICATION SOURCE TO pattern used for the other replicas. It must receive all writes made while it was offline.
- Keep the returned server fenced from application writes.
- Assess its data and replication metadata against the promoted source.
- Stop any old replication configuration and point it to the promoted source with the correct account, port, TLS settings, and GTID or file-position method.
- Start replication and verify that it applies the promoted source’s history without errors.
- Only after it is a clean, current replica should you consider it for a future promotion.
Before making the former source a source again, repeat the promotion procedure and run RESET MASTER. Oracle warns that skipping this step can allow replicas to pick up stale writes from before the outage. The reset is therefore part of a deliberate re-promotion, not a command to run casually during troubleshooting.
Troubleshooting common failure points
The replica will not start after re-pointing
Read the replication error and verify the source hostname, port, account, password, and TLS options. Confirm that the account has the privileges required for replication and that network controls permit the connection. If you changed an option that requires stopped threads, stop both threads and issue the CHANGE REPLICATION SOURCE TO statement again.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minuteThe replica reports missing transactions
Check whether the candidate and the downstream replica share the same GTID history. With file-and-position replication, verify that the selected log file and position belong to the promoted source’s binary logs. Do not invent a position or skip events to make the status turn green; skipping can create silent divergence.
Relay logs were not fully drained
Stop the promotion sequence and let the applier finish, or resolve the applier error first. A replica that was connected to the old source may still have unapplied events even when its receiver is stopped.
Clients still write to the old server
Inspect DNS answers, load-balancer membership, service-discovery data, and existing connection pools. Fence the old source at the network or database layer until every writer has moved. A successful DNS update does not terminate already-open database sessions.
Replicas disagree after the switch
Compare each replica independently. Replicas of one source can have different positions, and a re-point does not retroactively make their data identical. Identify the first divergent transaction, preserve error logs, and repair the affected replica from a known-good source or backup rather than layering another role change on top of the inconsistency.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Operational checklist
- Decide whether this is a controlled switchover or emergency failover.
- Quiesce or fence writes and record the maintenance time.
- Choose a candidate and verify its applied history and health.
- Stop the I/O thread on downstream replicas and confirm their relay logs are fully read.
- Run
STOP REPLICAandRESET MASTERon the candidate. - Repoint each remaining replica with
CHANGE REPLICATION SOURCE TO, including required GTID and TLS options. - Start replication and validate every replica separately.
- Redirect application writes and consistency-sensitive reads to the promoted source.
- Keep the former source fenced, then reintegrate it as a replica.
- Document coordinates, errors, endpoint changes, and any transactions that may have existed only on the failed source.
Bottom line
A reliable MySQL role switch is a coordinated topology change, not just a single promotion command. Drain relay logs, promote one verified candidate, re-point every downstream replica with the current replication syntax, move client traffic, and reintegrate the former source as a replica before using it again. GTID auto-positioning can simplify re-pointing, but it does not replace write fencing, privilege checks, TLS validation, or per-replica verification.
Frequently Asked Questions
Can I run the old CHANGE MASTER TO command on MySQL 8.0.23 or later?
It is deprecated in MySQL 8.0.23 and later. Use CHANGE REPLICATION SOURCE TO in new runbooks and update legacy automation during a controlled maintenance window.
Do all replicas have the same data if they use the same source?
No. Replicas can be at different execution positions and can have different relay-log backlogs. Check each replica’s applied history and drain its relay log before changing its source.
What should I do if the old source returns after failover?
Keep it fenced from application writes, configure it as a replica of the promoted source, let it receive all intervening writes, and only follow the promotion procedure with RESET MASTER if you later make it a source again.
Recommended Free Tools
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
