Set up MySQL or MariaDB primary-replica replication

MySQL replication copies every change from a primary server to one or more replicas in near real time. Replicas give you a hot standby, a place to run backups and reports without loading the primary, and read scaling. This guide sets up asynchronous primary-replica replication with GTIDs on MySQL 8 or MariaDB 10.6+.

Before you start

• Two Linux VPS servers running the same major version of MySQL or MariaDB (the replica may be newer, never older).
• A private network between them if possible; otherwise TLS and firewall rules limiting port 3306 to each other's IP.
• Example IPs: primary 10.0.0.1, replica 10.0.0.2.

Configure the primary

Add to the [mysqld] section (e.g. /etc/mysql/conf.d/replication.cnf):

[mysqld]
server_id = 1
log_bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
bind-address = 10.0.0.1

On MariaDB, drop the two GTID lines: MariaDB always has GTIDs. Restart the service, then create a replication user limited to the replica's IP:

CREATE USER 'repl'@'10.0.0.2' IDENTIFIED BY 'strong-password' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.2';

Remove REQUIRE SSL only if the link is a trusted private network.

Configure the replica

[mysqld]
server_id = 2
log_bin = mysql-bin
relay_log = relay-bin
gtid_mode = ON
enforce_gtid_consistency = ON
read_only = ON
super_read_only = ON

Each server needs a unique server_id. read_only stops accidental writes on the replica. (MariaDB has no super_read_only or GTID mode lines.) Restart the replica.

Seed the replica with data

On the primary, take a consistent dump that records the GTID position:

mysqldump --all-databases --single-transaction --triggers --routines --events --source-data=2 > seed.sql

On MariaDB use --master-data=2 --gtid. Copy it to the replica (scp seed.sql 10.0.0.2:) and import:

mysql < seed.sql

For large databases, Percona XtraBackup or Mariabackup is much faster than a logical dump.

Start replication

On MySQL 8.0.23+:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='10.0.0.1',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='strong-password',
  SOURCE_AUTO_POSITION=1,
  SOURCE_SSL=1;
START REPLICA;

On MariaDB:

CHANGE MASTER TO
  MASTER_HOST='10.0.0.1',
  MASTER_USER='repl',
  MASTER_PASSWORD='strong-password',
  MASTER_USE_GTID=slave_pos,
  MASTER_SSL=1;
START SLAVE;

Check replication health

SHOW REPLICA STATUS\G

(SHOW SLAVE STATUS\G on MariaDB.) Look for:

Test it: create a table on the primary and confirm it appears on the replica.

Troubleshooting

• IO thread: Connecting / error 2003: the replica cannot reach port 3306. Check bind-address, firewall and credentials.
• SQL thread stopped with a duplicate key error: someone wrote to the replica, or the seed was inconsistent. The clean fix is to reseed; skipping transactions risks silent data drift.
• Growing lag: the replica is under-powered or applies a huge transaction. Enable parallel replication (replica_parallel_workers) and check its disk speed.
• Binlogs fill the disk on the primary: set binlog_expire_logs_seconds (MySQL) or expire_logs_days (MariaDB), keeping enough history for replicas.

Replication FAQ

Is a replica a backup?

No. A mistaken DROP TABLE replicates instantly. Keep normal backups; the replica is a good place to take them.

Can I fail over to the replica?

Yes: stop replication, turn off read_only and point the app to it. Tools like Orchestrator or MaxScale automate this.

Async vs semi-sync replication?

Async is fastest but may lose the last transactions in a crash. Semi-sync waits for a replica to acknowledge each commit, trading a little latency for safety.

Next steps

• Back up MySQL with mysqldump
• Tune MySQL performance
• Connect servers privately with WireGuard