MariaDB Replication Setup Guide

Why Replication?

• Read scaling — distribute SELECT queries across replicas
• High availability — promote replica if primary fails
• Backups — run backups on replica without impacting production
• Geographic distribution — replicas closer to users

Architecture

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

Primary Server Configuration

# /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
expire_logs_days = 14
max_binlog_size = 100M

sudo systemctl restart mariadb

Create replication user:

CREATE USER 'replication'@'%' IDENTIFIED BY 'strong-password';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;

-- Get current position
SHOW MASTER STATUS;
-- Note the File and Position values

Replica Server Configuration

# /etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld]
server-id = 2
relay_log = /var/log/mysql/relay-bin
read_only = 1

sudo systemctl restart mariadb

Configure replication:

CHANGE MASTER TO
    MASTER_HOST='primary-ip',
    MASTER_USER='replication',
    MASTER_PASSWORD='strong-password',
    MASTER_LOG_FILE='mariadb-bin.000001',
    MASTER_LOG_POS=12345;

START SLAVE;
SHOW SLAVE STATUS\G

Check for:

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Seconds_Behind_Master: 0

Application Configuration

Route writes to primary, reads to replica:

// Simple read/write splitting
$primary = new PDO('mysql:host=primary-ip;dbname=myapp', 'user', 'pass');
$replica = new PDO('mysql:host=replica-ip;dbname=myapp', 'user', 'pass');

function query($sql, $params = []) {
    global $primary, $replica;
    $isWrite = preg_match('/^\s*(INSERT|UPDATE|DELETE|CREATE|ALTER|DROP)/i', $sql);
    $db = $isWrite ? $primary : $replica;
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    return $stmt;
}

Monitoring Replication

-- On replica
SHOW SLAVE STATUS\G

-- Key metrics
-- Seconds_Behind_Master: replication lag
-- Slave_IO_Running: binary log transfer
-- Slave_SQL_Running: SQL replay
-- Last_Error: any errors

Warning Replication lag means the replica is behind the primary. For critical reads (like right after a write), always query the primary. Only use replicas for reads that can tolerate a few seconds of delay.