What is MySQL?

Almost every application has to store data that relates to other data: customers, orders, products, payments. Instead of scattering that across files, you use a database, which keeps the information correct even when many people write at the same time. MySQL is one of the best-known relational databases for the job, and you will find it on shared hosting, behind WordPress sites and under many APIs.

What is MySQL?#

MySQL is a relational database management system (RDBMS) maintained by Oracle. Relational means data lives in tables made of rows and columns, and tables link to each other through keys. The "SQL" in the name stands for Structured Query Language, the standard language for querying databases.

According to the official documentation, MySQL follows a client/server model: a multithreaded SQL server talks to several clients and libraries. It is open source under the GPL. If you cannot use the GPL, for example when embedding MySQL in a commercial product, you can buy a commercial license from Oracle.

Don't mix up the terms: SQL is the language, and MySQL is one of the programs that understand it. PostgreSQL and SQL Server also speak SQL.

How does MySQL work?#

MySQL runs as a server. Your application opens a connection (the default port is 3306), sends SQL commands and gets rows back. Four ideas explain most of its behavior.

Schema. Each table has columns with defined types, and the database rejects data that breaks the rules. Constraints such as NOT NULL, UNIQUE, primary keys and foreign keys prevent, for example, an order pointing to a customer that does not exist.

The InnoDB engine. MySQL separates the server from the storage engine. The default is InnoDB: a table created without an ENGINE clause already uses InnoDB. It follows the ACID model, with commit, rollback and crash recovery, and supports foreign keys, row-level locking and MVCC.

Transactions. A transaction groups statements that either all happen or none do. If you debit one balance and credit another, both steps apply or neither does.

MVCC. Multiversion concurrency control lets each query see a consistent version of the data, so reads and writes get in each other's way less.

Example: two tables and a query#

CREATE TABLE customers (
  id    BIGINT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE orders (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  total       DECIMAL(10, 2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers (id)
);

INSERT INTO customers (email) VALUES ('ana@example.com');
INSERT INTO orders (customer_id, total) VALUES (1, 49.90);

SELECT c.email, SUM(o.total) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.email;

The FOREIGN KEY blocks an order without a customer, and the JOIN combines the two tables in the query. The server's default character set is utf8mb4, which covers accents and emoji.

Example: using it from Node.js#

With the mysql2 driver (npm install mysql2), you open a connection pool and pass values as parameters. Never hardcode the password: read it from environment variables.

import mysql from "mysql2/promise";

const pool = mysql.createPool({
  host: process.env.MYSQL_HOST,
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DATABASE,
  connectionLimit: 10,
});

const [rows] = await pool.execute(
  "SELECT id, email FROM customers WHERE email = ?",
  ["ana@example.com"],
);
console.log(rows);

await pool.end();

The ? is a parameter: the value travels separately from the SQL text, which prevents SQL injection. Never build a query by concatenating strings.

What is MySQL used for?#

• Web applications and APIs with users, orders, permissions and reports.
• Websites and online stores, since many CMSs and e-commerce platforms use MySQL as their default database.
• Bots that store per-server settings, economy data or moderation history, especially when the data is related.
• Registration and inventory systems, where a half-finished operation is unacceptable.

When should you not use MySQL?#

• Caches and counters that change constantly: an in-memory store such as Redis answers faster in that role.
• Large files: images and videos belong in object storage. Keep only the path or URL in the database.
• Data with no fixed structure and no relations: a document database such as MongoDB can be more direct.
• Very complex analytical queries or advanced extensions: compare it with PostgreSQL.

To decide between the options, read which database to choose. If you are torn between the two most common relational databases, see PostgreSQL vs MySQL.

Which MySQL versions exist?#

MySQL has two tracks. LTS series only receive necessary fixes and do not remove features within the series. Under Oracle Lifetime Support, each LTS gets 5 years of premier support plus 3 years of extended support. Innovation series bring new features, may change or remove behavior and are supported only until the next Innovation release.

A series that reaches end of life (EOL) no longer gets updates, and Oracle's end-of-life page lists the dates for each one. For production, pick a supported LTS and upgrade from one LTS to the next without skipping any.

Before any upgrade, have a copy of your database. The guide on how to back up a database walks through it.

How to do this on Square Cloud#

On Square Cloud, MySQL hosting starts on the Standard plan; Hobby does not include databases. You create the database in the dashboard, always on a recent version. It is ready in seconds and runs in the same datacenter as your applications, with about 1 ms average latency and mandatory TLS.

You take manual snapshots whenever you want, and every plan that includes databases also has a daily automatic snapshot. A snapshot can be restored into the same database, another one of the same engine or a new database, or downloaded as a .zip. To connect, store the host, port and password the dashboard shows in your application's environment variables (such as MYSQL_HOST and MYSQL_PASSWORD from the Node.js example). Then point the driver at the CA of the downloaded certificate and restart the application.

See MySQL hosting on Square Cloud and, for the general walkthrough, how to host a database.