MySQL In Depth

Table of Contents#

1. Why MySQL Gets Its Own Dedicated Part
2. MySQL's Architecture — The Pluggable Storage Engine
3. InnoDB vs MyISAM
4. The InnoDB Buffer Pool
5. The Binary Log (binlog) — MySQL's Own WAL Equivalent
6. Binlog Formats — Statement, Row, and Mixed
7. MySQL Replication in Practice
8. MySQL High Availability Tools
9. Essential Operational Commands
10. Diagnosing a Slow MySQL Server, Start to Finish
11. Locking in MySQL — A Real, Practical Gotcha
12. Backup Tools for MySQL Specifically
13. MySQL vs PostgreSQL — A Balanced Comparison
14. Common Mistakes
15. Worked Practice Problems
16. Summary and What's Next

---

Why MySQL Gets Its Own Dedicated Part#

Parts 1-4 of this series covered relational database concepts generally — replication, sharding, backups, and SQL itself — using mostly generic or PostgreSQL-flavored examples. MySQL remains one of the two most widely deployed relational databases in the industry (alongside PostgreSQL), and it has enough of its own specific architecture, terminology, and operational quirks that real MySQL experience is worth a focused, dedicated treatment — this Part takes every general concept from Parts 1-4 and grounds it concretely in MySQL's own specific implementation.

---

MySQL's Architecture — The Pluggable Storage Engine#

The single most distinctive architectural fact about MySQL, worth understanding before anything else: MySQL separates the SQL-processing layer from the actual storage layer, and that storage layer is genuinely pluggable — different tables in the same database can even use different storage engines.

Diagram

Why this pluggable design is worth knowing about, beyond trivia: it directly explains why choosing the RIGHT storage engine for a given table is a real, concrete decision in MySQL, in a way that doesn't really have a direct equivalent in most other relational databases (like PostgreSQL, which doesn't have this same pluggable-engine architecture) — this is a genuinely MySQL-specific piece of knowledge worth being able to speak to.

---

InnoDB vs MyISAM#

The single most commonly asked MySQL-specific interview question — a real, practical comparison worth knowing cold.

Diagram

Why the locking difference specifically is such a genuinely important, practical distinction, worth explaining concretely: MyISAM's table-level locking means a single write to ANY row blocks EVERY OTHER query against that entire table until it completes — a severe concurrency bottleneck under any real, simultaneous read/write load. InnoDB's row-level locking means a write only blocks access to the SPECIFIC rows it's actually modifying, letting unrelated rows be read and written concurrently, without contention. This single difference is the primary, practical reason InnoDB became — and remains — the default, standard choice for essentially all new MySQL tables today; MyISAM is now largely a legacy consideration, encountered mostly in older, unmigrated systems.

Copy
-- Explicitly specifying the storage engine (InnoDB is the
-- default in modern MySQL, so this is rarely needed, but
-- worth knowing the syntax exists)
CREATE TABLE orders (
  id INT PRIMARY KEY,
  total DECIMAL(10,2)
) ENGINE=InnoDB;

-- Check which engine an existing table uses
SHOW TABLE STATUS LIKE 'orders';

---

The InnoDB Buffer Pool#

InnoDB's single most important performance-related configuration setting — genuinely worth understanding, since it's a very commonly asked, practical tuning question.

Diagram

Copy
-- The single most impactful InnoDB tuning setting —
-- how much RAM is dedicated to the buffer pool
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- Check the buffer pool's actual hit rate
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

A widely-cited, genuinely practical rule of thumb worth knowing: on a dedicated database server, innodb_buffer_pool_size is commonly configured to roughly 70-80% of available RAM — large enough that the majority of the working dataset (the data actually being actively read/written, not necessarily the ENTIRE dataset) fits in memory, while still leaving enough RAM for the operating system, connection overhead, and other MySQL memory needs. A buffer pool that's too small for the real working set causes a high rate of disk reads for data that should be servable from memory — directly connecting to the iostat/disk-saturation diagnostic tools from the Linux & Networking Fundamentals series.

---

The Binary Log (binlog) — MySQL's Own WAL Equivalent#

