cd ~/tutorials/mysql

MySQL Tutorial

Learn MySQL from zero to production: install it, use the mysql client, design tables with the right data types and constraints, write queries with joins, grouping, CTEs and window functions, tune them with indexes and EXPLAIN, handle transactions and locking, program the server with views, stored procedures, triggers and events, work with JSON and full-text search, secure, back up, monitor and replicate your data, plus what changed in MySQL 8.4 and 9.7 LTS.

22 lessons · 185 min

Start the course →

This course takes you from “what is MySQL?” to running it with confidence in production. All along you build the database behind Orbit, the small notes app from the other courses on this blog: its users, notes and tags, the queries its API runs, the accounts it connects with and the backups that keep it safe.

Every lesson has SQL you can copy, results that come from the same data you created in earlier lessons, three short exercises to check what you learned and answers to the questions people ask most.

Before you start: you need a terminal. If commands like cd, ls and sudo are new to you, take the first lessons of the Linux Tutorial first. Lesson 2 shows how to install MySQL on Linux, macOS and Windows, or run it with Docker.

The examples target the two current long-term support lines, MySQL 8.4 LTS and MySQL 9.7 LTS. Almost everything also works on MySQL 8.0, and lessons point out when a feature needs a newer version. The last lesson collects what changed in 8.4 and 9.7 in one place. Keep the MySQL Cheatsheet open in another tab: it has over 200 ready-to-paste commands grouped by task.

If you already know PostgreSQL, the Postgres Tutorial builds the same Orbit database, so you can compare how the two servers solve the same problems.

