MySQL Replication Lag Spikes: A 10-Minute Diagnostic Framework

Core Principle: Seconds_Behind_Source Is Not the Only Evidence

Seconds_Behind_Source is an estimate based on thread execution time and source event timestamps. It can be NULL or misleading when replication is stopped, just started, clocks are out of sync, parallel workers are unbalanced, or the source has no new transactions. Reliable diagnosis requires the full replication status, GTID sets, worker errors, source write rate, and replica disk/lock metrics.

Ten-Minute Triage Path

The following steps outline a ten-minute triage for sudden replication lag spikes. Each step has a time budget, goal, and required evidence.

0–1 min: Confirm version, connection, and status. Must keep: full replication status output.

1–3 min: Distinguish IO thread vs SQL thread. Must keep: thread states, error fields, GTID sets.

3–5 min: Check for stuck worker or transaction. Must keep: worker errors, current transaction, locks.

5–7 min: Check for source write surge. Must keep: binlog, transaction, connection, commit status.

7–9 min: Check if replica is slowed by read traffic or disk. Must keep: processlist, InnoDB metrics, iostat.

9–10 min: Choose low-risk action. Must keep: wait, throttle, isolate, repair, or upgrade.

This path suits initial triage for sudden lag spikes. Data-consistency risk, replication break, failover, and transaction skipping are higher-severity events that need more than a ten-minute snapshot.

0–1 Minute: Verify You Are Querying the Correct Instance

1. Confirm Client and Server Versions

The command-line client version may differ from the server version; SHOW statements run against the server version. Record both:

#!/usr/bin/env bash
set -euo pipefail
MYSQL_BIN="<mysql-client-path>"
MYSQL_HOST="<replica-address>"
MYSQL_PORT="<replica-port>"
MYSQL_USER="<read-only-troubleshooting-user>"
"$MYSQL_BIN" --version
"$MYSQL_BIN" \
  --host="$MYSQL_HOST" \
  --port="$MYSQL_PORT" \
  --user="$MYSQL_USER" \
  --password \
  --connect-timeout=5 \
  --batch --raw --skip-column-names \
  -e 'SELECT VERSION(), @@hostname, @@port, @@server_uuid, @@read_only, @@super_read_only;'

Use a secure method for the password; never write it in command lines, scripts, or chat logs. If the connection times out, check network, port, and account privileges first—do not assume a connection failure equals SQL-thread lag.

2. Save Runtime Environment with One SQL

On the replica, run:

SELECT
  VERSION() AS mysql_version,
  @@hostname AS hostname,
  @@server_uuid AS server_uuid,
  @@global.gtid_mode AS gtid_mode,
  @@global.enforce_gtid_consistency AS enforce_gtid_consistency,
  @@global.binlog_format AS binlog_format,
  @@global.log_replica_updates AS log_replica_updates,
  @@global.replica_parallel_workers AS replica_parallel_workers,
  @@global.replica_parallel_type AS replica_parallel_type,
  @@global.read_only AS read_only,
  @@global.super_read_only AS super_read_only;

Variable names change across MySQL 8.0 minor versions (e.g., log_slave_updates vs log_replica_updates). If a variable is missing, use SHOW VARIABLES LIKE '%replica%'; and SHOW VARIABLES LIKE '%slave%'; to confirm the current version's naming before changing configuration.

1–3 Minutes: Classify the Lag

1. Read Full Replication Status

Run SHOW REPLICA STATUS/G (or SHOW SLAVE STATUS/G on pre-8.0.23). Do not copy only Seconds_Behind_Source; the combination of IO, SQL, error, and position fields determines the direction. Key fields to record:

Replica_IO_Running, Replica_SQL_Running, Replica_SQL_Running_State

Seconds_Behind_Source

Source_Host, Source_Port, Channel_Name

Last_IO_Error, Last_IO_Errno, Last_IO_Error_Timestamp

Last_SQL_Error, Last_SQL_Errno, Last_SQL_Error_Timestamp

Retrieved_Gtid_Set, Executed_Gtid_Set

Relay_Source_Log_File, Exec_Source_Log_Pos

Auto_Position, Using_Gtid, Source_Log_File, Read_Source_Log_Pos

2. Machine-Readable Extraction

