What is MySQL?
MySQL is the world's most popular open-source relational database management system (RDBMS). Originally created by Michael Widenius and David Axmark in 1995, MySQL has powered the backend of countless web applications — from small blogs to massive platforms like Facebook, Twitter, and YouTube. The name combines "My" (the first name of co-founder Widenius's daughter) with "SQL" (Structured Query Language).
In 2008, Sun Microsystems acquired MySQL AB, and in 2010 Oracle Corporation acquired Sun, making Oracle the current steward of MySQL. Despite corporate ownership, the Community Edition remains free and open-source under the GNU General Public License.
What is a Relational Database?
A relational database organizes data into tables — think of spreadsheets — where each table represents one type of entity. Tables are linked through relationships defined by foreign keys. This relational model was formalized by Edgar F. Codd at IBM in 1970 and remains the dominant paradigm for structured data storage.
Tables (Relations): Data is stored in rows and columns. Each table represents a single entity type (users, orders, products).
Rows (Tuples): Each row is one record — a single user, one order, one product.
Columns (Attributes): Each column stores one property, like a user's email or an order's total amount.
Primary Key: A column (or combination) that uniquely identifies each row in a table.
Foreign Key: A column that references the primary key of another table, creating a relationship.
Schema: The structure definition — table names, column names, data types, and constraints.
MySQL Version History
Understanding MySQL's evolution helps you know which features are available in your version and why certain behaviors exist:
Version | Year | Key Additions |
|---|---|---|
3.x | 1996–2000 | Basic SQL, MyISAM engine, initial release |
4.0 | 2003 | InnoDB transactions, UNION, subquery support |
5.0 | 2005 | Stored procedures, triggers, views, cursors, information_schema |
5.5 | 2010 | InnoDB becomes the default engine, semi-sync replication, PERFORMANCE_SCHEMA |
5.6 | 2013 | Full-text search in InnoDB, online DDL, GTID replication, memcached API |
5.7 | 2015 | JSON data type, generated columns, sys schema, improved optimizer |
8.0 | 2018 | Window functions, CTEs, roles, invisible indexes, atomic DDL, descending indexes |
8.4 LTS | 2024 | Long-term support release, GTID and replication refinements, improved defaults |
MySQL Server Architecture
MySQL follows a layered client-server architecture. The MySQL Server (the mysqld daemon) runs as a background service and handles all database operations. Clients connect over TCP/IP or a Unix socket using the MySQL wire protocol.
The server is organized into three main layers:
Layer | Components | Responsibility |
|---|---|---|
Connection Layer | Thread manager, authenticator, connection cache | Accepts client connections, authenticates users, assigns a thread per connection |
SQL Layer | Parser, analyzer, optimizer, executor | Parses SQL text, validates syntax, builds and costs execution plans, runs queries |
Storage Engine Layer | InnoDB, MyISAM, MEMORY, CSV, ARCHIVE, NDB | Physical on-disk storage, row format, transactions, locking, crash recovery |
How a query flows through the layers:
- Client sends SQL text over the wire
- Connection Layer authenticates the user and checks host access
- SQL Parser tokenizes and parses the statement into an internal AST
- Analyzer validates tables, columns, and privileges
- Query Optimizer evaluates possible execution plans using table statistics and index cardinality; picks the lowest-cost plan
- Executor asks the Storage Engine to fetch the required rows
- Storage Engine reads data from its buffer pool (memory) or disk pages
- Results flow back through the executor to the client
MySQL vs PostgreSQL vs SQLite vs MongoDB
Feature | MySQL 8.0 | PostgreSQL 16 | SQLite 3 | MongoDB 7 |
|---|---|---|---|---|
Type | Relational (RDBMS) | Object-Relational (ORDBMS) | Embedded relational | Document (NoSQL) |
License | GPL / Commercial | PostgreSQL (permissive) | Public Domain | SSPL / Commercial |
ACID Transactions | Yes (InnoDB) | Yes (full) | Yes | Yes (multi-document, v4.0+) |
Foreign Keys | Yes (InnoDB) | Yes | Yes (enforced since 3.26) | No native FK |
JSON Support | JSON type (5.7+) | JSONB (superior, indexed) | Text column workaround | Native BSON — primary data model |
Full-Text Search | Basic (no relevance tuning) | Advanced (tsvector, ranking) | Basic FTS extension | Atlas Search (separate service) |
Window Functions | Yes (8.0+) | Yes (mature) | Yes (3.25+) | Aggregation pipeline |
CTEs / Recursive | Yes (8.0+) | Yes (mature) | Yes (3.35+) | Limited via $graphLookup |
Replication | Async / semi-sync / Group | Streaming logical | None (file-based) | Replica sets, sharding |
Scaling | Read replicas, Vitess sharding | Citus / logical replication | Single process, single file | Built-in horizontal sharding |
Best for | Web apps, LAMP/LEMP stack | Complex SQL, GIS, analytics | Embedded, mobile, testing | Flexible schema, document storage |
Cloud MySQL Options
Service | Provider | Notes |
|---|---|---|
Amazon RDS MySQL | AWS | Managed MySQL 5.7 / 8.0. Easy ops, Multi-AZ failover, automated backups. |
Amazon Aurora MySQL | AWS | MySQL-compatible with up to 5x throughput, 6-way replication across 3 AZs, serverless option. |
Google Cloud SQL | GCP | Managed MySQL 5.7 / 8.0. Regional replicas, automated storage scaling. |
Azure Database for MySQL | Azure | Managed MySQL with Flexible Server option for zone-redundant HA. |
PlanetScale | Independent / Vitess | MySQL-compatible serverless DB. Schema branching workflow, horizontal sharding via Vitess. |
TiDB Cloud | PingCAP | MySQL-compatible distributed SQL. Handles OLTP and OLAP on the same cluster. |
Vitess (self-hosted) | Open source | Database clustering system that scales MySQL horizontally. Used at YouTube, Slack, GitHub. |
When to Choose MySQL
You're building a standard web application (CMS, e-commerce, SaaS) — MySQL's read performance and replication are battle-tested at massive scale.
Your team has existing MySQL expertise or you're using a LAMP/LEMP stack.
You need simple, fast reads with straightforward queries and want broad hosting support.
You're deploying on PlanetScale (MySQL-compatible), AWS Aurora MySQL, or Google Cloud SQL.
Your application uses an ORM (Laravel Eloquent, Rails ActiveRecord, Django ORM) that targets MySQL.
When to Choose PostgreSQL Instead
You need advanced SQL: complex window function frames, LATERAL joins, array types, range types, or custom data types.
You're doing geospatial work — PostGIS is best-in-class for geographic queries.
You want superior JSONB support for semi-structured data stored alongside relational tables with full indexing.
You need more powerful stored procedures using PL/pgSQL, PL/Python, or PL/JavaScript.
You require table inheritance, custom aggregate functions, or advanced index types (GiST, GIN, BRIN).
MySQL Editions
Edition | Cost | License | Key Features |
|---|---|---|---|
Community Edition | Free | GPL v2 | Full MySQL feature set, community support only |
Standard Edition | Paid | Commercial | High availability, monitoring, support SLA |
Enterprise Edition | Paid | Commercial | Thread pool, audit plugin, encryption, firewall, technical account manager |
Cluster CGE | Paid | Commercial | NDB Cluster for 99.999% uptime, in-memory storage, geographic replication |
For the vast majority of developers and companies, the Community Edition is all you need. It powers high-traffic sites handling billions of requests per day.
The MySQL Ecosystem
MariaDB: A community fork created by MySQL's original author after the Oracle acquisition. Wire-compatible with MySQL but adds its own extensions (columnar engine, window functions backported to 10.x). The default in many Linux distributions.
Percona Server: A hardened MySQL-compatible server with additional performance monitoring and operational features. Popular in high-performance self-hosted deployments.
Percona Toolkit: A collection of command-line tools (pt-query-digest, pt-online-schema-change, pt-duplicate-key-checker) that every MySQL DBA should have installed.
AWS Aurora MySQL: Cloud-native MySQL-compatible database with up to 5x throughput over standard MySQL, automatic storage scaling, and 6-way replication across 3 availability zones.
PlanetScale: Serverless MySQL-compatible platform built on Vitess. Offers database branching (like git for schemas) and horizontal sharding without downtime.
Vitess: A database clustering system that scales MySQL horizontally. Used originally at YouTube and now powering large-scale deployments at Slack and GitHub.
Primary Use Cases
Web Applications: MySQL is the "M" in the LAMP stack (Linux, Apache, MySQL, PHP). WordPress, Drupal, Magento, and most PHP frameworks use MySQL by default. Virtually every web hosting provider offers MySQL.
E-Commerce: E-commerce requires reliable transactions — orders, payments, inventory. InnoDB's ACID guarantees ensure that when a customer places an order, either all related records are saved together or none are — preventing corrupt partial data.
SaaS Applications: Multi-tenant SaaS applications use MySQL with a shared schema (all tenants distinguished by tenant_id) or a separate-schema approach (one database per tenant). PlanetScale has made MySQL-compatible databases attractive for modern SaaS.
Analytics and Reporting: MySQL handles analytical queries well for small-to-medium datasets. For heavy analytics workloads, teams typically replicate to a columnar store (ClickHouse, Redshift, BigQuery) for aggregations and use read replicas for reporting.
When NOT to Use MySQL
Graph relationships: For highly connected data (social networks, recommendation engines), a graph database like Neo4j handles deep relationship traversal far more efficiently.
Time-series data: For IoT sensor data or infrastructure metrics, specialized databases like InfluxDB or TimescaleDB offer far better storage efficiency and query performance.
Full-text search at scale: For Elasticsearch-quality search with relevance ranking, facets, and autocomplete, use Elasticsearch or OpenSearch alongside MySQL.
Massive analytics: Querying billions of rows with complex aggregations is best handled by columnar stores like ClickHouse, DuckDB, or BigQuery.
Highly dynamic schemas: If data structure changes frequently and unpredictably, a document store like MongoDB or PostgreSQL with JSONB may be a better fit.
How a Query Flows Through MySQL Architecture
Understanding the internal path of a query helps you diagnose problems and optimize effectively:
Step | Layer | What Happens |
|---|---|---|
1 | Connection Layer | Client opens a TCP connection or Unix socket. MySQL creates a thread for it. |
2 | Connection Layer | Username + password + host are checked against mysql.user. Access denied or allowed. |
3 | SQL Layer — Parser | SQL text is tokenized and parsed into an abstract syntax tree (AST). Syntax errors are caught here. |
4 | SQL Layer — Analyzer | Table names, column names, and user privileges are verified. Semantic errors are caught here. |
5 | SQL Layer — Optimizer | The optimizer generates candidate execution plans, estimates their cost using table statistics and index cardinality, and picks the cheapest plan. |
6 | SQL Layer — Executor | The executor walks the chosen plan and calls the storage engine API to fetch pages. |
7 | Storage Engine | InnoDB checks the buffer pool first. If the page is cached, it returns from memory. If not, it reads from disk into the buffer pool. |
8 | Connection Layer | Result rows are sent back to the client over the wire in the MySQL protocol format. |
MySQL Licensing and Dual Licensing
MySQL uses a dual license model:
- GPL v2 — Open source license. If you distribute software that links with MySQL's GPL client libraries, your software must also be open source under GPL. This affects applications that ship MySQL embedded.
- Commercial license — Paid Oracle license. Allows distributing closed-source software that includes MySQL. Required for proprietary ISV applications shipping with MySQL.
For almost all web application developers, neither license is relevant — you are using MySQL as a service, not embedding or distributing it, so the GPL does not apply to your application code.
MySQL Architecture Diagram (Text)
Client (application, mysql CLI, Workbench)
|
| TCP/IP or Unix socket
|
+---v----------------------------------+
| Connection Layer |
| - Thread manager |
| - Authentication (mysql.user table) |
| - Connection cache |
+---v----------------------------------+
| SQL Layer |
| - Parser (SQL -> AST) |
| - Analyzer (validate names/privs) |
| - Optimizer (cost-based plan) |
| - Executor (calls storage engine) |
+---v----------------------------------+
| Storage Engine API |
+---v-----------+----------------------+
| InnoDB | MyISAM | MEMORY | ...
| - Buffer pool | Table | Hash |
| - Redo log | cache | index |
| - Row locks | File | |
| - MVCC | locks | |
+---------------+--------+--------+
|
| Disk I/O
|
+---v------------------+
| Files |
| .ibd (table data) |
| ib_logfile0/1 (redo) |
| mysql-bin.* (binlog) |
+----------------------+MySQL on Different Operating Systems
OS | Recommended Install Method | Notes |
|---|---|---|
Ubuntu / Debian | apt-get install mysql-server or MySQL APT repository | System MySQL; or use official MySQL APT repo for latest version |
RHEL / CentOS / Amazon Linux | MySQL YUM repository or dnf | Official MySQL RPM packages for production stability |
macOS | Homebrew: brew install mysql | Easy updates; or use MySQL.pkg installer from mysql.com |
Windows | MySQL Installer (mysql.com/downloads) | Installs MySQL Server, Workbench, Shell, and Connector packages |
Docker | docker pull mysql:8.0 | Fast for development; use named volumes for data persistence |
Any (dev) | Docker Compose | Reproducible dev environment; can pin exact MySQL version |
# Quick Docker setup for development docker run --name mysql-dev -e MYSQL_ROOT_PASSWORD=secret -e MYSQL_DATABASE=myapp -p 3306:3306 -v mysql_data:/var/lib/mysql -d mysql:8.0 # Connect mysql -h 127.0.0.1 -u root -psecret myapp
MySQL Tools Ecosystem
Tool | Category | Purpose |
|---|---|---|
MySQL Workbench | GUI client | Official GUI for schema design, query editing, and server administration |
DBeaver | GUI client | Open-source cross-database GUI; supports MySQL, Postgres, SQLite, and many others |
TablePlus | GUI client | Fast native GUI for macOS/Windows; excellent for daily development work |
mysql CLI | CLI client | Built-in command-line client for scripting and quick queries |
mycli | CLI client | MySQL CLI with auto-completion, syntax highlighting, and smart query history |
mysqlcheck | Admin tool | Check, repair, and optimize tables from the command line |
mysqldump | Backup | Logical backup (SQL dump) of databases or tables |
mysqlpump | Backup | Parallel backup tool (faster than mysqldump for large databases) |
Percona Toolkit | Ops tools | pt-query-digest, pt-online-schema-change, pt-duplicate-key-checker, and more |
mysqltuner.pl | Tuning | Analyzes running instance and gives evidence-based configuration recommendations |
Your First MySQL Query
-- Greet the database server SELECT 'Hello, MySQL!' AS greeting; -- Check the server version SELECT VERSION(); -- See all databases on the server SHOW DATABASES; -- Select a database to work with USE my_database; -- See what tables exist in the current database SHOW TABLES; -- Check which user you are logged in as SELECT CURRENT_USER(); -- Show the current database SELECT DATABASE(); -- Show the current time and date from the database server SELECT NOW(), CURDATE(), CURTIME();
MySQL vs PostgreSQL — Choosing the Right Tool
Both are excellent production databases. The honest answer is that for most web applications either would work well. Here are the cases where one clearly wins:
Choose MySQL when... | Choose PostgreSQL when... |
|---|---|
Your stack is LAMP/LEMP (WordPress, Laravel, Rails) | You need PostGIS for geographic queries |
Your team already knows MySQL deeply | You need JSONB with GIN indexes for semi-structured data |
You want PlanetScale, Vitess, or Aurora compatibility | You need complex window functions with advanced frames |
Simplicity and tooling breadth matter more than advanced SQL | You want table inheritance or custom aggregate functions |
You need a cloud-managed database with broad support | You need logical replication to multiple subscribers |
Your ORM/framework defaults to MySQL | Your team is comfortable with Postgres-specific SQL extensions |
Storage Engines Overview
Engine | Transactions | Row Locking | Use Case |
|---|---|---|---|
InnoDB | Yes (ACID) | Yes | Default for all production tables — use this |
MyISAM | No | Table-level only | Legacy read-only tables; avoid for new work |
MEMORY | No | Table-level | Fast in-memory temporary tables; data lost on restart |
CSV | No | No | Storing data as CSV files; useful for imports |
ARCHIVE | No | Row-level insert only | Compressed storage for infrequently accessed historical data |
BLACKHOLE | No | No | Replication relay — writes are accepted but not stored |
NDB Cluster | Yes | Row-level | High-availability distributed MySQL Cluster (Enterprise) |
-- Check the engine for each table in your database SELECT TABLE_NAME, ENGINE, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() ORDER BY TABLE_NAME; -- Convert a MyISAM table to InnoDB ALTER TABLE old_table ENGINE = InnoDB;
Market Position and Adoption
MySQL consistently ranks as the #1 or #2 most popular database according to DB-Engines ranking (alongside PostgreSQL). Key adoption facts:
- Over 1 billion deployments estimated across the internet
- Powers the majority of WordPress sites (over 800 million WordPress installations)
- Used by companies like Facebook, Twitter/X, YouTube, Netflix, Airbnb, and Shopify at massive scale
- Default database in most shared web hosting environments
- Native support in virtually every programming language ecosystem
MySQL's dominance in web applications is partly historical (the LAMP stack from the early 2000s) and partly practical — it is fast, well-documented, and every web developer eventually encounters it. Even teams that use PostgreSQL or MongoDB for new projects often maintain MySQL systems from earlier eras.
What You Should Learn Next
Installation: Set up MySQL on your machine or use a Docker container for a quick start.
Connecting: Learn how to connect with the mysql CLI, a GUI tool, or a programming language driver.
CREATE DATABASE and CREATE TABLE: Define your first schema with proper data types and constraints.
SELECT, INSERT, UPDATE, DELETE: The four fundamental DML operations that drive every application.
Indexes: Understanding indexes is the single most important skill for MySQL performance.
EXPLAIN: Learn to read query execution plans to diagnose and fix slow queries.
Transactions and ACID: How InnoDB ensures data integrity even during crashes.