MySQL Replication — Semi-Sync Timeout Lost 50 Transactions

Every production database running at scale eventually hits the same wall: a single server can only handle so many reads before query times creep up, and a single point of failure is a liability no on-call engineer wants at 2 AM. MySQL replication is the foundational answer to both problems — it lets you distribute reads across multiple servers and gives you a warm standby that can take over within seconds if the primary crashes. It's not optional infrastructure. At any company running MySQL seriously, replication is day one.

The core problem replication solves is deceptively simple: how do you keep two or more MySQL instances in sync without locking everything or doubling write traffic? The answer lives in the binary log — a sequential, append-only journal of every state-changing event on the primary. Replicas connect, stream that log, and apply events in order. But the devil is in the details — GTID vs file-position tracking, parallel apply workers, semi-synchronous acknowledgment, replication filters, and lag under heavy write load are all areas where teams get burned in production.

By the end of this article you'll be able to configure a production-grade primary/replica pair from scratch, understand the exact bytes flowing between them, switch between GTID and position-based replication with confidence, monitor and diagnose replication lag, and avoid the three most expensive mistakes engineers make when they first set this up. This is not a tutorial for spinning up a throwaway demo — it's the setup you'd be comfortable putting in front of real traffic.

What Semi-Sync Replication Actually Guarantees (and Doesn't)

MySQL semi-synchronous replication ensures that for each transaction, the master waits for at least one replica to acknowledge receipt before returning a commit OK to the client. Unlike fully synchronous replication, the master does not wait for the replica to apply the transaction — only that it has been written to the replica's relay log. This reduces the window of data loss compared to async replication, but it does not eliminate it.

The key mechanic is a configurable timeout: if the replica does not acknowledge within rpl_semi_sync_master_timeout (default 10 seconds), the master falls back to asynchronous mode for that transaction. During that fallback window, any subsequent commit is at risk. In production, a network blip or overloaded replica can silently flip the master to async, and if the master crashes before the timeout expires, those unacknowledged transactions are lost — up to 50 in a typical high-throughput setup.

Use semi-sync when you need stronger durability than async but cannot afford the latency hit of fully synchronous replication (e.g., Paxos/MySQL Group Replication). It is a pragmatic middle ground for OLTP systems where a few lost transactions are tolerable but bulk losses are not. The real value is in reducing the replication lag window from seconds to milliseconds under normal conditions — but only if you monitor the fallback count and set timeouts aggressively.

How MySQL Replication Actually Works Under the Hood

Before touching a config file, you need a mental model of what's actually happening. MySQL replication is driven by three threads. On the primary, the binlog dump thread streams binary log events to any replica that connects. On the replica, the I/O thread maintains a persistent TCP connection to the primary, receives those events, and writes them into a local file called the relay log. Meanwhile, the SQL thread (or parallel apply workers in modern MySQL) reads the relay log and executes each event against the replica's local storage engine.

The binary log is not a simple text log — it's a structured binary format that records events at the statement level (STATEMENT mode), the row level (ROW mode), or a hybrid of both (MIXED mode). ROW-based replication is what you want in production. It records the actual before and after image of each changed row, which makes it deterministic — no ambiguity about what a non-deterministic function like UUID() or NOW() returned. STATEMENT mode can cause silent data divergence when queries contain functions whose output differs between servers.

GTID (Global Transaction Identifier) is a unique ID assigned to every committed transaction on the primary, formatted as source_uuid:transaction_sequence. GTIDs make failover and replica reconfiguration dramatically safer because a replica can tell any server 'here are the GTIDs I already have — send me everything else,' without needing to know a specific file name and byte offset. For any new setup in 2024, GTID mode is the right default.

-- ============================================================
-- 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

Step-by-Step: Configuring a GTID-Based Primary/Replica Pair

Let's build a working replication setup. We'll use two servers: primary at 10.0.1.10 and replica at 10.0.1.11, both running MySQL 8.0+. The configuration changes require a restart, so plan your maintenance window accordingly — or use MySQL's SET PERSIST for variables that support online changes.

The primary needs the binary log enabled with a unique server ID, and it needs a dedicated replication user with minimal privileges. The replication user should only have REPLICATION SLAVE privilege — nothing else. Least privilege matters here because this account's credentials live in plaintext in older versions, and even with mysql.slave_master_info table encryption in 8.0, you don't want a compromised replica credential giving an attacker DML access.

On the replica side, you set a distinct server ID, point it at the primary using CHANGE REPLICATION SOURCE TO (the modern syntax replacing CHANGE MASTER TO), and start the replication threads. With GTID mode, you don't need to know a binlog file or position — you just tell the replica 'start from the beginning of GTIDs I don't have.' That's the magic of GTID: the protocol figures out the gap automatically.

For an initial data load, use mysqldump with --single-transaction --master-data=2 --gtids, or for large datasets, Percona XtraBackup which takes a hot physical copy without locking tables. Never skip the initial consistent snapshot — if you do, the replica starts with wrong data and GTID tracking gives you a false sense of correctness.

# ============================================================
# 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

Bootstrapping Replication, Monitoring Lag, and Handling Failover

Configuration files are just the foundation. Actually starting replication requires three steps: create the replication user on the primary, take a consistent snapshot of primary data and load it into the replica, then issue CHANGE REPLICATION SOURCE TO on the replica and START REPLICA.

Lag monitoring is where teams get caught off guard. Seconds_Behind_Source in SHOW REPLICA STATUS is calculated as the difference between the current clock time and the timestamp embedded in the relay log event being applied. This sounds fine until you realize it resets to NULL when the SQL thread stalls, reports 0 when the I/O thread is lagging (the replica thinks it's caught up because it hasn't received the new events yet), and can spike from 0 to thousands of seconds instantly during a large transaction. Production monitoring should combine Seconds_Behind_Source with the GTID gap and heartbeat tables.

For failover, the clean approach with GTIDs is to run STOP REPLICA on the old replica, verify Executed_Gtid_Set matches the primary's gtid_executed, then promote with STOP REPLICA; RESET REPLICA ALL; SET GLOBAL read_only=OFF. Tools like Orchestrator or MySQL Router automate this entire flow and handle edge cases like errant transactions that exist on a replica but not the primary — a situation that can permanently break GTID-based replication if you try to re-add that server.

-- ============================================================
-- 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';

Replication Lag in Production: Root Causes and Monitoring Beyond Seconds_Behind_Source

Replication lag is the silent killer of read scalability. Your application reads from replicas thinking it's getting fresh data, but if the replica is behind, every query returns stale results. The problem is that nobody notices until a customer complains about missing an order that was created 30 seconds ago.

Monitoring lag properly requires a three-metric approach: (1) Seconds_Behind_Source for quick glance, (2) GTID gap: compute the difference between primary's gtid_executed and replica's retrieved_gtid_set, (3) Heartbeat table: insert a timestamp on primary every 5 seconds and read it on replica to calculate true lag independent of clock skew.

#!/bin/bash
# Simple lag check combining GTID and heartbeat
PRIMARY_GTID=$(mysql -h 10.0.1.10 -e "SELECT @@gtid_executed" -s 2>/dev/null)
REPLICA_GTID=$(mysql -h 10.0.1.11 -e "SELECT @@gtid_executed" -s 2>/dev/null)
MISSING=$(mysql -h 10.0.1.11 -e "SELECT GTID_SUBTRACT('$PRIMARY_GTID', '$REPLICA_GTID')" -s 2>/dev/null)
if [ "$MISSING" != "" ]; then
  echo "WARNING: Replica missing $MISSING"
else
  echo "GTID sets are identical."
fi

HEARTBEAT_LAG=$(mysql -h 10.0.1.11 -e "\
  SELECT TIMESTAMPDIFF(MICROSECOND, heartbeat_ts, NOW(6))/1000000.0 \
  FROM monitoring.replication_heartbeat WHERE server_id=1" -s 2>/dev/null)
echo "Heartbeat lag: $HEARTBEAT_LAG seconds"

Failover Strategies and Common Pitfalls

Promoting a replica to primary is the moment of truth. You need to guarantee that the promoted replica has all the data the old primary committed, and that there's no split-brain scenario where two servers accept writes.

With GTID, the procedure is: STOP REPLICA; RESET REPLICA ALL; SET GLOBAL read_only=OFF. That's it — the replica already has all transactions the old primary committed (accounting for potential loss with async replication). The risk comes from errant transactions: a write that accidentally happened on the replica (e.g., a stray admin query, a cron job, or a buggy migration). That errant GTID will exist on the replica's executed set but not on the primary's. When you promote the replica, that transaction is now part of the new primary's history, and if you ever try to re-add the old primary, the old primary will see a GTID it doesn't have and refuse to connect.

Tools like Orchestrator handle this by comparing gtid_purged and purging errant transactions with a controlled GTID skip. But the best defense is prevention: super_read_only=ON on all replicas, strict access controls, and never running any DML on replicas.

Another common pitfall: assuming semi-synchronous replication guarantees zero data loss. As we saw in the production incident, AFTER_SYNC mode (value at 1) allows the primary to commit before receiving the replica's ACK if the timeout expires. For zero loss, use AFTER_SYNC with a very high timeout or synchronous replication using a plugin that blocks the commit until the ACK arrives.

#!/bin/bash
# Script to safely promote replica and handle possible errant transactions
# Usage: ./promote_replica.sh <replica_ip>

set -e

REPLICA_IP=${1:?"Usage: $0 <replica_ip>"}

# Step 1: Stop replica and check errant GtIDS
MISSING=$(mysql -h $REPLICA_IP -e "SELECT GTID_SUBTRACT(@@gtid_executed, @@gtid_purged)" -s 2>/dev/null)
if [ -n "$MISSING" ]; then
  echo "WARNING: Errant transactions detected: $MISSING"
  echo "Manual intervention required to skip before promotion."
  exit 1
fi

# Step 2: Stop replication and promote
mysql -h $REPLICA_IP -e "STOP REPLICA; RESET REPLICA ALL; SET GLOBAL super_read_only=OFF; SET GLOBAL read_only=OFF;"
echo "Replica promoted to primary. Update application connection string to $REPLICA_IP"

Prerequisites: The Bare Minimum to Avoid a 3AM Pager

Skip this section if you enjoy debugging cryptic connection errors on a Friday night. You need two MySQL 8.0+ servers — one source, one replica — with network connectivity on port 3306. Both must have GTID enabled (gtid_mode=ON) and enforce_gtid_consistency=ON. If you're on MySQL 5.7, upgrade. Seriously. Semi-sync replication and crash-safe slaves are vastly more reliable there.

Your replica needs enough disk to hold the entire source dataset plus binary logs for catch-up after failures. Under-provision disk and you'll learn about relay log corruption the hard way. Use SSDs on both ends — replication lag skyrockets on spinning rust under write load.

Firewall rules: allow inbound TCP/3306 from the replica's IP. Test with telnet before you start configuring. I've seen engineers waste two hours debugging 'Access denied' when the real problem was iptables silently dropping packets. Don't be that person.

// io.thecodeforge — database tutorial

-- Verify GTID is on, both servers replicas
SHOW VARIABLES LIKE 'gtid_mode';
-- Expected: ON

SHOW VARIABLES LIKE 'enforce_gtid_consistency';
-- Expected: ON

-- Check binary logging is enabled on source
SHOW VARIABLES LIKE 'log_bin';
-- Expected: ON

-- Confirm replica can connect (run from replica shell)
-- mysql -h <source_ip> -u rep_user -p -e 'SELECT 1'

Understanding Replication in MySQL: What Actually Happens on the Wire

Here's the mental model: the source writes every committed transaction to its binary log (binlog). The replica connects, opens a TCP stream, and pulls those log events sequentially. Each event is a row change, statement, or GTID assignment. The replica writes them to its relay log, then replays them against its own data files. That's it. No magic. No dual-phase commits unless you've enabled semi-sync.

GTID changes the game. Without GTID, the replica tracks positions by filename and offset — brittle as hell after a crash or failover. With GTID, each transaction gets a unique identifier like '550e8400-e29b-41d4-a716-446655440000:1'. The replica knows exactly which transactions it has applied. Miss a failover? The replica will auto-skip duplicates and pick up where it left off. That's not convenience; that's survival in production.

Semi-sync adds a single ACK from the replica before the source commits to its client. It halves throughput but prevents data loss in a single-server crash. Use it on anything that bills customers or stores orders. Async is fine for analytics replicas where losing a few rows is acceptable — but you'd better have monitoring on lag.

// io.thecodeforge — database tutorial

-- Source side: check binlog file and position
SHOW MASTER STATUS\G

-- Replica side: show I/O and SQL thread status
SHOW REPLICA STATUS\G

Common Errors and Troubleshooting: What Kills Your Replication in the Dead of Night

Error 1032 (Can't find record) is the classic. You applied a DELETE or UPDATE on the source that doesn't match the replica's data — usually because someone ran a direct write on the replica. Fix: set sql_slave_skip_counter=1 and restart the SQL thread if you're not using GTID. With GTID, you inject a blank transaction to skip it, or you re-seed the replica from a fresh backup. Don't make this a habit; find the rogue writer.

Error 1062 (Duplicate entry) means the replica already has the row. Same root cause: writes on the replica. In production, set super_read_only=1 on all replicas. It prevents writes from application connections but still allows replication thread to apply changes. Non-negotiable.

Error 2003 (Can't connect to MySQL server on source) — network or firewall. Check the source's bind-address. In MySQL 8.0, the default is 127.0.0.1, so remote connections fail silently. Change it to 0.0.0.0 if you're not in a cluster. Yes, that exposes the port — use security groups, not the application's naivety.

Relay log corruption: the replica's relay log gets truncated during a crash. Solution: stop the replica, reset it with RESET REPLICA, and re-establish from the source's position. With GTID, you can just CHANGE MASTER TO MASTER_AUTO_POSITION=1 and skip the position hassle entirely.

// io.thecodeforge — database tutorial

-- Stop replica before any surgery
STOP REPLICA;

-- Skip one error (non-GTID only)
SET GLOBAL sql_slave_skip_counter = 1;
START REPLICA;

-- With GTID: inject a blank transaction to skip
SET GTID_NEXT='550e8400-e29b-41d4-a716-446655440000:351';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';
START REPLICA;

-- Reset relay log (GTID safe)
RESET REPLICA;
CHANGE MASTER TO
  MASTER_HOST='source.internal.local',
  MASTER_USER='rep_user',
  MASTER_PASSWORD='P@ssw0rd!',
  MASTER_AUTO_POSITION=1,
  GET_MASTER_PUBLIC_KEY=1;
START REPLICA;

-- Prevent future rogue writes
SET GLOBAL super_read_only = ON;

If Your Source Doesn’t Have Any Existing Data to Migrate: Skip the Dump, Go Straight to Replica

Most replication guides assume you have terabytes of production data to clone. That's not your problem. You're spinning up a fresh replica against a source that has zero application data or only insignificant test rows. Dumping and restoring a 10 MB schema is wasted time. You can go from CHANGE REPLICATION SOURCE TO to a fully synced replica in under two minutes. The key is to configure the replica with SOURCE_AUTO_POSITION = 1 and start replication immediately. GTIDs handle the rest. No dump, no restore, no --master-data. Just set the GTID_PURGED on the replica to match the source’s executed set, then start the slave thread. You verify by checking SHOW REPLICA STATUS for seconds_behind_source. If it's 0 and the IO and SQL threads are both Yes, you're done. This works because replication begins from the current position, not from a snapshot point. The replica catches any transactions committed after the change. For empty sources, there are no transactions to miss. Production trap: never skip this for populated databases—you'll corrupt your replica. Fresh source only.

// io.thecodeforge — database tutorial
// Bootstrap a replica from an empty source using GTIDs

-- On source: verify GTID mode is ON
SHOW VARIABLES LIKE 'gtid_mode';
-- Output: ON

-- On source: get executed GTID set
SHOW MASTER STATUS\G
-- Output:
-- File: mysql-bin.000042
-- Position: 194
-- Binlog_Do_DB:
-- Binlog_Ignore_DB:
-- Executed_Gtid_Set: 00000000-0000-0000-0000-000000000001:1-5

-- On replica: set GTID_PURGED to match source
-- Must have empty gtid_executed first
SET @@GLOBAL.gtid_purged = '00000000-0000-0000-0000-000000000001:1-5';

-- Configure replication channel
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '10.0.1.42',
  SOURCE_PORT = 3306,
  SOURCE_USER = 'repl_user',
  SOURCE_PASSWORD = 'strong_password_here',
  SOURCE_AUTO_POSITION = 1;

-- Start replication
START REPLICA;

-- Validate
SHOW REPLICA STATUS\G

Authentication Plugin Compatibility Issues: Why Your Replica Won't Connect and How to Fix It for Good

You configured CHANGE REPLICATION SOURCE TO with the right IP, port, user, and password. Still getting error 1045: Access denied. There's nothing wrong with your credentials. The problem is the authentication plugin. MySQL 8.0 defaults to caching_sha2_password for new users. Older replicas or connectors expect mysql_native_password. The source sends an RSA public key challenge during the handshake. If the replica's client library can't handle that plugin, the connection dies before any password exchange. Two fixes. One: recreate the replication user with the old plugin. That's CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'secret'. This is the quick hack and works with any replica version 5.6+. Two: configure the source to accept both plugins and force native for the replica user. That's ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'secret'. Don't change the default plugin server-wide unless you audit every connection. The production fix: upgrade your replicas to MySQL 8.0.19+ which handles the handshake natively. But when a 3AM failover wakes you up and the replica can't authenticate, the native_password override is your lifeline. Just remember to rotate that user's password post-recovery.

// io.thecodeforge — database tutorial
// Fix authentication plugin mismatch for replication user

-- On source: check current authentication plugin for user
SELECT user, host, plugin 
FROM mysql.user 
WHERE user = 'repl_user';
-- Output:
-- user      | host | plugin
-- repl_user | %    | caching_sha2_password

-- Error log on replica shows:
-- [ERROR] Replica I/O for channel '': error connecting to master 'repl_user'@'10.0.1.42' - retry-time: 60 retries: 1
-- Authentication plugin 'caching_sha2_password' cannot be loaded: plugin not enabled

-- Fix: change plugin to native_password (no data loss, immediate effect)
ALTER USER 'repl_user'@'%' 
IDENTIFIED WITH mysql_native_password BY 'new_strong_password';

-- Flush privileges to apply immediately
FLUSH PRIVILEGES;

-- On replica: update password if changed
STOP REPLICA;
CHANGE REPLICATION SOURCE TO
  SOURCE_PASSWORD = 'new_strong_password';
START REPLICA;

-- Verify connection works
SHOW REPLICA STATUS\G

MySQL Group Replication vs InnoDB Cluster vs Async Replication

MySQL offers several replication technologies, each suited for different use cases. Understanding the differences between Group Replication, InnoDB Cluster, and traditional Async Replication is crucial for designing a robust high-availability architecture.

Async Replication is the classic master-slave setup where the source commits transactions without waiting for replicas. It's simple and low-latency but risks data loss on source failure. Semi-sync replication reduces this risk by requiring at least one replica to acknowledge receipt before the source commits.

Group Replication implements a multi-master update-anywhere replication model using a Paxos-based consensus protocol. It provides strong consistency, automatic failover, and built-in conflict detection. However, it requires a minimum of three nodes and has higher latency due to the consensus round.

InnoDB Cluster is a complete high-availability solution built on Group Replication, MySQL Shell, and MySQL Router. It automates configuration, failover, and client routing. InnoDB Cluster is ideal for environments requiring automatic failover and easy management, while raw Group Replication offers more flexibility for custom setups.

For production, InnoDB Cluster is recommended for most new deployments due to its ease of use and reliability. However, for existing async replication setups, migrating to Group Replication requires careful planning and testing.

-- Enable Group Replication on each node
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SET GLOBAL group_replication_group_name='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee';
SET GLOBAL group_replication_local_address='node1:33061';
SET GLOBAL group_replication_group_seeds='node1:33061,node2:33061,node3:33061';
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

MySQL Replication Lag: Monitoring and Mitigation

Replication lag is the delay between a transaction being committed on the source and applied on the replica. Monitoring only Seconds_Behind_Source is insufficient because it can be misleading—it measures only SQL thread lag, not I/O thread lag, and can reset to 0 if the replica catches up momentarily.

Better monitoring metrics: - Seconds_Behind_Source: Still useful as a baseline but unreliable during bursts. - Master_Log_File and Read_Master_Log_Pos vs Relay_Master_Log_File and Exec_Master_Log_Pos: Compare to detect I/O vs SQL lag. - gtid_executed and gtid_purged: For GTID-based setups, compare executed GTID sets. - threads_running and threads_connected: High concurrency on replica can indicate lag. - Performance Schema tables: replication_applier_status_by_worker for multi-threaded replicas.

Mitigation strategies: 1. Optimize replica hardware: Faster disks, more memory, and CPU can reduce apply time. 2. Use multi-threaded replication: Set slave_parallel_workers > 0 and slave_parallel_type='LOGICAL_CLOCK' for parallel apply. 3. Reduce source write load: Offload read queries to replicas, batch writes, or use sharding. 4. Monitor and tune: Use pt-heartbeat from Percona Toolkit for precise lag measurement. 5. Avoid long-running transactions: They block replication on the replica.

Example: Setting up multi-threaded replication: ``sql STOP SLAVE; SET GLOBAL slave_parallel_workers=4; SET GLOBAL slave_parallel_type='LOGICAL_CLOCK'; START SLAVE; ``

For critical systems, implement lag alerts based on GTID differences rather than Seconds_Behind_Source.

-- Check GTID-based lag
SELECT GTID_SUBSET(@@GLOBAL.gtid_executed, @@GLOBAL.gtid_purged) AS lagging;
-- Or use pt-heartbeat
pt-heartbeat --update --daemonize --interval 1 --database percona --host source
pt-heartbeat --check --database percona --host replica

MySQL GTID-Based Replication: Setup and Failover

GTID (Global Transaction Identifier) simplifies replication management by uniquely identifying each transaction. With GTIDs, you don't need to track binary log file positions, making failover and setup easier.

Setup steps: 1. Enable GTID on both source and replica: gtid_mode=ON, enforce_gtid_consistency=ON. 2. On the source, create a replication user. 3. On the replica, configure CHANGE MASTER TO with MASTER_AUTO_POSITION=1. 4. Start replica.

Example configuration: ``ini [mysqld] gtid_mode=ON enforce_gtid_consistency=ON log_bin=mysql-bin binlog_format=ROW server-id=1 ``

Failover with GTIDs: When the source fails, promote a replica by: 1. Stop the replica: STOP SLAVE; 2. Reset slave: RESET SLAVE ALL; 3. Enable writes: SET GLOBAL read_only=OFF; 4. Point other replicas to the new source using MASTER_AUTO_POSITION=1.

GTIDs ensure that no transactions are lost or duplicated during failover. However, you must ensure all replicas have applied all transactions from the old source before promoting.

Common pitfalls: - Skipping transactions with SET GTID_NEXT can cause GTID gaps. - RESET MASTER on a source with replicas will break replication. - Mixing GTID and non-GTID replicas is not supported.

For automatic failover, consider using MySQL InnoDB Cluster or orchestration tools like Orchestrator.

-- On source
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- On replica
CHANGE MASTER TO
  MASTER_HOST='source_host',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_AUTO_POSITION=1;
START SLAVE;