How To Set Up MySQL Primary-Replica Replication on Ubuntu 22.04
Tricknowtech Team 20 minutes
Goal
Stand up asynchronous binary-log replication from a primary MySQL 8 server to a replica on Ubuntu 22.04, so writes made on the primary appear on the replica automatically, and confirm the link is healthy with SHOW REPLICA STATUS.
Prerequisites
Two separate Ubuntu 22.04 LTS servers with a non-root sudo user and ufw enabled on each — referred to as the primary (203.0.113.10) and the replica (203.0.113.11).
Network connectivity between the two servers on TCP port 3306, either over a private network or a firewall rule you control on the primary.
MySQL Server not yet installed on either machine, or installed without production data you're unwilling to overwrite.
Basic comfort with SSH and running SQL statements at the mysql> prompt.
Let an AI agent do this for you
Copy a ready-made prompt for an AI coding assistant with terminal access to your server (Claude Code, Cursor, or similar) — it can carry out the steps below for you. Review what it plans to run before it executes anything.
Primary-replica replication copies every change made on one MySQL server (the primary) to one or more other servers (replicas) automatically, in near real time. MySQL implements this by having the replica read the primary's binary log — a sequential record of every data-changing statement — and re-apply those changes locally. It's the building block behind read scaling (send SELECT-heavy traffic to replicas), off-primary backups (dump from a replica instead of locking up the primary), and disaster-recovery setups where a replica can be promoted if the primary fails.
This tutorial sets up classic binary-log-file-and-position replication (as opposed to GTID-based replication) between two MySQL 8 servers on Ubuntu 22.04, using the modern CHANGE REPLICATION SOURCE TO syntax that replaced the deprecated CHANGE MASTER TO in MySQL 8.0.23. By the end you'll have a replica continuously streaming writes from a primary and a working method for verifying the link is healthy.
Prerequisites
Two separate Ubuntu 22.04 LTS servers, referred to throughout this tutorial as the primary (example IP 203.0.113.10) and the replica (example IP 203.0.113.11).
On each server, a non-root user with sudo privileges and a basic ufw firewall enabled, both already configured before you begin.
Network connectivity between the two servers on TCP port 3306 — either a private network both servers share, or public IPs with a firewall rule you control on the primary.
MySQL Server not yet installed on either machine, or installed without any production data you're unwilling to overwrite (this guide installs and configures it from a clean state).
Basic comfort running commands over SSH and executing SQL statements at the mysql> prompt.
Step 1 — Install MySQL Server on Both Hosts
Run the same installation steps on both the primary and the replica. Start by updating the package index and installing the mysql-server package from the Ubuntu repositories.
Tricknowtech VPS Hosting
Dedicated KVM resources and full root access — deployed in under 60 seconds, no ticket required.
Once the install finishes, run the interactive security script to remove sample settings and set sensible defaults.
bash
sudo mysql_secure_installation
“On Ubuntu, the mysql-server package configures the root MySQL account to authenticate via the auth_socket plugin by default, not a password. That means local commands run as `mysql -u root -p` may fail even after mysql_secure_installation. Throughout this tutorial, use `sudo mysql` and `sudo mysqldump` for local administrative access instead — sudo's own authentication is what gets you in.”
Confirm the service is active on both machines before continuing.
bash
sudo systemctl status mysql
Step 2 — Configure the Primary Server for Replication
On the primary (203.0.113.10), open the main MySQL configuration file. On Ubuntu this is mysqld.cnf under mysql.conf.d, included automatically by /etc/mysql/my.cnf.
bash
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Under the [mysqld] section, set (or add) the following directives. bind-address controls which network interface MySQL listens on — by default it's restricted to 127.0.0.1, so it must be changed for the replica to reach it. server-id is a unique integer identifying this instance within the replication topology. log_bin turns on binary logging, which is what replication reads from. binlog_expire_logs_seconds prevents the binary log directory from growing forever by purging logs older than the given number of seconds — 604800 is 7 days.
Save the file and restart MySQL for the changes to take effect.
bash
sudo systemctl restart mysql
sudo systemctl status mysql
Step 3 — Open the Firewall for Replication Traffic
The replica needs to reach the primary on TCP port 3306. On the primary, allow that traffic only from the replica's IP address rather than opening the port to everyone.
bash
sudo ufw allow from 203.0.113.11 to any port 3306 proto tcp
sudo ufw status
“Scope this rule to the replica's exact IP address. A blanket `ufw allow 3306` exposes your database port to the entire internet.”
Step 4 — Create a Dedicated Replication User on the Primary
Replication should use its own account with only the privilege it needs, rather than the root user. Log in to the primary's MySQL prompt.
bash
sudo mysql
Create the user, scoped to connect only from the replica's IP address, and grant it the REPLICATION SLAVE privilege — the single permission needed to read the binary log stream.
sql
CREATE USER 'replica_user'@'203.0.113.11' IDENTIFIED BY 'ReplaceWithAStrongPassword!';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'203.0.113.11';
FLUSH PRIVILEGES;
EXIT;
“Replace 'ReplaceWithAStrongPassword!' with a strong, unique password, and keep it — you'll need it again in Step 9. Restricting the user's host to the replica's exact IP (instead of '%') means the credential is useless to anyone who can't already reach that network path.”
Step 5 — Take a Consistent Snapshot of the Primary
The replica needs a starting copy of the primary's data, taken at a known point in the binary log, so it can apply everything that happens after that point. mysqldump can produce both in one step.
--single-transaction takes the dump inside a single InnoDB transaction, giving a consistent snapshot without locking tables for the duration of the dump. --source-data=2 records the binary log file name and position that correspond to that exact snapshot, writing them into the dump file as a commented-out CHANGE REPLICATION SOURCE TO statement you'll use in Step 8. --all-databases dumps every database on the server; drop it and name specific databases if you only want to replicate a subset.
“--source-data is the mysqldump flag name from MySQL 8.0.26 onward. If your mysqldump version predates that, use --master-data=2 instead — it behaves identically, only the flag name changed. --single-transaction only guarantees consistency for InnoDB tables; if the server also has MyISAM tables, take the snapshot with FLUSH TABLES WITH READ LOCK held for the duration of the dump instead.”
Step 6 — Transfer and Import the Snapshot on the Replica
Copy the dump file from the primary to the replica using scp, run from the primary.
Replace your_user with your actual sudo username on the replica. Once the transfer finishes, import the dump on the replica.
bash
sudo mysql < ~/primary-dump.sql
This can take a while on a large dataset — let it finish before moving on.
Step 7 — Configure the Replica Server
On the replica (203.0.113.11), edit the same configuration file.
bash
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Set a unique server-id (it must differ from the primary's), a relay_log path (where the replica stages events it receives before applying them), and read_only so the database rejects writes from ordinary clients. Enabling log_bin here too is optional for a single primary/replica pair, but worth doing now if you might chain a further replica off this one later.
Restart MySQL on the replica to apply the changes.
bash
sudo systemctl restart mysql
sudo systemctl status mysql
“read_only blocks writes from ordinary client connections, but MySQL's replication applier threads are exempt from it regardless of any account or privilege — they're internal system threads, not client sessions, so they keep applying incoming changes even while read_only is enabled. A client account with the SUPER privilege is also exempt and can still write directly despite read_only. If you want to block even SUPER-privileged writes (for example, to stop an admin from writing directly to a replica by mistake), set super_read_only = ON instead — that blocks every client account while still leaving the replication threads themselves free to apply changes.”
Step 8 — Locate the Binary Log Coordinates
The --source-data=2 flag used in Step 5 wrote the exact binary log file and position matching the snapshot into the dump file, as a commented-out statement near the top. Find it.
The output looks something like this — note the file name and position, you'll need both in the next step.
sql
-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='mysql-bin.000001', SOURCE_LOG_POS=157;
Step 9 — Point the Replica at the Primary
Still on the replica, open the MySQL prompt.
bash
sudo mysql
Make sure replication isn't already running, then configure the connection to the primary using CHANGE REPLICATION SOURCE TO — the modern MySQL 8 syntax that replaced the deprecated CHANGE MASTER TO. Use the replication user and password from Step 4 and the log file/position from Step 8.
START REPLICA starts two background threads on the replica: an I/O thread that connects to the primary and streams binary log events into the local relay log, and a SQL thread that reads the relay log and applies the events to the replica's data.
Step 10 — Verify Replication Status
Check the replication link's health with SHOW REPLICA STATUS, the MySQL 8 replacement for the deprecated SHOW SLAVE STATUS.
sql
SHOW REPLICA STATUS\G
In the output, confirm these fields:
Replica_IO_Running: Yes — the I/O thread is connected to the primary and receiving events.
Replica_SQL_Running: Yes — the SQL thread is successfully applying those events.
Seconds_Behind_Source: 0 (or a small, shrinking number) — the replica has caught up to the primary.
Last_IO_Error and Last_SQL_Error: empty — no errors on either thread.
“If Replica_IO_Running shows "Connecting" instead of "Yes", double-check the firewall rule from Step 3, the SOURCE_HOST/SOURCE_PORT values, and the replication user's credentials and host restriction. If Replica_SQL_Running shows "No", read Last_SQL_Error in the same output — it names the exact statement and error that stopped the SQL thread.”
Step 11 — Test Replication End to End
With both threads running, confirm data actually flows through. On the primary, create a test database and insert a row.
bash
sudo mysql
sql
CREATE DATABASE replication_test;
USE replication_test;
CREATE TABLE demo (id INT PRIMARY KEY, note VARCHAR(50));
INSERT INTO demo VALUES (1, 'hello from primary');
Then, on the replica, check that the same data showed up without you doing anything else.
bash
sudo mysql
sql
SHOW DATABASES;
SELECT * FROM replication_test.demo;
If the replication_test database and its row appear on the replica, the primary is successfully streaming writes and the replica is applying them.
Conclusion
You installed MySQL 8 on two Ubuntu 22.04 servers, enabled binary logging and a unique server-id on the primary, created a scoped replication user, took a consistent snapshot with mysqldump --single-transaction, imported it on the replica, and used CHANGE REPLICATION SOURCE TO with the recorded binary log coordinates to start the replication link. You verified the I/O and SQL threads were both running with SHOW REPLICA STATUS and confirmed a write on the primary appeared on the replica. From here, this pair can support read-only query offloading, off-primary backups, and — with further work on monitoring and failover procedures — a foundation for high availability.