Directly connecting to the write-ahead log discussion from Part 1 of this series — MySQL's specific implementation of the same underlying idea, with its own specific name and mechanics.

Diagram

Copy
# Check binlog status and current position
mysql -e "SHOW MASTER STATUS;"

# List available binlog files
mysql -e "SHOW BINARY LOGS;"

# Point-in-time recovery: replay binlog events between two positions
mysqlbinlog --start-datetime="2026-06-01 02:00:00" \
            --stop-datetime="2026-06-01 14:44:59" \
            /var/log/mysql/binlog.000123 | mysql -u root -p

This is precisely, mechanically the same "restore a full backup, then replay the log up to the exact moment before a disaster" pattern from Part 3 of this series — MySQL's binlog plays the identical role PostgreSQL's WAL does for that tutorial's point-in-time recovery example, just under a different name and with MySQL-specific tooling (mysqlbinlog).

---

Binlog Formats — Statement, Row, and Mixed#

A genuinely important, MySQL-specific configuration decision, worth understanding the real tradeoff behind.

Diagram

Why the non-determinism risk with statement-based replication is such a genuinely important, concrete thing to understand: imagine UPDATE orders SET processed_at = NOW() WHERE status = 'pending' — if this exact SQL statement is replayed on a replica even a fraction of a second later than it ran on the primary, NOW() evaluates to a DIFFERENT timestamp on the replica than it did on the primary, causing the replica's data to silently, subtly diverge from the primary's — exactly the kind of correctness bug that's genuinely hard to detect until it causes a real, confusing downstream problem. Row-based (or mixed) replication exists specifically to eliminate this entire class of risk.

---

MySQL Replication in Practice#

Copy
# On the PRIMARY: create a dedicated replication user
mysql -e "CREATE USER 'repl'@'%' IDENTIFIED BY 'password'; \
          GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';"

# On the REPLICA: point it at the primary and start replicating
mysql -e "CHANGE MASTER TO \
  MASTER_HOST='primary-host', \
  MASTER_USER='repl', \
  MASTER_PASSWORD='password', \
  MASTER_LOG_FILE='binlog.000123', \
  MASTER_LOG_POS=4;
  START SLAVE;"

# Check replication health and lag — a genuinely essential,
# frequently-run diagnostic command
mysql -e "SHOW SLAVE STATUS\G"
# Look specifically at:
#   Slave_IO_Running: Yes
#   Slave_SQL_Running: Yes
#   Seconds_Behind_Master: 0

Why Seconds_Behind_Master specifically deserves its own callout: this is MySQL's direct, concrete equivalent of the replication lag metric already covered generically in Part 1 — a rising value here is exactly the Saturation-style leading indicator worth alerting on, warning that a replica is falling dangerously behind before it becomes unsafe to fail over to.

---

MySQL High Availability Tools#

Directly extending the automatic failover discussion from Part 1 — MySQL-specific tooling worth knowing by name.

Diagram

Why a routing layer (ProxySQL/MySQL Router) matters, worth stating explicitly, and directly connecting to the failover discussion from Part 1: after an automatic failover promotes a new primary, every application instance still needs to somehow discover and connect to the NEW primary instead of the old one — a routing layer solves this by giving the application ONE stable connection endpoint, which it transparently redirects to whichever server is currently the real primary, completely hiding the failover event from the application's perspective.

---

Essential Operational Commands#

A practical, memorizable reference — genuinely useful both for real work and for demonstrating hands-on MySQL fluency in an interview.

Copy
-- See ALL currently running queries/connections — the SINGLE
-- most important first command during a "MySQL is slow" incident
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;   -- shows the FULL query text, not truncated

-- Kill a specific runaway query by its process ID
KILL 12345;

-- See MySQL's own internal status/health counters
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Slow_queries';

-- See the CURRENT value of any configuration setting
SHOW VARIABLES LIKE 'max_connections';

Copy
# The slow query log — MySQL's built-in mechanism for
# automatically capturing queries slower than a threshold
mysql -e "SET GLOBAL slow_query_log = 'ON'; \
          SET GLOBAL long_query_time = 1;"   -- log anything over 1 second

