MySQL matters not only as a classic of the LAMP era. It shows how a mass-market database evolves through storage engines, replication, and platform layers such as Vitess.
In engineering practice, this chapter helps connect InnoDB behavior, clustered indexes, read replicas, and replication lag to a concrete workload profile.
In interviews and design reviews, it helps you speak honestly about the limits: where MySQL fully handles the OLTP problem, and where multi-region scale, heavy analytics, or stricter horizontal growth needs point elsewhere.
Practical value of this chapter
Engine-aware design
Model tables, keys, and transactions with InnoDB and clustered index behavior in mind.
Read scaling strategy
Design read replicas and failover topology with replication lag and read-consistency needs explicitly tracked.
Schema evolution
Plan online migrations and backward-compatible changes to avoid production instability under heavy load.
Interview position
Explain when MySQL is sufficient and when alternatives are needed for multi-region or analytics-heavy workloads.
Decision frame and editorial focus
Chapter focus
MySQL storage-engine evolution and relational workload scaling
Workload profile
Look at the critical user path: transactions, key-based reads, indexes, p95/p99 latency, and recovery behavior.
Good fit
The chapter should answer why this engine fits an operational/OLTP path, cache tier, or write-heavy model.
Boundary and risk
Avoid universal claims: every engine has a price in consistency, migrations, memory, indexing, or operational discipline.
Connect next
Compare against the database-selection framework, replication/sharding, and neighboring operational engines.
Source
MySQL
History of MySQL, the LAMP stack, storage engines, replication, and scaling ecosystem.
MySQL started out as a fast engine for the web and was long known for read speed rather than strict guarantees. Transactions, foreign keys, and predictable behavior under load came later, with InnoDB. So the choice here almost always comes down to the storage engine and to how the platform will scale once a single machine is no longer enough.
History and development milestones
First release of MySQL
MySQL's first release ships on May 23, 1995, and the project quickly gains traction in web applications.
MySQL AB and dual licensing
MySQL AB formalizes a model that combines GPL distribution with commercial licensing for enterprise use.
MySQL 4.0
The platform grows up enough for mainstream web applications and locks in as the M in the LAMP stack (Linux, Apache, MySQL, PHP).
MySQL 5.0
Stored procedures, triggers, and views arrive — logic can live closer to the data instead of only in the application, and the SQL model grows noticeably more capable.
Purchase of Sun Microsystems
Sun acquires MySQL AB and gains control of one of the key open-source SQL products.
MariaDB fork and move to Oracle
MariaDB is launched during the Sun/Oracle deal; in 2010, Oracle completes its acquisition of Sun.
InnoDB becomes default
In the MySQL 5.5 branch, the InnoDB engine becomes the default engine, strengthening ACID positioning.
MySQL 5.6
Replication and operations catch up: global transaction identifiers (GTID) make failover and topology changes easier, and InnoDB performance improves.
MySQL 5.7
JSON functions, generated columns, and optimizer improvements arrive — the relational database starts handling mixed workload profiles more gracefully.
MySQL 8.0
A major step: common table expressions (CTEs), window functions, and a transactional data dictionary — analytical queries no longer need workarounds, and metadata stops living apart from transactions.
New release model
The LTS line (8.4) appears, and development proceeds through innovation branches and subsequent major releases.
MySQL in a LAMP stack
Much of MySQL's popularity grew out of LAMP — the classic stack for web applications: Linux + Apache + MySQL + PHP/Perl/Python.
Linux
Operating system for servers and web hosting.
Apache
An HTTP server that serves web requests.
MySQL
Relational database for storing and processing data.
PHP / Perl / Python
Languages for server logic and application templates.
MySQL architecture by layers
MySQL divides responsibility between the client layer, the SQL layer, storage engines, and the OS and file-system layer. The boundary between the SQL layer and the engine is the key one: it is what lets you change a table's engine without rewriting the application's queries.
Additional subsystems work around the core layers and provide reliability, replication, and observability.
Logs
Observability
Replication
DDL and DML: how a request flows
DDL changes the schema and metadata; DML works with data and indexes. The difference matters in practice: a heavy DDL on a large table can block writes for a long time, which is exactly why such changes are moved to an online mode. Below are the stages for both types of requests.
How a request flows through MySQL
Comparing the execution chain for DDL (schema) and DML (data)
Active step
1. Parse and optimize
The optimizer builds an execution plan and selects indexes.
Data operations
- DML works with data and indexes without changing structure.
- The main pressure is on cache, logs, and row locks.
- Performance is often improved via indexing and plan selection.
Evolution of storage engines
The storage engine in MySQL is chosen per table and decides whether that table gets transactions, row-level locking, and crash resilience. Getting it wrong is expensive: changing the engine on a live table means a full data rewrite under load.
InnoDB (default)
Transactional storage engine: it gives ACID guarantees, foreign keys, Redo/Undo logs for crash recovery, and clustered indexes. It is the default for almost every new table.
MyISAM (legacy)
A historical engine with no transactions and table-level locking on writes. It is risky on live data; keep it around mostly to understand where MySQL came from.
NDB Cluster
A distributed engine behind MySQL Cluster: nodes share no disk and coordinate data among themselves. The price for that fault tolerance is a separate operational model that looks little like a single-node MySQL.
Documentation
Vitess: sharding for MySQL
How Vitess splits keyspace into shards and routes requests to MySQL.
Scaling: replication, Cluster, Vitess
Replication
Built-in replication offloads reads and prepares failover. The asynchronous mode is faster but lets replicas lag; the semi-synchronous mode narrows the data-loss window at the cost of write latency.
MySQL Cluster (NDB)
An NDB cluster spreads data across nodes with no shared storage and survives the loss of a node. In return it demands different operations and does not fit every workload profile.
Vitess
A routing and sharding layer on top of ordinary MySQL: the keyspace is split into shards, each with its own primary and replicas. This is the path once data no longer fits on one server and you need horizontal partitioning.
Architecture option with Vitess: requests go through a routing layer to multiple MySQL shards.
Clients
Applications
Routing
VTGate
MySQL
Shards
Primary + replicas
Related chapters
- Database Selection Framework - How to position MySQL among other database options based on workload profile, SLA constraints, and operational complexity.
- PostgreSQL: history and architecture - A direct comparison of two major OLTP paths: extensibility model, transactional semantics, and production trade-offs.
- Replication and sharding - Scaling practice for MySQL with read replicas, failover strategy, sharding patterns, and rebalancing.
- Introduction to Data Storage - How storage decisions influence API contracts and architecture evolution as product and load grow.
- Designing Data-Intensive Applications, 2nd Edition (short summary) - Conceptual foundation for transaction, replication, and consistency decisions behind SQL architecture choices.
- Database Guide (short summary) - A practical SQL and DBMS-architecture companion that enriches the MySQL overview with applied engineering context.