Set Up MySQL Primary-Replica Replication on a Linux VPS

MySQL primary-replica replication keeps a second server continuously in sync with your production database, so you can run reports, take backups or hold a failover target without touching the primary. This guide sets up GTID-based replication between two Linux VPS running MySQL 8.4, covering server-id, binary logging, the replication user, the initial snapshot and the replica configuration.

The primary is 203.0.113.10, the replica is 203.0.113.11, and the replication account is repl. Substitute your own addresses and a long random password as you go.

Before you start

• Two Linux VPS with MySQL 8.4 installed. If MySQL is not there yet, follow how to install MySQL 8.4 on Ubuntu and Debian. Both servers must run the same MySQL major version.
• Root or sudo access on both hosts, and the public or private IP each one uses to reach the other.
• TCP port 3306 open between the two hosts only — not to the internet.
• A current backup of the primary. See back up and restore MySQL databases with mysqldump.

Configure the primary for MySQL replication

Edit the MySQL server configuration on the primary:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add or uncomment these settings under [mysqld]:

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_expire_logs_seconds = 604800

server-id must be unique across the whole replication topology. ROW format is the safe default for replication. GTIDs make the replica track exactly which transactions it has applied, so you never have to calculate binary log positions by hand.

Restart MySQL and confirm it came back cleanly:

sudo systemctl restart mysql
sudo systemctl status mysql

Check the settings took effect:

sudo mysql -e "SELECT @@server_id, @@gtid_mode, @@log_bin;"

You should see 1, ON and 1. On MySQL 8.4, view the current binary log with:

SHOW BINARY LOG STATUS\G

On MySQL 8.0 the same information comes from SHOW MASTER STATUS.

Create the replication user

On the primary, create an account the replica will use. The host part must match the address the replica connects from:

CREATE USER 'repl'@'203.0.113.11' IDENTIFIED BY 'use-a-long-random-password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'203.0.113.11';
FLUSH PRIVILEGES;

REPLICATION SLAVE is the only privilege needed. Do not grant this account anything else, and do not reuse it for application traffic.

Take a consistent snapshot of the primary

The replica needs a full copy of the data plus the GTID set that copy represents. mysqldump produces both in one pass:

sudo mysqldump --single-transaction --source-data=2 --flush-logs \
  --routines --triggers --events --all-databases > /root/primary-dump.sql

--single-transaction gives a consistent InnoDB snapshot without locking tables. --source-data=2 writes the GTID position into the dump as a comment, which the replica reads on import. MyISAM tables are not covered by the transaction — if you still have any, use --lock-all-tables and accept the write lock.

Copy the dump to the replica:

scp /root/primary-dump.sql deploy@203.0.113.11:/home/deploy/

Configure the replica

On 203.0.113.11, edit /etc/mysql/mysql.conf.d/mysqld.cnf:

server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
relay_log = /var/log/mysql/mysql-relay-bin
read_only = ON

read_only blocks normal clients from writing while still allowing the replication applier to apply changes. If you want to block even users with SUPER, add super_read_only = ON — the applier is exempt from both.

Restart the service:

sudo systemctl restart mysql

Then load the snapshot:

sudo mysql < /home/deploy/primary-dump.sql

The dump sets `@@GLOBAL.g