What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Antihost offers Linux VPS plans with full root access, unlimited traffic on a 1 Gbps port and a 14-day free trial.
For a new two-server deployment, configure one MySQL 8.4 server as the source and the other as the replica, seed the replica from a consistent copy of the source, and use GTID auto-positioning so MySQL can identify the transactions it still needs. The source must have binary logging enabled, both servers need unique identities, and the replica must be able to connect to the source. The procedure below also covers legacy file-and-position replication, validation, and recovery from the most common bootstrap failure.
What MySQL replication does
Replication copies committed changes from one MySQL server, called the source, to another server, called the replica. The normal two-server design is one-way: applications write to the source, while the replica receives the source’s binary-log events and applies them locally. MySQL replication is asynchronous by default, so a successful commit on the source does not mean the same transaction has already been applied on the replica.
A replica can be used for read traffic, reporting, backup workflows, or a manually managed failover plan. It is not automatically a highly available cluster, and it does not remove the need for independent backups. If the source is lost before events reach the replica, asynchronous replication can leave the replica behind.
- Source: accepts writes and records them in its binary log.
- Replica: connects to the source, receives binary-log events into relay logs, and applies them.
- Replication channel: stores the connection and execution state for one source-to-replica relationship.
- Direction: this guide configures one source and one replica. Additional replicas or multi-source channels require separate design and operational controls.
Choose GTID or file-position replication
Global transaction identifiers (GTIDs) are normally the clearest choice for a new MySQL 8.4 topology. Oracle’s MySQL documentation describes GTID replication as transactional and notes that it removes the need to manage binary-log file names and positions for common tasks. File-position replication remains useful when you must interoperate with an existing installation that does not use GTIDs or when a legacy procedure requires explicit coordinates.
#1 Best Overall
| Decision factor | GTID-based replication | File-and-position replication |
|---|---|---|
| New MySQL 8.4 deployment | Usually preferred; the replica negotiates missing transactions automatically. | Works, but requires careful recording and maintenance of coordinates. |
| Existing servers with GTIDs already enabled | Usually the least disruptive continuation method. | Possible, but adds manual bookkeeping that GTIDs are designed to avoid. |
| Legacy server without usable GTID history | May require a coordinated conversion and a fresh usable backup. | Often the practical compatibility option. |
| Bootstrap data | The snapshot must include the GTID state or transaction history needed to continue. | The snapshot must be paired with the exact source log file and position at which it was taken. |
| Failure after source log purging | Auto-positioning stops if the source no longer retains a required GTID; re-seeding is normally required. | Replication also fails if the required coordinates or events are no longer available. |
When GTIDs are the better fit
Use GTIDs when you control both servers, can schedule a coordinated setup, and want routine restart, re-seeding, or failover work to avoid hand-editing log coordinates. GTID mode must be enabled on both servers, and GTID consistency must be enforced before the channel is configured.
When file positions are still appropriate
Choose file positions for a compatibility-driven migration, a legacy environment where GTID conversion is not yet possible, or a runbook that already captures exact coordinates. The position is meaningful only in relation to the data snapshot loaded into the replica; using a position from a different snapshot can duplicate transactions or leave data missing.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsPrerequisites and design decisions
Complete these checks before changing either server:
- Run supported, compatible MySQL versions on both machines. This article’s commands use MySQL 8.4 terminology, including
CHANGE REPLICATION SOURCE TOandSTART REPLICA. - Give the source and replica different
server_idvalues. A duplicate identity can cause rejected events and unsafe topology behavior. - Make sure the replica can reach the source over TCP on the configured MySQL port, normally 3306, through firewalls, security groups, and routing rules.
- Enable binary logging on the source and retain enough history for the replica to catch up. A replica can use GTIDs without enabling its own binary log, but disabling replica logging removes options for chaining and some failover designs.
- Create a dedicated replication account with only the privileges required by your security policy. Do not reuse an administrative login.
- Decide whether you can stop writes and restart both servers. A cold start simplifies GTID conversion; a busy existing system needs a carefully staged maintenance procedure.
- Choose where the initial data copy will come from and verify that the destination has enough disk space for the data, relay logs, and future growth.
- Plan how you will protect credentials and replication traffic. Use encrypted connections when data or passwords cross an untrusted network.
- Keep an independent backup and recovery plan. A replica can support backup operations, but it is not a substitute for tested restores.
Prepare the source and replica
Assign server identities
On the source, set a unique identity and enable binary logging in the server configuration. On the replica, set a different identity and configure a relay-log location. Exact file paths depend on your operating system and package layout.
[mysqld]
server_id=101
log_bin=mysql-bin[mysqld]
server_id=102
relay_log=replica-relay-bin
read_only=ONThe values 101 and 102 are examples; use values that are unique within your replication topology. read_only=ON helps prevent ordinary application accounts from writing to the replica, but it is not a complete access-control boundary. Administrative accounts can still have privileges that bypass it, so restrict those accounts as well.
Check connectivity before loading data
From the replica host, test that the source name resolves to the intended machine and that the MySQL port is reachable. Confirm that intermediate firewalls allow the connection in the source direction. A replication thread cannot authenticate or transfer events if the network path is blocked, even when the SQL configuration is correct.
Recommended Free Tools
Seed the replica with a consistent snapshot
Replication cannot make an empty or unrelated database converge safely by itself. First create a consistent copy of the source data, load it into the replica, and preserve the transaction history or coordinates that describe the point represented by that copy.
Logical dump with source metadata
For an InnoDB-dominated database, a logical dump can be taken with a transaction-consistent option and source metadata. A representative command is:
mysqldump --all-databases --single-transaction --routines --events --triggers --source-data=2 > seed.sqlThe exact options must match your client version and data. --single-transaction provides a consistent view for transactional tables; nontransactional tables may need a different locking or export plan. The source metadata option records the replication starting information in the dump rather than leaving you to guess it later. Review the generated file and your dump logs before loading it.
Load the dump
- Stop application writes or otherwise establish the consistency point required by your dump method.
- Transfer the dump to the replica through a protected channel.
- Load it with a MySQL client connected to the replica, for example
mysql < seed.sqlusing an account authorized to create the dumped objects. - Confirm that the expected schemas, tables, routines, events, and row counts are present before starting replication.
For large InnoDB datasets, a raw storage snapshot or MySQL Enterprise Backup can reduce the time required to provision the replica. A storage snapshot by itself is not enough: the replica still needs the GTID state or binary-log coordinates that correspond exactly to the snapshot.
Free tools Windows power users keep installed
One-click scans. No signup required.
Do not mix a snapshot with unrelated coordinates
The source position or GTID state must describe the same instant represented by the data you loaded. If you take a dump at one point and configure a position captured at another point, the replica can skip valid transactions or apply events for rows that were not yet in the snapshot. Treat the data copy and its replication metadata as one inseparable bootstrap record.
Configure a new topology with GTIDs
The following is a cold-start path for servers you can coordinate. It assumes the existing data is synchronized and that you will create a fresh usable backup after GTID mode is enabled.
1. Quiesce writes and let the current replica catch up
If the servers already contain data, make the relevant instances read-only, stop application writes, and allow any existing replication to reach a consistent point. Verify that no transaction is still being written without a GTID. Do not proceed while the source and replica represent different datasets.
2. Stop both MySQL servers
Use your operating system’s service manager to stop MySQL cleanly on both hosts. A clean stop makes it possible to change the identity and GTID settings without new transactions arriving during the transition.
3. Enable GTID mode and consistency enforcement
Add these settings under the [mysqld] section on both servers:
gtid_mode=ON
enforce-gtid-consistency=ONMySQL warns that binary logs containing transactions without GTIDs cannot be used after GTID mode is enabled. For that reason, preserve the old logs only if your conversion plan explicitly supports them, and take a new backup that is known to be usable with the new GTID state.
4. Restart in a controlled order
Start the source first, then start the replica with replication startup suppressed while you configure its channel. The option is commonly exposed as skip-replica-start; use the spelling provided by your MySQL 8.4 package and service configuration. Suppressing automatic startup prevents an incomplete channel definition from connecting with unintended settings.
5. Create a dedicated replication account on the source
Run account-management statements on the source, using a strong secret stored in your secret manager. A generic pattern is:
CREATE USER 'replication'@'replica.example.com' IDENTIFIED BY 'store-this-in-a-secret-manager';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'replica.example.com';Replace the host restriction and privilege details with the least-privilege policy approved for your environment. If TLS is mandatory, require it in the account definition and configure matching certificates on the replica. Never publish or paste a production password into a runbook, ticket, shell history, or article.
6. Point the replica at the source with auto-positioning
With replication threads stopped, run this on the replica:
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;CHANGE REPLICATION SOURCE TO stores the connection and replication parameters for the channel. In MySQL 8.4, SOURCE_AUTO_POSITION=1 tells the replica to compare GTID sets and request the transactions it is missing. Keep the real password out of shared documentation; the value shown here is deliberately non-secret.
Rank #4
7. Start and inspect the channel
START REPLICA;
SHOW REPLICA STATUSGRead the status output rather than assuming that a successful START REPLICA statement means the channel is healthy. Check at least these fields:
| Status item | Healthy result | What a problem indicates |
|---|---|---|
Replica_IO_Running |
Yes |
The receiver thread is connected and reading source events. |
Replica_SQL_Running |
Yes |
The applier thread is executing relay-log events. |
Last_IO_Error |
Empty | Network, authentication, TLS, or source-log problems may be stopping the receiver. |
Last_SQL_Error |
Empty | An event may be failing during apply because of a data or schema problem. |
Source_Host and channel fields |
Match the intended source | The replica may be connected to the wrong host or channel. |
Auto_Position |
Enabled for the GTID setup | The channel may not be using the method you intended. |
Perform a controlled end-to-end test after the status is healthy. On the source, create a temporary test object and insert a distinctive row, then query the same object on the replica. Record the test and remove it according to your change-control policy; do not use an existing production table for the probe.
Use file-and-position replication when required
The initial preparation, data seeding, account creation, and connectivity checks are the same. The difference is how the replica is told where to begin.
- Capture the source log file and position that correspond exactly to the snapshot. If the dump embeds source metadata, use the values recorded in that dump; otherwise capture them using your approved consistent-snapshot procedure.
- Load the snapshot into the replica without allowing replication to start first.
- Run
CHANGE REPLICATION SOURCE TOwith the recorded coordinates and without GTID auto-positioning.
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;mysql-bin.000123 and 456789 are example values, not universal coordinates. Substitute the file name and position captured for your snapshot. Starting at an arbitrary position is unsafe because the coordinates are meaningful only for the exact binary-log stream and data copy from which they came.
Start the channel with START REPLICA and inspect SHOW REPLICA STATUSG in the same way as the GTID setup. File-position replication requires you to update the coordinates manually whenever you re-seed, promote a server, or change the source log stream.
Troubleshoot a replica that will not start
The receiver thread is not running
Check Last_IO_Error, the source host and port, DNS, firewall rules, and the replication account’s host restriction. Confirm that the account’s password and TLS requirements match the values stored by CHANGE REPLICATION SOURCE TO. A receiver that cannot connect cannot download relay events, regardless of whether the SQL thread is configured correctly.
The applier thread is not running
Read Last_SQL_Error before restarting repeatedly. The message identifies the event that failed during apply. Preserve the error and the surrounding status output, then correct the underlying schema, permissions, or data condition under change control. Blindly skipping events can create permanent divergence between source and replica.
Best Value
GTID auto-positioning reports missing required transactions
Auto-positioning depends on the source retaining every transaction the replica still needs. If the source has purged required GTIDs, the channel cannot continue and reports a missing-required-GTIDs condition. The reliable recovery is to re-seed the replica from a fresh consistent snapshot that is covered by the source’s retained history, or restore the missing transaction history through a supported recovery process. After loading the new seed, repeat the GTID CHANGE REPLICATION SOURCE TO configuration and start the channel.
The replica is running but data is behind
Asynchronous replication naturally has lag. Compare the receiver and applier state, inspect the reported delay and error fields, and review source and replica logs. A growing delay means the replica is receiving events faster than it can apply them, or that the receiver is intermittently disconnected. Do not treat a single instant of zero delay as proof that the system will remain caught up under peak load.
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 →Scan for outdated or missing drivers - takes under a minuteDriver Scan →The replica contains unexpected or missing rows
Stop and investigate the bootstrap record: confirm that the loaded snapshot, GTID state or file position, and source binary-log stream belong together. Also check whether applications or administrators have written directly to the replica. Re-seed rather than attempting ad hoc edits when you cannot prove which transactions were applied.
Security and ongoing operations
Protect the channel
Restrict the replication account to the source host or a narrowly defined network range, rotate its secret through your normal credential process, and use encrypted replication connections whenever credentials or database contents traverse an untrusted network. Limit firewall access to the required MySQL port and monitor failed authentication attempts.
Monitor health, lag, and logs
Collect SHOW REPLICA STATUS fields, receiver and applier state, lag indicators, and MySQL error logs. Alert on either replication thread stopping, non-empty last-error fields, a channel connecting to an unexpected source, or lag that exceeds the recovery objective for your workload.
Keep backups independent
A replica can provide a convenient consistent source for backups and can reduce load on the writer, but replication also copies accidental deletes, bad updates, and corruption. Retain independent backup copies and regularly perform restore tests. A backup that has never been restored is not evidence that recovery will work.
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 matchWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallChoose the delivery mode deliberately
MySQL documents asynchronous, semisynchronous, and delayed replication as separate operational choices. Asynchronous replication is the default used here. Semisynchronous replication can change how commits wait for acknowledgment, while delayed replication intentionally preserves a time gap that can help recover from operator mistakes. Select a mode based on your recovery-point objective, acceptable write latency, and failure-handling process rather than enabling it as a substitute for testing.
When two self-managed servers are not enough
A source-and-replica pair is a useful starting topology, but it does not by itself provide automatic leader election, conflict-free multi-writer behavior, or guaranteed zero data loss. Consider a managed MySQL service or a more formal high-availability design when your team cannot provide continuous monitoring, tested restoration, secure network operations, and a documented promotion procedure. If you add more replicas, define which one is eligible for promotion, how GTID continuity will be preserved, and how clients will be redirected after a failure.
Quick Recap
Completion checklist
- The source and replica run compatible MySQL versions and have unique
server_idvalues. - The source has binary logging enabled and retains enough history for the replica’s expected catch-up window.
- The replica was loaded from a consistent snapshot, with matching GTID state or exact file-position metadata.
- Both servers have
gtid_mode=ONandenforce-gtid-consistency=ONfor the GTID method. - The replication account is dedicated, least-privilege, protected by a managed secret, and restricted by host and network policy.
CHANGE REPLICATION SOURCE TOpoints to the intended source and uses eitherSOURCE_AUTO_POSITION=1or the coordinates captured for the loaded snapshot.START REPLICAhas been run andSHOW REPLICA STATUSGshows both receiver and applier threads running with empty error fields.- A controlled write has been observed on the replica, monitoring and alerts are active, and an independent restore-tested backup exists.
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.