mysql \
  --host="$MYSQL_HOST" \
  --port="$MYSQL_PORT" \
  --user="$MYSQL_USER" \
  --password \
  --connect-timeout=5 \
  --batch --raw \
  -e 'SHOW REPLICA STATUS\G' \
  | awk -F': '
    /^(Channel_Name|Replica_IO_Running|Replica_SQL_Running|Replica_SQL_Running_State|Seconds_Behind_Source|Last_IO_Error|Last_SQL_Error|Retrieved_Gtid_Set|Executed_Gtid_Set|Relay_Source_Log_File|Exec_Source_Log_Pos|Auto_Position|Using_Gtid):/ {
      print $1 "=" $2
    }'

If the version lacks SHOW REPLICA STATUS, substitute SHOW SLAVE STATUS. The awk pattern works for typical key: value output; multi-line errors may be truncated, so always save the full raw output.

3. Four Immediate Branches

Based on the observed symptoms, narrow the investigation to one of four primary directions:

Replica_IO_Running=No, Last_IO_Error present: Network, auth, source connection, missing log files.

IO=Yes, SQL=No, Last_SQL_Error present: SQL apply error, conflict, schema, or privilege.

IO=Yes, SQL=Yes, Seconds_Behind_Source rising: Replica throughput insufficient, long transaction, lock, read traffic, or source write surge.

IO=Yes, SQL=Yes, Seconds=NULL: Replication just started, source has no new transactions, time or state anomaly.

This only narrows scope; root cause must be proven by error logs, worker status, GTID, transactions, and resource metrics.

Investigating the IO Thread: Are Events Being Pulled to the Replica?

1. On IO Thread Failure, Read Errors First—Do Not Reset Replication

If Replica_IO_Running is No, examine error fields and the MySQL error log:

SELECT
  CHANNEL_NAME,
  SERVICE_STATE,
  THREAD_ID,
  LAST_ERROR_NUMBER,
  LAST_ERROR_MESSAGE,
  LAST_ERROR_TIMESTAMP,
  RECEIVED_TRANSACTION_SET
FROM performance_schema.replication_connection_status\G

performance_schema columns vary by version; if a column is missing, run DESCRIBE performance_schema.replication_connection_status; first.

2. Check Replica Error Log for the Same Time Window

Find the log path and verbosity:

SHOW VARIABLES LIKE 'log_error';
SHOW VARIABLES LIKE 'log_error_verbosity';

Then extract the relevant window (adjust time format to match your log):

ERROR_LOG="<MySQL-error-log-path>"
START_TIME="<start-time>"
END_TIME="<end-time>"
awk -v start="$START_TIME" -v end="$END_TIME" '
  $0 >= start && $0 <= end {print}
' "$ERROR_LOG" \
  | grep -Ei 'replica|slave|source|connect|access denied|fatal|error|relay|binlog'

awk time comparison assumes ISO-like timestamps at line start; adapt for other formats (journalctl, etc.). No grep matches does not mean no problem—verify time format and log rotation.

3. Verify Source Is Still Generating Binary Logs

On the source:

SHOW MASTER STATUS\G
SHOW BINARY LOGS;

If the source position advances while the replica's Read_Source_Log_Pos stalls, the IO thread, network, or relay log write may be blocked. If the replica has received events but Executed_Gtid_Set does not advance, the issue is in the SQL apply path.

Investigating the SQL Thread: Events Arrived but Not Applied

1. Check Coordinator and Workers

With multi-threaded replication, a single worker error or long transaction can stall the whole pipeline while Seconds_Behind_Source shows only an aggregate. Use performance_schema:

SELECT
  CHANNEL_NAME,
  THREAD_ID,
  SERVICE_STATE,
  LAST_ERROR_NUMBER,
  LAST_ERROR_MESSAGE,
  LAST_ERROR_TIMESTAMP
FROM performance_schema.replication_applier_status_by_coordinator\G

SELECT
  CHANNEL_NAME,
  WORKER_ID,
  THREAD_ID,
  SERVICE_STATE,
  LAST_ERROR_NUMBER,
  LAST_ERROR_MESSAGE,
  LAST_ERROR_TIMESTAMP,
  APPLYING_TRANSACTION,
  LAST_APPLIED_TRANSACTION,
  LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP
FROM performance_schema.replication_applier_status_by_worker
ORDER BY WORKER_ID\G

