MySQL DBA Interview Questions

 MySQL DBA Interview Questions


1. MySQL Architecture & Internals (Advanced)

  1. Explain the internal architecture of InnoDB buffer pool, change buffer, double-write buffer, redo logs, undo logs, and adaptive hash index.
  2. What happens internally when you run this query?
    UPDATE users SET balance = balance + 100 WHERE id = 10;
  3. Explain the full journey of a transaction from BEGIN → COMMIT inside MySQL.
  4. How does the MySQL optimizer choose an execution plan?
  5. Why does MySQL sometimes prefer a full table scan even when an index exists?
  6. Explain InnoDB crash recovery phases in detail.
  7. Why is InnoDB redo logging physical and binlogging logical?
  8. Explain MVCC implementation in InnoDB (hidden transaction IDs, rollback pointers).
  9. What is the difference between persistent statistics and histograms?
  10. What happens in memory and on disk when a page split occurs?

🟦 2. Advanced Storage Engine Concepts (Senior Level)

  1. Why does InnoDB sometimes hold gap locks even for equality conditions?
  2. Explain next-key locking and how it prevents phantom reads.
  3. How does InnoDB purge process work? What causes purge lag?
  4. Explain fragmentation types in InnoDB and how to fix each.
  5. Why does InnoDB not reclaim space after DELETE or TRUNCATE in certain cases?
  6. What is the importance of log sequence number (LSN)?
  7. How is insert buffer merging handled during crash recovery?
  8. How does InnoDB handle index cardinality and stats updates?

🟦 3. Replication (Architect-Level)

  1. Explain GTID lifecycle in MySQL during write, relay, and apply stages.
  2. What is an “errant transaction”? How do you detect and fix it?
  3. Why can multi-threaded replication still run single-threaded?
  4. Explain replication lag root causes and deep-dive diagnosis steps.
  5. What is WRITESET-based parallel replication? When should you use it?
  6. What is the difference between logical clock and commit order?
  7. Why does DDL break replication? How do you prevent DDL drift?
  8. What are the challenges of multi-source replication?
  9. How do you rebuild a replica without stopping writes on master?
  10. Explain binlog crash recovery and binlog checksum.

🟦 4. High Availability, Group Replication, Failover

  1. Explain the internal working of MySQL Group Replication (GCS, certification layer, applier threads).
  2. Why do multi-primary clusters generate conflicts? How to prevent them?
  3. What causes cluster “expelled member” events?
  4. How does quorum calculation work in Group Replication?
  5. What is flow control and why can it freeze your cluster?
  6. How does MySQL Router detect primary role changes?
  7. What is a split-brain scenario? How to prevent it?
  8. When does Group Replication lose consistency guarantees?
  9. How do you design zero-downtime failover with GTID replication?
  10. How do you avoid data loss during failover?

🟦 5. Performance Optimization (Expert Level)

  1. What are the top 10 signals from Performance Schema that indicate bottlenecks?
  2. Why does MySQL perform poorly on write-heavy workloads? How to fix it?
  3. What is the impact of redo log size on performance?
  4. How do you detect CPU-based bottlenecks using PFS wait events?
  5. What leads to excessive temporary table creation?
  6. Why can an index degrade performance instead of improving it?
  7. How do you tune MySQL for:
    • 90% reads / 10% writes
    • 80% writes / 20% reads
  8. What is the impact of page flush, checkpoint age, and I/O capacity?
  9. How do you detect and fix plan regression in MySQL 8?
  10. How do histograms help the optimizer?

🟦 6. Query Optimization (Hard)

  1. Explain the scenarios where EXPLAIN output is misleading.
  2. How do you optimize multi-table JOIN with millions of rows?
  3. Why is ORDER BY + LIMIT slow even with index?
  4. How do you optimize queries using window functions?
  5. Why does MySQL use filesort? How do you eliminate it?
  6. What causes BLOATED B+Tree index? How to fix it?
  7. Why does MySQL ignore a composite index?
  8. How do you drastically reduce temp-table creation for analytic queries?
  9. Why does MySQL store temp tables on disk even when memory is available?

🟦 7. Locking, Deadlocks, Concurrency

  1. Explain in detail InnoDB deadlock internal detection.
  2. Why do deadlocks increase after adding an index?
  3. What is metadata lock? How does it impact production?
  4. How do you track down a long-running transaction holding locks?
  5. What causes “Waiting for flush” or “Waiting for table metadata lock”?
  6. Why do online ALTER operations cause application timeouts?
  7. How does MySQL handle lock escalation?

🟦 8. Partitioning, VLDB, Scaling

  1. Why does partition pruning fail?
  2. Should you partition or shard? When?
  3. What happens to indexes when table is partitioned?
  4. How do you scale MySQL beyond 1B rows?
  5. Why do partitions degrade write performance?
  6. What is the impact of global index in partitioning?
  7. How do you migrate a 5TB table with near-zero downtime?

🟦 9. Backup, Restore, and DR (Senior Level)

  1. Explain the architecture of XtraBackup.
  2. Why can a partial prepare stage make backup unrecoverable?
  3. How do you perform cross-region PITR?
  4. How do you restore a single table from physical backup?
  5. Explain backup consistency under heavy write load.
  6. How does MySQL handle encrypted backup and restore?
  7. What causes binlog corruption? How do you fix it?

🟦 10. Disaster Recovery, Failover, Chaos Testing

  1. Master crashes with unsent transactions — how do you avoid data loss?
  2. How do you detect and fix GTID gaps after failover?
  3. How do you simulate crash recovery for DR testing?
  4. How do you handle MySQL crash with half-written pages?
  5. How do you perform cluster-wide failover testing without downtime?

🟦 11. Cloud, Aurora, RDS MySQL

  1. When will Aurora replica lag even though hardware is superior?
  2. Why does RDS failover take 40–90 seconds?
  3. How is aurora redo logging different from InnoDB?
  4. How do you recover an RDS instance stuck in storage full state?
  5. How do you fix “InnoDB: Write throttled” in cloud environments?
  6. How does Aurora Global DB replication actually work?
  7. Why does RDS performance degrade after large parameter change?

🟦 12. Senior-Level Scenario Questions

  1. CPU suddenly spikes to 90% on master — walk through your step-by-step investigation.
  2. Replication lag increases from 1 sec → 3600 sec — what do you do?
  3. MySQL crashes during heavy writes — how do you diagnose crash cause?
  4. Application performance drops suddenly — how do you isolate DB bottleneck?
  5. A 400GB table ALTER is stuck for 12 hours — how do you recover?
  6. A table reports corruption in production — what are immediate next steps?
  7. Failover happens but replicas refuse writes — why?
  8. EXPLAIN shows correct index but query still slow — what next?
  9. MySQL slows down at peak time every day — how to find the reason?
  10. Your MySQL instances show “too many connections” — how to fix root cause?

Comments

Post a Comment