Baza znanja

Replication copies data from a primary database server to one or more replicas in near real-time, useful for read scaling, backups without impacting the primary, and disaster recovery. This guide covers a basic primary-replica (master-slave) setup.

Prerequisites

• Two VPS instances with MySQL or MariaDB installed (primary and replica)
• Network connectivity between them (private networking recommended if available)
• Root or sudo access on both

Step 1 — Configure the Primary Server

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

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = myapp

sudo systemctl restart mysql

Step 2 — Create a Replication User on the Primary

sudo mysql

CREATE USER 'replica_user'@'%' IDENTIFIED BY 'CHANGE_ME_STRONG_PASSWORD';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
FLUSH PRIVILEGES;

Step 3 — Get the Primary's Binary Log Position

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Note the File and Position values shown — you'll need them shortly.

Step 4 — Export the Primary's Existing Data

In a separate terminal, without releasing the lock:

mysqldump -u root -p --all-databases --master-data > primary-dump.sql

Then release the lock:

UNLOCK TABLES;

Step 5 — Transfer the Dump to the Replica

scp primary-dump.sql user@REPLICA_IP:/tmp/

Step 6 — Configure the Replica Server

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

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

sudo systemctl restart mysql

Step 7 — Import the Dump on the Replica

mysql -u root -p < /tmp/primary-dump.sql

Step 8 — Point the Replica to the Primary

sudo mysql

CHANGE MASTER TO
  MASTER_HOST='PRIMARY_IP',
  MASTER_USER='replica_user',
  MASTER_PASSWORD='CHANGE_ME_STRONG_PASSWORD',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=12345;

Use the exact File and Position values from Step 3.

Step 9 — Start Replication

START SLAVE;

Step 10 — Verify Replication Is Working

SHOW SLAVE STATUS\G

Confirm both Slave_IO_Running and Slave_SQL_Running show Yes, and that Seconds_Behind_Master is low.

Firewall Consideration

Restrict MySQL's port 3306 to only the specific replica's IP:

sudo ufw allow from REPLICA_IP to any port 3306

Common Errors

"Slave_IO_Running: No" — check network connectivity and credentials between servers, and review:

SHOW SLAVE STATUS\G

for the specific Last_IO_Error message.

Replication lag growing continuously — the replica's hardware may be underpowered relative to write volume on the primary; investigate resource usage on the replica.

Best Practices

• Use private networking between primary and replica when available, not the public internet
• Never treat a replica alone as a backup — replication propagates accidental deletions too; keep separate backups
• Monitor Seconds_Behind_Master regularly

FAQ

Can a replica be written to directly?
Not safely in a standard setup — treat replicas as read-only to avoid conflicting with data coming from the primary.

Related Articles

• How to Install and Secure MySQL 8 on Ubuntu & Debian
• How to Back Up and Restore MySQL/MariaDB Databases
• How to Configure UFW Firewall on a Linux VPS