MySQL Replication Lag Spikes to 10 Min: Topology Optimization Rescue Guide

1. Preserve Evidence Before Any Fix

When replication lag hits 10 minutes, do not immediately restart replicas, skip transactions, or raise alert thresholds. These actions destroy forensic evidence and may cause data inconsistency. First, capture source binlog coordinates, IO/SQL thread states, worker execution status, slow queries, disk latency, and network health. Answer three questions: Is data still replicating? Which segment is falling behind? Is lag converging or diverging?

1.1 Confirm Instance Version and Replication Syntax

SELECT VERSION() AS mysql_version,
       @@version_comment AS version_comment,
       @@server_uuid AS server_uuid,
       @@server_id AS server_id;

Version determines whether to use SHOW REPLICA STATUS (MySQL 8.0) or the legacy SHOW SLAVE STATUS (MySQL 5.7). Do not assume both commands exist; field names differ across minor versions and cloud distributions.

1.2 Capture Replica Status Snapshot

SHOW REPLICA STATUS\G

Save the full output. Key fields: Replica_IO_Running, Replica_SQL_Running, Seconds_Behind_Source, Source_Log_File, Read_Source_Log_Pos, Relay_Source_Log_File, Exec_Source_Log_Pos, Relay_Log_Space, Last_IO_Error, Last_SQL_Error. If threads are not Yes, replication is broken, not just slow.

1.3 MySQL 5.7 Compatibility Command

SHOW SLAVE STATUS\G

Use only on 5.7 or compatible environments. Field meanings map to 8.0 terms but verify actual column names. Do not mix configuration parameters across versions (e.g., 5.7's slave_parallel_workers vs 8.0's replica_parallel_workers).

1.4 Use Performance Schema for Structured Thread State

SELECT CHANNEL_NAME, SERVICE_STATE,
       LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE
FROM performance_schema.replication_connection_status;

SELECT CHANNEL_NAME, SERVICE_STATE,
       LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE
FROM performance_schema.replication_applier_status;

Multi-source replication yields multiple CHANNEL_NAME values; never assume a single default channel. Performance Schema is better for automated checks but requires the schema to be enabled and the monitoring account to have read privileges.

1.5 Check if Parallel Workers Are Stalled on a Single Transaction

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

Most workers idle while a few remain in APPLYING_TRANSACTION indicates large transactions, hot-row locks, DDL, or commit-order waits. Configured worker count does not guarantee actual parallelism; transaction dependencies still limit replay.

1.6 Record Source Binary Log Baseline Configuration

SHOW VARIABLES WHERE Variable_name IN (
  'log_bin','binlog_format','gtid_mode',
  'enforce_gtid_consistency','binlog_transaction_dependency_tracking','sync_binlog'
);

binlog_format=ROW

is the production baseline. Record original values before any change; do not modify sync_binlog or consistency parameters solely because of lag, as this alters durability guarantees.

1.7 Use GTID to Determine Real Catch-Up Scope

SELECT @@GLOBAL.gtid_executed AS replica_gtid_executed,
       @@GLOBAL.gtid_purged  AS replica_gtid_purged;

Collect on both source and replica simultaneously. Seconds_Behind_Source is unreliable when SQL thread stops, during parallel replay, with clock skew, or with long transactions. GTID sets show exactly which transactions have been executed, though transaction count does not equal byte volume or wall-clock time.

1.8 Calculate Missing GTIDs on Source

SELECT GTID_SUBTRACT(
  @@GLOBAL.gtid_executed,
  '<replica_gtid_executed_from_snapshot>'
) AS transactions_not_on_replica;

Replace placeholder with the replica's full GTID set. Empty result means replica is caught up; non-empty only proves backlog exists, not how long it will take. For multi-source, analyze per channel/source.

1.9 Check Source Binlog Growth and Current Write Position

SHOW BINARY LOGS;
SHOW MASTER STATUS;

Observe recent file growth trends against business deployments, batch jobs, and DDL timelines. File size alone does not identify the root-cause transaction; correlate with slow logs, application job records, and worker states.