# Analyze the slow query log's contents, summarized and ranked
mysqldumpslow -s t /var/log/mysql/slow.log

Why SHOW PROCESSLIST is worth calling out as THE single most important first command, worth stating explicitly: it directly shows you, live, exactly what MySQL is currently doing — which queries are running, how long they've been running, and what state they're in (Sending data, Locked, Sleep) — exactly the equivalent of the 60-second Linux triage checklist's top command from the Linux & Networking Fundamentals series, but specifically for what's happening INSIDE the database.

---

Diagnosing a Slow MySQL Server, Start to Finish#

A full, worked narrative, directly reusing the structured-investigation pattern already established throughout this course (the Linux troubleshooting toolkit, the checkout-slowness worked incident from Monitoring Methodologies).

Diagram

This worked scenario ties together nearly every concept in this Part: SHOW PROCESSLIST for live diagnosis, InnoDB's row-level locking (explaining WHY other queries were blocked, not just slow), and EXPLAIN (Part 4) confirming the actual, concrete root cause — a missing index turning a fast, targeted update into a slow, lock-holding full table scan.

---

Locking in MySQL — A Real, Practical Gotcha#

A genuinely important, concrete extension of the InnoDB row-level locking discussion above, worth its own callout.

Diagram

Why this is such a genuinely important, practical thing to know, worth stating explicitly: this is precisely why "the UPDATE only changed 3 rows, why did it block the whole table for 30 seconds" is a real, recurring production question — the answer is almost always a missing index forcing InnoDB to scan and lock far more rows than the query's actual, final result set, exactly the mechanism demonstrated in the worked incident above.

---

Backup Tools for MySQL Specifically#

Directly extending the backup discussion from Part 3 — the MySQL-specific tools worth knowing by name.

Copy
# mysqldump — the classic, simplest LOGICAL backup tool
# (exports actual SQL statements to recreate the data)
mysqldump -u root -p --single-transaction --all-databases > backup.sql

# Restoring from a mysqldump backup
mysql -u root -p < backup.sql

# Percona XtraBackup — a PHYSICAL backup tool, copying the
# actual InnoDB data files directly (much faster for large
# databases, and supports TRUE hot/online backups)
xtrabackup --backup --target-dir=/backup/full

