MySQL Master-Slave Replication: Setup and Monitoring

Why MySQL Replication

MySQL replication maintains an exact copy of your database on a second server in real time. Use cases: read replicas, failover recovery, zero-impact backups.

Configure Master Server

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

mysql -u root -p -e "
CREATE USER 'replicator'@'%' IDENTIFIED BY 'ReplPass123!';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;"
mysql -u root -p -e "SHOW MASTER STATUS\G"

Configure Slave Server

[mysqld]
server-id = 2
read_only = 1

mysql -u root -p -e "
CHANGE MASTER TO
    MASTER_HOST='192.168.1.10',
    MASTER_USER='replicator',
    MASTER_PASSWORD='ReplPass123!',
    MASTER_AUTO_POSITION=1;
START SLAVE;"

Monitor Replication

mysql -u root -p -e "SHOW SLAVE STATUS\G"
-- Slave_IO_Running: Yes (must be Yes)
-- Slave_SQL_Running: Yes (must be Yes)
-- Seconds_Behind_Master: 0