Setting Up Asynchronous MySQL Replication: A Step-by-Step Guide

Asynchronous MySQL replication is still one of the most useful building blocks in a database setup. You can use it for read replicas, safer backups, reporting copies, migration staging, and disaster recovery. The important word is "asynchronous": the source commits a transaction without waiting for the replica to apply it. That keeps the source from blocking on every replica write, but it also means the replica can lag.

The old terminology in many MySQL commands says "master" and "slave". Newer MySQL versions use "source" and "replica" in commands such as SHOW REPLICA STATUS and CHANGE REPLICATION SOURCE TO. You may still see the older commands in older systems, examples, and scripts. This guide uses source and replica in the explanation, and shows the newer command form first with notes where older syntax differs.

The example uses two servers:

• Source: 192.168.1.100
• Replica: 192.168.1.101
• Replication user: repl_user
• Scope: all databases, unless you intentionally filter

Do this first in a test environment if you have not run the procedure before. Replication setup is simple when everything is clean. It becomes stressful when the source is busy, the dump is inconsistent, or the replica already contains old data.

Before You Touch Configuration

Confirm the basics:

• Both servers run compatible MySQL versions.
• The replica can reach the source on the MySQL port, usually 3306.
• You have administrative access to both MySQL instances.
• You can edit the MySQL configuration file and restart MySQL if needed.
• The replica is either empty or you know exactly what existing data must be replaced.
• The source has enough disk space for binary logs.

On the replica host, test basic network access:

nc -vz 192.168.1.100 3306

If nc is not available, use telnet or your cloud provider's connectivity tools. Fix firewalls, security groups, bind addresses, and routing before configuring replication. A replication user will not help if the TCP path is blocked.

Configure the Source Server

The source must write changes to binary logs. The replica reads those events and stores them in relay logs before applying them.

Edit the MySQL configuration file on the source. Common locations are /etc/mysql/mysql.conf.d/mysqld.cnf, /etc/my.cnf, or a file included from those paths. Add or verify these settings under [mysqld]:

[mysqld]
server-id=1
log-bin=mysql-bin
binlog_format=ROW

server-id must be unique across every server in the replication topology. log-bin enables binary logging. binlog_format=ROW is a practical default for most modern replication setups because it records row changes rather than relying on statement re-execution behavior.

Be careful with binlog-do-db and binlog-ignore-db. Filtering sounds convenient, but it can surprise you because statement-based behavior depends on the default database selected by the session. If you need filtered replication, design and test it deliberately. For a first reliable setup, replicate everything.

Restart MySQL on the source:

sudo systemctl restart mysql
# or, on some systems
sudo systemctl restart mysqld

Verify the settings:

SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';

log_bin should be ON. The server_id should be nonzero and unique.

Create a Replication User

Create a dedicated account on the source that the replica will use. Limit the host to the replica address if your network design allows it:

CREATE USER 'repl_user'@'192.168.1.101' IDENTIFIED BY 'use_a_real_secret_here';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.101';

The privilege name is still REPLICATION SLAVE in MySQL grants, even though newer documentation often says "replica" elsewhere.

Test the login from the replica host:

mysql -h 192.168.1.100 -u repl_user -p

If this fails, fix authentication and networking now. Common causes are bind-address on the source, firewall rules, user host mismatch, DNS resolving to a different address, and authentication plugin incompatibility with an older client.

Configure the Replica Server

On the replica, configure a different server-id. Relay logs are usually enabled automatically for replication, but naming them explicitly can make operations clearer:

[mysqld]
server-id=2
relay_log=mysql-relay-bin
read_only=ON

For stronger protection, consider super_read_only=ON after setup. read_only does not stop every privileged account from writing. super_read_only is safer for replicas that should never accept application writes, but you may need to turn it off temporarily for certain administrative tasks.

Restart MySQL on the replica and verify:

sudo systemctl restart mysql

SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE 'read_only';

Take a Consistent Initial Snapshot

The replica must start from a data copy that matches a specific binary log position. If the snapshot and position do not match, replication may start and still be wrong.

For InnoDB-heavy databases, mysqldump --single-transaction is usually the simplest consistent method. It avoids a long global read lock for transactional tables. Include --source-data=2 on newer MySQL versions so the dump records the source binary log file and position as a commented line. Older versions use --master-data=2.

Run this on a trusted host that can connect to the source:

mysqldump -h 192.168.1.100 -u root -p \
  --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --source-data=2 \
  > source_dump.sql

If your MySQL version does not support --source-data, use:

mysqldump -h 192.168.1.100 -u root -p \
  --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --master-data=2 \
  > source_dump.sql

--single-transaction is safe for InnoDB consistency, but it does not make non-transactional MyISAM tables consistent in the same way. If you still have MyISAM tables, plan a maintenance window or use another backup method that gives you a consistent snapshot.

Check the dump for the recorded replication coordinates:

grep -m 1 -E "CHANGE (MASTER|REPLICATION SOURCE)" source_dump.sql

You should see a commented line containing a binary log file and position. Keep it. You will use it when pointing the replica at the source, unless you choose GTID-based replication.

Import the Snapshot on the Replica

Transfer the dump to the replica using your normal secure method:

scp source_dump.sql db-replica:/tmp/source_dump.sql

On the replica, make sure you are not accidentally writing to an existing production-like dataset. If this replica should be a clean copy, drop and recreate only what your migration plan says to replace. Then import:

mysql -u root -p < /tmp/source_dump.sql