A non-zero LAST_ERROR_NUMBER or non-empty LAST_ERROR_MESSAGE on any worker is often closer to the root cause than Seconds_Behind_Source. If multiple workers are running but the last applied transaction timestamp is stale, investigate locks, disk, and individual large transactions.

2. Check for Lock Waits on the Replica

MySQL 8.0's performance_schema.data_lock_waits and data_locks show InnoDB lock waits:

SELECT
  rw.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx_id,
  rw.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx_id,
  r.OBJECT_SCHEMA AS waiting_schema,
  r.OBJECT_NAME AS waiting_table,
  r.INDEX_NAME AS waiting_index,
  r.LOCK_TYPE AS waiting_lock_type,
  r.LOCK_MODE AS waiting_lock_mode,
  r.LOCK_DATA AS waiting_lock_data
FROM performance_schema.data_lock_waits AS rw
JOIN performance_schema.data_locks AS r
  ON r.ENGINE_LOCK_ID = rw.REQUESTING_ENGINE_LOCK_ID
WHERE r.OBJECT_SCHEMA IS NOT NULL;

To get the blocking thread and SQL, join performance_schema.threads, events_statements_current, or information_schema.innodb_trx. Insufficient privileges may return empty results. Do not disable performance_schema collection just to query locks.

3. View Long Transactions and Current Statements on the Replica

SELECT
  trx_id,
  trx_started,
  trx_state,
  trx_mysql_thread_id,
  trx_rows_locked,
  trx_rows_modified,
  trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 20;

SELECT
  THREAD_ID,
  EVENT_NAME,
  SQL_TEXT,
  TIMER_WAIT / 1000000000000 AS seconds_running
FROM performance_schema.events_statements_current
WHERE SQL_TEXT IS NOT NULL
ORDER BY TIMER_WAIT DESC
LIMIT 20;

Read transactions on the replica may block purge or consume resources but do not necessarily block replication writes; conflict depends on tables, indexes, and isolation level. A large trx_rows_modified or early trx_started is a clue, not an automatic kill justification.

Determine If a Single Large Transaction or Sustained Write Rate Is the Cause

1. Observe Source Write Intensity

On the source, sample cumulative counters twice, separated by a few minutes, to compute rates:

SHOW GLOBAL STATUS
WHERE Variable_name IN (
  'Com_insert',
  'Com_update',
  'Com_delete',
  'Com_commit',
  'Threads_connected',
  'Threads_running',
  'Binlog_bytes_written',
  'Binlog_commits',
  'Binlog_group_commits',
  'Binlog_cache_use',
  'Binlog_cache_disk_use'
);

These are cumulative counters; a single reading cannot show current rate. Rapid growth of Binlog_bytes_written or commit counters in the window indicates a source write surge; rising Threads_running warrants further lock, slow-query, and connection-pool investigation—do not equate it directly with replication lag.

2. Identify Long Transactions on the Source

SELECT
  trx_id,
  trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_seconds,
  trx_state,
  trx_mysql_thread_id,
  trx_rows_locked,
  trx_rows_modified,
  trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 20;

A long transaction can generate large binlog events, cause lock waits, and delay replica apply. Do not kill based solely on age_seconds; first confirm connection ownership, business impact, whether it is an online DDL or batch job, and the rollback time a kill would incur.

3. Check Source Blocking and Slow Statements

SELECT
  p.ID,
  p.USER,
  p.HOST,
  p.DB,
  p.COMMAND,
  p.TIME,
  p.STATE,
  p.INFO
FROM information_schema.PROCESSLIST AS p
WHERE p.COMMAND <> 'Sleep'
ORDER BY p.TIME DESC;

PROCESSLIST TIME is the current state duration, not total statement time; INFO may be truncated. For execution plans, use EXPLAIN or EXPLAIN ANALYZE on the same SQL in a low-risk environment—never run analysis that executes the statement on a production replica.

Check Replica's Own Disk, Read Traffic, and Resource Contention

1. Read Traffic May Contend with SQL Apply Threads

Read replicas typically serve query traffic. Re-run the PROCESSLIST query on the replica in the same time window. Long Sending data does not always mean network send; it can indicate scan or data retrieval. High query volume, temporary tables, sorts, and buffer pool misses can starve the replication SQL thread of CPU, locks, or disk I/O.

2. Inspect Replica InnoDB and Temporary Table Pressure

