MySQL Primary-Replica Replication on Arch Linux

Architecture

Primary (source): 192.168.1.10
Replica: 192.168.1.11

Step 1 – Configure the Primary

Edit my.cnf on the primary:

[mysqld]
server-id   = 1
log_bin     = /var/log/mysql/mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800

Restart MySQL and create a replication user:

CREATE USER 'replicator'@'192.168.1.11'
    IDENTIFIED WITH mysql_native_password BY 'ReplPass!';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'192.168.1.11';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;   -- note File and Position

Step 2 – Take a consistent backup

mysqldump --all-databases --source-data=2 --single-transaction \
    -u root -p > /tmp/full_backup.sql

Transfer to the replica:

scp /tmp/full_backup.sql [email protected]:/tmp/

Step 3 – Configure the Replica

Edit my.cnf on the replica:

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

Restart MySQL, restore the dump, and start replication:

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

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='192.168.1.10',
    SOURCE_USER='replicator',
    SOURCE_PASSWORD='ReplPass!',
    SOURCE_AUTO_POSITION=1;
START REPLICA;
SHOW REPLICA STATUS\G

Step 4 – Monitor

SHOW REPLICA STATUS\G
-- Check: Replica_IO_Running: Yes, Replica_SQL_Running: Yes
-- Seconds_Behind_Source: 0 (or close to 0)