For large dumps, run the import in screen or tmux, and watch disk space. A failed import because /var/lib/mysql or /tmp filled up wastes time and can leave you with a half-loaded replica.

Point the Replica at the Source

On MySQL 8.0.23 and newer, use CHANGE REPLICATION SOURCE TO:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.1.100',
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='use_a_real_secret_here',
  SOURCE_LOG_FILE='mysql-bin.000001',
  SOURCE_LOG_POS=1234;

Replace the file and position with the values from the dump. If you are using older syntax, the equivalent is:

CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='use_a_real_secret_here',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=1234;

If your environment uses GTIDs, the setup is different. You normally configure GTID mode on both servers, restore a dump that preserves GTID state, and use SOURCE_AUTO_POSITION=1 instead of a file and position. Do not mix GTID and file-position instructions casually; choose one approach and test it.

A Short Note on GTID Replication

GTID replication is often easier to operate after it is set up because MySQL tracks transactions by global transaction IDs instead of making you manage a binary log file and position by hand. It is especially helpful during failover, source changes, and replica rebuilds.

That does not mean you should turn it on casually in the middle of a migration. Both servers need compatible GTID settings, and your backup or dump process must preserve the right GTID state. A common pattern is to configure:

[mysqld]
gtid_mode=ON
enforce_gtid_consistency=ON

Then, after restoring a GTID-aware dump, configure the replica with:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.1.100',
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='use_a_real_secret_here',
  SOURCE_AUTO_POSITION=1;

Use this only if the source and replica GTID history is clean and understood. If you are not sure, file-and-position replication is easier to reason about for a first setup. The worst choice is mixing examples from both approaches until replication starts but the transaction history is not what you think it is.

Start replication:

START REPLICA;

Older syntax:

START SLAVE;

Check status:

SHOW REPLICA STATUS\G

Older syntax:

SHOW SLAVE STATUS\G

The key fields are:

Field	Healthy value	What it tells you
Replica_IO_Running	Yes	The replica can connect and fetch binary log events.
Replica_SQL_Running	Yes	The replica can apply relay log events.
Last_IO_Error	empty	Network, credential, or source log problems show here.
Last_SQL_Error	empty	Data conflicts and apply errors show here.
Seconds_Behind_Source	low or falling	A rough lag indicator.

Older output uses Slave_IO_Running, Slave_SQL_Running, and Seconds_Behind_Master.

Test with a Small Write

Do not declare victory after the threads say Yes. Create a small test table or insert a harmless row in an existing test schema on the source, then verify it appears on the replica.

Example on the source:

CREATE DATABASE IF NOT EXISTS repl_check;
CREATE TABLE IF NOT EXISTS repl_check.heartbeat (
  id INT PRIMARY KEY,
  checked_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
REPLACE INTO repl_check.heartbeat (id) VALUES (1);

On the replica:

SELECT * FROM repl_check.heartbeat;

This catches simple mistakes such as pointing at the wrong source, filtering out the database, or using stale coordinates.

Secure the Replication Channel

If replication traffic crosses an untrusted network, require TLS. Even inside a private network, many teams now prefer encrypted database traffic because network boundaries change over time.

At minimum, create the replication user and channel so credentials are not shared with application accounts. For TLS-based channels, configure certificates according to your MySQL version and then include SSL options in the replication source configuration. The exact options vary by version and certificate policy, but the intent is the same: the replica should verify that it is connecting to the expected source and protect credentials and row changes in transit.

Also keep the replication user's privilege narrow. It does not need broad DDL or DML access. If someone obtains that password, the blast radius should be limited to reading replication logs, not writing application data.

Common Setup Problems

If Replica_IO_Running is No, the replica cannot fetch events. Check:

• SOURCE_HOST is correct.
• The source is listening on the expected address and port.
• Firewalls and security groups allow traffic.
• The replication user host matches the replica's source IP.
• The password and authentication plugin work with the replica's client/server version.
• The requested binary log file still exists on the source.

If Replica_SQL_Running is No, the replica fetched events but could not apply them. Check Last_SQL_Error. Duplicate keys often mean the replica was not initialized from the exact matching snapshot or someone wrote directly to the replica. Missing rows often mean data drift. Skipping a transaction with SQL_SLAVE_SKIP_COUNTER may get the thread moving, but it can also make the replica wrong. Use it only when you understand the failed transaction and accept the divergence risk.

If lag is high right after setup, let the replica catch up and watch whether Seconds_Behind_Source falls. A large dump import followed by replication startup can leave a backlog. If lag grows instead of shrinking, inspect disk I/O on the replica and write volume on the source.

Keep the Replica Healthy After Setup

Configure binary log retention on the source so replicas can survive maintenance and outages. Modern MySQL uses binlog_expire_logs_seconds:

[mysqld]
binlog_expire_logs_seconds=604800

That example keeps logs for about 7 days. Pick a value based on your recovery needs and disk capacity. Older systems may use expire_logs_days.

Monitor replication state and lag. At minimum, alert when either replication thread stops, when lag exceeds your tolerance, and when source disk usage grows because binary logs are not purged. For data consistency checks, many teams use Percona Toolkit tools such as pt-table-checksum and pt-table-sync, but test them carefully before running them on production-sized data.

Finally, keep application traffic away from the replica until you are sure it is read-only, caught up, and monitored. A replica that accepts accidental writes is worse than no replica, because the damage can stay hidden until failover or recovery.

Asynchronous replication works well when the starting snapshot, binary log coordinates, privileges, and monitoring all line up. Most failed setups come from one of those being assumed instead of verified.