# MariaDB: A Practical Guide to the Fork That Grew Up

Install "MySQL" from the package manager on many Linux distributions, type `mysql` at the prompt, and read the welcome line carefully. Quite often it says: `Welcome to the MariaDB monitor`.

So what is this database that answers to another one's name? Is it MySQL with a different logo, or something you should choose, or avoid, on purpose?

The short answer: MariaDB began as a copy of MySQL and has since become its own database, with features MySQL does not have and a few differences that matter when you move between the two.

## What MariaDB is

MariaDB is an open-source relational database. It was started in 2009 by some of the original MySQL developers, led by Michael "Monty" Widenius, when Oracle was in the process of acquiring Sun Microsystems, which owned MySQL. They took the MySQL source code and continued it as a separate project (a "fork") that would stay open source whatever happened.

Today the server is developed in the open under the GPL licence, with the non-profit MariaDB Foundation looking after the project. A company, MariaDB plc, sells support and commercial products around it.

For years MariaDB described itself as a drop-in replacement for MySQL. For old MySQL versions that was close to true. For current ones it is not: the two have grown apart in JSON storage, replication details, authentication and the optimiser. The everyday SQL you write is still nearly identical, and most MySQL drivers and tools connect to MariaDB without changes.

## What you get that MySQL does not give you

### History built into a table

A system-versioned table keeps every old version of every row, and lets you ask what the data looked like at any moment.

```sql
CREATE TABLE prices (
  product_id INT UNSIGNED NOT NULL PRIMARY KEY,
  price      DECIMAL(10,2) NOT NULL
) WITH SYSTEM VERSIONING;

INSERT INTO prices VALUES (7, 4.50);
UPDATE prices SET price = 5.00 WHERE product_id = 7;

-- What was the price at the start of the year?
SELECT price FROM prices
FOR SYSTEM_TIME AS OF TIMESTAMP '2026-01-01 00:00:00'
WHERE product_id = 7;

-- Every version, with the time each one was valid
SELECT price, ROW_START, ROW_END FROM prices
FOR SYSTEM_TIME ALL
WHERE product_id = 7;
```

Audit trails, "who changed this price" questions and undoing a bad update all become a query instead of a project.

### Rows back from a write

`RETURNING` hands you the rows a statement just wrote or removed, so you do not need a second query:

```sql
INSERT INTO customers (name, email)
VALUES ('Asha Rao', 'asha@example.com')
RETURNING id, created_at;

DELETE FROM sessions
WHERE expires_at < NOW()
RETURNING user_id;
```

### Sequences

A sequence is a number generator that is not tied to one table. It is useful for invoice numbers shared across tables, or for getting an id before you insert.

```sql
CREATE SEQUENCE invoice_seq START WITH 1000 INCREMENT BY 1;
SELECT NEXT VALUE FOR invoice_seq;
```

### Clustering in the box

MariaDB ships with Galera Cluster, a way to run several servers that all accept writes and stay in sync. A write is confirmed only after the other nodes have received it, so losing one server loses no committed data.

It is not free speed. Every write is checked against the other nodes, so write-heavy workloads do not get faster, and two clients changing the same row on different nodes can see one transaction fail and need a retry. Use it for availability, not for write scaling. You can check a cluster's health with:

```sql
SHOW STATUS LIKE 'wsrep_cluster_size';
```

### More in the free edition

Some things MySQL keeps for its paid edition are part of the standard MariaDB server, including a thread pool, which helps when many clients connect at once. MariaDB also has extra storage engines for special jobs: ColumnStore for analytics and Spider for spreading a table across servers. InnoDB remains the default and the right choice for ordinary application data.

## MariaDB or MySQL?

| | MariaDB | MySQL |
|---|---|---|
| Steward | MariaDB Foundation and MariaDB plc | Oracle |
| Licence of the server | GPL | GPL (Community), commercial (Enterprise) |
| JSON | Stored as text, with JSON functions and a validity check | Native binary `JSON` type |
| Row history | System-versioned tables | Not built in |
| `RETURNING` | Yes | No |
| Multi-writer clustering | Galera Cluster | Group Replication, InnoDB Cluster |
| Managed cloud offers | Available on some clouds, including Amazon RDS | Available on nearly every cloud |

Neither is "better". If your cloud, framework or hosting provider is built around one of them, that is usually a good enough reason to pick it.

## Where MariaDB fits, and where it does not

**A strong choice for:**

- The same web and application workloads MySQL serves
- Self-hosted servers on Linux, where it is often the packaged default
- Data that needs an audit history, using system-versioned tables
- High availability across a few servers with Galera
- Teams that prefer a community-governed project

**Look elsewhere when:**

- **Your tooling targets MySQL 8 specifically.** Some products rely on MySQL-only features such as the binary JSON type or the MySQL Shell utilities. Check before assuming MariaDB will do.
- **You want the widest choice of managed services.** MySQL and PostgreSQL are offered in more places.
- **You need rich extensions, location data or advanced index types.** PostgreSQL goes further.
- **You are scanning billions of rows for dashboards.** A dedicated column store such as ClickHouse is the simpler path.

