Backend interview prep

Start

MySQL Interview Questions (Top 100 Questions with Answers)

Master MySQL Interview Questions with production-oriented questions covering MySQL Architecture, InnoDB, Transactions, Indexes, Replication, Partitioning, Performance Tuning, Backup & Recovery, and real-world troubleshooting scenarios.

Introduction

MySQL is one of the world's most widely used relational databases.

It powers

  • Banking Applications
  • E-Commerce Platforms
  • SaaS Products
  • ERP Systems
  • Healthcare Applications
  • Cloud Applications

Popular companies using MySQL include

  • Facebook
  • Netflix
  • Uber
  • Shopify
  • Booking.com
  • GitHub

This guide contains the Top 100 MySQL Interview Questions frequently asked in Java, Backend, DevOps, Cloud, and Database interviews.

MySQL Interview Roadmap

MySQL Basics
      │
      ▼
Architecture
      │
      ▼
Storage Engines
      │
      ▼
Indexes
      │
      ▼
Transactions
      │
      ▼
Replication
      │
      ▼
Partitioning
      │
      ▼
Performance
      │
      ▼
Production Scenarios

MySQL Fundamentals

1. What is MySQL?

An open-source relational database management system (RDBMS) that stores data in tables using SQL.

2. What are MySQL features?

  • Open Source
  • ACID Support
  • Replication
  • Partitioning
  • Stored Procedures
  • Triggers
  • Views
  • Transactions

3. MySQL vs PostgreSQL?

MySQL PostgreSQL
Simpler Feature Rich
InnoDB Advanced SQL
Easy Administration Better Extensibility
Faster Simple Queries Better Complex Queries

4. MySQL Editions?

  • Community
  • Enterprise
  • Cluster (NDB)

5. Which companies use MySQL?

  • Facebook
  • Uber
  • Shopify
  • Netflix
  • GitHub

Architecture

6. Explain MySQL Architecture.

Main components

  • Client
  • Connection Manager
  • SQL Parser
  • Optimizer
  • Storage Engine
  • Data Files

7. What is MySQL Server?

The process that accepts client connections and executes SQL statements.

8. What is a Storage Engine?

Component responsible for storing and retrieving data.

9. Default Storage Engine?

InnoDB

10. Difference between InnoDB and MyISAM?

InnoDB MyISAM
Transactions No Transactions
Row Locking Table Locking
Foreign Keys No Foreign Keys
Crash Recovery Limited Recovery

InnoDB

11. Why is InnoDB preferred?

  • ACID
  • MVCC
  • Row Locking
  • Crash Recovery
  • Foreign Keys

12. What is the InnoDB Buffer Pool?

Memory area caching

  • Data Pages
  • Index Pages

13. What is Redo Log?

Stores committed changes for crash recovery.

14. What is Undo Log?

Stores previous row versions for rollback and MVCC.

15. What is Doublewrite Buffer?

Protects against partial page writes during crashes.

16. What is Change Buffer?

Buffers secondary index modifications for better write performance.

17. What is Adaptive Hash Index?

Automatically created hash index for frequently accessed data.

18. What is InnoDB Clustered Index?

Stores table data together with the Primary Key.

19. What is Secondary Index?

Separate index that references clustered index records.

20. How does InnoDB support MVCC?

Using

  • Undo Logs
  • Transaction IDs
  • Read Views

SQL & Indexes

21. Types of Indexes?

  • Primary
  • Unique
  • Composite
  • Full Text
  • Spatial

22. Composite Index?

Index on multiple columns.

23. Leftmost Prefix Rule?

Composite indexes are efficiently used when queries start with the leftmost indexed columns.

24. Covering Index?

Contains every column required by a query.

25. Full Table Scan?

Reads every row.

26. Explain EXPLAIN.

Displays query execution plan.

27. Possible EXPLAIN Access Types?

  • const
  • eq_ref
  • ref
  • range
  • index
  • ALL

28. Which EXPLAIN type is best?

const

29. Which EXPLAIN type is worst?

ALL

30. How do you optimize slow queries?

  • Add Indexes
  • Rewrite SQL
  • Review EXPLAIN
  • Reduce Returned Rows

Transactions

31. Does MySQL support ACID?

Yes.

InnoDB is fully ACID compliant.

32. Default Isolation Level?

Repeatable Read

33. Supported Isolation Levels?

  • Read Uncommitted
  • Read Committed
  • Repeatable Read
  • Serializable

34. Savepoints?

Supported.

35. MVCC?

Implemented in InnoDB.

36. Row Locking?

Supported by InnoDB.

37. Table Locking?

Used primarily by MyISAM or explicit lock statements.

38. Deadlocks?

Automatically detected and one transaction is rolled back.

39. Gap Locks?

Locks index gaps to help prevent phantom reads.

40. Next-Key Lock?

Combination of row lock and gap lock.

Replication

41. What is Replication?

Copies data from Primary to Replica servers.

42. Replication Types?

  • Asynchronous
  • Semi-Synchronous
  • Group Replication

43. Primary-Replica Replication?

One Primary

Multiple Replicas.

44. Binary Log?

Stores all data changes.

45. Relay Log?

Stores events received from Primary.

46. GTID?

Global Transaction Identifier.

47. Advantages of Replication?

  • High Availability
  • Read Scaling
  • Backup
  • Disaster Recovery

48. What causes Replication Lag?

  • Slow Replica
  • Heavy Writes
  • Network Delay

49. How do you monitor Replication?

SHOW REPLICA STATUS;

(Older versions use SHOW SLAVE STATUS;.)

50. Group Replication?

High availability with multiple nodes.

Partitioning

51. What is Partitioning?

