Lesson 01 beginner

Introduction to MySQL

What a relational database is, where MySQL came from and why so much of the web runs on it, how the LTS and Innovation release tracks work, how a MySQL server is organised into databases, tables and storage engines, how MySQL relates to MariaDB, Percona Server and TiDB, and the Orbit database you will build in this course.

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.

idemailname
1ana@orbit.devAna Silva
2ben@orbit.devBen 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:

EditionLicenseWhat you get
CommunityGPL v2, freeThe full server, client tools, InnoDB, replication, Group Replication, MySQL Shell and Router
EnterpriseCommercialCommunity 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:

TrackPurposeSupport
LTS (long-term support)Production systems that want stability. Only fixes, no behaviour changes, within a seriesAbout 5 years of premier support plus 3 of extended support
InnovationNew features about every quarter. Production quality, but syntax and defaults may changeUntil 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 orbit and CREATE DATABASE orbit do 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:

DatabaseWhat it holds
mysqlAccounts, privileges, time zone tables and other server metadata
information_schemaRead-only views describing every database, table, column and index
performance_schemaLive instrumentation: running statements, waits, locks, memory
sysFriendly 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:

LessonsYou build
2 to 3A running server and a comfortable mysql client setup
4 to 6The orbit database, its tables, the right data types and the first rows
7 to 11The queries Orbit’s API runs: filtering, joins, reports and functions
12 to 14Constraints, indexes that keep queries fast and safe transactions
15 to 17Views, stored procedures, triggers, scheduled events, JSON settings and note search
18 to 22Application 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

TermMeaning
mysqldThe MySQL server process
mysqlThe command-line client
Database / schemaA named container of tables; the two words are synonyms
InnoDBThe default transactional storage engine
LTSLong-term support release line (8.4, 9.7)
InnovationFeature release line with short support
3306Default TCP port

Exercise

In MySQL, what is the difference between CREATE DATABASE orbit and CREATE SCHEMA orbit?

  1. None, they are synonyms and create the same object
  2. A schema lives inside a database, like in PostgreSQL
  3. CREATE SCHEMA only creates the metadata, not the data files
  4. CREATE SCHEMA is Enterprise-only
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?

  1. MyISAM, because it is faster for reads
  2. MEMORY, because it avoids disk access
  3. InnoDB, the default transactional engine
  4. CSV, so other tools can read the files
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?

  1. Jump straight to any newer Innovation release
  2. It cannot be upgraded; you must dump and reload into a new version
  3. Upgrade to 8.0 first, then to 9.7
  4. Upgrade from 8.4 to 9.7
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.

↑ ↓ navigate Enter open Esc close