How to Set Up MySQL Replication (Primary-Replica)

What Is Replication?

MySQL/MariaDB replication copies data from a primary server to one or more replicas in real-time. Use it for read scaling, backups, and high availability.

Configure the Primary

Edit /etc/mysql/mariadb.conf.d/50-server.cnf:

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mariadb-bin
binlog_format = ROW
binlog_do_db = myapp  # Optional: replicate specific database

sudo systemctl restart mariadb

Create Replication User

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

Get Primary Position

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
-- Note the File and Position values
-- Example: mariadb-bin.000001, Position: 785

Export Data (if existing)

mysqldump -u root --all-databases --master-data > dbdump.sql
UNLOCK TABLES;

Configure the Replica

Edit config on the replica server:

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

sudo systemctl restart mariadb

Import Data and Start Replication

# Import the dump
mysql -u root < dbdump.sql

# Configure replication source
CHANGE MASTER TO
    MASTER_HOST='198.48.63.241',
    MASTER_USER='replicator',
    MASTER_PASSWORD='SecureReplicaPass123',
    MASTER_LOG_FILE='mariadb-bin.000001',
    MASTER_LOG_POS=785;

START SLAVE;

Verify Replication

SHOW SLAVE STATUS\G
-- Look for:
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0

Monitoring

# Check replication lag
SHOW SLAVE STATUS\G | grep Seconds_Behind_Master