1.10 Monitor Relay Log Accumulation on Replica

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Relay_log_space','Bytes_received','Bytes_sent',
  'Threads_running','Threads_connected'
);

Continuously growing Relay_log_space with normal connection threads but lagging applier points to replay bottleneck. No growth with stopped IO thread suggests network, authentication, source binlog retention, or connection errors. Sample repeatedly; a single point has no trend value.

1.11 Examine Error Log for Replication, InnoDB, and Disk Evidence

sudo journalctl -u mysqld --since '<start_time>' --until '<end_time>' --no-pager | \
  rg -i 'replica|slave|source|master|relay|innodb|error|disk|space'

For systemd-managed Linux; if log_error points to a file, read that path. Look for No space left on device, connection errors, relay log read errors, deadlocks, or crash recovery messages. Unrelated warnings are not root-cause evidence.

1.12 Check Data, Relay Log, and Temp Directory Space

df -hT <mysql_data_dir> <relay_log_dir> <tmp_dir>
df -i  <mysql_data_dir> <relay_log_dir> <tmp_dir>

Check both capacity and inodes. Never manually delete relay logs; MySQL manages their lifecycle. If space is short, follow change-management to expand, clean confirmed-orphan files, or release space from backup/archive processes.

1.13 Continuously Observe Storage Latency and Queue Depth

iostat -xmd 1 10

Requires sysstat. Focus on the block device hosting MySQL: await, r_await, w_await, aqu-sz, %util trends. Correlate with lag window and healthy replicas. No universal threshold exists for all cloud disks or RAID; a single spike does not prove I/O is the root cause.

1.14 Observe CPU, Memory Swapping, and Run Queue

vmstat 1 10
mpstat -P ALL 1 10

Requires sysstat. Persistently high run queue, rising iowait, sustained si/so (swap in/out) can explain applier slowdown, but must be combined with mysqld threads, container limits, and disk metrics. Backups, checksums, ETL, and reporting workloads also contend on the replica.

1.15 Check Network Path from Source to Replica

ping -c 20 <source_host>
ss -tnpi | rg ':3306\b'

ping

only verifies ICMP reachability, not MySQL TCP quality. ss TCP retransmission data depends on kernel and permissions. Conclusions must combine network monitoring, packet loss/retransmission, IO thread errors, and cross-datacenter link data.

2. Classify "Slow" into Four Categories by Path

Replication includes at least two stages: source-to-relay-log fetch and relay-log-to-InnoDB replay. Transactions may also stall in fsync or commit-order waits. Cascaded replication passes upstream lag downstream. Correct classification prevents blind parameter tuning.

2.1 Structurally Distinguish Connection Thread vs Applier Thread Failures

SELECT CHANNEL_NAME, SERVICE_STATE, LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE
FROM performance_schema.replication_connection_status
UNION ALL
SELECT CHANNEL_NAME, SERVICE_STATE, LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE
FROM performance_schema.replication_applier_status;

Connection thread failures: prioritize TLS, user privileges, network, source reachability, missing binlogs. Applier failures: prioritize SQL errors, data drift, disk issues, DDL compatibility. Never authorize transaction skip without error codes and GTIDs.

2.2 Check Long Transactions and Lock Contention on Replica

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

Local reporting or admin queries on the replica may hold locks, block purge, or contend with applier DDL. trx_query may contain sensitive data; store output per data classification. Do not kill a transaction solely because it exists; confirm business owner, rollback cost, and lock impact first.

2.3 Read Replica's Cumulative Slowest Statement Digests

SELECT DIGEST_TEXT, COUNT_STAR,
       ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_seconds,
       ROUND(AVG_TIMER_WAIT / 1000000000000, 4) AS avg_seconds
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

Digest table accumulates since instance start or stats reset, not just the incident window. Keep snapshots and correlate with slow log/APM time ranges. If reporting queries chronically consume I/O, move them off the failover candidate replica rather than blindly adding parallel workers.

