Free tools Windows power users keep installed
One-click scans. No signup required.
To replicate MySQL between two servers, designate one server as the source, enable binary logging, seed the second server with a consistent copy of the data, and configure the second server as a replica. For a new MySQL 8.4 deployment, GTID-based replication is usually the clearest method: after both servers have GTIDs enabled, the replica can automatically request transactions it is missing. File-and-position replication is still useful for older installations and compatibility work.
Antihost offers Linux VPS plans with full root access, unlimited traffic on a 1 Gbps port and a 14-day free trial.
The procedure below covers prerequisites, data seeding, GTID configuration, legacy file-position setup, validation, security, and the failures that most often prevent a replica from starting.
As an Amazon Associate I earn from qualifying purchases.
Recommended Free Tools
What MySQL replication does
Replication copies committed changes from one MySQL database server, called the source, to another, called the replica. In the normal two-server design, applications write to the source. MySQL records those changes in the source’s binary log. The replica connects to the source, receives the log events, writes them to its relay log, and applies them to its own data.
Standard replication is one-way and asynchronous. A successful commit on the source does not mean the transaction has already been applied on the replica. A network interruption, a slow query, or an overloaded replica can therefore create replication lag. Replication can improve read scaling, reporting isolation, and recovery options, but it is not a replacement for independent backups.
#1 Best Overall
Recommended topology
- One source accepts writes.
- One replica receives and applies the source’s transactions.
- Applications use the source for writes and, only when your consistency requirements allow it, the replica for read-only work.
Additional replicas, multi-source channels, delayed replicas, and semisynchronous replication are separate design choices. Start with one source and one replica unless a documented workload or recovery requirement justifies more complexity.
Decide between GTID and file-position replication
MySQL’s newer GTID method identifies each transaction globally. It removes the need to maintain binary-log file names and byte positions for routine reconnects and makes failover and reconfiguration easier. File-position replication identifies a starting point by an exact binary-log file and position; it remains practical when an existing estate already uses it or when a compatibility constraint prevents GTID adoption.
Outdated 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 matchPC 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 & 11| Decision factor | GTID-based replication | File-position replication |
|---|---|---|
| Best fit | New MySQL 8.4 topologies and simpler ongoing operations | Legacy servers, migrations, or tooling built around log coordinates |
| Replica connection | SOURCE_AUTO_POSITION=1 |
Exact SOURCE_LOG_FILE and SOURCE_LOG_POS |
| Bootstrap requirement | Both servers use gtid_mode=ON and enforce_gtid_consistency=ON |
Coordinates must correspond exactly to the snapshot loaded on the replica |
| Operational burden | Replica negotiates missing transactions by GTID | Operators must preserve and update file and position information |
| Main failure condition | The source has purged a GTID the replica still needs | The recorded coordinate does not match the loaded data |
Choose after checking whether either server already contains production data, whether downtime is available for a consistent cold start, whether GTIDs are enabled, how much binary-log history is retained, and whether simplicity or legacy compatibility is the priority.
Prerequisites and safety checks
Use compatible MySQL installations
Run supported, compatible MySQL versions on both machines and confirm the exact release before starting. Replication behavior and option names can differ between major versions. This guide uses MySQL 8.4 terminology, including CHANGE REPLICATION SOURCE TO and START REPLICA.
Give each server a unique identity
Set a different numeric server_id on every MySQL server. Duplicate IDs can cause events to be ignored or create confusing loops. A typical arrangement is server_id=1 on the source and server_id=2 on the replica.
Enable and retain binary logs on the source
The source must have binary logging enabled and must retain logs long enough for a disconnected replica to catch up. Retention that is too short is the direct cause of many missing-GTID and missing-coordinate failures. A replica can use GTIDs without writing its own binary log, although disabling replica logging limits future failover, chaining, and promotion options.
Provide network reachability
Allow the replica to reach the source’s MySQL TCP port, normally 3306, through host firewalls, cloud security groups, and intermediate firewalls. Restrict the source’s listening address and permitted client networks rather than exposing the database port broadly.
Create a least-privilege replication account
Create a dedicated account whose host pattern is limited to the replica’s address or protected network. Grant only the privilege required for replication, and store the password in a secret manager instead of a configuration file that is readable by unrelated users.
CREATE USER 'replication'@'10.0.0.%' IDENTIFIED BY 'store-this-in-a-secret-manager';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'10.0.0.%';Replace the network range with the replica’s real address policy. If traffic crosses an untrusted network, configure encrypted replication and verify the certificate and cipher settings supported by your MySQL version.
Seed the replica with a consistent data copy
Replication cannot repair a replica that starts with unrelated or inconsistent data. First create a snapshot or dump that represents one coherent point in time, then load that copy into the empty or disposable replica. The replica also needs the transaction history or GTID state that follows that point.
Logical dump
For predominantly InnoDB data, a transaction-consistent mysqldump is a common starting point. Include the objects your application needs, such as triggers, routines, and events, and include source metadata so you can identify the corresponding replication point. An illustrative command is:
mysqldump --all-databases --single-transaction --triggers --routines --events --source-data=2 > seed.sqlReview the options for the installed 8.4 client and your storage engine mix. Nontransactional tables cannot be made consistent by --single-transaction alone; schedule a lock or maintenance window for those tables. Transfer the dump securely, load it on the replica, and verify that the expected schemas, users, routines, and event definitions exist.
Physical or managed snapshots
Raw storage snapshots and MySQL Enterprise Backup are alternatives for large InnoDB datasets where a logical dump would take too long. The snapshot must still record the binary-log coordinates or GTID state associated with the captured data. A snapshot without that history is not enough to start replication safely.
Protect the source during seeding
For a busy system, measure how long the dump takes and ensure the source retains all transactions generated during that interval. If you cannot guarantee that retention, pause writes or use a backup method that supplies a transactionally consistent snapshot and its matching log metadata.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Set up new GTID-based replication on MySQL 8.4
1. Prepare server configuration
On the source, configure a unique identity and binary logging. On the replica, configure a different identity and relay-log settings. A minimal configuration outline is:
[mysqld]
server_id=1
log_bin=mysql-binUse the corresponding unique server_id on the replica. Keep paths, storage limits, and log-retention settings appropriate for each host.
2. Enable GTIDs in a controlled cold start
If both servers are new, enable GTIDs before loading the seed. If either server already contains data, first make the servers read-only as appropriate, allow the existing replica to catch up, and plan a maintenance window. Stop MySQL on both servers, then restart each with:
gtid_mode=ON
enforce-gtid-consistency=ONStart the replica with replication startup suppressed while you finish the connection configuration. On MySQL 8.4 this is commonly done with the skip_replica_start option during bootstrap.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Binary logs containing transactions without GTIDs cannot be used as a normal source of GTID transactions after this change. Take a new usable backup after GTID mode is enabled, and retain it with the corresponding history.
3. Load the seed
Import the consistent dump or restore the physical snapshot on the replica while replication threads are stopped. Confirm that the data set corresponds to the GTID state or source metadata captured at backup time.
4. Configure the replica connection
With both replication threads stopped, run the following on the replica. Keep the real password outside published configuration and inject it through your secrets process.
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='source.example.com',
SOURCE_PORT=3306,
SOURCE_USER='replication',
SOURCE_PASSWORD='use-a-secret-manager',
SOURCE_AUTO_POSITION=1;SOURCE_AUTO_POSITION=1 tells MySQL to use GTIDs to negotiate the transactions missing from the replica. The statement also stores the source connection parameters for the replication channel.
5. Start and inspect the channel
START REPLICA;
SHOW REPLICA STATUSGCheck that the receiver and applier threads report running, the source host and channel are the intended ones, and the error fields are empty. Confirm that the retrieved GTID set advances as new source transactions are committed.
6. Perform a controlled end-to-end test
- Write a harmless test row in a noncritical table on the source.
- Wait for the replica’s receiver and applier to process the event.
- Read the row on the replica and then remove it according to your change-control procedure.
- Record the result, observed delay, and the relevant status output for your runbook.
This test proves connectivity, authentication, log transmission, and SQL application together; checking only that a connection exists is not sufficient.
Use file-position replication when GTIDs are not appropriate
File-position replication follows the same broad sequence: configure identities and binary logging, capture a consistent source snapshot, load it into the replica, configure the source account, and start the channel. The critical difference is that the replica must start at the exact source log file and byte position represented by the snapshot.
Record the matching coordinates
Capture source metadata as part of the dump or backup. On MySQL 8.4, inspect the source’s binary-log status with:
SHOW BINARY LOG STATUS;Use the file and position that correspond to the data loaded into the replica, not the values observed later after additional transactions have been committed.
Configure the replica
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='source.example.com',
SOURCE_PORT=3306,
SOURCE_USER='replication',
SOURCE_PASSWORD='use-a-secret-manager',
SOURCE_LOG_FILE='mysql-bin.000123',
SOURCE_LOG_POS=456789;Replace the example file and position with the values recorded for the snapshot. Start the channel with START REPLICA and inspect SHOW REPLICA STATUSG exactly as in the GTID procedure.
Diagnose a replica that will not start
| Symptom | Likely cause | Action |
|---|---|---|
| Receiver thread is not running | Network filtering, wrong host or port, invalid credentials, or TLS mismatch | Test TCP reachability from the replica, verify the account’s host restriction and password, and inspect the receiver error fields and MySQL error log. |
| Applier thread stops | A SQL error, schema difference, or conflicting local write | Read Last_SQL_Error, correct the underlying data or schema issue, and follow a documented recovery plan instead of skipping transactions blindly. |
| Missing required GTIDs | The source purged binary logs containing transactions still needed by the replica | Re-seed the replica from a fresh consistent snapshot, or restore the missing transaction history if you have a complete and verified copy. |
| File-position setup starts at the wrong data | The log coordinates do not match the snapshot that was loaded | Discard the inconsistent replica data, create a new matching snapshot, and record its exact file and position. |
| Events appear to be ignored | Duplicate server_id values |
Assign unique IDs, restart as required, and verify the identity in replica status. |
| Replication is connected but lag grows | The replica cannot apply transactions as quickly as the source generates them, or storage and network resources are constrained | Measure apply delay, inspect CPU, memory, disk latency, relay-log space, and long-running transactions before changing workload or topology. |
| Data differs despite running threads | The initial seed was inconsistent, writes occurred directly on the replica, or an earlier error was bypassed | Stop relying on the replica for reads, compare data, investigate the first divergence, and re-seed when integrity cannot be proven. |
When investigating, save the complete output of SHOW REPLICA STATUSG, the source’s binary-log retention information, and the MySQL error logs. The first error in time is usually more useful than the final cascading message.
Security, consistency, and operations after setup
Keep replication traffic private and encrypted
Use network controls that allow only the replica to connect to the source’s replication port. Use encrypted connections when credentials or database contents cross a network you do not fully trust. Rotate the replication account’s secret through your normal secret-management process and avoid putting it in shell history or shared tickets.
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 →Prevent accidental writes on the replica
Applications should not write to a read replica. Enforce read-only policy appropriate to your failover design and restrict administrative access. If you intentionally permit maintenance writes, document how those writes are isolated so they cannot conflict with source transactions.
Monitor health and lag
- Receiver and applier running state.
- Last I/O and SQL error text and timestamps.
- Retrieved and executed GTID sets, or the current file and position.
- Replication delay, while remembering that a delay value can be misleading during idle periods.
- Binary-log retention on the source and relay-log disk usage on the replica.
- Disk latency, capacity, CPU, memory, and long-running transactions.
Keep independent backups
A replica can reduce load during backup jobs, but it is not an independent recovery boundary: accidental deletes, application corruption, and replicated ransomware can reach it. Keep backups on separate storage, test restores regularly, and document which backup contains the transaction history needed to rebuild a replica.
Choose the right delivery behavior
Asynchronous replication is the default and usually has the lowest write latency, but a source failure can lose transactions that had not reached the replica. MySQL also documents semisynchronous and delayed replication. Semisynchronous operation trades some commit latency for stronger confirmation that another server received the transaction; delayed replication intentionally waits before applying events so an operator has a recovery window for certain logical mistakes. Select among them according to recovery-point objectives, acceptable latency, and failure-handling procedures rather than enabling a mode without a tested runbook.
When to re-seed instead of repairing in place
Re-seeding is the safer choice when required GTIDs have been purged, the initial snapshot cannot be proven consistent, data divergence is extensive, or a replica has accumulated unknown manual changes. Stop the channel, preserve diagnostic logs, create a fresh consistent snapshot with its transaction metadata, replace the replica data, and repeat the GTID or file-position configuration. A short, controlled rebuild is preferable to silently running a replica whose contents cannot be trusted.
Quick Recap
Operational checklist
- Both servers run compatible, supported MySQL versions.
server_idvalues are unique.- The source has binary logging enabled and retention sized for the longest expected outage.
- The replication account is restricted, least-privilege, and protected by the organization’s credential policy.
- The seed is transactionally consistent and includes matching GTID state or log coordinates.
- GTID mode and GTID consistency are enabled on both servers when using auto-positioning.
CHANGE REPLICATION SOURCE TOcontains the correct source endpoint and selected positioning method.START REPLICAsucceeds andSHOW REPLICA STATUSGshows healthy receiver and applier threads.- A controlled source write has been observed on the replica.
- Monitoring, alerting, independent backups, and a documented re-seeding procedure are in place.
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.