## Using it the right way

### Use the MariaDB names

Current versions ship their tools under their own names: `mariadb` for the client, `mariadb-dump` for logical backups, `mariadb-backup` for physical ones. The old `mysql` names are usually still present as links, but scripts written with the new names will keep working.

```bash
mariadb -h db.example.com -u shop_app -p shop
```

On a fresh Linux install, the `root` database account normally signs in through the operating system instead of a password (this is called `unix_socket` authentication). So `sudo mariadb` works, and `mariadb -u root -p` does not. That is deliberate and safer. Leave it alone and create separate accounts for applications:

```sql
CREATE USER 'shop_app'@'10.0.%' IDENTIFIED BY 'use-a-long-random-one';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'10.0.%';
```

### Set the character set yourself

Do not rely on the server default, which on older versions is `latin1`. Say what you want on every database or table:

```sql
CREATE DATABASE shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
```

`utf8mb4` is full UTF-8. The plain name `utf8` has historically meant a three-byte version that cannot store emoji.

### See what a query really did

MariaDB's `ANALYZE` statement runs a query and shows the plan with real numbers next to the estimates:

```sql
ANALYZE
SELECT id, total_cents FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;
```

Compare `rows` (the estimate) with `r_rows` (what actually happened). A large gap means the statistics are stale; `ANALYZE TABLE orders;` refreshes them. `type: ALL` means a full table scan, which on a big table usually points to a missing index.

### Treat JSON as validated text

In MariaDB, `JSON` is an alias for `LONGTEXT` with a check that the content is valid JSON. The functions work as you would expect:

```sql
SELECT id, JSON_VALUE(details, '$.coupon') AS coupon
FROM orders
WHERE JSON_VALUE(details, '$.coupon') = 'SUMMER10';
```

To make that fast, add a generated column holding the value and index it. And if you copy data between MariaDB and MySQL, JSON columns are one of the first places to test.

### Back up both ways

A logical backup is portable SQL, fine for small and medium databases:

```bash
mariadb-dump --single-transaction --routines --triggers shop > shop.sql
```

A physical backup copies the data files while the server runs, and is quicker to restore:

```bash
mariadb-backup --backup --target-dir=/backups/full --user=backup --password=secret
mariadb-backup --prepare --target-dir=/backups/full
```

The `--prepare` step makes the copied files consistent. A physical backup is not usable until you have run it. As with any database, the backup only counts once you have restored it somewhere and looked at the data.

### Choose a long-term release

MariaDB publishes short-lived releases and long-term support (LTS) releases. For production, stay on an LTS series such as 10.11 or 11.4 and plan the move to the next one before support ends.

## Common mistakes

- **Assuming it is MySQL.** Features, error messages and configuration options differ. Search for MariaDB documentation specifically, and test migrations in both directions.
- **Replicating between MariaDB and MySQL without checking.** The two use different global transaction ID formats, and newer versions on each side are not guaranteed to understand the other.
- **Versioning every table.** History grows forever unless you remove it. Version the tables where the history has value, and partition or prune the old rows.
- **Treating Galera as a way to scale writes.** It gives availability. Heavy writes to the same rows from several nodes cause conflicts and retries.
- **Forgetting primary keys in a Galera cluster.** Every table needs one for replication to work properly.
- **Skipping `--prepare`.** A raw `mariadb-backup` copy that was never prepared may not start.
- **Leaving the default character set.** Text with accents or emoji gets damaged in ways that are hard to repair later.

## Quick reference

| Task | Command |
|---|---|
| Connect | `mariadb -h host -u user -p dbname` |
| Sign in as root on the server | `sudo mariadb` |
| Server version | `SELECT VERSION();` |
| List tables | `SHOW TABLES;` |
| Table definition | `SHOW CREATE TABLE orders\G` |
| Running queries | `SHOW FULL PROCESSLIST;` |
| Stop a query | `KILL QUERY 1234;` |
| Real query plan | `ANALYZE SELECT ...;` |
| Row history | `SELECT ... FOR SYSTEM_TIME ALL` |
| Logical backup | `mariadb-dump --single-transaction dbname > dump.sql` |

MariaDB is easiest to live with once you stop thinking of it as a MySQL clone. Learn the handful of things it does differently, use the ones that help, and it is a dependable home for application data.

## Exploring MariaDB with CreamDB

The command line covers everything in this guide. For the times you want to see tables and results side by side, [CreamDB](https://www.creamdb.com) has MariaDB as its own connection type and opens a workspace in your browser, with nothing to install.

- Browse databases, tables and views in a tree. Open a table in a grid with paging, sorting and filtering done by the database.
- Write SQL with autocomplete, formatting and history, then export results to CSV, JSON or Excel.
- View the schema as an ER diagram, watch sessions and variables in the Status panel, and read users and their grants on a read-only page.
- Ask a question in plain English and get the SQL, a result table and a chart.

The assistant reads on its own, but any change waits as an approval card for you to answer, and dropping or truncating a table needs its name typed. Read-only mode is enforced on the server, and private servers can be reached through an SSH tunnel.

There is a free plan, and new accounts get a 30-day trial with no card.

