Interview Q&A

Pick a topic — languages & databases — 100+ questions and answers in the center column.

Calculators Salary Emoji Website tools ← Listings

MySQL

Interview questions & answers

InnoDB, replication, binlog, JSON, and ops topics.

102 questions

Question 1
What is MySQL?
Popular open-source relational database (Oracle-owned). Known for InnoDB storage engine, replication, and wide hosting support—common LAMP stack component.
Example code
```
SELECT VERSION()
```
Question 2
InnoDB vs MyISAM?
InnoDB is default: ACID transactions, row locks, foreign keys, crash recovery. MyISAM is table-level locks, no FK—legacy; avoid for new apps.
Example code
```
SHOW ENGINES
```
Question 3
What is the buffer pool?
InnoDB caches data and index pages in memory—tune innodb_buffer_pool_size to fit hot working set on dedicated DB servers.
Example code
```
SHOW VARIABLES LIKE 'innodb_buffer_pool_size'
```
Question 4
Clustered index in InnoDB?
Primary key defines clustered index order; secondary indexes leaf nodes store PK values—choose short selective PKs (often surrogate int or UUID strategy).
Example code
```
CREATE TABLE t (id INT PRIMARY KEY) ENGINE=InnoDB
```
Question 5
AUTO_INCREMENT behavior?
Monotonic per table; gaps after rollback/insert failure are normal; LAST_INSERT_ID() for connection-scoped last value.
Example code
```
SELECT LAST_INSERT_ID()
```
Question 6
How does MySQL replication work?
Source writes binary log (row or statement format); replicas apply relay logs—async by default; replication lag is real.
Example code
```
SHOW BINARY LOG STATUS
```
Question 7
GTID?
Global transaction IDs simplify failover and consistency tracking across topology—recommended for managed HA setups.
Example code
```
SELECT @@gtid_mode
```
Question 8
Semi-sync replication?
Source waits for at least one replica ack before commit—reduces data loss risk vs pure async at latency cost.
Example code
```
SET rpl_semi_sync_master_enabled = 1
```
Question 9
What is binlog?
Logical change log for replication and PITR—row-based safer for non-deterministic statements; statement-based can be smaller.
Example code
```
SHOW BINLOG EVENTS LIMIT 5
```
Question 10
Redo vs undo log?
Redo replays committed changes after crash; undo powers rollback and MVCC consistent reads—both central to InnoDB durability.
Example code
```
-- redo/undo handled by InnoDB internally
```
Question 11
MVCC in InnoDB?
Consistent non-locking reads use snapshot—READ COMMITTED vs REPEATABLE READ differ in what snapshot you see; phantom reads handled with next-key locks in RR.
Example code
```
SELECT @@transaction_isolation
```
Question 12
Isolation default?
REPEATABLE READ default in InnoDB—gap/next-key locking can cause unexpected lock waits vs READ COMMITTED tradeoffs.
Example code
```
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
```
Question 13
Lock wait timeout?
innodb_lock_wait_timeout—tune and handle errors; investigate slow queries and lock order to reduce deadlocks.
Example code
```
SHOW ENGINE INNODB STATUS
```
Question 14
EXPLAIN formats?
EXPLAIN ANALYZE executes query (8.0.18+)—shows actual vs estimated rows; traditional EXPLAIN for plan shape.
Example code
```
EXPLAIN FORMAT=TREE SELECT * FROM t WHERE id=1
```
Question 15
Covering index in MySQL?
Extra Using index in EXPLAIN—index alone satisfies query; watch select * defeating covering.
Example code
```
EXPLAIN SELECT * FROM t WHERE a=1 AND b=2
```
Question 16
JSON columns?
JSON type with functions JSON_EXTRACT, -> operator—functional indexes on expressions (8.0+) help filter paths.
Example code
```
SELECT JSON_EXTRACT(doc,'$.x') FROM t
```
Question 17
Full-text search?
FULLTEXT index with MATCH…AGAINST—InnoDB supported; tune ft_min_word_len; not a Google-scale search engine.
Example code
```
SELECT * FROM articles WHERE MATCH(title) AGAINST('sql')
```
Question 18
What is sql_mode?
Strictness flags—ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES affect silent truncation and GROUP BY compliance.
Example code
```
SET sql_mode = 'STRICT_TRANS_TABLES'
```
Question 19
Character set and collation?
utf8mb4 for full Unicode including emoji; collation affects sort/compare—consistency across columns and connections.
Example code
```
SET NAMES utf8mb4
```
Question 20
Prepared statements in MySQL?
Server-side prepares reduce parse cost; drivers use them for injection safety—watch prepare threshold in pools.
Example code
```
PREPARE s FROM 'SELECT * FROM t WHERE id=?'
```
Question 21
Connection and max_connections?
Each connection uses memory—size pool * instances below server limit; monitor Threads_connected.
Example code
```
SHOW STATUS LIKE 'Threads_connected'
```
Question 22
Slow query log?
Log queries exceeding long_query_time—feed to pt-query-digest or performance_schema for tuning.
Example code
```
SET GLOBAL slow_query_log = ON
```
Question 23
performance_schema?
Low-level instrumentation—consumer tables for waits, stages, statements; overhead manageable with selective enabling.
Example code
```
SELECT * FROM performance_schema.events_statements_summary
```
Question 24
What is the data dictionary?
8.0+ transactional DD in InnoDB tables—atomic DDL; backup tools must understand new metadata layout.
Example code
```
SELECT * FROM information_schema.tables
```
Question 25
Online DDL?
ALGORITHM=INPLACE, LOCK=NONE where supported—still can stall; test on clones; some ops require rebuild.
Example code
```
ALTER TABLE t ALGORITHM=INPLACE, LOCK=NONE ADD COLUMN x INT
```
Question 26
Partitioning in MySQL?
RANGE/LIST/HASH/KEY—partition pruning helps scans; many restrictions on FKs and unique keys across partitions.
Example code
```
ALTER TABLE t PARTITION BY RANGE (YEAR(d)) (...)
```
Question 27
Stored routines?
PROCEDURE/FUNCTION with SQL or pluggable languages—version in source control; harder to test than app code.
Example code
```
DELIMITER //
CREATE PROCEDURE p() BEGIN END//
```
Question 28
Triggers?
BEFORE/AFTER insert/update/delete—hidden logic; replication implications with statement vs row binlog.
Example code
```
CREATE TRIGGER tr BEFORE INSERT ON t FOR EACH ROW SET NEW.x=1
```
Question 29
XA transactions?
Two-phase commit across resources—rare in app code; understand timeout and recovery stories.
Example code
```
XA START 'xid'; XA END 'xid'; XA PREPARE 'xid'
```
Question 30
What is Group Replication?
Built-in multi-primary or single-primary cluster with consensus—InnoDB Cluster tooling wraps it.
Example code
```
SELECT * FROM performance_schema.replication_group_members
```
Question 31
MySQL Shell / InnoDB Cluster?
Admin CLI for provisioning HA—alternative to external orchestrators for many teams.
Example code
```
mysqlsh --js
```
Question 32
Backup: logical vs physical?
mysqldump logical portable slow restore; Percona XtraBackup physical fast—need consistent snapshots for InnoDB.
Example code
```
mysqldump --single-transaction db > dump.sql
```
Question 33
PITR with binlog?
Restore full backup then mysqlbinlog apply events to target time—requires intact binlog chain.
Example code
```
mysqlbinlog binlog.000001 | head
```
Question 34
User grants model?
CREATE USER + GRANT—roles (8.0) simplify bundles; principle of least privilege.
Example code
```
CREATE USER 'app'@'%' IDENTIFIED BY '***'
```
Question 35
Authentication plugins?
caching_sha2_password default in 8.0—clients must support; mysql_native_password legacy.
Example code
```
ALTER USER 'u' IDENTIFIED WITH caching_sha2_password BY '***'
```
Question 36
Resource groups?
8.0 CPU affinity for threads—advanced tuning for mixed workloads.
Example code
```
CREATE RESOURCE GROUP rg TYPE = USER VCPU = 0-1
```
Question 37
Invisible columns?
8.0 columns skipped for SELECT * unless named—migration helper patterns.
Example code
```
ALTER TABLE t ADD COLUMN opt INT INVISIBLE
```
Question 38
HeatWave / cloud variants?
HeatWave is Oracle analytics acceleration; Aurora/RDS are managed deployments—operational features differ from vanilla.
Example code
```
-- HeatWave / Aurora: managed deployment notes
```
Question 39
What is HANDLER interface (MySQL)?
Low-level ISAM-style row API—niche; prefer normal SQL.
Example code
```
-- MySQL: HANDLER interface
SELECT 1
```
Question 40
What is Federated engine (MySQL)?
Remote table proxy—legacy; not for production HA.
Example code
```
-- MySQL: Federated engine
SELECT 1
```
Question 41
What is Blackhole engine (MySQL)?
Accepts writes discards data—useful for replication filter testing only.
Example code
```
-- MySQL: Blackhole engine
SELECT 1
```
Question 42
What is Archive engine (MySQL)?
Compressed append-only—warehouse niche, not general OLTP.
Example code
```
-- MySQL: Archive engine
SELECT 1
```
Question 43
What is NDB Cluster (MySQL)?
MySQL Cluster distributed engine—different operational model from InnoDB replicas.
Example code
```
-- MySQL: NDB Cluster
SELECT 1
```
Question 44
What is Percona Server (MySQL)?
Drop-in fork with extra instrumentation and features—check compatibility with your version.
Example code
```
-- MySQL: Percona Server
SELECT 1
```
Question 45
What is MariaDB divergence (MySQL)?
Fork with different optimizers and features—test migrations both directions.
Example code
```
-- MySQL: MariaDB divergence
SELECT 1
```
Question 46
What is Optimizer hints (MySQL)?
/*+ INDEX() */ comment style—use sparingly when stats cannot be fixed.
Example code
```
-- MySQL: Optimizer hints
SELECT 1
```
Question 47
What is Index merge (MySQL)?
Union/intersect multiple indexes—sometimes planner choice; composite index may be better.
Example code
```
-- MySQL: Index merge
SELECT 1
```
Question 48
What is ICP (index condition pushdown) (MySQL)?
Filters applied in storage layer using index—reduces row lookups.
Example code
```
-- MySQL: ICP (index condition pushdown)
SELECT 1
```
Question 49
What is MRR (multi-range read) (MySQL)?
Batches random PK lookups into ordered ranges—reduces disk seeks.
Example code
```
-- MySQL: MRR (multi-range read)
SELECT 1
```
Question 50
What is Batched key access (MySQL)?
Join optimization using MRR—EXPLAIN may show BKA.
Example code
```
-- MySQL: Batched key access
SELECT 1
```
Question 51
What is Derived merge (MySQL)?
Optimizer flattens subqueries into joins—behavior version-dependent.
Example code
```
-- MySQL: Derived merge
SELECT 1
```
Question 52
What is Temp table on disk (MySQL)?
MEMORY limit exceeded or BLOB columns—watch Created_tmp_disk_tables status.
Example code
```
-- MySQL: Temp table on disk
SELECT 1
```
Question 53
What is Sort merge vs filesort (MySQL)?
ORDER BY without suitable index may filesort—limit sort sizes.
Example code
```
-- MySQL: Sort merge vs filesort
SELECT 1
```
Question 54
What is Join buffer (MySQL)?
Block nested loop uses join_buffer_size when no index—can hide missing indexes temporarily.
Example code
```
-- MySQL: Join buffer
SELECT 1
```
Question 55
What is max_allowed_packet (MySQL)?
Large inserts or blobs need limit raised consistently client and server.
Example code
```
-- MySQL: max_allowed_packet
SELECT 1
```
Question 56
What is wait_timeout (MySQL)?
Idle connection kill—align with pool idle eviction.
Example code
```
-- MySQL: wait_timeout
SELECT 1
```
Question 57
What is interactive_timeout (MySQL)?
Separate timeout for interactive clients—session tools vs apps.
Example code
```
-- MySQL: interactive_timeout
SELECT 1
```
Question 58
What is innodb_flush_log_at_trx_commit (MySQL)?
Durability vs performance knob—=1 safest sync redo each commit; =2 OS buffer risk on power loss.
Example code
```
-- MySQL: innodb_flush_log_at_trx_commit
SELECT 1
```
Question 59
What is sync_binlog (MySQL)?
Binlog durability frequency—1 safest for crash; higher throughput with risk.
Example code
```
-- MySQL: sync_binlog
SELECT 1
```
Question 60
What is doublewrite buffer (MySQL)?
InnoDB torn page protection—disable only expert benchmarking.
Example code
```
-- MySQL: doublewrite buffer
SELECT 1
```
Question 61
What is change buffer (MySQL)?
Caches secondary index changes when pages not in buffer pool—speeds mixed workloads.
Example code
```
-- MySQL: change buffer
SELECT 1
```
Question 62
What is adaptive hash index (MySQL)?
In-memory hash for hot B-tree pages—sometimes disable on high-contention benchmarks.
Example code
```
-- MySQL: adaptive hash index
SELECT 1
```
Question 63
What is page size (MySQL)?
innodb_page_size rarely changed—plan at provisioning for large rows.
Example code
```
-- MySQL: page size
SELECT 1
```
Question 64
What is compression (MySQL)?
Transparent page compression—CPU cost; test with realistic data entropy.
Example code
```
-- MySQL: compression
SELECT 1
```
Question 65
What is encryption at rest (MySQL)?
Keyring plugins—manage key rotation and backups carefully.
Example code
```
-- MySQL: encryption at rest
SELECT 1
```
Question 66
What is audit plugin (MySQL)?
Enterprise or Percona audit—who changed what for compliance.
Example code
```
-- MySQL: audit plugin
SELECT 1
```
Question 67
What is Firewall / Enterprise proxy (MySQL)?
Query allowlists—defense in depth vs app bugs.
Example code
```
-- MySQL: Firewall / Enterprise proxy
SELECT 1
```
Question 68
What is Router / ProxySQL (MySQL)?
Connection multiplexing and query routing—sharding and read/write split helper.
Example code
```
-- MySQL: Router / ProxySQL
SELECT 1
```
Question 69
What is Read-after-write consistency (MySQL)?
Async replica lag—sticky sessions or version tokens for UX.
Example code
```
-- MySQL: Read-after-write consistency
SELECT 1
```
Question 70
What is Circular replication (MySQL)?
Multi-source topology risks conflicts—avoid without discipline.
Example code
```
-- MySQL: Circular replication
SELECT 1
```
Question 71
What is Binlog filters (MySQL)?
Replicate-do-db rules—easy to misconfigure partial replication.
Example code
```
-- MySQL: Binlog filters
SELECT 1
```
Question 72
What is Seconds_behind_master (MySQL)?
Legacy lag metric—use performance_schema replication tables in 8.0.
Example code
```
-- MySQL: Seconds_behind_master
SELECT 1
```
Question 73
What is Clone plugin (MySQL)?
8.0 fast physical clone for replicas—great for provisioning.
Example code
```
-- MySQL: Clone plugin
SELECT 1
```
Question 74
What is Persisted variables (MySQL)?
SET PERSIST survives restart—document server config as code.
Example code
```
-- MySQL: Persisted variables
SELECT 1
```
Question 75
What is Component services (MySQL)?
8.0 modular logging/auth—replace some plugin architecture.
Example code
```
-- MySQL: Component services
SELECT 1
```
Question 76
What is Replica parallel workers (MySQL)?
Apply threads must preserve commit order—throughput vs consistency settings.
Example code
```
-- MySQL: Replica parallel workers
SELECT 1
```
Question 77
What is Binlog transaction compression (MySQL)?
Reduce network IO replication—CPU tradeoff on both ends.
Example code
```
-- MySQL: Binlog transaction compression
SELECT 1
```
Question 78
What is Partial revokes (MySQL)?
8.0 fine-grained privilege subtraction—layered security models.
Example code
```
-- MySQL: Partial revokes
SELECT 1
```
Question 79
What is Dual passwords (MySQL)?
Rotate credentials without downtime—two active passwords transitional.
Example code
```
-- MySQL: Dual passwords
SELECT 1
```
Question 80
What is Firewall for SQL injection (MySQL)?
Not a substitute for binding parameters—WAF last line.
Example code
```
-- MySQL: Firewall for SQL injection
SELECT 1
```
Question 81
What is Testcontainers MySQL (MySQL)?
Integration tests with real engine—CI resource cost vs mocks.
Example code
```
-- MySQL: Testcontainers MySQL
SELECT 1
```
Question 82
What is Docker MySQL volume (MySQL)?
Bind mount data dir—watch file permissions and SELinux.
Example code
```
-- MySQL: Docker MySQL volume
SELECT 1
```
Question 83
What is Kubernetes operator (MySQL)?
StatefulSet + PVC—pod identity and storage class matter for InnoDB.
Example code
```
-- MySQL: Kubernetes operator
SELECT 1
```
Question 84
What is mysqlslap (MySQL)?
Built-in load generator—quick sanity benchmarks.
Example code
```
-- MySQL: mysqlslap
SELECT 1
```
Question 85
What is sys schema (MySQL)?
8.0 helper views wrapping performance_schema—easier diagnostics.
Example code
```
-- MySQL: sys schema
SELECT 1
```
Question 86
What is super_read_only (MySQL)?
Prevent accidental writes on replicas—including SUPER users—during failover.
Example code
```
-- MySQL: super_read_only
SELECT 1
```
Question 87
What is read_only mode (MySQL)?
Server-level block non-SUPER writes—pair with replica promotion procedures.
Example code
```
-- MySQL: read_only mode
SELECT 1
```
Question 88
What is relay log (MySQL)?
Replica-side buffered binlog events—disk space and corruption recovery matter.
Example code
```
-- MySQL: relay log
SELECT 1
```
Question 89
What is slave_parallel_type (MySQL)?
DATABASE vs LOGICAL_CLOCK—throughput vs ordering for parallel apply.
Example code
```
-- MySQL: slave_parallel_type
SELECT 1
```
Question 90
What is slave_preserve_commit_order (MySQL)?
Commits on replica match source order—needed for some consistency models.
Example code
```
-- MySQL: slave_preserve_commit_order
SELECT 1
```
Question 91
What is binlog_row_image (MySQL)?
FULL vs MINIMAL—storage and replication bandwidth tradeoffs.
Example code
```
-- MySQL: binlog_row_image
SELECT 1
```
Question 92
What is binlog_format (MySQL)?
ROW vs STATEMENT vs MIXED—consistency vs volume; ROW default modern best practice.
Example code
```
-- MySQL: binlog_format
SELECT 1
```
Question 93
What is innodb_thread_concurrency (MySQL)?
Historical throttle—usually 0 unlimited on modern InnoDB.
Example code
```
-- MySQL: innodb_thread_concurrency
SELECT 1
```
Question 94
What is innodb_io_capacity (MySQL)?
Flash vs HDD flush rates—tune for SSD write endurance and latency.
Example code
```
-- MySQL: innodb_io_capacity
SELECT 1
```
Question 95
What is innodb_log_file_size (MySQL)?
Large redo logs reduce checkpoint churn—balance recovery time.
Example code
```
-- MySQL: innodb_log_file_size
SELECT 1
```
Question 96
What is innodb_flush_neighbors (MySQL)?
SSD usually off—avoid extra writes on SSD media.
Example code
```
-- MySQL: innodb_flush_neighbors
SELECT 1
```
Question 97
What is table_open_cache (MySQL)?
File descriptors for open tables—raise for many-table workloads.
Example code
```
-- MySQL: table_open_cache
SELECT 1
```
Question 98
What is table_definition_cache (MySQL)?
Cached table metadata—large schemas need bumps.
Example code
```
-- MySQL: table_definition_cache
SELECT 1
```
Question 99
What is skip_name_resolve (MySQL)?
Avoid reverse DNS on connect—faster safer in LAN with IP grants.
Example code
```
-- MySQL: skip_name_resolve
SELECT 1
```
Question 100
What is validate_password component (MySQL)?
8.0 password policy plugin—enterprise compliance.
Example code
```
-- MySQL: validate_password component
SELECT 1
```
Question 101
What is clone for upgrade (MySQL)?
In-place server upgrade assist—follow Oracle upgrade docs.
Example code
```
-- MySQL: clone for upgrade
SELECT 1
```
Question 102
What is mysql_config_editor (MySQL)?
Store login paths securely—less .my.cnf plaintext.
Example code
```
-- MySQL: mysql_config_editor
SELECT 1
```