3. Parallel Replication Tuning When Replay Is Slow

Only when IO thread is healthy, relay log grows continuously, and applier workers cannot keep up should you tune parallel replication. Goal: let independent transactions apply in parallel, not merely increase worker count. Single huge transactions, continuous hot-row writes, global DDL, and strict commit order can all limit parallelism gains.

3.1 View Current Parallel Replication Settings

SHOW VARIABLES WHERE Variable_name IN (
  'replica_parallel_workers','replica_parallel_type',
  'replica_preserve_commit_order','replica_checkpoint_group','replica_checkpoint_period'
);

Defaults and maximums vary by version; verify against the live instance. replica_parallel_workers=0 means single-threaded apply. Existing workers may be idle; prove the bottleneck is actually parallelizable via worker states and CPU/I/O before increasing.

3.2 Canary Increase Workers on a Non-Critical Replica

SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL replica_parallel_workers = <worker_count>;

<worker_count>

must be based on CPU, storage, load testing, and current load—not simply core count or a large constant. Parameter effectiveness timing depends on version documentation and state verification; record original values, observe lag, workers, error log, and I/O before persisting.

3.3 Controlled Restart of SQL/Applier Thread to Load Settings

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

High-impact operation: applier pauses during stop, briefly increasing lag; IO thread may continue writing relay log. Ensure relay log space is sufficient and no replication errors exist. For multi-source, target the correct channel. After restart, verify threads and workers, not just command success.

3.4 Persist Validated Parameters to Config Fragment

[mysqld]
# Enable only after load testing and canary validation
replica_parallel_type=LOGICAL_CLOCK
replica_parallel_workers=<worker_count>
replica_preserve_commit_order=ON

Config file location varies by distribution (commonly /etc/my.cnf or /etc/mysql/my.cnf). replica_preserve_commit_order=ON helps maintain commit order but may introduce waits; read consistency and failover requirements usually outweigh marginal throughput gains.

3.5 Evaluate Source's Transaction Dependency Tracking Mode

SHOW VARIABLES LIKE 'binlog_transaction_dependency_tracking';
SET GLOBAL binlog_transaction_dependency_tracking = 'WRITESET';

WRITESET

provides finer-grained dependency information for subsequent binlogs, potentially improving parallel replay where supported. This change affects the source; assess CPU, version compatibility, all replica capabilities, and rollback plan. It cannot split a single large transaction or eliminate business hot-write conflicts.

3.6 Verify Gains via Applier Worker Runtime State

SELECT WORKER_ID, SERVICE_STATE,
       APPLYING_TRANSACTION,
       LAST_APPLIED_TRANSACTION,
       LAST_ERROR_NUMBER
FROM performance_schema.replication_applier_status_by_worker
ORDER BY WORKER_ID;

After changes, expect converging lag, multiple workers doing meaningful work, unsaturated CPU/I/O, and no new errors. If workers remain mostly idle, the bottleneck is likely dependency chains or a single large transaction; adding more workers will not help.

4. Large Transactions, DDL, and Hotspots: Parameters Cannot Replace Write Governance

A large transaction is recorded and applied as a unit; even many workers cannot split it internally. Long-running ALTER TABLE, bulk UPDATE/DELETE, large imports, and unthrottled data fixes can rapidly pile up lag. The most effective measures are splitting transactions, controlling concurrency, choosing appropriate online DDL methods, and scheduling in observable windows.

4.1 Read-Only Inspection of Suspicious Binlog Window

mysqlbinlog --read-from-remote-source=BINLOG-DUMP-GTIDS \
  --host=<source_host> --port=3306 --user=<readonly_analysis_user> \
  --start-datetime='<start_time>' --stop-datetime='<end_time>' \
  --base64-output=DECODE-ROWS --verbose \
  <binlog_file> > binlog-window.sql

Requires a controlled analysis account with binary log read privileges (permissions vary by version). Output may contain business data and sensitive fields; store in secured directory and clean per security policy. First verify local client supports --read-from-remote-source via mysqlbinlog --help.

