MariaDB: MySQL Compatibility, Engines, and Config Files

MariaDB is an open-source relational database in the MySQL family. It speaks the MySQL client protocol and supports familiar SQL, while maintaining its own releases, features, storage engines, and compatibility rules.

Think of MariaDB and MySQL as two branches from the same road. They share an early route and many signs, but each branch now has turns that do not exist on the other. Test the route your application actually follows.

How MariaDB Began

MariaDB development began in 2009 as a fork of MySQL led by MySQL co-founder Michael "Monty" Widenius. Oracle announced its agreement to acquire Sun Microsystems in April 2009, and the acquisition was completed in January 2010. Sun owned MySQL at the time, so the fork preserved a separately governed MySQL-family database during that ownership change.

The first MariaDB Server release arrived in October 2009. MariaDB continued the naming pattern recorded in the official histories: MySQL was named after Widenius's daughter My, and MariaDB was named after his daughter Maria.

The shared history explains the similar client protocol, SQL syntax, tools, and terminology without guaranteeing permanent equivalence. MariaDB and MySQL now publish separate release lines and implement features independently.

Where MariaDB Fits

MariaDB fits applications that already use MySQL-family conventions and have explicit support from the framework, host, Linux distribution, or managed provider. Ordinary tables, joins, indexes, InnoDB transactions, and common connectors can make the first setup look very similar to MySQL.

Choose MariaDB when the surrounding application and operations support these conditions:

• The application officially supports the intended MariaDB release.
• The host or operating system maintains MariaDB packages and upgrades.
• The team already understands MySQL-family administration and SQL.
• MariaDB-specific features or storage engines solve a documented requirement.
• Backup, restore, monitoring, and replication tools have been tested with MariaDB.

Do not choose it only because a dependency says "MySQL compatible." Compatibility depends on the exact server, connector, SQL mode, collation, feature set, and migration path.

How to Install, Connect, and Check the Server

Follow MariaDB's platform-specific installation instructions for the target environment. Package names and option-file locations vary across Linux distributions, containers, macOS, Windows, and managed services.

On a Debian or Ubuntu development machine, the package-manager path commonly uses these commands:

sudo apt update
sudo apt install mariadb-server mariadb-client
sudo systemctl status mariadb

After installation, use the install-time administrative connection and ask the server for its identity:

sudo mariadb

SELECT VERSION();
SHOW VARIABLES LIKE 'version_comment';
SHOW VARIABLES LIKE 'default_storage_engine';

The output confirms the server release, distribution label, and default storage engine. Record the full version in deployment notes instead of writing only "MySQL/MariaDB."

How to Create an Application User

MariaDB accounts combine a user name with a host. The account 'app_runtime'@'127.0.0.1' is different from 'app_runtime'@'%'. Use the narrowest host rule that matches the deployment.

Create a runtime user for one application database:

CREATE DATABASE codewalkers_app
CHARACTER SET utf8mb4;

CREATE USER 'app_runtime'@'127.0.0.1'
IDENTIFIED BY 'replace-with-a-generated-secret';

GRANT SELECT, INSERT, UPDATE, DELETE
ON codewalkers_app.*
TO 'app_runtime'@'127.0.0.1';

SHOW GRANTS FOR 'app_runtime'@'127.0.0.1';

Leave the administrative session, then verify the restricted account through the same host used by the PHP connection:

mariadb -u app_runtime -p -h 127.0.0.1 codewalkers_app

PHP uses the PDO MySQL driver for MariaDB connections because MariaDB supports the MySQL client protocol. Use the same restricted account in the application:

$pdo = new PDO(
    'mysql:host=127.0.0.1;dbname=codewalkers_app;charset=utf8mb4',
    'app_runtime',
    $password,
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ],
);

The runtime role can read and change application rows, while schema changes remain outside its grants. Run migrations through a separate account with the required DDL (schema-change) privileges, then remove or disable that access when the deployment process allows it.

Use secret storage provided by the deployment platform and rotate a credential if it appears in a log, terminal history, or committed file. Test the restricted account before deployment because an application that silently depends on root privileges has an incomplete permission model.

How System-Versioned Tables Work

MariaDB introduced system-versioned tables in its 10.3.4 release. A system-versioned table keeps earlier row versions, allowing a query to inspect data as it existed at another time. This can support auditing and change analysis when the storage and retention costs are planned.

Create a small price table with system versioning enabled:

CREATE TABLE product_prices (
  product_id BIGINT UNSIGNED NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (product_id)
) WITH SYSTEM VERSIONING;

INSERT INTO product_prices (product_id, price)
VALUES (101, 19.95);

UPDATE product_prices
SET price = 21.50
WHERE product_id = 101;

A normal selection returns the current price:

SELECT product_id, price
FROM product_prices
WHERE product_id = 101;

-- product_id | price
-- 101        | 21.50

Use FOR SYSTEM_TIME ALL to include the stored history:

SELECT product_id, price
FROM product_prices
FOR SYSTEM_TIME ALL
WHERE product_id = 101
ORDER BY ROW_START;

-- product_id | price
-- 101        | 19.95
-- 101        | 21.50

The exact period timestamps depend on when the statements run. Historical rows consume storage, and retention must be designed before a busy table keeps every change indefinitely. Verify version support and backup behavior before using this feature in production.

