MySQL 8.4 Replication Setup: Two-Server Guide

Updated 4 November 2026 · Standard formula, shown below

This guide walks you through setting up MySQL 8.4 replication between two servers, where one server acts as the source (primary) and the other as a replica. You’ll configure the source to record changes in its binary log, then point the replica at it to continuously apply those changes. By the end, you’ll have a working replica that stays in sync with the source, useful for backups, read scaling, or failover testing.

What You’ll Need

Two servers running MySQL 8.4 (or 8.0+, as the steps are nearly identical) with network access to each other on the MySQL port (default 3306).
Root or administrative access to MySQL on both servers (via sudo or the root MySQL user).
Both servers should have the same MySQL major version (e.g., both 8.4.x) to avoid compatibility issues.
Firewall rules that allow the replica to connect to the source’s MySQL port.
Optional but recommended: a dedicated MySQL user for replication (we’ll create one).

Step-by-Step

Configure the source server (primary)
1. Edit the MySQL configuration file (usually /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/my.cnf) on the source server.
2. Add or uncomment the following lines under the [mysqld] section:
```
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
```
  The server-id must be unique across your replication topology. log_bin enables the binary log, and binlog_format=ROW is recommended for most setups.
3. Save the file and restart MySQL: sudo systemctl restart mysql (or service mysql restart on older systems).
Create a replication user on the source
1. Connect to MySQL as root: mysql -u root -p
2. Create a user with replication privileges. Replace replica_user and strong_password with your own:
```
CREATE USER 'replica_user'@'%' IDENTIFIED BY 'strong_password';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
FLUSH PRIVILEGES;
```
  Using @'%' allows connection from any host; for better security, restrict it to the replica’s IP (e.g., @'192.168.1.20').
3. Record the source’s binary log coordinates. Run:
```
SHOW MASTER STATUS;
```
  Note the values of File (e.g., mysql-bin.000001) and Position (e.g., 157). You’ll need these for the replica.
Prepare the replica server
1. Edit the MySQL configuration file on the replica server.
2. Add a unique server-id (e.g., 2) and optionally enable the relay log (it’s on by default). Add:
```
server-id = 2
```
3. Save and restart MySQL on the replica.
Copy existing data from source to replica (if any)
1. If the source already has databases you want to replicate, you need a consistent snapshot. The easiest method is to use mysqldump:
```
mysqldump -u root -p --all-databases --master-data=2 --single-transaction > backup.sql
```
  Run this on the source. The --master-data=2 option automatically records the binary log coordinates in the dump file (as a comment).
2. Copy the dump file to the replica server (e.g., using scp).
3. On the replica, import the dump:
```
mysql -u root -p < backup.sql
```
Configure the replica to connect to the source
1. Connect to MySQL on the replica: mysql -u root -p
2. Run the CHANGE REPLICATION SOURCE TO statement (in MySQL 8.4, the old CHANGE MASTER TO is deprecated but still works). Use the source’s IP, replication user, password, and the binary log coordinates you noted (or those from the dump file if you used --master-data=2):
```
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='source_server_ip',
  SOURCE_USER='replica_user',
  SOURCE_PASSWORD='strong_password',
  SOURCE_LOG_FILE='mysql-bin.000001',
  SOURCE_LOG_POS=157;
```
  If you used mysqldump with --master-data=2, you can find the coordinates in the dump file (look for CHANGE MASTER TO line) and use those instead.
Start replication and verify
1. Start the replica threads:
```
START REPLICA;
```
2. Check the replica status:
```
SHOW REPLICA STATUS\G
```
  Look for Slave_IO_Running: Yes and Slave_SQL_Running: Yes. If both are Yes, replication is active.
3. Test by making a change on the source (e.g., create a test database) and confirm it appears on the replica.

Common Mistakes