4.2 Check Currently Running Long Transactions

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

Shows only uncommitted transactions; completed large transactions must be found in binlog, slow log, and application job records. Killing a transaction is high-risk: confirm thread ID, business owner, rollback cost, and lock impact first. Do not kill just to make charts look good.

4.3 Batched Commit Skeleton to Replace One-Shot Bulk Updates

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

MYSQL_HOST="<source_host>"
MYSQL_DB="<business_db>"
BATCH_SIZE=1000

while :; do
  affected=$(mysql --host="${MYSQL_HOST}" --database="${MYSQL_DB}" \
    --batch --skip-column-names -e \
    "UPDATE <table_name>
     SET <column_name> = <target_value>
     WHERE <repeatable_filter_condition>
     LIMIT ${BATCH_SIZE};
     SELECT ROW_COUNT();")
  [[ "${affected}" == "0" ]] && break
  sleep 1
done

Operational skeleton only—do not run directly. Filter condition must be idempotent and repeatable; ensure LIMIT with deterministic ordering avoids misses or duplicates. Validate in staging, add rate limiting, log batch progress, monitor replication lag; stop subsequent batches if lag worsens, rather than forcibly interrupting already-committed transactions.

4.4 Check Table Size, Indexes, and DDL Preconditions

SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE,
       TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '<business_db>'
  AND TABLE_NAME = '<table_name>';

SHOW CREATE TABLE `<business_db>`.`<table_name>`;

TABLE_ROWS

is an estimate for InnoDB but sufficient to gauge table and index scale. Before actual DDL, evaluate MySQL version, ALGORITHM / LOCK capabilities, foreign keys, triggers, application write patterns, and replication topology. Do not assume INPLACE or INSTANT keywords guarantee zero impact.

4.5 Explicitly Request DDL Algorithm and Lock Level, Refuse Silent Downgrade

ALTER TABLE `<business_db>`.`<table_name>`
ADD INDEX `<index_name>` (<column_name>),
ALGORITHM=INPLACE,
LOCK=NONE;

High-risk DDL example; execute only after confirming support in the actual MySQL version and table structure. Explicit algorithm and lock level cause an error if unsupported, rather than silently falling back to heavier table rebuild or write lock. Pre-requisites: backup, capacity assessment, canary, rollback plan. Index rollback is DROP INDEX, which also consumes resources.

4.6 Check Source Slow Log for Anomalous Write Patterns

SHOW VARIABLES WHERE Variable_name IN (
  'slow_query_log','long_query_time',
  'log_queries_not_using_indexes','log_output'
);

Slow log is not a complete record of transaction size but helps correlate slow DML/DDL in the incident window. Changing slow log parameters affects overhead and volume; follow change management. Do not permanently enable high-overhead audit without retention policy.

4.7 Read-Only Summary of Slowest Statement Templates from Slow Log

sudo mysqldumpslow -s t -t 20 <slow_log_file_path>

mysqldumpslow

works on text-format slow logs; if log_output=TABLE or external logging is used, use the corresponding query method. Output is a summary clue, not proof that a specific statement caused replication lag; still correlate with execution time, locks, binlog, and worker states.

5. Replication Topology Optimization: Isolate Failover, Read Traffic, Backup, and Analytics

A single replica serving real-time reads, reporting, backup, ETL, validation, and multiple downstream replicas will inevitably lag. Topology design must define each replica's role: failover candidates prioritize freshness and consistency; business read replicas prioritize availability and acceptable freshness; analytics replicas tolerate higher lag but must not squeeze core paths; backup replicas take backup load in controlled windows. Goal is not zero lag everywhere, but verifiable RPO/RTO per role.

5.1 Record Identity, Read-Only State, and Binlog Capability on Every Instance

SELECT @@hostname AS hostname,
       @@server_uuid AS server_uuid,
       @@server_id AS server_id,
       @@read_only AS read_only,
       @@super_read_only AS super_read_only,
       @@log_bin AS log_bin;

Run on each instance and aggregate into a controlled topology inventory. read_only does not block high-privilege sessions; super_read_only enforces stricter constraints but impacts maintenance and replication flows. Do not enable without understanding platform account behaviors.

5.2 Verify Replica Meets Prerequisites for Cascading Source

SHOW VARIABLES WHERE Variable_name IN (
  'log_replica_updates','log_bin','gtid_mode',
  'relay_log_recovery','read_only','super_read_only'
);

Cascading replicas typically need log_bin=ON and log_replica_updates=ON to write applied transactions into their own binlog for downstream consumption. This adds disk and I/O pressure; suitable for isolating analytics/remote replicas from primary connection load, but upstream lag propagates downstream.

5.3 Audit Long-Running Reads on Failover Candidate Replicas

SELECT USER, SUBSTRING_INDEX(HOST, ':', 1) AS client_host,
       DB, COMMAND, TIME, STATE,
       LEFT(INFO, 200) AS statement_sample
FROM information_schema.PROCESSLIST
WHERE COMMAND <> 'Sleep'
ORDER BY TIME DESC;

Output may contain SQL text and user info; store securely. Moving ETL, exports, and heavy reporting off failover candidates is usually more effective than continuing to tune workers. Do not ban users based on a single processlist snapshot; correlate with connection pools, task systems, slow logs, and business owners.

5.4 Provide Replication Thread Health Check SQL for Read Routing

SELECT CASE
  WHEN (SELECT SERVICE_STATE
        FROM performance_schema.replication_applier_status
        LIMIT 1) <> 'ON' THEN 0
  WHEN (SELECT SERVICE_STATE
        FROM performance_schema.replication_connection_status
        LIMIT 1) <> 'ON' THEN 0
  ELSE 1
END AS replication_threads_healthy;

This only checks thread liveness, not maximum freshness. Real read routing must combine business-allowed lag, GTID or heartbeat delta, database availability, and connection pool behavior. If using a proxy or cloud router, implement per its official health-check mechanism.

5.5 Optional: Write Real Replication Freshness Heartbeat with pt-heartbeat

pt-heartbeat \
  --host=<source_host> --user=<monitor_user> \
  --database=<business_db> --table=heartbeat \
  --update --daemonize --log=/var/log/pt-heartbeat-source.log

pt-heartbeat

is part of Percona Toolkit; evaluate software source, version, and permissions first. It continuously writes to a table that should be independent of business, properly indexed, and included in backups. Command parameters per local pt-heartbeat --help.

5.6 Optional: Read pt-heartbeat Lag on Replica

pt-heartbeat \
  --host=<replica_host> --user=<monitor_user> \
  --database=<business_db> --table=heartbeat \
  --monitor

Requires source-side heartbeat writer running and replicating to replica. Host time must be synchronized via NTP/chrony; otherwise clock skew pollutes results. Heartbeat measures observed write freshness, not full data consistency.

5.7 Record Read-Only Topology and Lag Inspection Script

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

MYSQL_HOST="<replica_host>"
MYSQL_USER="<monitor_user>"
MYSQL_DEFAULTS_FILE="<client_credential_file>"

mysql --host="${MYSQL_HOST}" --user="${MYSQL_USER}" \
  --defaults-extra-file="${MYSQL_DEFAULTS_FILE}" --batch --skip-column-names \
  -e "SELECT CHANNEL_NAME, SERVICE_STATE, LAST_ERROR_NUMBER
       FROM performance_schema.replication_connection_status;"

mysql --host="${MYSQL_HOST}" --user="${MYSQL_USER}" \
  --defaults-extra-file="${MYSQL_DEFAULTS_FILE}" --batch --skip-column-names \
  -e "SELECT CHANNEL_NAME, SERVICE_STATE, LAST_ERROR_NUMBER
       FROM performance_schema.replication_applier_status;"

Script is read-only; does not start, stop, or repair replication. Passwords must not appear in shell arguments or script plaintext; <client_credential_file> should be permission-protected. Execution account needs only minimal privileges to read relevant Performance Schema tables.

6. Configuration Changes Must Follow "Backup—Modify—Validate—Rollback"

Replication parameters can affect both source and replica. High-risk points include mysqld restart, GTID changes, replica rebuild, source switch, and writing wrong parameters to config. In production, canary on a non-critical replica first, then roll out per topology; do not change workers, disk, read routing, and business batch processing simultaneously, otherwise attribution is impossible. For GitOps or config management, final changes must be written back to the declarative source to avoid being overwritten on next release.

6.1 Confirm mysqld's Actual Config File Read Path

sudo mysqld --help --verbose 2>/dev/null | \
  sed -n '/Default options are read from the following files/,/Variables and options/p'

Distributions, container images, and managed services differ. Verify read order first, then modify a file that is under config management; do not write to a guessed /etc/my.cnf and assume parameters took effect.

6.2 Backup Actual Config File Preserving Permissions

sudo cp -a <actual_mysql_config> "<actual_mysql_config>.bak.$(date +%Y%m%d%H%M%S)"
sudo ls -l "<actual_mysql_config>" "<actual_mysql_config>.bak.<timestamp>"

cp -a

preserves owner, permissions, and timestamps. The timestamp in the second command must be replaced with the actual generated suffix. Backup file should reside on the same controlled volume or compliant config backup location, not only in a volatile temp directory.

6.3 Validate Config Syntax on Supported Versions

sudo mysqld --defaults-file=<actual_mysql_config> --validate-config

--validate-config

is only available on supporting MySQL versions; run mysqld --help to confirm. Passing validation only means parsable, not that parameters suit production load, nor does it replace rolling restart verification of replication and business functionality.

6.4 Restart Single Replica in Maintenance Window and Verify Basic Liveness

sudo systemctl restart mysqld
sudo systemctl is-active mysqld
mysqladmin --host=127.0.0.1 --user=<local_admin_user> ping

High-impact operation: interrupts local read connections and replication on that replica. Before execution, remove from read routing, confirm business replica redundancy and maintenance window. mysqladmin ping only proves service responsiveness; after restart, still check replication threads, GTID convergence, error log, and application read probes.

6.5 Rollback Config File and Verify Before Restart

sudo cp -a <actual_mysql_config>.bak.<timestamp> <actual_mysql_config>
sudo mysqld --defaults-file=<actual_mysql_config> --validate-config
sudo systemctl restart mysqld

This rolls back configuration only; it does not restore already-skipped transactions, changed source, or rebuilt replica data state. Before executing, confirm backup is from before this change and that ownership, permissions, and include-file relationships are intact. GitOps environments should also revert the corresponding commit in the config repository.

7. Rebuild Replica or Change Source: Last Resort, Not Guesswork

When source binlogs are purged, relay logs are irrecoverably corrupted, data drift cannot be safely fixed, or replica base data is untrustworthy, rebuilding is often safer than forced catch-up. However, it consumes source I/O, network, and storage, and may impact backup windows. First assess qualified backup, GTID, version, data volume, recovery time, binlog retention, and read traffic handover before entering controlled operation.

7.1 Verify Source Binlog Retention and GTID Mode Before Rebuild

SHOW BINARY LOGS;
SHOW VARIABLES WHERE Variable_name IN (
  'binlog_expire_logs_seconds','expire_logs_days',
  'gtid_mode','enforce_gtid_consistency'
);

If the replica requests a binlog file that no longer exists, IO thread typically reports 1236-class errors; repeated START REPLICA will not restore the file. Retention must cover maximum allowed lag, backup recovery, audit, and disk capacity—not extended indefinitely for a single alert.

7.2 Save Replica Identity and Replication Metadata Before Rebuild

SELECT @@server_uuid AS server_uuid,
       @@server_id AS server_id,
       @@GLOBAL.gtid_executed AS gtid_executed,
       @@GLOBAL.gtid_purged AS gtid_purged;
SHOW REPLICA STATUS\G

Write output to controlled ticket. During restore, mishandling auto.cnf, server UUID, GTID_PURGED, and replication connection parameters can cause UUID conflicts, wrong transaction sets, or connection to wrong environment. Different backup tools have different restore semantics; only use a rehearsed runbook.

7.3 GTID Auto-Positioning Replication Connection Example

STOP REPLICA;
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '<source_host>',
  SOURCE_PORT = 3306,
  SOURCE_USER = '<replication_user>',
  SOURCE_PASSWORD = '<replication_password>',
  SOURCE_AUTO_POSITION = 1;
START REPLICA;
SHOW REPLICA STATUS\G

High-risk command; only for scenarios where GTID is correctly enabled, base data restored from qualified backup, and source identity verified. Passwords must not remain in terminal history, scripts, or ticket body; use controlled credential injection. Save original replication config, confirm <source_host> does not point to wrong environment; multi-source requires correct channel.

7.4 Minimum Acceptance Queries Before Re-adding Rebuilt Replica to Read Routing

SHOW REPLICA STATUS\G
SELECT @@GLOBAL.gtid_executed;
SELECT @@read_only, @@super_read_only;

Acceptance must cover: IO/SQL threads running, no Last Error, GTID gap converged, heartbeat lag within SLA, read-only probe healthy, backup and monitoring re-onboarded. Merely seeing Seconds_Behind_Source=0 is insufficient for full traffic; canary first.

8. Monitoring and Prevention: Detect Lag Before It Hits 10 Minutes

Monitoring must distinguish at least: "unreplicable", "lag growing", "lag stable but exceeds role threshold", "read routing freshness insufficient". Prometheus metric names vary by mysqld_exporter, Percona exporter, or cloud vendor; expressions below assume actual exporter metrics, labels, and units. Verify metric existence in Prometheus before promoting to production rules.

8.1 Monitor Replication Thread Liveness

mysql_slave_status_slave_io_running == 0
or
mysql_slave_status_slave_sql_running == 0

Some exporters already use replica/source field names or different metric names; rely on actual exporter output. Thread stop should be high-priority alert, carrying instance, channel, and recent error link to avoid on-call seeing only a number.

8.2 Monitor Whether Lag Is Continuously Growing

deriv(mysql_slave_status_seconds_behind_master[10m]) > 0

Suitable as a trend signal, not a standalone correctness judgment; missing values, resets, and short windows affect results. Real alerts should combine thread state, minimum duration, business freshness threshold, and instance role. Metric names and labels per actual exporter.

8.3 Monitor Host IOWait Trend

rate(node_cpu_seconds_total{mode="iowait"}[5m])

Common node_exporter metric representing CPU time fraction in iowait, not equivalent to a specific disk or mysqld I/O bottleneck. Correlate with disk queue, replication workers, lag trend, and healthy replicas in same group. Metrics and labels per actual exporter.

8.4 Daily Replication Health Check Minimal SQL Task

SELECT 'connection' AS component, CHANNEL_NAME, SERVICE_STATE,
       LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE
FROM performance_schema.replication_connection_status
UNION ALL
SELECT 'applier' AS component, CHANNEL_NAME, SERVICE_STATE,
       LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE
FROM performance_schema.replication_applier_status;

Suitable for execution by a controlled inspection account. Empty result or status not ON triggers monitoring alert. It does not modify replication nor replace deep troubleshooting; for multi-channel, alerts must carry specific channel name, not a generic "replication anomaly".

Conclusion

The correct sequence for handling replication lag is: first, collect evidence and distinguish IO, applier, storage, transaction, and topology issues; second, restore threads and throughput with minimal changes; third, codify parallel replication, binlog dependency tracking, read traffic isolation, topology roles, and real freshness monitoring into long-term capabilities. A 10-minute lag does not authorize skipping transactions, forcing source switch, or restarting all replicas. Only conclusions jointly supported by error logs, replication status, GTID gaps, worker states, host metrics, and business change records deserve entry into production remediation plans.