MySQL Replication Setup: Master-Replica Configuration, Internals & Production Gotchas

-- ============================================================
-- Run these on the PRIMARY to inspect binlog state
-- ============================================================

-- Show all binary log files and their sizes on disk
SHOW BINARY LOGS;
-- Output tells you how many logs exist and how much disk they consume

-- Inspect the live stream of binary log events (human-readable)
-- Replace 'mysql-bin.000003' with your current binlog file
SHOW BINLOG EVENTS IN 'mysql-bin.000003' LIMIT 20;

-- Confirm GTID mode is enabled and see executed GTID sets
SHOW VARIABLES LIKE 'gtid_mode';          -- Should return ON
SHOW VARIABLES LIKE 'enforce_gtid_consistency'; -- Should return ON
SHOW MASTER STATUS\G
-- gtid_executed column shows every GTID this server has committed

-- ============================================================
-- Run these on the REPLICA to inspect the three replication threads
-- ============================================================

-- The single most important replication health command
SHOW REPLICA STATUS\G
-- Key fields to check:
--   Replica_IO_Running: Yes        -- I/O thread is connected to primary
--   Replica_SQL_Running: Yes       -- SQL thread is applying relay log
--   Seconds_Behind_Source: 0       -- 0 means replica is caught up
--   Last_IO_Error: (empty)         -- Any value here means connection trouble
--   Last_SQL_Error: (empty)        -- Any value here means apply-side failure
--   Retrieved_Gtid_Set            -- GTIDs received from primary
--   Executed_Gtid_Set             -- GTIDs applied to this replica's data

-- Check how many parallel SQL apply workers are configured
SHOW VARIABLES LIKE 'replica_parallel_workers'; -- 0 = single-threaded
SHOW VARIABLES LIKE 'replica_parallel_type';    -- LOGICAL_CLOCK is the modern choice

# ============================================================
# PRIMARY SERVER: /etc/mysql/mysql.conf.d/mysqld.cnf
# Apply to server at 10.0.1.10, then restart MySQL
# ============================================================
[mysqld]
# Every server in a replication topology MUST have a unique ID
server-id                    = 1

# Enable the binary log — replication cannot work without this
log-bin                      = mysql-bin
binlog_format                = ROW          # Deterministic row-level events
binlog_row_image             = FULL         # Log complete before+after row images

# Retain binlogs for 7 days — long enough for replicas to catch up after downtime
binlog_expire_logs_seconds   = 604800

# GTID settings — both must be ON together
gtid_mode                    = ON
enforce_gtid_consistency     = ON

# Crash safety: flush binlog to disk on every commit (safer, slightly slower)
sync_binlog                  = 1
innodb_flush_log_at_trx_commit = 1

# ============================================================
# REPLICA SERVER: /etc/mysql/mysql.conf.d/mysqld.cnf
# Apply to server at 10.0.1.11, then restart MySQL
# ============================================================
[mysqld]
server-id                    = 2            # Must differ from primary's ID

# Relay log stores events received from primary before they're applied
relay-log                    = relay-bin
relay_log_recovery           = ON           # Auto-recover relay log on crash

# Write replica's own changes to its binlog — essential if this replica
# might ever become a primary (chained or cascaded replication)
log-bin                      = mysql-bin
log_replica_updates          = ON

# GTID — must match primary
gtid_mode                    = ON
enforce_gtid_consistency     = ON

# Parallel apply workers dramatically reduce lag under heavy write workloads
# LOGICAL_CLOCK preserves transaction ordering from the primary's commit order
replica_parallel_workers     = 4
replica_parallel_type        = LOGICAL_CLOCK

# Make replica read-only — prevents accidental writes that cause divergence
read_only                    = ON
super_read_only              = ON           # Also blocks SUPER privilege users

-- ============================================================
-- STEP 1: Run on PRIMARY — create the replication user
-- ============================================================
CREATE USER 'replication_user'@'10.0.1.11'
    IDENTIFIED WITH caching_sha2_password  -- Modern auth plugin for MySQL 8.0
    BY 'StrongR3plPass!2024';