How to Configure MariaDB for an Application

MariaDB reads server and client options from files such as my.cnf or my.ini. The search order depends on the installation. Use the installed server to print its option-file locations instead of guessing:

mariadbd --help --verbose

Keep the initial configuration small and documented:

[mariadb]
default_storage_engine=InnoDB
character-set-server=utf8mb4

[client]
default-character-set=utf8mb4

InnoDB is the normal storage engine for transactional application tables. It supports transactions, row-level locking, crash recovery, and foreign keys. Other MariaDB storage engines target different workloads, so each non-default choice needs an operational reason.

Production also needs backups, restore tests, monitoring, TLS where connections cross a network, known SQL modes, matching character sets, and a supported upgrade path. A local connection only proves the query syntax. Production readiness requires the operating checks as well.

How to Back Up and Upgrade MariaDB

A backup is useful after it has been restored and checked. For a small InnoDB database, mariadb-dump can create a logical backup that is readable and suitable for a rehearsed restore:

sudo mariadb-dump \
  --single-transaction \
  --routines \
  --events \
  --dump-history \
  --databases codewalkers_app > codewalkers_app.sql

sudo mariadb < codewalkers_app.sql

--single-transaction provides a consistent logical snapshot for transactional InnoDB tables without locking them for the full dump. --dump-history includes system-versioned table history and requires MariaDB 10.11 or later. The --databases form writes the database creation and selection statements needed by the straight restore. Check the documentation before applying the same command to non-transactional tables or a database with special backup requirements.

Verify a restored backup by checking row counts, constraints, routines, events, application queries, and representative file or object references. Store backups away from the server they protect and test the recovery time against the application's needs.

For an upgrade, read every release note between the source and destination lines. Run the upgrade on a restored copy, execute migrations and integration tests, compare important query plans, then rehearse rollback. Connector support and managed-host support must cover the destination release before production changes.

Keep the exact server version beside each backup. Recovery after a failed or interrupted upgrade may require the previous major release to start and repair the data directory before another upgrade attempt.

How MariaDB Differs from MySQL

MariaDB and MySQL share common SQL and protocol behavior, but the branches have diverged as their implementation differences have grown. Current migrations can encounter different authentication plugins, collations, JSON behavior, replication features, optimizer decisions, system tables, release policies, and on-disk formats.

Before moving an application between them, prepare a tested migration plan:

1. List every server-specific data type, function, SQL mode, and storage engine.
2. Check the connector and framework support matrix for the destination release.
3. Run the full migration and test suite against a copy of production-shaped data.
4. Export and import through documented tools where direct file compatibility is unsupported.
5. Restore a backup on the destination release and verify row counts and constraints.

A shared port and driver make connection setup easier. Migration testing still proves whether the application works.

Common Pitfalls

Assuming Drop-In Means Identical

Cause: old compatibility guidance is applied to a newer MariaDB and MySQL pair. Fix: compare the exact versions, read the compatibility notes, and run migrations plus integration tests on MariaDB.

Running the Application as Root

Cause: local setup credentials are copied into the deployed application. Fix: create a limited runtime user, keep schema-change permissions in a separate migration role, and rotate exposed credentials.

Copying Unknown Tuning Settings

Cause: a large configuration from another workload is treated as a universal baseline. Fix: keep defaults unless a measured workload justifies a change, then document the reason and test the result.

Ignoring History Growth

Cause: system versioning is enabled without retention or storage planning. Fix: estimate change volume, define how long history is required, monitor table growth, and test backup and restore times.

Frequently Asked Questions

Is MariaDB a drop-in replacement for MySQL?

Drop-in compatibility is specific to the MySQL version and application. MariaDB retains substantial MySQL protocol and syntax compatibility, but features, defaults, authentication, collations, replication, and on-disk formats have diverged. Check the support matrix and test migrations, queries, backups, and restores against the exact MariaDB release.

Why does PHP PDO use a mysql DSN for MariaDB?

PHP's PDO MySQL driver speaks the MySQL client protocol used by MariaDB, so the DSN begins with mysql. That shared driver does not make every server feature identical. Keep the server version explicit and test the application's SQL against MariaDB.

Which storage engine should a MariaDB web app use?

InnoDB is the normal starting point for transactional web applications. It supports transactions, row-level locking, crash recovery, and foreign keys. Choose another engine only for a documented workload requirement, then test its durability, backup, locking, and operational behavior.

Should a MariaDB application use utf8mb4 or the plain utf8 character set?

Use utf8mb4. The historical utf8 character set in the MySQL family stores only a 3-byte subset of Unicode and cannot hold emoji or other characters outside that range. utf8mb4 stores the full range, and it is what this page's own schema and connection examples use.

Does system versioning replace the need for regular backups?

No. System versioning keeps row history inside the same live database and does not protect against a dropped table, a failed disk, or a corrupted data directory. Treat it as a query feature for auditing, kept alongside a tested backup and restore process.

What to Read Next

Read the MySQL guide for the other branch of the MySQL family, then use MySQL vs MariaDB vs MaxDB for a direct comparison. Continue with SQL transactions and ACID and PHP PDO before connecting an application.

Return to the SQL databases hub to compare MariaDB with PostgreSQL and SQLite.