SHOW GLOBAL STATUS
WHERE Variable_name IN (
  'Threads_running',
  'Created_tmp_tables',
  'Created_tmp_disk_tables',
  'Innodb_buffer_pool_reads',
  'Innodb_buffer_pool_read_requests',
  'Innodb_row_lock_current_waits',
  'Innodb_row_lock_time',
  'Innodb_os_log_pending_fsyncs',
  'Innodb_data_pending_reads',
  'Innodb_data_pending_writes',
  'Innodb_buffer_pool_pages_dirty'
);

Compare Created_tmp_disk_tables, pending reads/writes, and row-lock counters across time windows. High cumulative values do not guarantee a current anomaly. If read queries cause disk latency or rising lock waits, prioritize traffic throttling or moving queries to another replica; this is a business change requiring confirmation of connection-pool and routing rollback procedures.

3. Confirm Device Latency at OS Level

iostat -xz 1 10
df -hT
df -ih

iostat fields depend on the local sysstat version. Focus on devices holding the MySQL data directory, relay logs, and tmpdir. df -hT and df -ih check byte space and inodes; full disks or inode exhaustion can break relay log writes.

Verify Replication Configuration Has Not Been Accidentally Changed

1. Compare Key Variables on Source and Replica

SHOW VARIABLES
WHERE Variable_name IN (
  'server_id',
  'server_uuid',
  'gtid_mode',
  'enforce_gtid_consistency',
  'binlog_format',
  'sync_binlog',
  'innodb_flush_log_at_trx_commit',
  'replica_parallel_workers',
  'replica_parallel_type',
  'replica_preserve_commit_order',
  'replica_pending_jobs_size_max',
  'replica_transaction_retries'
);

Parallel worker count, commit order preservation, and retry policy affect apply throughput and consistency semantics. Do not blindly max out replica_parallel_workers to chase lag; first examine CPU, disk, locks, and transaction conflicts, then observe replay order and errors after any change.

2. Check Replication Filters and Table Structures

SHOW VARIABLES
WHERE Variable_name LIKE '%replicate%'
   OR Variable_name LIKE '%binlog_do%'
   OR Variable_name LIKE '%binlog_ignore%';

SHOW CREATE TABLE `<db>`.`<table>`\G

Filter differences may cause the replica to intentionally skip certain schemas/tables; schema differences can make the SQL thread error. SHOW CREATE TABLE is read-only but <db> and <table> must be explicit objects—never interpolate unvalidated user input into automated scripts.

3. Check for Accidental Writes on the Replica

SELECT
  @@global.read_only AS read_only,
  @@global.super_read_only AS super_read_only,
  @@session.sql_log_bin AS sql_log_bin;

SELECT
  USER,
  HOST,
  COMMAND,
  COUNT(*) AS connections
FROM information_schema.PROCESSLIST
GROUP BY USER, HOST, COMMAND
ORDER BY connections DESC;

Accidental writes on the replica can cause replication conflicts or consume resources. read_only and super_read_only behavior depends on account privileges; do not conclude from variable names alone. Any change to read-only status is high-risk and must be preceded by failover, backup, and rollback confirmation.

Use GTID to Determine "How Many Received" vs "How Many Executed"

1. Read GTID Sets

SELECT
  @@global.gtid_executed AS server_gtid_executed,
  @@global.gtid_purged AS server_gtid_purged;

SHOW REPLICA STATUS\G

Retrieved_Gtid_Set represents transactions the replica has received; Executed_Gtid_Set represents those applied. A visible gap means events are in the replica but not yet applied. GTID sets are interval strings—do not use string length as transaction count. Use MySQL GTID functions or dedicated tools to parse, accounting for multiple UUIDs and intervals.

2. Limited Verification with GTID Wait

To verify a specific GTID has executed, use WAIT_FOR_EXECUTED_GTID_SET with a timeout:

SELECT WAIT_FOR_EXECUTED_GTID_SET(
  '<source-UUID>:<transaction-number>',
  5
) AS wait_seconds;

Returns 0 if satisfied within timeout, 1 on timeout, NULL on parameter or execution error. Do not inject unknown GTID strings into production queries; confirm from SHOW REPLICA STATUS output first. The wait only verifies progress—it does not fix replication.

Ten-Minute Read-Only Evidence Collection Script

