Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →To switch MySQL replication roles safely, promote a caught-up replica, stop replication on it, reset its source history, repoint every downstream replica with CHANGE REPLICATION SOURCE TO, start replication again, and redirect application traffic to the promoted server. In MySQL 8.0.23 and later, MySQL calls the old “master” a source and the old “slave” a replica; the current command names use that terminology.
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
In asynchronous replication, one server writes transactions to its binary log and replicas retrieve and apply those events. A role switch changes which server is authoritative for new writes. The former replica becomes the source, while the other replicas—and eventually the former source—follow it.
As an Amazon Associate I earn from qualifying purchases.
A controlled switchover is planned: you pause or coordinate application writes, let the chosen replica catch up, and then move clients. An emergency failover happens after the source is unavailable. The mechanics are similar, but an emergency may leave transactions that never reached any replica and therefore cannot be recovered automatically.
Oracle MySQL notes that replicas connected to the same source can be at different positions. Never assume that two replicas are synchronized merely because both report that replication is running. Select one candidate, verify its executed transactions, and drain relay logs before changing the topology.
#1 Best Overall
Current and legacy MySQL terminology
| Older documentation | MySQL 8.0.23 and later | Purpose |
|---|---|---|
| master | source | Server whose binary log supplies events |
| slave | replica | Server that retrieves and applies events |
CHANGE MASTER TO |
CHANGE REPLICATION SOURCE TO |
Changes a replica’s connection and replication settings |
START SLAVE |
START REPLICA |
Starts replica threads |
STOP SLAVE |
STOP REPLICA |
Stops replica threads |
CHANGE MASTER TO is deprecated in MySQL 8.0.23 and later. Old runbooks may still show the legacy names, but use the current names when operating a supported current release.
Checks to complete before a planned switchover
Coordinate application writes
Decide exactly when writes will stop. The safest window is one in which the application is placed in maintenance mode or writes are paused at the application or routing layer. If writes continue while you promote a replica, the old source and the new source can accept different transactions, creating a fork that must be resolved manually.
Free tools Windows power users keep installed
One-click scans. No signup required.
Choose and inspect the candidate replica
On the candidate, run:
SHOW REPLICA STATUSGConfirm that the receiver and applier are healthy, inspect the executed and retrieved coordinates, and verify that the candidate has received every transaction you require. GTID deployments should compare the relevant executed and retrieved GTID sets. File-position deployments should compare the source log file and position with the candidate’s executed position. A zero or small Seconds_Behind_Source value is useful context, but it is not proof that all required transactions have been applied; inspect the actual coordinates and application state.
Check the operating prerequisites
- Replication is configured with binary logging on the server that will become the source.
- The account used by replicas has the required authentication and TLS settings.
- You have the
REPLICATION_SLAVE_ADMINprivilege (or the deprecatedSUPERprivilege) forCHANGE REPLICATION SOURCE TO. - You know the promoted host name or address, port, replication user, password-handling method, and any certificate or TLS options required in your environment.
- Application connection pools, read/write routing, monitoring, and backups can be redirected to the new source.
Planned switchover: step-by-step
1. Quiesce writes and record the starting state
Pause application writes, or use an equivalent coordination mechanism that guarantees no new transactions are committed on the old source during the cutover. Record the source identity, the candidate’s replication status, and the time of the change so that operators can correlate later errors with the topology change.
2. Stop receiving events on replicas that will remain downstream
For every replica that will continue running after the promotion, stop its replication I/O thread:
STOP REPLICA IO_THREAD;Stopping only the I/O thread prevents new events from arriving while the applier continues processing events already present in the relay log. On each server, run:
SHOW PROCESSLIST;Wait until the relay log has been fully read and the applier has finished the transactions that were already retrieved. Oracle’s failover procedure specifically requires that all replicas process statements in their relay logs before the source is changed. Do this independently on every downstream replica; two replicas sharing a source may still have different positions.
3. Promote the candidate
On the replica selected to become the new source, stop replication completely:
STOP REPLICA;Then run the promotion step described by the MySQL failover procedure:
RESET MASTER;Run this only on the server being promoted, after it has stopped acting as a replica. Treat it as a destructive binary-log and replication-history operation: preserve any logs or recovery evidence your procedures require before executing it, and never issue it casually on a server that is still the authoritative source for other systems.
At this point, the promoted server is the writer. Ensure its binary logging and access controls are suitable for accepting new application transactions before reopening writes.
4. Repoint each remaining replica
On every downstream replica, stop both replication threads before changing connection parameters:
STOP REPLICA;Point the replica at the promoted host. A current-form example is:
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='new-source',
SOURCE_USER='replication',
SOURCE_PASSWORD='password',
SOURCE_PORT=3306;Add the connection, certificate, and TLS options required by your environment. Do not place a production secret in a shared script or shell history; use the secret-management method approved for your deployment. The statement changes the replica’s connection parameters and updates MySQL’s replication metadata repositories.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →If your topology uses GTID auto-positioning, include SOURCE_AUTO_POSITION=1 in the statement. For this option, both the receiver and applier must be stopped before changing the configuration. Auto-positioning lets the replica request the transactions it is missing without manually calculating a source log file and byte position.
After the change, start replication:
START REPLICA;Run SHOW REPLICA STATUSG and verify that the source host is the promoted server, both replication threads are running, and the executed transaction set advances. Check the replica’s error fields and the source’s connection and security logs rather than relying only on a “running” indicator.
5. Redirect application traffic
Move application writes and any reads that require the writer to the promoted server. A dynamic DNS record is one documented way to give clients a stable name while changing the underlying source. A database-aware proxy, load balancer, or application configuration can serve the same purpose if it is part of your architecture. Keep writes paused until the new endpoint is accepting connections and at least one downstream replica is applying its events normally.
6. Reopen writes and observe the new topology
Allow writes only after the endpoint change is effective. Monitor the promoted server’s binary log activity, replica connection errors, applier errors, and transaction progress. Leave the old source isolated until it has been deliberately reintegrated; do not let it accept writes merely because it has returned to the network.
Recommended Free Tools
GTID coordinates versus binary-log file positions
The re-pointing command depends on how replication coordinates are managed.
| Coordinate method | What you provide | Operational effect |
|---|---|---|
| GTID with auto-positioning | Connection details plus SOURCE_AUTO_POSITION=1 |
The replica requests missing transactions by GTID, avoiding manual file-and-position arithmetic |
| Binary-log file and position | Connection details plus the exact SOURCE_LOG_FILE and SOURCE_LOG_POS that follow the last applied event |
Operators must obtain and verify matching coordinates before starting the replica |
A file-position example, when GTID auto-positioning is not in use, looks like this:
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='new-source',
SOURCE_USER='replication',
SOURCE_PASSWORD='password',
SOURCE_PORT=3306,
SOURCE_LOG_FILE='binlog.000123',
SOURCE_LOG_POS=456789;The file name and position above are illustrative values; use coordinates taken from your actual topology. A position copied from the wrong server or from before the relay log was drained can cause duplicate, skipped, or conflicting transactions.
MySQL’s asynchronous connection failover also requires SOURCE_AUTO_POSITION=1. If you use that feature, configure the retry and source-selection settings documented for your MySQL release, and test how a replica chooses among the available source endpoints.
Emergency failover when the source is unavailable
If the source has failed, you cannot quiesce it or drain its relay log. First isolate the failed host so that it cannot resume accepting writes unexpectedly. Select the replica with the most complete, verified transaction history, using GTID sets or executed file positions rather than a hostname preference.
- Confirm that the failed source is fenced off and that clients cannot continue writing to it.
- Inspect candidate replicas with
SHOW REPLICA STATUSGand choose the one with the furthest required executed history. - On the candidate, run
STOP REPLICA;, then perform the documented promotion step withRESET MASTER;. - On every surviving replica, stop replication, run
CHANGE REPLICATION SOURCE TOfor the promoted host, and runSTART REPLICA;. - Redirect application traffic and document the possibility that transactions present only on the failed source were lost.
When replicas have processed the same events from the same binary log, changing their source should not change database structure or integrity if the cutover is performed carefully. Emergency failover is riskier because you may not know whether the chosen candidate had received the last committed transactions, and you cannot use the old source to drain or compare its final relay state.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Reintegrating the former source
When the unavailable source returns, keep it out of the writer path. Convert it into a replica of the promoted source using the same connection pattern as the other replicas:
- Verify that the returning server is isolated from application writes.
- Stop any old replication configuration and remove credentials or endpoints that still point at the pre-failure source.
- Run
CHANGE REPLICATION SOURCE TOwith the promoted host, replication account, port, TLS settings, and either GTID auto-positioning or the correct file-position coordinates. - Run
START REPLICA;and wait for it to receive and apply all writes made while it was offline. - Check its executed history and application consistency before considering it a candidate for future promotion.
Before making the former source a source again, follow the normal promotion procedure and run RESET MASTER;. Oracle warns that omitting this reset can allow replicas to encounter stale writes that originated before the outage. The old source must therefore be treated as a rebuilt replica first, not as an automatically trustworthy primary.
Troubleshooting common cutover failures
The candidate is behind
Keep writes paused and allow the candidate’s applier to finish. Compare its executed GTID set or file position with the source’s final coordinates. If it cannot catch up, choose another replica; promoting a lagging server knowingly creates a shorter data history.
A downstream replica still has relay events
Do not repoint it while its relay log contains events from the old source. Stop its I/O thread, leave the applier running, and continue checking SHOW PROCESSLIST; until the relay log is fully read. If the applier is stopped because of an SQL error, fix or deliberately resolve that error before continuing; blindly changing the source can hide the original inconsistency.
CHANGE REPLICATION SOURCE TO is rejected
Check that the account has REPLICATION_SLAVE_ADMIN (or, on older configurations, SUPER), and verify that the required replication threads are stopped. Options such as SOURCE_AUTO_POSITION=1 require both the receiver and applier to be stopped. Confirm that the command uses the syntax supported by the server version.
The replica will not reconnect
Verify DNS or network reachability, the source port, the replication username and password, and every TLS certificate, key, and verification option required by the environment. Inspect the replica’s last I/O error in SHOW REPLICA STATUSG and the promoted server’s connection logs. A successful TCP connection alone does not prove that authentication or certificate validation succeeded.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minuteClients still write to the old source
Recheck the application endpoint, connection-pool refresh behavior, DNS caching, and any hard-coded host names. Fence the former source until reintegration is complete; otherwise a delayed client can create writes outside the new replication chain.
Replicas diverge after promotion
Stop writes if necessary, identify the first transaction or coordinate at which histories differ, and do not assume that restarting replication will repair it. Reconcile the data according to your recovery policy, then rebuild or re-seed the affected replica if its history cannot be trusted.
What to record in the runbook
A repeatable role switch should name the candidate-selection query, the exact write-quiescence mechanism, the relay-drain check, the promotion commands, the complete source connection options, the client endpoint to change, and the verification queries. Record whether the topology uses GTIDs or file positions, which MySQL versions are supported, who holds the required privilege, and how the former source is fenced and rebuilt. This turns an otherwise error-prone sequence into an auditable change rather than an improvised command session.
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.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
