Mysql Replication Lag Resolver

-- Find long-running queries on master that may block binlog sync
SELECT
  ID, USER, HOST, DB, COMMAND,
  TIME AS duration_sec,
  STATE,
  LEFT(INFO, 300) AS sql_fragment
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep'
  AND TIME > 60
  AND USER != 'system user'
ORDER BY TIME DESC;

-- Check master binlog position vs what slaves have read
SHOW MASTER STATUS;
-- Compare with Exec_Master_Log_Pos from each slave

IF SQL starts with ALTER/CREATE/DROP/TRUNCATE/RENAME:
  → Type: DDL, Kill: NOT RECOMMENDED (explain risk to user)
ELSE IF SQL starts with SELECT:
  → Type: SELECT, Kill: SAFE (kill first)
ELSE IF SQL starts with UPDATE/DELETE and duration > 300:
  → Type: DML, Kill: CAUTION (present risk)
ELSE IF Command = 'Binlog Dump':
  → Type: REPLICATION, Kill: NEVER
ELSE:
  → Type: OTHER, Kill: REVIEW

-- Kill confirmed threads
KILL {thread_id};

-- If KILL fails (thread already gone), report it
-- Alternative: KILL QUERY {thread_id} (kills query, keeps connection)

-- Recheck slave status
SHOW SLAVE STATUS\G

-- Expected recovery pattern:
-- Seconds_Behind_Master: 623 → 412 → 89 → 0

🔄 Replication Lag Alert
═══════════════════════
📊 Current Status
  Master: {host}:{port} (Binlog: {file}, Pos: {pos})
  Slave:  {host}:{port}
  Lag: {seconds}s 🔴 CRITICAL
  IO Thread: {io_status}
  SQL Thread: {sql_status}

🔍 Root Cause Analysis
  Cause: {count} long-running queries on master blocking binlog sync
  Impact: Slave is {seconds}s behind, serving stale data to {app_count} applications

📋 Blocking Queries (candidates for KILL)
  | Thread | User | Duration | Type | SQL | Kill Safety |
  |--------|------|----------|------|-----|-------------|
  | 23451  | app1 | 892s     | SELECT | SELECT ... FROM order_detail | ✅ Safe |
  | 23487  | app2 | 567s     | SELECT | SELECT ... FROM user_session | ✅ Safe |
  | 23502  | app3 | 423s     | SELECT | SELECT ... FROM payment_record | ✅ Safe |

  ℹ️ Skipped: 2 Binlog Dump threads (replication, NEVER kill)

⚠️ Action Required (Step 1 of 2):
  Reply "KILL {thread_ids}" to select targets,
  "KILL ALL" for all candidates,
  or "SKIP" to cancel.
  ⚠️ A final confirmation will be required before execution.

⚠️ DOUBLE CONFIRMATION REQUIRED (Step 2 of 2)
══════════════════════════════════════════════
You are about to KILL {count} queries on MASTER:

  | Thread | User | Duration | Type | SQL |
  |--------|------|----------|------|-----|
  | 23451  | app1 | 892s     | SELECT | ... |
  | 23487  | app2 | 567s     | SELECT | ... |
  | 23502  | app3 | 423s     | SELECT | ... |

  ❗️ Please type EXACTLY "CONFIRM KILL" to proceed.
     Any other response cancels this operation.

✅ KILL Execution Complete
═════════════════════════
  Thread 23451 (892s, SELECT): ✅ Killed
  Thread 23487 (567s, SELECT): ✅ Killed
  Thread 23502 (423s, SELECT): ✅ Killed

📈 Recovery Timeline
  0s:   Lag = 623s (KILL executed)
  30s:  Lag = 412s ↓
  60s:  Lag = 89s ↓
  90s:  Lag = 0s ✅ Replication normalized

💡 Root Cause Summary:
  3 concurrent full-table-scan queries on master blocked binlog sync.
  Recommended: Add indexes on order_detail(status, create_time) and
  user_session(last_active) to prevent recurrence.

🔄 Replication Lag Alert
═══════════════════════
  Slave: db-slave-01:3306
  Lag: 623s 🔴 CRITICAL
  Root Cause: 3 long-running SELECT queries on master blocking binlog sync

  KILL candidates:
    23451 · app1 · 892s · SELECT * FROM order_detail WHERE ...
    23487 · app2 · 567s · SELECT * FROM user_session WHERE ...
    23502 · app3 · 423s · SELECT * FROM payment_record WHERE ...

  Reply "KILL ALL" or specify thread IDs.

⚠️ CONFIRM: Kill all 3 queries on master?
Type "CONFIRM KILL" to proceed.

✅ 3 queries killed. Lag recovering: 623 → 412 → 89 → 0. Normalized.