What is MySQL?

InnoDB vs MyISAM?

What is the buffer pool?

Clustered index in InnoDB?

AUTO_INCREMENT behavior?

How does MySQL replication work?

GTID?

Semi-sync replication?

What is binlog?

Redo vs undo log?

MVCC in InnoDB?

Isolation default?

Lock wait timeout?

EXPLAIN formats?

Covering index in MySQL?

JSON columns?

Full-text search?

What is sql_mode?

Character set and collation?

Prepared statements in MySQL?

Connection and max_connections?

Slow query log?

performance_schema?

What is the data dictionary?

Online DDL?

Partitioning in MySQL?

Stored routines?

Triggers?

XA transactions?

What is Group Replication?

MySQL Shell / InnoDB Cluster?

Backup: logical vs physical?

PITR with binlog?

User grants model?

Authentication plugins?

Resource groups?

Invisible columns?

HeatWave / cloud variants?

What is HANDLER interface (MySQL)?

What is Federated engine (MySQL)?

What is Blackhole engine (MySQL)?

What is Archive engine (MySQL)?

What is NDB Cluster (MySQL)?

What is Percona Server (MySQL)?

What is MariaDB divergence (MySQL)?

What is Optimizer hints (MySQL)?

What is Index merge (MySQL)?

What is ICP (index condition pushdown) (MySQL)?

