iwantcoding.com
🔥 Daily 👥 Rooms 🏆 Top Log in Sign up

Replication

By · Reviewed & last updated · Part of MySQL Tutorial

Replication ships writes from a primary to one or more replicas, asynchronously by default. Reasons people use it: read scale-out, hot standby for failover, cross-region disaster recovery, and offloading reports. The two consistency models to know are statement-based and row-based (default since 5.7); row-based is safer and what you almost always want.

Set up async primary→replica with GTIDs

EXAMPLE
-- ===== ON THE PRIMARY =====
-- 1) Configure my.cnf
-- [mysqld]
-- server-id            = 1
-- log-bin              = mysql-bin
-- binlog_format        = ROW
-- gtid-mode            = ON
-- enforce-gtid-consistency = ON

-- 2) Create a dedicated replication user
CREATE USER 'repl'@'%' IDENTIFIED WITH caching_sha2_password BY 'strong-secret-here';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

-- 3) Take a consistent snapshot to seed the replica
-- mysqldump --all-databases --single-transaction --triggers --routines \
--          --source-data=2 --set-gtid-purged=ON > snapshot.sql

-- ===== ON THE REPLICA =====
-- 4) my.cnf
-- [mysqld]
-- server-id            = 2
-- relay-log            = mysql-relay
-- read-only            = ON
-- super-read-only      = ON

-- 5) Load the snapshot, then point at the primary and start
SOURCE snapshot.sql;
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST       = 'primary.db.internal',
  SOURCE_PORT       = 3306,
  SOURCE_USER       = 'repl',
  SOURCE_PASSWORD   = 'strong-secret-here',
  SOURCE_AUTO_POSITION = 1,
  SOURCE_SSL        = 1;

START REPLICA;

-- 6) Verify health
SHOW REPLICA STATUS\G
-- Look for:
--   Replica_IO_Running:  Yes
--   Replica_SQL_Running: Yes
--   Seconds_Behind_Source: 0
--   Last_Errno: 0

-- 7) Promote a replica during failover (after STOP REPLICA)
STOP REPLICA;
RESET REPLICA ALL;
SET GLOBAL super_read_only = OFF;
SET GLOBAL read_only       = OFF;

-- 8) Monitoring queries to alert on
SELECT SERVICE_STATE, LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE
FROM performance_schema.replication_connection_status;
SELECT SERVICE_STATE, LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE
FROM performance_schema.replication_applier_status_by_worker;

Why it matters

Always run replicas with super_read_only=ON. A stray write on a replica creates a divergence that GTID-based replication cannot heal automatically and you will spend a Saturday fixing it. Lock the door before someone wanders in.

Tip: Tweak the snippet with Try it Yourself », then sit the quiz at the bottom of the page.

Example

Example
-- Primary
CHANGE MASTER TO MASTER_USER='replicator', MASTER_PASSWORD='…';
SHOW MASTER STATUS;
-- Replica
CHANGE MASTER TO MASTER_HOST='primary';
START SLAVE;
Try it Yourself »

Discussion

Loading…