Setting Up MySQL Replication for Redundancy

MySQL replication copies data from a primary server to one or more replicas in near real-time — the foundation for read scaling, failover, and zero-downtime backups on a live database.

[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW

-- On the primary: create a replication user
CREATE USER 'repl'@'%' IDENTIFIED BY 'strongpassword';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- Note the binlog position after a consistent dump
SHOW MASTER STATUS;

-- On the replica: point it at the primary
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='primary.example.com',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='strongpassword',
  SOURCE_LOG_FILE='mysql-bin.000001',
  SOURCE_LOG_POS=157;

START REPLICA;
SHOW REPLICA STATUS\G

Check Seconds_Behind_Source in the replica status output regularly — replication lag under load is the most common operational issue, and it directly determines how stale data on the replica can get.

Setting Up MySQL Replication for Redundancy

Recommended

Categories