Source
MySQL
History of MySQL, LAMP stack, evolution of versions and key features.
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
The first release of MySQL is published (May 23, 1995), the project begins to quickly gain popularity in web workloads.
MySQL AB and dual licensing
The MySQL AB company is being formed and the GPL model + commercial license for enterprise scenarios is being formed.
MySQL 4.0
The platform for mass web-use-case is being stabilized; MySQL is anchored as part of the LAMP stack.
MySQL 5.0
Stored procedures, triggers and views appear - an important step towards a more mature SQL model.
Purchase of Sun Microsystems
Sun acquires MySQL AB and gains control of one of the key open-source SQL products.
Fork in MariaDB and move to Oracle
MariaDB fork launches amid Sun/Oracle deal; in 2010, Oracle completed its purchase 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
Added JSON functions, generated columns and optimizer improvements for mixed workload.
MySQL 8.0
Major technology update: CTE, window functions, transactional data dictionary and further modernization of the kernel.
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 system layer.
Additional subsystems work around the core layers and provide reliability, replication, and observability.
Logs
Observability
Replication
DDL vs DML: how the request goes
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 + 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)
Transaction engine with support for ACID and foreign keys; in MySQL 5.5 the default storage engine is InnoDB.
MyISAM (legacy)
One of the built-in MySQL storage engines, available along with other engines.
NDB Cluster
Engine for MySQL Cluster: shared-nothing clustering.
Documentation
Vitess: sharding for MySQL
How Vitess divides keyspace into shards and routes requests to MySQL.
Scaling: replication, Cluster, Vitess
Replication
Built-in replication supports asynchronous and semi-sync scenarios to scale reads.
MySQL Cluster (NDB)
Shared-nothing clustering via MySQL Cluster based on the NDB engine.
Vitess
Sharding layer: The key space can be divided into sharded MySQL databases, where each shard has a primary and replicas.
Architecture option with Vitess: requests go through a routing layer to several MySQL shares.
Clients
Applications
Routing
VTGate
MySQL
Shards
Primary + replicas