The script below assumes MySQL 8.0.23+, runs on the replica, and collects replication status, worker state, locks, transactions, and system I/O. It does not execute STOP/START REPLICA, KILL, or configuration changes.

#!/usr/bin/env bash
set -euo pipefail

MYSQL_HOST="<replica-address>"
MYSQL_PORT="<replica-port>"
MYSQL_USER="<read-only-troubleshooting-user>"
OUT_DIR="/tmp/mysql-replica-evidence-$(date +%Y%m%d%H%M%S)"
mkdir -p "$OUT_DIR"
umask 077

run_mysql() {
  mysql --host="$MYSQL_HOST" --port="$MYSQL_PORT" \
    --user="$MYSQL_USER" --password --connect-timeout=5 \
    --batch --raw "$@"
}

run_mysql -e 'SELECT NOW(), VERSION(), @@hostname, @@server_uuid;' \
  > "$OUT_DIR/identity.txt"
run_mysql -e 'SHOW REPLICA STATUS\G' \
  > "$OUT_DIR/replica-status.txt"
run_mysql -e '
  SELECT CHANNEL_NAME, WORKER_ID, SERVICE_STATE,
         LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE,
         LAST_ERROR_TIMESTAMP, APPLYING_TRANSACTION,
         LAST_APPLIED_TRANSACTION
  FROM performance_schema.replication_applier_status_by_worker
  ORDER BY WORKER_ID;
' > "$OUT_DIR/worker-status.txt"
run_mysql -e '
  SELECT trx_id, trx_started, trx_state, trx_mysql_thread_id,
         trx_rows_locked, trx_rows_modified, trx_query
  FROM information_schema.innodb_trx
  ORDER BY trx_started
  LIMIT 50;
' > "$OUT_DIR/innodb-trx.txt"
run_mysql -e 'SHOW FULL PROCESSLIST;' \
  > "$OUT_DIR/processlist.txt"
run_mysql -e "
  SHOW GLOBAL STATUS
  WHERE Variable_name IN (
    'Threads_running', 'Created_tmp_disk_tables',
    'Innodb_row_lock_current_waits', 'Innodb_os_log_pending_fsyncs',
    'Innodb_data_pending_reads', 'Innodb_data_pending_writes',
    'Innodb_buffer_pool_pages_dirty'
  );
" > "$OUT_DIR/status.txt"

iostat -xz 1 10 > "$OUT_DIR/iostat.txt"
df -hT > "$OUT_DIR/df-h.txt"
df -ih > "$OUT_DIR/df-i.txt"

tar -C "$(dirname "$OUT_DIR")" -czf "$OUT_DIR.tar.gz" "$(basename "$OUT_DIR")"
printf 'evidence archive: %s
' "$OUT_DIR.tar.gz"

Passwords are still read interactively by the mysql client; not suitable for unattended environments. If the account lacks performance_schema privileges, the script may fail mid-way; split read-only queries and record failures. The archive contains processlist and SQL text—treat as sensitive operational data with restricted permissions and retention.

Ten-Minute Decision Branches

Based on the observed symptom, follow the corresponding first look and next step:

Replica_IO_Running=No: First look at Last_IO_Error, source connectivity, log retention, relay log disk. Next step: check auth, network, log files, space; do not RESET REPLICA or repoint source before evidence is clear.

Replica_SQL_Running=No: First look at Last_SQL_Error, Last_SQL_Errno, worker, current GTID. Next step: check schema, unique keys, privileges, storage engine, locks; do not skip errors blindly.

Both threads running but lag rising: First look at source binlog/commit rate vs replica apply progress. Next step: determine if source write surge or replica read traffic, disk, locks, or parallel conflicts.

Seconds_Behind_Source=NULL: First look at both thread states, error fields, GTID sets, source new transactions. Next step: if threads healthy and no new transactions, verify with explicit GTID or position.

When threads error, save error number, error text, worker state, and GTID. Skipping transactions creates divergence between replica and source; only after data verification, owner approval, and a compensation plan should you enter a formal skip or rebuild process. A single SHOW STATUS cumulative value cannot compare rates—resample every 30–60 seconds.

Mitigation Actions: Reduce Contention First, Then Consider Replication Thread Operations

1. Remove Read Traffic from the Replica First

