Module 1 · The server

MySQL architecture

Beginner 18 min read ⭐ The map for everything else

MySQL is two programs in one process: a server layer that understands SQL, and a storage engine that stores rows. They talk through a narrow interface. Knowing which side of that line a symptom lives on is the first move in diagnosing almost any MySQL problem.

After this lesson you can…

Trace one query from the network socket to the data file and back.
Say which component raises syntax errors, chooses indexes, and takes row locks.
Explain the thread-per-connection model and why max_connections matters.
Say what the binary log is for and why it is not the same as the redo log.

1. The mental picture

🏦

A bank branch

The server layer is the front office: a teller per customer (a thread per connection), a clerk who checks your form is filled in correctly (the parser), and a manager who decides the fastest way to fetch what you asked for (the optimizer). The storage engine is the vault: it knows where every box physically is, who currently has a box open (locks), and keeps a ledger so nothing is lost if the power fails (the redo log). The front office never touches the vault directly — it sends slips through a hatch (the handler API).

2. The life of one query

The optimizer decides; InnoDB fetches. Note that filtering and sorting can happen on either side: conditions InnoDB can use in the index are applied there; the rest are applied by the server after the row comes up — which is exactly what EXPLAIN's "Using where" and "Using index condition" tell you (lesson 13).

3. Who does what

Responsibility	Server layer	InnoDB
Authentication, privileges	✅
Parsing, syntax errors	✅
Choosing indexes and join order	✅ (optimizer)	supplies statistics
Joins, GROUP BY, ORDER BY, functions	✅
Binary log (replication, PITR)	✅
Storing rows and indexes		✅
Transactions, row locks, MVCC		✅
Caching pages (buffer pool)		✅
Crash recovery (redo/undo)		✅
Foreign keys		✅ (enforced inside InnoDB)

4. Threads and connections

By default MySQL uses one thread per client connection. Each thread has its own memory for sorting, joining and temporary tables. That makes connections comparatively expensive, and it is why max_connections (default 151) is a real limit, not a formality.

SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS    LIKE 'Threads_connected';
SHOW STATUS    LIKE 'Max_used_connections';   -- the high-water mark since startup
SHOW FULL PROCESSLIST;                        -- one row per connection and what it's doing

"Too many connections" is usually an application bug

Raising max_connections to 5,000 treats the symptom: each connection can use tens of MB under load, and thousands of threads fighting for CPU make everything slower. The fix is a connection pool in the application (or ProxySQL / MySQL Router in front), so a few dozen connections are reused by thousands of requests.

5. Two logs, often confused

Redo log — InnoDB

Physical: "change bytes X on page Y". Written before commit returns, so a crash can replay committed changes that had not reached the data files yet. Purpose: crash recovery. Circular, fixed size (lesson 07).

Binary log — server layer

Logical: the row changes (or statements) of committed transactions, in commit order. Kept as a series of files. Purpose: replication and point-in-time recovery (lessons 18–19). On by default in MySQL 8.

Why both must agree

A transaction must be in both logs or neither — otherwise a replica (fed by the binlog) and the source (recovered from redo) would disagree after a crash. MySQL coordinates them with an internal two-phase commit. That is also why the safest durability settings flush both on every commit: innodb_flush_log_at_trx_commit=1 and sync_binlog=1 (both defaults in MySQL 8).

6. Versions and forks

Line	Status	Notes
MySQL 5.7	End of life (Oct 2023)	Still widespread; no window functions or CTEs
MySQL 8.0	Widely deployed	CTEs, window functions, JSON table, instant DDL, hash joins
MySQL 8.4 LTS	Long-term support	The sensible target for new deployments; this course's reference
MySQL 9.x	"Innovation" releases	Short support windows; new features land here first
MariaDB	Fork since 2009	Compatible for basic SQL, diverged in many details (JSON, optimizer, replication)
Percona Server	Drop-in MySQL build	Extra instrumentation; ships XtraBackup

SELECT VERSION();                         -- e.g. 8.4.x
SHOW ENGINES;                             -- InnoDB should be DEFAULT
SELECT @@default_storage_engine, @@transaction_isolation;   -- InnoDB, REPEATABLE-READ

Recap

Server layer: connections, parsing, optimizer, executor, binlog. InnoDB: rows, indexes, locks, MVCC, buffer pool, redo/undo.
The handler API is the boundary; EXPLAIN's Extra column shows which side did the filtering.
Thread per connection — pool connections rather than raising max_connections.
Redo = crash recovery; binlog = replication and PITR. Both flushed on commit by default.
Target MySQL 8.4 LTS; 5.7 is end of life.

Checkpoint

1 · Which component chooses whether to use an index for a query?

The optimizer compares the estimated cost of candidate plans — full scan, each usable index, join orders — using cardinality and row-count statistics that InnoDB maintains. InnoDB then just executes the access paths it is asked for.

2 · Your app hits "Too many connections" at peak. Best first fix?

Each connection is a thread with its own memory. Thousands of them cost memory and CPU scheduling, and usually indicate the app opens a connection per request. A pool (in the driver, framework, or a proxy) caps concurrency at a sane level and removes connect overhead.

3 · What is the binary log primarily used for?

Crash recovery is the redo log's job. The binlog records committed changes in order at the server layer, which replicas replay and which you replay on top of a backup to recover to a specific moment.