MySQL/MariaDB Replication

Set up MySQL or MariaDB master-replica replication for read scaling and high availability

MySQL/MariaDB replication automatically copies data from a primary (master) server to one or more replica (slave) servers in near real-time. Use cases include read scaling, backups without locking the primary, and failover.

Architecture

Primary (writes) ──binlog──► Replica 1 (reads)
                           ► Replica 2 (reads)

All writes go to the primary. Reads can be distributed across replicas. Replication is asynchronous by default, replicas may lag slightly.

Step 1: Configure the primary

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# MariaDB:
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = myapp_db   # Replicate only this DB (omit to replicate all)
bind-address = 0.0.0.0    # Allow replica connections

Restart:

sudo systemctl restart mysql

Step 2: Create a replication user on the primary

sudo mysql -u root -p

CREATE USER 'replicator'@'REPLICA_IP' IDENTIFIED BY 'StrongReplicaPassword!';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'REPLICA_IP';
FLUSH PRIVILEGES;

Step 3: Take a consistent snapshot

Lock the primary, record the binary log position, dump the data:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Note the File and Position values, you'll need them on the replica.

In another terminal, dump the database:

mysqldump -u root -p --all-databases --master-data > /tmp/primary_dump.sql

Unlock:

UNLOCK TABLES;

Step 4: Configure the replica

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

[mysqld]
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
read_only = 1   # Prevent accidental writes to replica

Restart:

sudo systemctl restart mysql

Step 5: Import the snapshot on the replica

mysql -u root -p < /tmp/primary_dump.sql

Step 6: Connect the replica to the primary

sudo mysql -u root -p

CHANGE MASTER TO
  MASTER_HOST='PRIMARY_IP',
  MASTER_USER='replicator',
  MASTER_PASSWORD='StrongReplicaPassword!',
  MASTER_LOG_FILE='mysql-bin.000001',   -- from SHOW MASTER STATUS
  MASTER_LOG_POS=154;                   -- from SHOW MASTER STATUS

START SLAVE;

For MariaDB:

CHANGE MASTER TO
  MASTER_HOST='PRIMARY_IP',
  MASTER_USER='replicator',
  MASTER_PASSWORD='StrongReplicaPassword!',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;

START SLAVE;

Step 7: Verify replication

SHOW SLAVE STATUS\G

Look for:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

If both are Yes and lag is near 0, replication is working.

Test replication

On the primary:

CREATE DATABASE replication_test;
CREATE TABLE replication_test.test (id INT PRIMARY KEY, val VARCHAR(50));
INSERT INTO replication_test.test VALUES (1, 'hello from primary');

On the replica (should appear within seconds):

SELECT * FROM replication_test.test;

GTID-based replication (recommended for MariaDB/MySQL 5.6+)

GTID (Global Transaction ID) replication is more robust and easier to manage than position-based:

Primary /etc/mysql/mysql.conf.d/mysqld.cnf:

gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = /var/log/mysql/mysql-bin.log
server-id = 1

Replica:

gtid_mode = ON
enforce_gtid_consistency = ON
server-id = 2
read_only = 1

Connect the replica without specifying log position:

CHANGE MASTER TO
  MASTER_HOST='PRIMARY_IP',
  MASTER_USER='replicator',
  MASTER_PASSWORD='StrongReplicaPassword!',
  MASTER_AUTO_POSITION=1;

START SLAVE;

Monitor replication lag

-- On replica
SHOW SLAVE STATUS\G
-- Check: Seconds_Behind_Master

-- Quick check
mysql -u root -p -e "SHOW SLAVE STATUS\G" | grep -E "Running|Behind"

Set up a cron alert if lag exceeds threshold:

#!/bin/bash
LAG=$(mysql -u root -p'pass' -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep Seconds_Behind_Master | awk '{print $2}')
if [ "$LAG" -gt 30 ]; then
  echo "Replication lag: ${LAG}s" | mail -s "ALERT: MySQL Replication Lag" admin@example.com
fi

Failover (promote replica to primary)

If the primary fails:

-- On the replica
STOP SLAVE;
RESET SLAVE ALL;

-- Remove read_only
SET GLOBAL read_only = 0;

Update your app's DB connection string to point to the former replica. Update DNS or a load balancer if you use one.

Common issues

Issue	Cause	Fix
`Slave_IO_Running: No`	Can't connect to primary	Check firewall, credentials, `bind-address`
`Slave_SQL_Running: No`	SQL error on replica	`SHOW SLAVE STATUS\G` for error, skip with `SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1`
Large lag	Heavy writes on primary	Add more replicas, use `sync_binlog=0` on primary
Duplicate key on replica	Manual write on replica	Set `read_only=1`, investigate the write source

On this page