If evidence shows read queries causing disk, CPU, lock, or temporary table pressure, mark the instance unreadable at the connection pool, proxy, or service discovery layer. This is easier to roll back than stopping replication but still affects connection distribution and overall capacity.

Pre-checks:

Do other replicas have sufficient capacity?

Will read traffic fall back to the source?

Will connection pools reuse existing connections?

Does the proxy health check truly recognize the removal?

Can you retain timestamps and configs before/after the routing change?

Post-execution verification: error rates, connection counts, Threads_running, iostat, and replication apply progress—not just proxy weight drops.

2. Handle Long Queries Cautiously

If a specific query is confirmed to block replication or exhaust replica resources, cancel via application layer, connection pool timeout, or authorized KILL QUERY. KILL carries risk: the query may hold a transaction or trigger rollback.

SELECT
  ID,
  USER,
  HOST,
  DB,
  COMMAND,
  TIME,
  STATE,
  INFO
FROM information_schema.PROCESSLIST
WHERE ID = <thread-ID>\G

KILL QUERY <thread-ID>;

<thread-ID> must come from the current instance's current-window PROCESSLIST and be confirmed by authorized personnel. KILL QUERY terminates only the current statement; the connection may persist. KILL CONNECTION is higher risk. After execution, verify transaction rollback, lock release, and worker continuation.

3. When Is START REPLICA Appropriate?

Only consider START REPLICA if the stop was planned, network is restored, error cause is eliminated, and state was saved. Do not treat START REPLICA as a universal fix for all lag.

START REPLICA;
SHOW REPLICA STATUS\G

START REPLICA may start both IO and SQL threads depending on version and parameters. Afterward, observe multiple sampling windows to confirm both threads stay running, Last_Error stays empty, GTID executed set advances, lag does not widen, and replica resources are not spiked by replay.

4. Stopping Replication Is High-Risk

STOP REPLICA halts event receipt or application; which thread stops depends on whether IO_THREAD or SQL_THREAD is specified. It can widen lag, fill relay logs, and affect failover availability. Use only to protect the replica, handle a known error, or perform controlled maintenance.

SHOW REPLICA STATUS\G
STOP REPLICA SQL_THREAD;
SHOW REPLICA STATUS\G

Before execution: save full state, confirm relay log space, ensure the instance won't be mistaken for a healthy replica, and notify dependents. Record stop time and reason. Recovery uses START REPLICA SQL_THREAD, validated by GTID, workers, and business read traffic.

Do Not Skip Transactions Without Sufficient Evidence

Skipping transactions alters Executed_Gtid_Set relative to the source's data semantics. In GTID mode, sql_replica_skip_counter is rarely a suitable general tool; different replication modes and versions require different handling—empty transaction injection, data repair and re-execution, or replica rebuild from backup.

Any skip action requires at minimum:

Record offending GTID, transaction content, affected tables, and error number.

Determine if the transaction committed successfully on the source.

Assess whether the replica already has partial effects.

Back up affected tables or obtain a verifiable snapshot.

Complete source/replica data verification or compensation.

Obtain business and database owner approval.

Document a recoverable path, not just replication restart.

If the transaction is DDL, privilege change, financial write, or critical config change, it should not be skipped during an on-call call. Safer options: isolate the replica, rebuild it, or repair data first.

Backup and Rollback for Replication Configuration Changes

1. Save Replication State Before Changes

#!/usr/bin/env bash
set -euo pipefail

MYSQL_HOST="<replica-address>"
MYSQL_PORT="<replica-port>"
MYSQL_USER="<read-only-troubleshooting-user>"
OUT_FILE="/tmp/replica-before-change-$(date +%Y%m%d%H%M%S).txt"

mysql \
  --host="$MYSQL_HOST" \
  --port="$MYSQL_PORT" \
  --user="$MYSQL_USER" \
  --password \
  --connect-timeout=5 \
  --batch --raw \
  -e '
    SELECT NOW(), VERSION(), @@hostname, @@server_uuid;
    SHOW REPLICA STATUS\G
    SHOW VARIABLES LIKE "replica%";
    SHOW VARIABLES LIKE "gtid%";
  ' > "$OUT_FILE"

chmod 600 "$OUT_FILE"
printf 'saved: %s
' "$OUT_FILE"

