MySQL or MariaDB replication basics

Topic: Databases core

Set up MySQL replication with binary logging on the primary and CHANGE REPLICATION SOURCE on the replica. Use for read scaling or HA. Use this when you need a replica for reads or failover.

Intent: How-to

Quick answer

  • Primary: server-id and log_bin; create replication user. Replica: server-id; CHANGE REPLICATION SOURCE TO; START REPLICA.
  • Get log file and position from primary. Replica must have consistent data (snapshot) at that position.
  • Monitor SHOW REPLICA STATUS; fix errors from error log.

Prerequisites

Steps

  1. Primary

    Set server-id and log_bin. Create replication user. Note binary log file and position.

  2. Replica

    Restore snapshot. Set server-id. CHANGE REPLICATION SOURCE TO; START REPLICA.

  3. Monitor

    SHOW REPLICA STATUS; check IO and SQL running. Fix errors from log.

Summary

Enable binary log on primary; set replica source and position; start replica and monitor.

Prerequisites

Steps

Step 1: Primary

Configure server-id and log_bin; create replication user; note position.

Step 2: Replica

Restore data; set server-id and source; start replica.

Step 3: Monitor

Check REPLICA STATUS; fix errors.

Verification

  • Slave_IO_Running and Slave_SQL_Running Yes.

Troubleshooting

Replication stops — Check error log; re-sync from snapshot if needed.

Next steps

Continue to