Duplicate server-id: If both servers have the same server-id, replication will fail with errors. Ensure they are different.
Incorrect binary log coordinates: If you set the wrong SOURCE_LOG_FILE or SOURCE_LOG_POS, the replica may start at the wrong point, causing errors or missing data. Always use the exact values from SHOW MASTER STATUS or the dump file.
Firewall blocking port 3306: The replica must be able to reach the source’s MySQL port. Check firewalls (ufw, firewalld) and security groups.
Using the same MySQL data directory: If you copied the entire data directory instead of using mysqldump, you may have copied the source’s server-id and binary log files, causing conflicts. Always use mysqldump or a proper backup tool.
Not enabling binary logging on the source: Without log_bin, the source won’t record changes, and replication will fail. Verify with SHOW VARIABLES LIKE 'log_bin';.

Tips

Use a dedicated replication user with minimal privileges (REPLICATION SLAVE) rather than a full admin user.
Set binlog_do_db or binlog_ignore_db on the source if you only want to replicate specific databases. This reduces overhead and avoids replicating system logs.
Monitor replication lag with SHOW REPLICA STATUS and check the Seconds_Behind_Master field. A high value indicates the replica is falling behind.
Consider enabling gtid_mode=ON and enforce_gtid_consistency=ON on both servers for easier failover and management. This is a more advanced setup but simplifies replication in the long run.
Take regular backups of the replica to avoid overloading the source with backup tasks.

Troubleshooting

Replica IO thread not running: Check Last_IO_Error in SHOW REPLICA STATUS. Common causes: wrong host/port, firewall, or authentication issues. Verify the replication user can connect from the replica (test with mysql -u replica_user -p -h source_ip).
Replica SQL thread not running: Check Last_SQL_Error. Usually, this is due to a duplicate key or missing table. You can skip the problematic transaction by running SET GLOBAL sql_slave_skip_counter = 1; (not recommended for production) or use STOP REPLICA; SET GLOBAL sql_slave_skip_counter = 1; START REPLICA; to move past it. Better: investigate the error and fix the root cause.
Replica is lagging: If Seconds_Behind_Master is high, the replica may be underpowered or the network is slow. Optimize queries, increase replica resources, or consider parallel replication settings.
Errors like ‘Could not find first log file name in binary log index’: This means the binary log file specified in CHANGE REPLICATION SOURCE TO doesn’t exist on the source. Check SHOW BINARY LOGS; on the source and use an existing file.
Replication stops after a crash: MySQL should auto-recover, but if not, check the error log and restart the replica threads.

Frequently Asked Questions

Can I replicate only specific databases?

Yes. On the source, you can set binlog_do_db in the config to a comma-separated list of databases to include in the binary log. On the replica, you can use REPLICATE_DO_DB in the CHANGE REPLICATION SOURCE TO statement to filter which databases are applied. Be careful with cross-database queries.

What is the difference between statement-based and row-based replication?

Statement-based replication logs the SQL statements, while row-based logs the actual changed rows. Row-based is safer for most cases because it avoids issues with non-deterministic functions (like NOW()) and is the default in MySQL 8.4. Statement-based can be faster for bulk operations but may have edge cases.

Do I need to use GTIDs for replication?

No, but GTIDs (Global Transaction Identifiers) make replication easier to manage and failover simpler. They are enabled by default in MySQL 8.0+, so you may already have them on. If not, you can enable them with gtid_mode=ON and enforce_gtid_consistency=ON.

Can I replicate from a replica (cascading)?

Yes, you can set up a replica to act as a source for another replica. To do this, enable log_bin and log_slave_updates on the intermediate replica. This is useful for scaling read loads across many replicas.

How do I stop replication without losing my setup?

Run STOP REPLICA; to pause replication. To fully remove the replication configuration, run RESET REPLICA ALL; which clears the connection settings. The data on the replica remains intact.

What happens if the source server goes down?

Replication will pause. When the source comes back, the replica will automatically reconnect and continue from where it left off, as long as the binary logs are still available. If the source is permanently lost, you can promote the replica to be the new primary (requires manual steps).

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.