This records pre-change state, not a data backup. For table repair, replica rebuild, or failover, follow the database backup strategy to obtain a restorable backup or snapshot. The replication state file cannot replace a backup.

2. Inspect Current Source Configuration Before CHANGE

SHOW REPLICA STATUS\G
SHOW VARIABLES LIKE 'source%';
SHOW VARIABLES LIKE 'master%';
SHOW VARIABLES LIKE 'report%';

CHANGE REPLICATION SOURCE TO alters connection, log position, GTID auto-positioning, or TLS parameters—high-risk. Do not copy-paste entire commands from the internet, especially do not specify file/position in a GTID auto-positioning environment. Execute only when you know the current mode, backup, and rollback method.

Minimum Recovery Verification Checklist

After replication threads recover, verify in order—not just a single Seconds_Behind_Source line:

IO, SQL threads, and workers run continuously; error fields show no new entries.

Retrieved_Gtid_Set keeps receiving; Executed_Gtid_Set catches up or narrows the gap.

Source write rate and replica apply rate balance; disk, lock waits, and Threads_running return to acceptable ranges.

Before restoring read traffic, validate key queries, data freshness, connection pools, and proxy health.

Continuous verification SQL:

SELECT NOW() AS checked_at;
SHOW REPLICA STATUS\G

SELECT
  CHANNEL_NAME,
  WORKER_ID,
  SERVICE_STATE,
  LAST_ERROR_NUMBER,
  LAST_ERROR_MESSAGE,
  LAST_APPLIED_TRANSACTION,
  LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP
FROM performance_schema.replication_applier_status_by_worker
ORDER BY WORKER_ID;

Save timestamp and result of each run to judge progress, stall, or oscillation. If error fields are empty but GTID does not advance, continue checking: does the source have new transactions? Is the replica waiting for a large transaction commit? Are read traffic or device bottlenecks present?

Continuous Sampling to Confirm Trend

To determine whether GTID, thread states, and cumulative counters are advancing or stalled, repeat SHOW REPLICA STATUS and key status queries every 30 seconds on the same instance for at least ten minutes. Record sampling times; do not compare rates using a single SHOW GLOBAL STATUS cumulative snapshot. One read-only troubleshooting connection is usually enough; avoid launching many simultaneous collection connections across multiple instances. Output includes error fields and host info—handle as sensitive data.

Signals That Warrant Escalation, Not More Waiting

Escalate to the database owner or failover owner immediately if any of the following occur:

IO or SQL thread remains stopped and the error is not a known brief maintenance window.

Relay log or data disk free space approaches protection thresholds.

Last_SQL_Error points to schema, unique key, storage engine, or data consistency issues.

GTID gap keeps widening; replica apply rate is clearly below source write rate.

A single transaction runs for an extended period, or replica read traffic cannot be shed and continues to contend for resources.

The current replica is the only failover candidate and lag already exceeds business freshness SLAs.

"Wait five more minutes" is reasonable only when threads are healthy, GTID gap is shrinking, and disk/lock pressure is controlled. Healthy threads but a persistently widening gap means waiting only increases recovery cost.

Four High-Risk Misconceptions

Restarting mysqld directly loses the scene; it cannot fix schema mismatches, GTID gaps, or missing source logs. Before restart: preserve evidence, take backups, assess changes.

Relying solely on Seconds_Behind_Source cannot distinguish IO interruption, SQL error, source idle, or worker imbalance. Always save the full replication status.

Blindly increasing parallel workers may amplify lock, disk, or commit-order contention. Verify parallel parameters and resources first, then make gradual changes.

Skipping error transactions outright conflates replication recovery with data correctness. Critical tables, DDL, financial writes, and privilege changes should be repaired or rebuilt first—never skipped ad-hoc on a conference call.

Conclusion: Ten Minutes Turns Guesswork into Branches

When master-slave lag suddenly spikes, the most effective action is not hunting for a magic command but sequentially narrowing the scope:

Replication status → IO thread or SQL thread → GTID received vs executed → worker, lock, or large transaction → source write or replica resource → low-risk mitigation or database-level failure process

As long as you preserve complete state, error fields, GTID, transactions, and resource evidence, you avoid mistaking a single lag number for a single root cause. Ten minutes may not fix every replication fault, but it should clarify whether the next step is safe waiting, read-traffic isolation, handling a confirmed anomaly, or stopping further attempts and escalating.