Question 1

Explain MySQL transaction isolation levels and how InnoDB handles locking.

Accepted Answer

MySQL supports four isolation levels: Read Uncommitted, Read Committed, Repeatable Read (Default), and Serializable. InnoDB uses row-level locking. It handles concurrency using Multi-Version Concurrency Control (MVCC) and next-key locks, preventing phantom reads.

Question 2

Explain compound indexes and the left-most prefix rule in MySQL.

Accepted Answer

Compound indexes cover multiple columns. MySQL only uses the index if the query filters include the left-most columns first (equality prefix rule). If a query filters on the second column only, the index is ignored.

Question 3

How do you set up master-slave replication in MySQL?

Accepted Answer

Configure the master server to enable binary logging (`log-bin`) and assign a unique server ID. On the slave server, configure replication credentials pointing to the master's binary log coordinates.

Question 4

How do you write unit tests for MySQL repositories using in-memory databases?

Accepted Answer

Use an in-memory SQL database like H2, configured in MySQL compatibility mode. Run database migrations on startup, execute repository tests, and rollback transactions after each test.

Question 5

Explain the difference between optimistic and pessimistic locking in MySQL.

Accepted Answer

- Optimistic Locking: Checks if the version of a row has changed before committing writes.
- Pessimistic Locking: Uses SQL locks (`SELECT ... FOR UPDATE`) to lock rows, blocking other threads until the transaction commits.

Question 6

Explain the role of the slow query log and how to profile queries.

Accepted Answer

Enable `slow_query_log = 1` in configuration and set `long_query_time` thresholds (e.g. 2 seconds). This logs slow queries to a file for analysis using tools like `mysqldumpslow`.

Question 7

What is index cardinality and how does it affect query optimization?

Accepted Answer

Cardinality measures the uniqueness of values in an index. High cardinality (like UUIDs) makes indexes highly selective and fast. Low cardinality (like status flags) makes indexes slow.

Question 8

How do you mock MySQL database models in unit tests?

Accepted Answer

Mock database repository classes or ORM methods (using Jest or Mockito). Stub query methods to return mock objects directly, isolating code tests from active databases.

Question 9

Explain how MySQL handles database deadlocks.

Accepted Answer

InnoDB automatically detects deadlocks (threads waiting on locks held by each other). It rolls back the transaction with the fewest insertions/updates to break the deadlock, returning errors to the client.

Question 10

What is the purpose of the InnoDB buffer pool?

Accepted Answer

The InnoDB buffer pool caches table data and index pages in memory. Sizing it correctly (e.g. 70-80% of system RAM) reduces disk I/O operations, optimizing read speeds.

Question 11

Explain table partitioning in MySQL.

Accepted Answer

Partitioning splits a large table into smaller physical tables (e.g. partitioning by ranges of IDs). This improves performance by allowing query execution to scan only relevant partitions.

Question 12

How do you monitor database connection pools in MySQL?

Accepted Answer

Monitor active connections using `SHOW STATUS LIKE 'Threads_connected';`. Set maximum connections limits in config to prevent connection exhaustion under high API traffic.

Question 13

How do you test database transaction rollbacks in integration tests?

Accepted Answer

Open a transaction, perform database mutations, trigger a constraint or runtime error, call `rollback`, and assert that the database state remains unchanged.

Question 14

Explain how to write custom stored procedures in MySQL.

Accepted Answer

Use `CREATE PROCEDURE` and change the delimiter. Write procedural blocks with control logic (loops, conditionals), and execute them using the `CALL` statement.

Question 15

What is the difference between logical and physical backups in MySQL?

Accepted Answer

- Logical Backups (`mysqldump`): Exports SQL structures and data, which is slow but portable.
- Physical Backups (Percona XtraBackup): Copies raw database files directly, which is fast and crash-safe.

Question 16

How do you handle schema upgrades in MySQL using migration scripts?

Accepted Answer

Use migration frameworks (like Flyway or Sequelize-cli) to execute versioned SQL scripts. Migrations are tracked in a database table to avoid duplicate runs.

Question 17

Explain the MySQL InnoDB storage architecture, detailing the Buffer Pool, Redo Log, Undo Log, and Doublewrite Buffer.

Accepted Answer

InnoDB is a transaction-safe storage engine. Its architecture includes:
1. Buffer Pool: Caches table data and index pages in memory.
2. Redo Log (Write-Ahead Log): Records all data page changes. Writes are sequential and fast. If the server crashes, InnoDB uses the Redo Log to recover committed transactions.
3. Undo Log: Stores old versions of rows modified by active transactions, supporting MVCC and rollback operations.
4. Doublewrite Buffer: InnoDB writes pages to this buffer before writing them to data files, preventing data corruption caused by partial page writes on system crashes.

Question 18

How would you optimize a high-traffic MySQL database experiencing deadlock spikes and slow query backlogs?

Accepted Answer

Optimize high-load MySQL databases by:
1. Index Optimization: Audit slow queries using the slow query log and build compound indexes to avoid full table scans.
2. Deadlock Mitigation: Keep transaction scopes small, access tables in the same order, and configure `innodb_lock_wait_timeout` to fail fast.
3. Buffer Pool Tuning: Increase `innodb_buffer_pool_size` to cache more datasets, and adjust `innodb_io_capacity` matching disk IOPS.

Question 19

Explain MySQL replication lag and how to scale read capacity in high-traffic applications.

Accepted Answer

Replication lag occurs when standby replica nodes cannot apply master binary logs fast enough. Scale read capacity by:
- Read/Write Splitting: Route write queries to the primary master, and read queries to replicas.
- Multi-Threaded Replication: Configure replicas to apply logs using multiple threads (`replica_parallel_workers`), reducing sync delays.

Question 20

Explain how to secure MySQL databases, focusing on SSL/TLS connections, grant structures, and auditing plugins.

Accepted Answer

Secure MySQL by:
1. SSL/TLS: Enable SSL in configuration to encrypt client-server communication.
2. Grant Access: Define users with strict host limits (`'user'@'10.0.0.5'`) and grant permissions to specific tables rather than using administrative grants.
3. Auditing: Enable auditing plugins to log logins, failed connections, and DDL commands.

Question 21

How would you design a database sharding layer in MySQL at scale?

Accepted Answer

Use middleware layers (like Vitess or ShardingSphere) to manage sharding. The middleware routes queries dynamically to different database instances based on sharding keys, abstracts transactions, and handles scaling.

Question 22

Explain how the MySQL query optimizer chooses between index scans and table scans.

Accepted Answer

The optimizer calculates cost estimates based on table statistics. If a query matches a large percentage of rows, the optimizer will perform a table scan rather than an index scan to avoid random I/O overhead.

Question 23

How do you run database schema migrations on tables with 10M+ rows without downtime?

Accepted Answer

Avoid blocking `ALTER TABLE` locks. Use online schema migration tools (like `pt-online-schema-change` or `gh-ost`). These tools create a duplicate table, apply migrations, sync data using triggers or binary logs, and swap tables.

Senior MySQL Interview Questions (5+ Years Experience) (2026)

What is MySQL and Why is it Critical in Modern Engineering?

MySQL Lifecycle Visualizer

Core Architectural Concepts in MySQL

InnoDB Storage Engine

Transaction Isolation Levels

Compound Index Rules

Master-Slave Replication

Query Execution Plans

check_circleWhy Modern Companies Choose MySQL

lightbulbStrategic Preparation Tips

errorCrucial Mistakes to Avoid

trending_upHiring Trends & Career Outlook (2026)

Architecture