ls ./lessons

  1. Introduction to MySQLWhat 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.beginner8 min readCompleted
  2. Installing MySQLInstall MySQL 8.4 or 9.7 LTS on Ubuntu, Debian, RHEL and Rocky Linux from the official repositories, on macOS with Homebrew, on Windows with the installer, or run it in Docker; then secure the fresh server, find its configuration and data directory, and create the orbit database and account used in the rest of the course.beginner7 min readCompleted
  3. The mysql Client EssentialsGet fluent with the mysql command-line client: connection options, option files and login paths so you never type passwords on the command line, client commands such as \G, \s, SOURCE and pager, SHOW statements to explore a server, running SQL files and one-off queries from scripts, and a prompt that tells you where you are.beginner7 min readCompleted
  4. Databases and TablesCreate, select and drop databases; understand character sets and collations and why utf8mb4 is the only sensible choice; design Orbit's users, notes, tags and note_tags tables with CREATE TABLE, AUTO_INCREMENT, primary and foreign keys; change tables with ALTER TABLE and RENAME TABLE; and learn TRUNCATE, DROP, temporary tables, CREATE TABLE LIKE and how identifier case works.beginner9 min readCompleted
  5. MySQL Data TypesPick the right type for every column: integers and their ranges, DECIMAL for money versus FLOAT and DOUBLE, CHAR, VARCHAR and TEXT, BINARY and BLOB, DATE, TIME, DATETIME and TIMESTAMP with time zones and the 2038 limit, BOOLEAN, ENUM and SET, BIT, JSON, UUIDs stored compactly, generated columns, and how strict SQL mode protects you from silent data loss.beginner8 min readCompleted
  6. Inserting, Updating and Deleting DataFill Orbit with data and change it safely: single and multi-row INSERT, LAST_INSERT_ID, INSERT ... SELECT, upserts with ON DUPLICATE KEY UPDATE and row aliases, INSERT IGNORE and why REPLACE is dangerous, UPDATE and DELETE with ORDER BY and LIMIT, multi-table UPDATE and DELETE with joins, affected-row counts, safe update mode and TRUNCATE.beginner8 min readCompleted
  7. Querying Data with SELECTRead data with SELECT: choose columns and expressions, filter with WHERE, IN, BETWEEN and correct AND/OR grouping, handle NULL with IS NULL and the null-safe <=> operator, sort (including custom orders with FIELD), paginate with LIMIT and keyset pagination, match text with LIKE and REGEXP under MySQL's case-insensitive collations, branch with CASE and IF, format dates, and learn the order in which a query is evaluated.beginner9 min readCompleted
  8. Joins, Subqueries and Set OperationsCombine tables the way Orbit's API needs: INNER JOIN, LEFT and RIGHT JOIN, finding rows with no match, many-to-many joins through a link table, self joins and CROSS JOIN, emulating FULL OUTER JOIN, scalar, IN and EXISTS subqueries, correlated subqueries, derived tables and LATERAL, and UNION, INTERSECT and EXCEPT.intermediate9 min readCompleted
  9. Grouping and Aggregate FunctionsSummarise data for Orbit's reports: COUNT, SUM, AVG, MIN and MAX, the difference between COUNT(*) and COUNT(col), GROUP BY and the ONLY_FULL_GROUP_BY rule, HAVING versus WHERE, conditional aggregation, GROUP_CONCAT to fold rows into lists, subtotals with WITH ROLLUP and GROUPING(), and grouping by month.intermediate7 min readCompleted
  10. CTEs and Window FunctionsWrite readable multi-step queries with common table expressions, generate series and walk hierarchies with recursive CTEs (and avoid the column-width trap), then rank, number and compare rows with window functions: ROW_NUMBER, RANK, DENSE_RANK, LAG and LEAD, running totals, shares of a total, frames and named windows, including the classic "latest row per group".intermediate10 min readCompleted
  11. Built-in Functions and VariablesA practical tour of MySQL's built-in functions: strings (CONCAT, CONCAT_WS, SUBSTRING_INDEX, LPAD, REPLACE, TRIM), numbers (ROUND, TRUNCATE, DIV, MOD), dates and times (DATE_ADD, DATEDIFF, TIMESTAMPDIFF, DATE_FORMAT, STR_TO_DATE, LAST_DAY, CONVERT_TZ), control flow (IF, IFNULL, COALESCE, NULLIF, GREATEST), CAST and the implicit conversion traps, plus user-defined variables and SELECT ... INTO.intermediate7 min readCompleted
  12. Constraints and Data IntegrityLet MySQL guard your data: NOT NULL and DEFAULT, primary keys, UNIQUE keys and how they treat NULL, CHECK constraints (enforced since 8.0.16), foreign keys with CASCADE, SET NULL and RESTRICT, the error codes each rule raises, disabling foreign key checks for bulk loads, the 8.4 rule against non-unique foreign key targets, and how to list and change constraints.intermediate8 min readCompleted
  13. Indexes and Query PerformanceMake Orbit's queries fast: how InnoDB's clustered primary key and secondary indexes work, composite indexes and the leftmost-prefix rule, covering indexes, descending, prefix, functional and invisible indexes, reading EXPLAIN (type, key, rows, Extra), EXPLAIN ANALYZE, statistics and histograms, index hints, the slow query log, and finding unused and redundant indexes with the sys schema.intermediate10 min readCompleted
  14. Transactions and LockingChange data safely with many users at once: ACID and autocommit, START TRANSACTION, COMMIT, ROLLBACK and savepoints, statements that commit implicitly, InnoDB's MVCC snapshots and the four isolation levels with REPEATABLE READ as default, locking reads with FOR UPDATE, FOR SHARE, NOWAIT and SKIP LOCKED, lock waits, deadlocks and how to diagnose them, metadata locks, and why long transactions hurt.intermediate9 min readCompleted
  15. Views, Stored Procedures and FunctionsPut logic inside MySQL: create views for reusable queries, understand MERGE and TEMPTABLE algorithms, updatable views and WITH CHECK OPTION, then write stored procedures with DELIMITER, IN and OUT parameters, local variables, IF, CASE and loops, error handlers, SIGNAL and RESIGNAL, transactions and cursors, plus stored functions, prepared statements, DEFINER security and how to list and change routines.intermediate11 min readCompleted
  16. Triggers and Scheduled EventsReact to data changes and run jobs inside MySQL: BEFORE and AFTER triggers on INSERT, UPDATE and DELETE, the NEW and OLD rows, validating and normalising data, an audit log for Orbit's notes, trigger order with FOLLOWS and PRECEDES, the limits of triggers (including foreign key cascades that do not fire them), and the event scheduler for recurring and one-time jobs.intermediate8 min readCompleted
  17. JSON and Full-Text SearchStore flexible user settings in a JSON column: build documents with JSON_OBJECT, read them with -> and ->>, JSON_EXTRACT and JSON_VALUE, change them with JSON_SET, JSON_INSERT, JSON_REPLACE and JSON_REMOVE, search arrays with MEMBER OF and JSON_CONTAINS, turn JSON into rows with JSON_TABLE, aggregate into JSON, and index it; then add word search to Orbit with FULLTEXT indexes, MATCH ... AGAINST, boolean mode and the ngram parser.intermediate9 min readCompleted
  18. Users, Privileges and SecuritySecure Orbit's database: accounts as user plus host, caching_sha2_password and the retired mysql_native_password, password policies, expiry, lockout and dual passwords for rotation, GRANT and REVOKE at global, database, table, column and routine level, roles for least privilege, SHOW GRANTS, TLS-only connections, network exposure and a security checklist.intermediate8 min readCompleted
  19. Backup, Restore, Import and ExportProtect Orbit's data: logical versus physical backups, consistent dumps with mysqldump --single-transaction, faster parallel dumps with MySQL Shell's dumpInstance and loadDump, physical backups with XtraBackup and the clone plugin, point-in-time recovery from binary logs with mysqlbinlog, testing restores, a least-privilege backup account and automation, plus CSV import and export with LOAD DATA and SELECT ... INTO OUTFILE.intermediate8 min readCompleted
  20. Server Administration and MonitoringRun MySQL day to day: option files and SET PERSIST, global and session system variables, sizing the InnoDB buffer pool and redo log, innodb_dedicated_server, the error, slow and general logs, watching connections with SHOW PROCESSLIST and KILL, the status counters and sys schema views worth graphing, table sizes from information_schema, ANALYZE, CHECK and OPTIMIZE TABLE, mysqlcheck and mysqladmin.advanced8 min readCompleted
  21. Replication, High Availability and UpgradesScale reads and survive failures: how binary log replication works, GTIDs, setting up a source and replica with CHANGE REPLICATION SOURCE TO and the clone plugin, monitoring with SHOW REPLICA STATUS, replication lag and parallel appliers, semi-synchronous replication, read_only replicas, Group Replication, InnoDB Cluster and MySQL Router, and how to upgrade safely from 8.0 to 8.4 and 9.7 LTS.advanced9 min readCompleted
  22. What’s New in MySQL 8.4 and 9.7 LTSEverything that changed across the two current LTS releases in one place: MySQL 8.4's removals and new defaults (mysql_native_password off, MASTER/SLAVE syntax gone, modern InnoDB defaults, mysqlpump removed, stricter foreign keys), then MySQL 9.7's additions (the VECTOR type, JSON duality views, the hypergraph optimizer in Community, cluster and replication improvements, the four-part version number) and the move to calendar versioning.intermediate8 min readCompleted

↑ ↓ navigate Enter open Esc close