Skip to content
Tutorials Center
My learning

Topics

Lesson 1 of 75 ยท Getting Started with MySQL

What Is MySQL?

Learn what MySQL is, how it differs from MariaDB and PostgreSQL, how the 8.0, 8.4 LTS and 9.x releases work, and what you can run live in this tutorial.

MySQL is an open-source relational database management system. You store data in tables made of rows and columns, and you read and change it with SQL. It first appeared in 1995, is owned by Oracle today, and sits behind a huge share of the web: content management systems, online shops, forums, SaaS back ends and countless internal tools.

This tutorial teaches MySQL as it works today, with InnoDB tables, utf8mb4 text and the modern features of the 8.x line. If you have never written SQL, the SQL tutorial is a good warm-up. Everything here builds on it and concentrates on what is specific to MySQL.

The client/server model

MySQL is a server. A long-running program called mysqld owns the data files, listens on TCP port 3306 (or a local socket) and does all the work. Your code never opens the data files itself. It connects as a client, sends SQL text, and receives result sets.

flowchart LR
  A["Your app<br/>(PHP, Python, Node)"] -->|"SQL over TCP 3306"| S["mysqld<br/>(the server)"]
  B["mysql command-line client"] -->|"SQL"| S
  C["MySQL Workbench"] -->|"SQL"| S
  S --> E["InnoDB storage engine"]
  E --> D[("Data files on disk")]

Every client talks to the same server process, which hands the data to a storage engine.

This is the biggest difference from SQLite, which is a library that reads one file inside your own process. A server gives you things a file cannot: many simultaneous users, accounts and permissions, replication to other machines, and network access.

What a MySQL server gives you

Many users Hundreds of connections work at once.
Accounts Each user gets only the rights they need.
Replication Copy data to other servers.
Storage engines InnoDB is the default and the one to use.

A file-based database cannot do most of this.

1 / 2

MySQL, MariaDB and PostgreSQL

Three names come up in almost every discussion.

  • MariaDB is a fork created in 2009 by MySQL's original author when Oracle was acquiring Sun Microsystems, MySQL's then owner. It started as a drop-in replacement. Since then the two have drifted apart: different JSON storage, different replication details, different optimizer features. Basic SQL looks the same, but you should treat them as two products.
  • PostgreSQL is a separate, older project with a different design. It has a richer type system, more SQL features and stricter defaults. MySQL is usually seen as simpler to operate and extremely well supported by hosting providers.
  • SQLite is the embedded engine. It is the one that runs inside your browser on this site.
MySQL MariaDB PostgreSQL SQLite
Runs as Server Server Server Library
Default engine InnoDB InnoDB Built in Built in
Typical use Web apps, SaaS Web apps, Linux distros Complex queries, analytics Local files, mobile, tests

None of them is "the best". MySQL is a safe default when your application is a typical web application and you want the broadest tooling and hosting choice.

Versions and the release model

MySQL's versioning changed in 2023, and it matters when you pick something to install.

  • 8.0 was the long-lived major version for years. It introduced the data dictionary, window functions, CTEs, roles and utf8mb4_0900_ai_ci as the default collation. It has reached or is reaching the end of its support window, so new projects should not start on it.
  • 8.4 LTS is a long-term support release. It gets bug and security fixes for years and changes behavior very little. This is the version to run in production.
  • 9.x "Innovation" releases come out every few months. They add features sooner, are supported only until the next one arrives, and are meant for people who want to try new things.

Version numbers matter inside the SQL too. A few features in this tutorial are marked with the version that introduced them, such as CHECK constraints in 8.0.16 or INTERSECT in 8.0.31. If a statement fails on an old server, the first thing to check is the output of SELECT VERSION();.

Managed services (Amazon RDS, Google Cloud SQL, Azure Database for MySQL) offer MySQL 8.0 and 8.4 and sometimes lag behind the newest releases by a few months. Aurora MySQL has its own engine and version numbers that map to MySQL versions.

What runs live on this site

The examples on this site run in your browser, and there is no MySQL server in your browser. The runner is sql.js, which is SQLite compiled to WebAssembly. That shapes how this tutorial is written, and we want to be honest about it:

  • Live blocks use portable SQL: SELECT, JOIN, GROUP BY, window functions, CTEs, constraints and transactions. These behave the same in MySQL and SQLite, so you can run them, change them and see real results.
  • Stand-ins are used where SQLite has a close equivalent, for example strftime() in place of DATE_FORMAT(). The lesson says so every time.
  • Read-only blocks show MySQL-only features: AUTO_INCREMENT details, storage engines, users and privileges, replication, stored procedures, EXPLAIN output and the mysql client itself. Their output is shown as text, and we mark it as what a MySQL server prints.

You will install a real server in the next lesson so you can try the read-only parts yourself. Here is a first live example. The query would run unchanged on a MySQL server:

SQL A query that runs on both MySQL and SQLite

Press Run (or Ctrl + Enter) to see the result here.

The result lists MySQL (1995), PostgreSQL (1996) and MariaDB (2009) in that order. The table is a quick reminder that three of these four products are servers.

The next block shows one place where MySQL and SQLite differ: how a server reports its identity. This is read-only because only a real MySQL server can answer it:

SQL
SELECT VERSION(), @@sql_mode, @@character_set_server;
-- On a MySQL 8.4 server the result looks like:
-- 8.4.3 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,... | utf8mb4

The version number you see will differ. The @@name syntax reads a system variable, which you will use constantly while configuring and tuning a server.

SQLite has a similar function, and you can run it. It shows the engine behind the live examples on this page:

SQL Which engine is running here?

Press Run (or Ctrl + Enter) to see the result here.

You will see a SQLite version number. It is the stand-in engine, not MySQL, which is why we keep MySQL-only syntax in read-only blocks.

How this tutorial is organized

The 14 chapters go from first connection to production topics:

  1. Getting started: install, connect, the client, your first table and SQL modes.
  2. Data types and schema design: choosing types, constraints, keys, views.
  3. Querying and changing data: MySQL's flavor of joins, grouping, CTEs, window functions and upserts.
  4. JSON, search and spatial features.
  5. Internals and performance: InnoDB, indexes, EXPLAIN, tuning.
  6. Programmable objects, transactions, security and operations.
  7. Using MySQL from code, five projects and a review chapter.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What kind of program is MySQL?

    MySQL runs as the long-lived process mysqld. Clients such as your application or the mysql command-line tool connect to it and send SQL.
  2. 2. Which release should a new production system normally use?

    LTS releases receive fixes for years and change behavior very little. Innovation releases are supported only until the next one ships.
  3. 3. What is MariaDB?

    MariaDB was forked from MySQL in 2009. Basic SQL is similar, but the products differ in many details, so treat them as separate.
  4. 4. Why are some code blocks in this tutorial read-only?

    The browser runs SQLite compiled to WebAssembly. Portable SQL runs live, while server features such as users, replication and storage engines are shown as read-only code with real output.

Summary

  • MySQL is a client/server relational database: mysqld owns the data and many clients connect to it.
  • MariaDB is a diverged fork, PostgreSQL is a different system, and SQLite is an embedded library.
  • Run 8.4 LTS in production. 8.0 is aging and 9.x Innovation releases are for early adopters.
  • On this site, portable SQL runs live in a SQLite stand-in, and every MySQL-only feature is a read-only block that says so.
  • System variables are read with @@name, and SELECT VERSION(); tells you what you are talking to.

Next: Installing MySQL and Connecting, where you set up a real server on your machine.