-- Only grant the minimum required privilege
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'10.0.1.11';
FLUSH PRIVILEGES;

-- Verify the user exists with the right privileges
SHOW GRANTS FOR 'replication_user'@'10.0.1.11';

-- ============================================================
-- STEP 2: Take a consistent snapshot on PRIMARY
-- Run this in a shell, NOT in mysql client
-- --single-transaction avoids table locks for InnoDB
-- --source-data=2 adds CHANGE REPLICATION SOURCE comment to dump
-- --triggers --routines --events captures full schema
-- ============================================================
-- $ mysqldump \
--     --single-transaction \
--     --source-data=2 \
--     --set-gtid-purged=ON \
--     --triggers \
--     --routines \
--     --events \
--     --all-databases \
--     -u root -p > /backup/primary_snapshot_$(date +%Y%m%d_%H%M%S).sql

-- ============================================================
-- STEP 3: Load snapshot on REPLICA (shell command)
-- ============================================================
-- $ mysql -u root -p < /backup/primary_snapshot_2024XXXX_XXXXXX.sql

-- ============================================================
-- STEP 4: Run on REPLICA — connect to primary and start replication
-- GTID mode means we don't need SOURCE_LOG_FILE or SOURCE_LOG_POS
-- ============================================================
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST      = '10.0.1.10',
    SOURCE_PORT      = 3306,
    SOURCE_USER      = 'replication_user',
    SOURCE_PASSWORD  = 'StrongR3plPass!2024',
    SOURCE_AUTO_POSITION = 1,   -- This is the GTID magic: find my own gap automatically
    SOURCE_SSL       = 1,       -- Always use SSL for replication in production
    SOURCE_SSL_CA    = '/etc/mysql/ssl/ca.pem',
    SOURCE_SSL_CERT  = '/etc/mysql/ssl/replica-cert.pem',
    SOURCE_SSL_KEY   = '/etc/mysql/ssl/replica-key.pem',
    SOURCE_CONNECT_RETRY = 10,  -- Retry connection every 10 seconds on disconnect
    SOURCE_RETRY_COUNT  = 86400; -- Retry for up to 24 hours before giving up

-- Start both replication threads
START REPLICA;

-- Confirm everything is healthy
SHOW REPLICA STATUS\G

-- ============================================================
-- ONGOING MONITORING: Detect real lag using a heartbeat approach
-- Run on PRIMARY every 5 seconds (via cron or application job)
-- ============================================================
CREATE TABLE IF NOT EXISTS monitoring.replication_heartbeat (
    server_id        INT          NOT NULL,
    heartbeat_ts     DATETIME(6)  NOT NULL,
    PRIMARY KEY (server_id)
) ENGINE=InnoDB;

-- Insert/update the heartbeat timestamp
INSERT INTO monitoring.replication_heartbeat (server_id, heartbeat_ts)
    VALUES (@@server_id, NOW(6))
    ON DUPLICATE KEY UPDATE heartbeat_ts = NOW(6);

-- Run this on REPLICA to calculate true lag in seconds
SELECT
    TIMESTAMPDIFF(
        MICROSECOND,
        heartbeat_ts,
        NOW(6)
    ) / 1000000.0 AS replication_lag_seconds,
    heartbeat_ts   AS last_primary_write
FROM monitoring.replication_heartbeat
WHERE server_id = 1;  -- Primary's server_id

-- ============================================================
-- FAILOVER: Promote replica to primary (after primary failure)
-- ============================================================
-- Verify replica has all transactions before promoting
SHOW REPLICA STATUS\G  -- Confirm Seconds_Behind_Source: 0

-- Stop replica threads cleanly
STOP REPLICA;

-- Disconnect from old primary and clear replication config
RESET REPLICA ALL;

-- Lift read-only restrictions
SET GLOBAL super_read_only = OFF;
SET GLOBAL read_only       = OFF;

-- Verify this server is now writable and has all GTIDs
SHOW MASTER STATUS\G
SHOW VARIABLES LIKE 'read_only';