What is MRR (multi-range read) (MySQL)?

What is Batched key access (MySQL)?

What is Derived merge (MySQL)?

What is Temp table on disk (MySQL)?

What is Sort merge vs filesort (MySQL)?

What is Join buffer (MySQL)?

What is max_allowed_packet (MySQL)?

What is wait_timeout (MySQL)?

What is interactive_timeout (MySQL)?

What is innodb_flush_log_at_trx_commit (MySQL)?

What is sync_binlog (MySQL)?

What is doublewrite buffer (MySQL)?

What is change buffer (MySQL)?

What is adaptive hash index (MySQL)?

What is page size (MySQL)?

What is compression (MySQL)?

What is encryption at rest (MySQL)?

What is audit plugin (MySQL)?

What is Firewall / Enterprise proxy (MySQL)?

What is Router / ProxySQL (MySQL)?

What is Read-after-write consistency (MySQL)?

What is Circular replication (MySQL)?

What is Binlog filters (MySQL)?

What is Seconds_behind_master (MySQL)?

What is Clone plugin (MySQL)?

What is Persisted variables (MySQL)?

What is Component services (MySQL)?

What is Replica parallel workers (MySQL)?

What is Binlog transaction compression (MySQL)?

What is Partial revokes (MySQL)?

What is Dual passwords (MySQL)?

What is Firewall for SQL injection (MySQL)?