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.
Source
MySQL
History of MySQL, the LAMP stack, storage engines, replication, and scaling ecosystem.
MySQL is one of the most popular relational DBMSs. It started out as a simple and fast engine, became part of the LAMP stack, and then received transactional capabilities, a flexible storage engines model, and a scaling ecosystem.
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 becomes more mature for mainstream web applications, and MySQL becomes a fixture of the LAMP stack.
MySQL 5.0
Stored procedures, triggers, and views arrive, making MySQL a more capable SQL platform.
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
Improved replication and performance capabilities (including GTID and InnoDB performance).
MySQL 5.7
JSON functions, generated columns, and optimizer improvements expand support for mixed workload profiles.
MySQL 8.0
A major update adds CTEs, window functions, a transactional data dictionary, and more kernel modernization.
New release model
The LTS line (8.4) appears, and development proceeds through innovation branches and subsequent major releases.
MySQL in a LAMP stack
LAMP is a 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
The classic MySQL architecture divides responsibility between the client layer, the SQL layer, storage engines, and the OS/file-system layer.
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, while DML works with data and indexes. Below is a visualization of the key 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
MySQL supports multiple storage engines, allowing you to choose the right one for different types of tables.
InnoDB (default)
Transactional storage engine with ACID support, foreign keys, Redo/Undo logs, and clustered indexes.
MyISAM (legacy)
A historical storage engine without full transactional guarantees; mostly useful for understanding MySQL's evolution.
NDB Cluster
The storage engine behind MySQL Cluster, designed for distributed storage without shared disks.
Documentation
Vitess: sharding for MySQL
How Vitess splits keyspace into shards and routes requests to MySQL.
Scaling: replication, Cluster, Vitess
Replication
Built-in replication supports asynchronous and semi-synchronous modes for read scaling and failover preparation.
MySQL Cluster (NDB)
A cluster option based on NDB, where data is distributed across nodes without shared storage.
Vitess
A routing and sharding layer that splits keyspace into MySQL shards, each with a primary and replicas.
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.