Splits a table into smaller partitions.

52. Types of Partitioning?

  • RANGE
  • LIST
  • HASH
  • KEY

53. Benefits?

  • Faster Queries
  • Easier Maintenance
  • Faster Deletes

54. Partition Pruning?

Reads only required partitions.

55. When should Partitioning be used?

Very large tables.

Backup & Recovery

56. mysqldump?

Logical backup tool.

57. Physical Backup?

Copies database files.

58. Binary Logs?

Support Point-in-Time Recovery.

59. Point-in-Time Recovery?

Restore backup

Replay Binary Logs.

60. Difference between Logical and Physical Backup?

Logical exports SQL.

Physical copies database files.

Performance

61. Slow Query Log?

Logs slow SQL statements.

62. Performance Schema?

Collects performance metrics.

63. INFORMATION_SCHEMA?

Metadata tables.

64. SHOW PROCESSLIST?

Displays running sessions.

65. SHOW ENGINE INNODB STATUS?

Displays InnoDB internals including locks and deadlocks.

66. Explain Query Cache.

Removed in MySQL 8.0.

External caching solutions such as Redis are preferred.

67. Connection Pooling?

Reuse database connections.

68. Batch Processing?

Reduces round trips.

69. Prepared Statements?

Prevent SQL Injection.

70. AUTO_INCREMENT?

Automatically generates unique IDs.

Security

71. Authentication Methods?

Password

Plugins

SSL

72. GRANT?

Assign permissions.

73. REVOKE?

Remove permissions.

74. Roles?

Simplify privilege management.

75. SSL?

Encrypts client-server communication.

Production Scenarios

76. High CPU?

Check

  • Slow Queries
  • Missing Indexes
  • EXPLAIN

77. High Disk Usage?

Check

  • Binary Logs
  • Large Tables
  • Temporary Files

78. Database Slow?

Check

  • Buffer Pool
  • Slow Query Log
  • Locks

79. Frequent Deadlocks?

Review

  • Lock Ordering
  • Transactions
  • Indexes

80. Replication Lag?

Monitor

Replica

Network

Workload

81. Full Table Scan?

Add indexes.

82. High Connections?

Use connection pooling.

83. Lock Contention?

Reduce transaction duration.

84. Buffer Pool Too Small?

Increase

innodb_buffer_pool_size.

85. Long Running Transactions?

Commit earlier.

Senior-Level Questions

86. Explain InnoDB Internals.

Buffer Pool

Redo Log

Undo Log

Doublewrite Buffer

Adaptive Hash Index

87. Explain MVCC Internals.

Undo Logs

Read Views

Transaction IDs

88. Explain Crash Recovery.

Redo Logs

Undo Logs

Checkpoint

89. Explain Binary Log Internals.

Stores committed transaction events used for replication and recovery.

90. Explain Query Optimizer.

Chooses the lowest-cost execution plan.

91. Explain Connection Lifecycle.

Client

↓

Authentication

↓

SQL Parsing

↓

Optimization

↓

Execution

↓

Result

92. Explain InnoDB Locking.

  • Row Lock
  • Gap Lock
  • Next-Key Lock

93. Explain High Availability.

Replication

Automatic Failover

94. Explain Disaster Recovery.

Backup

Replication

Recovery

95. Explain Scaling Strategy.

  • Read Replicas
  • Sharding
  • Partitioning
  • Caching

96. Best Monitoring Tools?

  • Performance Schema
  • MySQL Enterprise Monitor
  • Prometheus
  • Grafana

97. Most Common Production Issues?

  • Slow Queries
  • Deadlocks
  • Replication Lag
  • High CPU
  • High I/O

98. Best Optimization Techniques?

  • Proper Indexes
  • EXPLAIN
  • Query Rewrite
  • Partitioning

99. What should be monitored continuously?

  • CPU
  • Memory
  • Connections
  • Slow Queries
  • Replication
  • Buffer Pool Hit Ratio

100. MySQL Performance Checklist?

  • Use Indexes
  • Avoid SELECT *
  • Keep Transactions Short
  • Review EXPLAIN
  • Tune Buffer Pool
  • Monitor Slow Query Log
  • Use Connection Pooling

MySQL Production Workflow

Application
      │
      ▼
Connection Pool
      │
      ▼
MySQL Server
      │
      ▼
Optimizer
      │
      ▼
InnoDB
      │
      ▼
Buffer Pool
      │
      ▼
Redo Log
      │
      ▼
Data Files

Quick Revision

Area Focus
Architecture Client, Optimizer
Storage Engine InnoDB
Memory Buffer Pool
Recovery Redo, Undo
Transactions MVCC
Replication Binary Log
Performance EXPLAIN
Monitoring Performance Schema
Backup mysqldump
Scaling Replication, Partitioning

Interview Tips

For MySQL interviews

  • Explain why InnoDB is the default storage engine.
  • Discuss Buffer Pool, Redo Log, and Undo Log together.
  • Use EXPLAIN when discussing query optimization.
  • Mention Slow Query Log and Performance Schema for troubleshooting.
  • Explain MVCC, Gap Locks, and Next-Key Locks for concurrency.
  • Discuss replication, GTIDs, and Binary Logs for high availability.
  • Relate answers to real production scenarios and performance tuning.

Summary

MySQL is one of the most widely used relational databases for enterprise and cloud-native applications. Strong MySQL interview performance requires understanding architecture, InnoDB internals, transactions, MVCC, indexes, replication, partitioning, backup & recovery, and performance tuning.

Mastering these 100 MySQL interview questions prepares you for Backend Developer, Database Engineer, Senior Java Developer, DevOps Engineer, Solution Architect, and MySQL DBA interviews.