Database Replication with MySQL and MariaDB

What is Replication?

Database replication copies data from a primary (master) server to one or more replica (slave) servers. This provides read scaling, high availability, and backup capabilities.

Primary Server Configuration

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

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

sudo systemctl restart mariadb

Create replication user:

CREATE USER 'replicator'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;

Replica Server Configuration

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

CHANGE MASTER TO
  MASTER_HOST='primary-ip',
  MASTER_USER='replicator',
  MASTER_PASSWORD='repl_password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;

START SLAVE;
SHOW SLAVE STATUS\G

Monitoring

# Check replication status
SHOW SLAVE STATUS\G

# Key values to monitor:
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
# Seconds_Behind_Master: 0