How to Set Up MySQL Replication (Master-Slave) on Linux

Overview

MySQL replication is the process of automatically copying data from one MySQL server (the master) to one or more other servers (the slaves). Changes made on the master — inserts, updates, deletes — get written to a binary log, and the slave reads that log and replays the same changes on its own copy of the data.

You’ll want replication set up if you’re running a high-traffic site that needs read queries offloaded to a second server, if you need a live standby for failover, or if you want a replication target you can safely back up without locking your production database. It’s also common on VPS SSD Hosting setups where you’re running your own database tier across two nodes.

This guide covers classic async master-slave replication using binary logging, tested on MySQL 8.0 and MariaDB 10.6+ on Ubuntu 22.04 and AlmaLinux 9. GTID-based replication is covered in the notes — I’d recommend it for any new setup since it makes failover and re-synchronisation significantly less painful.

Prerequisites

• Two Linux servers (master and slave) — both must be able to reach each other over TCP port 3306, or whatever port MySQL is bound to
• MySQL 8.0+ or MariaDB 10.5+ installed on both servers
• Root or sudo access on both servers
• Identical MySQL/MariaDB major versions on both nodes — replication from MySQL 8.0 master to 5.7 slave is not supported
• A consistent snapshot of the master database to seed the slave (covered in Step 5)
• Both servers should have their clocks synced via NTP — clock drift causes intermittent replication lag that’s annoying to diagnose

Step-by-Step Instructions

Step 1: Configure the Master Server

Open the MySQL configuration file on the master. On Ubuntu/Debian it’s typically /etc/mysql/mysql.conf.d/mysqld.cnf. On RHEL/AlmaLinux it’s /etc/my.cnf or /etc/my.cnf.d/mysql-server.cnf.

Add or update the following under the [mysqld] section:

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = your_database_name
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON

A few things worth knowing here:

• server-id must be unique across every server in your replication topology — 1 for master, 2 for first slave, and so on. Duplicate IDs will silently break replication.
• binlog_do_db restricts replication to a specific database. Leave it out if you want to replicate everything.
• binlog_format = ROW is the safest option. STATEMENT mode can cause data drift with non-deterministic functions like NOW() or UUID().
• GTID mode is optional but strongly recommended — skip the GTID lines if you’re on a very old setup, but you’ll need to track binary log positions manually.

Restart MySQL to apply the changes:

sudo systemctl restart mysql

Step 2: Create a Replication User on the Master

Log into MySQL on the master and create a dedicated replication user. Don’t reuse your application’s database user for this.

CREATE USER 'replicator'@'slave_ip_address' IDENTIFIED WITH mysql_native_password BY 'StrongPasswordHere';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'slave_ip_address';
FLUSH PRIVILEGES;

Replace slave_ip_address with the actual private IP of your slave server. Never leave example passwords in production — generate something with openssl rand -base64 24 and store it in your password manager.

Step 3: Record the Master Binary Log Position

Still on the master, lock the tables briefly and note the current binary log file and position. You need this to tell the slave exactly where to start reading.

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

You’ll see output like this:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Executed_Gtid_Set|
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      154  | your_db      |                  |
+------------------+----------+--------------+------------------+

Note the File and Position values. Keep this MySQL session open — closing it will release the lock.

Step 4: Export the Master Database

In a second terminal, dump the master database while the lock is held. This ensures the dump matches the log position you just recorded.

mysqldump -u root -p --single-transaction --master-data=2 --databases your_database_name > master_dump.sql

📝 Note: --master-data=2 embeds the binary log position as a comment in the dump file, which is useful for reference. If you’re using GTID mode, you can also add --set-gtid-purged=ON.

Once the dump is complete, go back to your first terminal and release the lock:

UNLOCK TABLES;

Step 5: Import the Dump on the Slave

Copy the dump file to the slave server and import it:

scp master_dump.sql user@slave_ip:/tmp/
ssh user@slave_ip
mysql -u root -p your_database_name < /tmp/master_dump.sql

Step 6: Configure the Slave Server

On the slave, edit its MySQL config file with a unique server-id and enable GTID if you’re using it:

[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin.log
gtid_mode = ON
enforce_gtid_consistency = ON
read_only = ON

read_only = ON is important. It prevents application code or a runaway script from accidentally writing to the slave and causing data drift. Your replication user is exempt from this restriction, so replication still works.

sudo systemctl restart mysql

Step 7: Connect the Slave to the Master

Log into MySQL on the slave and run the CHANGE MASTER TO command with the values you recorded in Step 3:

CHANGE MASTER TO
  MASTER_HOST='master_ip_address',
  MASTER_USER='replicator',
  MASTER_PASSWORD='StrongPasswordHere',
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=154;

START SLAVE;

📝 Note: On MySQL 8.0.23+, CHANGE MASTER TO is deprecated in favour of CHANGE REPLICATION SOURCE TO, and START SLAVE becomes START REPLICA. The old syntax still works but you’ll see deprecation warnings in the error log.

Step 8: Verify Replication is Running

On the slave, check the replication status:

SHOW SLAVE STATUSG

Look for these two lines — both must say Yes:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Also check Seconds_Behind_Master. A value of 0 means the slave is fully caught up. A steadily growing number means the slave can’t keep up with the master’s write rate, which usually points to under-resourced hardware.

⚠ Warning: If either thread shows No, check the Last_IO_Error and Last_SQL_Error fields in the same output. Those will tell you exactly what went wrong.

Common Issues and Troubleshooting

Slave_IO_Running: Connecting (never reaches Yes)

This almost always means the slave can’t reach the master on port 3306. Check your firewall rules on both servers. On Ubuntu with UFW:

sudo ufw allow from slave_ip to any port 3306

On AlmaLinux/RHEL with firewalld:

sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="slave_ip" port port=3306 protocol="tcp" accept'
sudo firewall-cmd --reload

Also confirm MySQL is actually binding on the master’s network interface and not just 127.0.0.1. Check bind-address in mysqld.cnf — it should be 0.0.0.0 or the master’s private IP, not 127.0.0.1.

Error: “Could not execute Write_rows event on table” (Error 1062: Duplicate entry)

This happens when the slave already has a row that the master is trying to insert — usually because someone wrote directly to the slave, or the initial dump wasn’t taken while the lock was held. The fix depends on how far the data has diverged. For a small mismatch you can skip the offending event:

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

⚠ Warning: Skipping events causes permanent data drift. If you’re seeing this repeatedly, re-seed the slave from a fresh dump. Don’t just keep skipping.

Replication Stopped After MySQL Upgrade

Upgrading MySQL on the master without upgrading the slave first will break replication, sometimes silently. Always upgrade the slave first, then the master. Check that both report the same major version with SELECT VERSION(); before restarting replication.

Slave Replication Lag Growing Continuously

A single-threaded slave SQL thread can fall behind on write-heavy masters. Enable parallel replication in the slave’s config:

slave_parallel_workers = 4
slave_parallel_type = LOGICAL_CLOCK

Restart MySQL on the slave after making the change. LOGICAL_CLOCK is generally safer than DATABASE for most workloads.

“Binary logging not possible” Error After Enabling GTIDs

This error appears when you enable GTID mode but the existing binary logs contain transactions without GTIDs. You need to let those logs expire (or purge them manually) and then restart replication. Running RESET MASTER; on the master clears the binary logs — only do this if the slave is already caught up and you’re prepared to re-seed it.

FAQ