Topolearn

Learn MySQL

Widely deployed open-source relational database with the InnoDB engine

20 lessons · 4 hours · 4 levels · covers MySQL 8.4 LTS

MySQL is one of the most widely used open-source relational databases, powering a huge share of web applications from WordPress sites to large-scale platforms. Its default InnoDB storage engine provides transactions, row-level locking and crash recovery, and built-in replication makes it easy to scale reads. This curriculum targets the MySQL 8.4 LTS release and covers day-to-day use through to InnoDB internals and high availability.

Sign in free to track progressOfficial docs ↗

Before you start

MySQL builds on these; the tree links to the exact lessons you need.

SQLEssentials level

How deep do you want to go?

1. EssentialsRead and write simple code, do the everyday basics7 lessons · 1 hour in total
2. PracticalUse it productively at work, with the usual idioms12 lessons · 2 hours in total
3. AdvancedThe less common features, performance and trade-offs16 lessons · 3 hours in total
4. ExpertInternals and the edge cases experts know20 lessons · 4 hours in total

The MySQL tree

MySQLInstalling MySQL and the mysql clientInstalling MySQL andthe mysql clientUsers, hosts and privilegesUsers, hosts andprivilegesMySQL data typesMySQL data typesCharacter sets and collationsCharacter sets andcollationsInnoDB and the clustered primary keyInnoDB and theclustered primary k…AUTO_INCREMENT and upsertsAUTO_INCREMENTand upsertsSQL modes and strictnessSQL modes andstrictnessIndexing in InnoDBIndexing in InnoDBEXPLAIN and EXPLAIN ANALYZEEXPLAIN andEXPLAIN ANALYZETransactions and autocommitTransactions andautocommitJSON columns and multi-valued indexesJSON columns andmulti-valued indexesBackups: mysqldump and MySQL ShellBackups: mysqldumpand MySQL ShellChoosing data typesChoosing data typesPrimary keys and identity columnsPrimary keys andidentity columnsINSERT, UPDATE and DELETEINSERT, UPDATE andDELETEIndex basicsIndex basicsTransactions and ACIDTransactions andACID
Essentials and Practical shown (12 of 20 lessons); the full list is below. Click a lesson to read it.

All 20 lessons, in order

Getting started

  1. Installing MySQL and the mysql client · Essentials · 10 min

    Run a MySQL server, connect with the mysql command-line client and explore databases and tables.

  2. Users, hosts and privileges · Essentials · 12 min

    Create accounts as user@host, grant least privileges, use roles, and understand the default authentication plugin.

Types and schema

  1. MySQL data types · Essentials · 12 min

    Choose integer sizes, DECIMAL, VARCHAR vs TEXT, and DATETIME vs TIMESTAMP correctly in MySQL.

  2. Character sets and collations · Essentials · 10 min

    Use utf8mb4 everywhere, understand collations like utf8mb4_0900_ai_ci, and avoid the legacy utf8mb3 trap.

  3. InnoDB and the clustered primary key · Essentials · 12 min

    InnoDB stores each table in primary-key order, so your choice of primary key shapes storage and performance.

  4. AUTO_INCREMENT and upserts · Essentials · 12 min

    Generate keys with AUTO_INCREMENT, read them with LAST_INSERT_ID(), and upsert with ON DUPLICATE KEY UPDATE.

  5. SQL modes and strictness · Essentials · 8 min

    Know what STRICT_TRANS_TABLES and ONLY_FULL_GROUP_BY do, and why you should never turn them off.

  6. JSON columns and multi-valued indexes · Practical · 12 min

    Store and query JSON with ->, ->>, JSON_TABLE and JSON_SET, and index it with generated columns or multi-valued indexes.

Indexes and query performance

  1. Indexing in InnoDB · Practical · 14 min

    Create secondary, composite, prefix, functional, descending and invisible indexes in MySQL.

  2. EXPLAIN and EXPLAIN ANALYZE · Practical · 14 min

    Read MySQL's EXPLAIN columns (type, key, rows, Extra) and the tree output of EXPLAIN ANALYZE.

  3. Partitioning in MySQL · Advanced · 12 min

    Partition InnoDB tables by RANGE, LIST, HASH or KEY, and know the unique-key and foreign-key restrictions.

  4. Optimizer statistics, histograms and hints · Expert · 15 min

    Fix bad plans with persistent index statistics, column histograms, optimizer hints and the optimizer trace.

Transactions and locking

  1. Transactions and autocommit · Practical · 12 min

    Use START TRANSACTION, understand autocommit, implicit commits from DDL, and InnoDB's Repeatable Read default.

  2. InnoDB locking: record, gap and next-key locks · Advanced · 15 min

    How InnoDB locks index records and gaps, why that blocks inserts, and how to diagnose deadlocks.

Backup, replication and HA

  1. Backups: mysqldump and MySQL Shell · Practical · 12 min

    Take consistent logical backups with mysqldump --single-transaction or MySQL Shell's parallel dump utilities.

  2. Online schema changes · Advanced · 14 min

    Use ALGORITHM=INSTANT and INPLACE with LOCK=NONE, understand metadata locks, and when to use gh-ost or pt-osc.

  3. Binlog replication and GTIDs · Advanced · 15 min

    Set up source-replica replication with GTIDs, choose row-based binlogs, and monitor lag.

  4. Group Replication and InnoDB Cluster · Expert · 15 min

    Highly available MySQL with Group Replication's consensus, InnoDB Cluster, MySQL Router and automatic failover.

InnoDB internals

  1. Buffer pool, redo log and durability · Expert · 15 min

    How InnoDB caches pages, writes the redo log and doublewrite buffer, and what innodb_flush_log_at_trx_commit trades.

  2. MVCC, undo logs and purge · Expert · 15 min

    How InnoDB builds consistent reads from undo logs, and why long transactions grow the history list.