MySQL Introduction

What MySQL is#

MySQL is the world's most widely deployed open-source relational database, powering everything from small hobby projects to large-scale production systems (it's the default database for a huge share of PHP and Laravel applications, WordPress sites, and countless others). It's owned by Oracle, dual-licensed under the GPL and a commercial license, with a large ecosystem of managed hosting options (Amazon RDS, PlanetScale, Google Cloud SQL) built around it.

MySQL speaks standard SQL — everything in this track's SQL pages (SELECT, joins, GROUP BY, subqueries) works unchanged against MySQL. This section covers what's specific to MySQL itself: its storage engines, data types, indexing behavior, and transaction handling.

Storage engines#

MySQL's storage engine is a pluggable layer that determines how tables actually store and retrieve data on disk — this is a MySQL-specific concept without a direct equivalent in PostgreSQL, which has one fixed storage layer.

InnoDB — the default#

InnoDB has been MySQL's default storage engine since MySQL 5.5 (2010), and for any new application it's essentially the only sensible choice. It provides:

• Transactions — COMMIT/ROLLBACK with full ACID guarantees, covered in depth on the transactions page in this section.
• Row-level locking — two transactions can update different rows of the same table concurrently without blocking each other, unlike table-level locking which serializes all writes to a table.
• Foreign key constraints — enforced referential integrity, as used in this track's orders → users/products schema.
• Crash recovery — a write-ahead log (the InnoDB redo log) lets the database replay uncommitted work and recover to a consistent state after a crash.

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

MyISAM — historical context#

MyISAM was MySQL's original default engine before InnoDB. It's simpler and was historically faster for read-heavy, single-threaded workloads, but it has no transactions, only table-level locking (a write blocks all other reads and writes on the entire table), and no foreign key enforcement. It's still occasionally seen in older systems or for specific full-text search use cases predating InnoDB's own full-text support, but there's essentially no reason to choose it for a new table today.

Installing and connecting#

MySQL ships for every major platform; on Ubuntu/Debian:

sudo apt install mysql-server
sudo systemctl start mysql

Connect with the mysql command-line client:

mysql -u root -p

Once connected, basic navigation:

SHOW DATABASES;
CREATE DATABASE shop;
USE shop;
SHOW TABLES;
DESCRIBE users;

DESCRIBE tablename (or its alias DESC) is one of the most-used commands day to day — it prints a table's columns, types, nullability, and keys at a glance without querying information_schema directly.

Common mistakes#

• Creating a table without specifying ENGINE=InnoDB on a very old MySQL version where the default wasn't InnoDB yet — modern MySQL (5.5+) defaults to InnoDB, but it's worth confirming on legacy systems with SHOW TABLE STATUS.
• Assuming MyISAM tables support transactions because they use familiar SQL syntax — a ROLLBACK against a MyISAM table simply does nothing, silently leaving partial writes in place.
• Connecting as root for application traffic instead of creating a dedicated, least-privilege MySQL user for the application to use.