Why --single-transaction matters specifically for mysqldump, worth stating explicitly, and directly connecting to the Isolation discussion from Part 4: it wraps the entire dump in one single, consistent transaction (relying on InnoDB's MVCC — Multi-Version Concurrency Control — to take a consistent snapshot) so the backup reflects one single, coherent moment in time, even while other writes continue happening concurrently on the live database during the (potentially long) dump process — without it, a large backup taken over many minutes could capture a genuinely inconsistent mix of before-and-after states for different tables.

Why xtrabackup (a physical backup) is often preferred for genuinely large production databases, worth stating the concrete tradeoff: mysqldump's logical backup (re-executing SQL statements to rebuild data) is dramatically slower to both create AND restore for a large database, compared to xtrabackup's approach of directly copying the underlying data files — a real, practical distinction that matters directly for the RTO discussion from Part 3 (how fast can you actually recover).

---

MySQL vs PostgreSQL — A Balanced Comparison#

A genuinely common, real interview question — a strong answer names concrete, specific differences rather than vague generalities.

A genuinely balanced, senior-level interview line: "Both are excellent, mature, widely-proven relational databases — I wouldn't frame the choice as one being objectively better. MySQL's pluggable storage engine and historically simpler operational model made it extremely popular for straightforward, high-traffic web applications; PostgreSQL's richer type system and stronger standards compliance make it a common choice when the application needs more sophisticated querying or data modeling. In practice, the choice is often driven as much by existing team expertise and ecosystem/tooling familiarity as by a purely technical difference between the two."

---

Common Mistakes#

---

Worked Practice Problems#

Problem 1: A small, seemingly targeted UPDATE statement affecting only 3 rows ends up blocking every other query against a 10-million-row table for over a minute. SHOW PROCESSLIST confirms other queries are stuck in a Locked state. What's the most likely root cause, and how would you confirm and fix it?

Answer: The most likely cause is a missing index on the column used in the UPDATE's WHERE clause — without one, InnoDB must scan (and lock, row by row, as it goes) a large portion of the table to find the matching rows, holding those locks for the entire scan duration even though only 3 rows ultimately match and get modified. I'd confirm by running EXPLAIN on the exact same WHERE clause (Part 4) and checking for a Seq Scan/full table scan instead of an index-based lookup — the fix is adding an index on the filtered column, which turns the operation from a slow, broad, lock-heavy scan into a fast, narrowly-targeted update that releases its locks almost immediately.

Problem 2: A team configures MySQL replication using statement-based binlog format. Weeks later, they discover a replica's data has subtly diverged from the primary's for rows updated by a specific batch job that uses NOW() in its UPDATE statements. What happened, and how would switching to row-based replication have prevented it?

Answer: With statement-based replication, the literal SQL statement (including the NOW() function call) is replayed on the replica — but NOW() evaluates independently, at replay time, on the replica, which is inherently a moment later than when the same statement originally ran on the primary. This means the replica's rows can end up with a genuinely different timestamp value than the primary's equivalent rows, a real, silent data divergence. Row-based replication would have prevented this entirely, since it logs the actual, already-computed final data change (the specific new timestamp value that was actually written on the primary) rather than the statement that produced it — the replica simply applies that exact same value, with no possibility of re-evaluating a non-deterministic function differently.

Problem 3: A company runs a 2TB production MySQL database and currently backs it up nightly using mysqldump, which now takes over 4 hours to complete and even longer to restore during a recent disaster recovery drill, well outside their 1-hour RTO target from Part 3. What would you recommend, and why?

Answer: Switch to a physical backup tool like Percona XtraBackup instead of mysqldump — logical backups (mysqldump re-executes SQL to rebuild data, row by row) are dramatically slower to both create and restore at this scale compared to a physical backup, which directly copies the underlying InnoDB data files. XtraBackup also supports true hot/online backups with minimal impact on the live database, and its restore process (essentially copying files back into place) is far faster than logically replaying millions of INSERT statements — directly addressing both the backup-window concern and, more importantly, bringing actual restore time back within the stated 1-hour RTO target.

---

Summary and What's Next#

• MySQL's pluggable storage engine architecture is its single most distinctive design trait — InnoDB (row-level locking, full ACID transactions, the modern default) has effectively superseded MyISAM (table-level locking, no transactions) for essentially all new tables.
• The InnoDB buffer pool is MySQL's primary in-memory performance lever — sized too small, it forces disk reads for data that should be servable from memory.
• The binary log (binlog) is MySQL's specific implementation of the write-ahead-log pattern from Part 1, used for both replication and point-in-time recovery — and the choice between statement-based, row-based, and mixed formats is a real, concrete correctness tradeoff, especially around non-deterministic queries.
• Seconds_Behind_Master is MySQL's direct, named equivalent of the generic replication lag concept from Part 1 — worth monitoring as a Saturation-style leading indicator.
• SHOW PROCESSLIST is the single most important first command for diagnosing "the database is slow," directly analogous to the Linux troubleshooting toolkit's top.
• InnoDB's row-level locking still locks every row it must scan, not just the ones matching a query — a missing index on a large table can turn a small, targeted change into a severe, table-wide locking incident.
• mysqldump (logical) and XtraBackup (physical) represent the same fundamental full/incremental and RPO/RTO tradeoffs from Part 3, now grounded in MySQL-specific tooling — physical backups are the standard choice once database size makes logical backup/restore speed genuinely unacceptable.
• MySQL and PostgreSQL are both mature, excellent choices — the right one for a given system depends on concrete requirements (data types, query complexity, team expertise) rather than either being universally superior.

This completes the Databases & Storage Reliability series (SQL and NoSQL fundamentals, replication, sharding, backup/recovery, and MySQL specifics). See questions.md in this folder for the full interview question bank covering all six parts.