Almost every application keeps its important data in a database: accounts, orders, messages, settings. MySQL is one of the most widely deployed databases in the world. It powers WordPress (including this blog), Wikipedia’s early years, Facebook’s and YouTube’s core storage, and countless SaaS products. This lesson explains what MySQL is, how a MySQL server is organised and how its versions work, so the commands in the next lessons make sense.
What a relational database is
A relational database stores data in tables. A table has named, typed columns and any number of rows. Tables are linked by values they share: a note row stores the id of the user who wrote it.
| id | name | |
|---|---|---|
| 1 | ana@orbit.dev | Ana Silva |
| 2 | ben@orbit.dev | Ben Carter |
You talk to the database in SQL (Structured Query Language). SQL is declarative: you describe the result you want, and the server’s optimizer works out the fastest way to get it.
SELECT name FROM users WHERE email = 'ana@orbit.dev';
You never said “open the file, read each line, compare the email”. MySQL decides whether to scan the table or use an index, and that choice can change as your data grows without any change to your query.
A database also gives you guarantees that plain files cannot:
- Transactions: a group of changes happens completely or not at all, even if the server crashes halfway.
- Constraints: rules such as “every note belongs to an existing user” are enforced for every program that writes data.
- Concurrency: hundreds of connections can read and write at once without corrupting anything.
A short history
MySQL was created by Michael Widenius and David Axmark at the Swedish company MySQL AB, with the first release in 1995. It was fast, free and easy to install, which made it the “M” in the LAMP stack (Linux, Apache, MySQL, PHP) that built much of the early web.
Sun Microsystems bought MySQL AB in 2008, and Oracle bought Sun in 2010. Oracle still develops MySQL today and ships it in two editions:
| Edition | License | What you get |
|---|---|---|
| Community | GPL v2, free | The full server, client tools, InnoDB, replication, Group Replication, MySQL Shell and Router |
| Enterprise | Commercial | Community plus extras such as Enterprise Backup, audit, data masking, the firewall, JavaScript stored programs and Oracle support |
Everything in this course works on the free Community edition. When a feature is Enterprise-only, the lesson says so.
The MySQL family
Because MySQL is open source, several close relatives exist. They speak almost the same SQL and use the same client protocol, so most of this course applies to them too:
- MariaDB: a fork started in 2009 by MySQL’s original author. It has diverged over the years (different JSON storage, authentication plugins and optimizer features), so check its documentation for newer features.
- Percona Server for MySQL: a drop-in replacement that tracks Oracle’s releases and adds monitoring and performance features.
- Managed services: Amazon RDS and Aurora, Google Cloud SQL, Azure Database for MySQL and Oracle’s HeatWave run MySQL for you.
- TiDB, Vitess, PlanetScale: distributed systems that speak the MySQL protocol so applications can scale beyond one server.
How releases work
Since 2023 MySQL ships on two tracks:
| Track | Purpose | Support |
|---|---|---|
| LTS (long-term support) | Production systems that want stability. Only fixes, no behaviour changes, within a series | About 5 years of premier support plus 3 of extended support |
| Innovation | New features about every quarter. Production quality, but syntax and defaults may change | Until the next Innovation release |
The LTS series so far are 8.4 (April 2024) and 9.7 (April 2026). MySQL 8.0, the version many servers still run, reached the end of its support in April 2026, so plan an upgrade if you still use it. Lesson 21 explains how.
MySQL 9.7 is also the last release with the old numbering. From July 2026, versions use the calendar format YY.M: 26.7 is the July 2026 release, 27.7 the July 2027 one. Upgrades must go from one LTS series to the next without skipping one: 8.4 to 9.7, then 9.7 to the following LTS.
Tip: Run SELECT VERSION(); on any server to see what you are talking to. You will do that in lesson 3.
How a MySQL server is organised
A running MySQL server is one process called mysqld (the “d” is for daemon). Clients connect to it over TCP port 3306 or a local socket file. Inside, the hierarchy is short:
mysqld (one server, one data directory)
├── database orbit ← "database" and "schema" mean the same thing
│ ├── table users
│ ├── table notes
│ ├── view, stored procedure, trigger, event ...
├── database another_app
└── system databases: mysql, information_schema, performance_schema, sys
Two details surprise people who come from PostgreSQL or SQL Server:
- In MySQL, a database and a schema are the same thing.
CREATE SCHEMA orbitandCREATE DATABASE orbitdo exactly the same. There is no extra level in between. - You can query across databases on the same server just by prefixing the table:
SELECT * FROM other_app.customers.
The four system databases are worth knowing from day one:
| Database | What it holds |
|---|---|
mysql | Accounts, privileges, time zone tables and other server metadata |
information_schema | Read-only views describing every database, table, column and index |
performance_schema | Live instrumentation: running statements, waits, locks, memory |
sys | Friendly views on top of performance_schema for everyday diagnostics |
Storage engines and InnoDB
MySQL separates the SQL layer (parsing, optimizing, privileges) from the storage engine that actually stores rows on disk. Each table names its engine. In practice you will use one:
- InnoDB is the default and the right choice for virtually every table. It supports transactions, row-level locking, foreign keys, crash recovery and stores each table ordered by its primary key.
- MyISAM was the old default. It has no transactions and locks whole tables on writes. You only meet it in legacy systems.
- MEMORY, CSV, ARCHIVE and BLACKHOLE exist for special cases you will rarely need.
Everything in this course assumes InnoDB.
The Orbit database
Orbit is a simple notes app. Over the next lessons you will build its database step by step:
| Lessons | You build |
|---|---|
| 2 to 3 | A running server and a comfortable mysql client setup |
| 4 to 6 | The orbit database, its tables, the right data types and the first rows |
| 7 to 11 | The queries Orbit’s API runs: filtering, joins, reports and functions |
| 12 to 14 | Constraints, indexes that keep queries fast and safe transactions |
| 15 to 17 | Views, stored procedures, triggers, scheduled events, JSON settings and note search |
| 18 to 22 | Application accounts, backups, monitoring, replication and upgrades |
The final schema is small on purpose: users, notes, tags and the note_tags table that links notes to tags. Small enough to keep in your head, big enough to show every idea.
Quick reference
| Term | Meaning |
|---|---|
| mysqld | The MySQL server process |
| mysql | The command-line client |
| Database / schema | A named container of tables; the two words are synonyms |
| InnoDB | The default transactional storage engine |
| LTS | Long-term support release line (8.4, 9.7) |
| Innovation | Feature release line with short support |
| 3306 | Default TCP port |
Exercise
In MySQL, what is the difference between CREATE DATABASE orbit and CREATE SCHEMA orbit?
Show answer
Answer: A. MySQL has no separate schema level. DATABASE and SCHEMA are interchangeable keywords everywhere, including SHOW DATABASES and SHOW SCHEMAS.
Exercise
Which storage engine should a new table use unless you have a very specific reason?
Show answer
Answer: C. InnoDB is the default and supports transactions, row locks, foreign keys and crash recovery. The other engines are for legacy or special cases.
Exercise
A server runs MySQL 8.4 LTS. What is the supported upgrade path to the next LTS series?
Show answer
Answer: D. In-place upgrades go from one LTS series to the next one. 8.4 to 9.7 is supported; skipping a whole LTS series is not.
Key takeaways
- MySQL is an open-source relational database developed by Oracle, free in its Community edition.
- A server (mysqld) holds databases; in MySQL “database” and “schema” are the same thing.
- Use InnoDB for every table: transactions, row locks and foreign keys.
- Production servers should run an LTS line: 8.4 or 9.7. Versions after 9.7 use calendar numbers such as 26.7.
FAQ
Is MySQL free for commercial use?
The Community edition is licensed under the GPL v2. You can run it to power any application, commercial or not, at no cost. The GPL only matters if you distribute MySQL itself inside your product; in that case either follow the GPL or buy a commercial license.
Should I choose MySQL or PostgreSQL?
Both are excellent. MySQL is simpler to operate, very fast for typical web workloads and has mature replication tooling; PostgreSQL has a richer SQL dialect and more extensions. If your framework, host or team already uses one, that is usually the best reason to pick it.
Is MariaDB the same as MySQL?
It started as a copy in 2009 and stays largely compatible for everyday SQL, but the two have diverged: authentication defaults, JSON storage, replication features and system tables differ. Treat MariaDB as a close cousin, not a drop-in clone, for anything beyond basic queries.
What happened to MySQL 5.7 and 8.0?
Both are end of life: 5.7 in October 2023 and 8.0 in April 2026. They no longer receive security fixes. Lesson 21 shows how to upgrade to 8.4 and then 9.7.