MariaDB

MariaDB began as a fork of MySQL 5.1 and for several years was a genuine drop-in replacement. That has not been true for a long time. The two projects now share a wire protocol, a SQL dialect that mostly overlaps, and an InnoDB lineage. They diverge in the places that break migrations: system tables, JSON, GTIDs, and authentication. Treating MariaDB as “MySQL with a different logo” is the single most expensive assumption you can make about it.

The release model is the second thing to get straight. MariaDB ships a long-term-support series roughly annually and quarterly rolling releases in between. Rolling releases (12.0, 12.1, 12.2) are maintained only until the next rolling release supersedes them, a window of about three months. Only the LTS series get multi-year maintenance: 12.3 (GA 28 May 2026, community maintenance to 12 June 2029) is current, with 11.8, 11.4 and 10.11 still maintained. 10.6 LTS reached end of life on 6 July 2026. Run an LTS in production unless you have a specific reason not to; the rolling releases are a preview channel with a three-month support life.

Where MariaDB and MySQL actually diverge

MariaDB’s own documentation compares against MySQL 8.0 (there is no equivalent published comparison against MySQL 8.4). The differences below are the ones that break things, not the ones that add features.

System tables and authentication. From MariaDB 10.4 all user accounts, passwords and global privileges live in mysql.global_priv. mysql.user still exists with the same columns, but it is a view over global_priv and cannot be updated. Any tooling that writes directly to mysql.user fails. On Unix-like systems root@localhost and the data-directory owner are created with unix_socket authentication first and mysql_native_password USING 'invalid' as the fallback, so there is no default root password and mysql -uroot will not work - sudo mariadb will. To see the real privilege data:

SELECT CONCAT(user, '@', host, ' => ', json_detailed(priv)) FROM mysql.global_priv;

Users created with MySQL’s SHA-256 password algorithm cannot be used in MariaDB at all. MariaDB adds its own ed25519 and PARSEC plugins, and supports multiple authentication plugins per account so you can migrate users gradually.

JSON. MariaDB stores JSON as text; the JSON data type is an alias for LONGTEXT. MySQL stores it in a binary format and compares values as JSON. In MariaDB, JSON strings are ordinary strings and compare as strings. Two consequences: you cannot replicate a MySQL JSON column into MariaDB (convert it to TEXT/LONGTEXT on the MySQL side, or use the mysql_json plugin to convert on import), and up to and including 11.8 the -> and ->> operators do not exist, so use JSON_EXTRACT() and JSON_UNQUOTE() instead. Those operators were added in MariaDB 13.1.

GTIDs. The implementations are not compatible. A MariaDB GTID is domain-server_id-sequence (for example 0-1-10), where the 32-bit domain ID identifies an independent binlog stream. MySQL uses UUID-based sets. The system variables differ too, so a migration must rewrite them. In practice: MariaDB can be a replica of a MySQL primary, but MySQL cannot be a replica of a MariaDB primary. Replicating from MySQL 8.0 requires MariaDB 10.6.21 / 10.11.11 / 11.4.5 / 11.7.2 or newer, GTID mode off on the MariaDB side, and on the MySQL side:

[mysqld]
binlog-row-value-options=""
binlog_transaction_compression=0
binlog_format=row

Both settings disable binlog events MariaDB cannot parse (PARTIAL_UPDATE_ROWS_EVENT and TRANSACTION_PAYLOAD_EVENT). caching_sha2_password for the replication user is only supported from MariaDB 11.4.9 / 11.8.4 / 12.1.1; before that, create a second replication user with mysql_native_password.

Other things that bite. MariaDB has no SET PERSIST, no RESTART statement, no --initialize (use mariadb-install-db), no lateral derived tables, no CREATE TABLESPACE for InnoDB, no FOR SHARE spelling (LOCK IN SHARE MODE works), and no MAX_EXECUTION_TIME hint. performance_schema is off by default in MariaDB and on in MySQL. MariaDB removed the InnoDB change buffer. Regular expressions use PCRE, not MySQL’s ICU engine, so patterns are not guaranteed to be portable. Roles behave differently: MariaDB never allows authentication via a role and permits only one active role at a time.

None of that makes MariaDB worse. It makes “drop-in” false.

Choosing a storage engine

InnoDB is the default and should be the default choice. The others exist for specific reasons.

Aria is worth understanding because you are already using it. Its TRANSACTIONAL=1 table option is misleadingly named: it means crash-safe, not transactional (MDEV-21364). Crash safety comes from the PAGE row format, which is the default; setting ROW_FORMAT=FIXED or DYNAMIC to emulate MyISAM gives up crash safety.

ColumnStore is a separate deployment concern, not a table option you sprinkle on. It reserves 75% of system RAM by default, so running it alongside a large InnoDB buffer pool on one host will get the OOM killer involved.

Mixing engines in one transaction is where correctness quietly disappears: a rollback that touches a MyISAM or Aria table rolls back only the InnoDB part.

InnoDB settings that matter

Verify these against the running server instead of assuming; several defaults changed in 10.5 and 10.6.

The buffer pool. 128 MiB is a development default and nothing else. Size it to hold the working set. The diagnostic is the ratio of Innodb_buffer_pool_reads (reads that had to touch disk) to Innodb_buffer_pool_read_requests: sample both over a minute, and if the delta in the former is under 1% of the delta in the latter, the pool is large enough. A rising Innodb_buffer_pool_wait_free means it is not. Oversizing is worse than undersizing, because swapping costs more than the cache buys.

From 10.11.12 / 11.4.6 / 11.8.2 the pool resizes in 1 MiB increments up to innodb_buffer_pool_size_max, which must be set at startup; innodb_buffer_pool_chunk_size is deprecated and ignored, and Innodb_buffer_pool_resize_status was removed. SET GLOBAL innodb_buffer_pool_size now blocks until the resize finishes.

Durability. innodb_flush_log_at_trx_commit is the setting people get wrong, so be concrete about what each value loses:

The distinction between 0 and 2 is the one that matters: at 2, only the operating system or the machine going down loses data, because the writes have already left the server process. At 0, the mariadbd process dying is enough. For a genuinely durable configuration set innodb_flush_log_at_trx_commit=1 and sync_binlog=1; sync_binlog defaults to 0, meaning the OS decides when the binary log reaches disk, which quietly undermines binlog-based point-in-time recovery. With innodb_flush_log_at_trx_commit=1 and the binary log enabled, binlog group commit reduces the cost to two shared syncs per group instead of three.

Redo log size. Larger means less checkpoint flushing and more I/O headroom, at the cost of longer crash recovery. From 10.5 recovery was reworked and the value can safely be set well above the old advice, even larger than the buffer pool. Do not delete ib_logfile files to resize; from 10.8 the server refuses to start if its existing redo log files are missing. Resize online:

SET GLOBAL innodb_log_file_size = 2 * 1024 * 1024 * 1024;
SHOW GLOBAL VARIABLES LIKE 'innodb_log_file_size';

The resize runs asynchronously in the background, so also write it to the configuration file or it will not survive a restart.

Flush method. innodb_flush_method is deprecated from 11.0 and replaced by four dynamic boolean variables: innodb_data_file_buffering, innodb_log_file_buffering, innodb_data_file_write_through and innodb_log_file_write_through, all defaulting to OFF. The old enum still works and is translated into them: O_DSYNC sets both write-through flags on and buffering off; fsync/littlesync/nosync turn write-through off and data-file buffering on. Because the new variables are dynamic and the old one is not, prefer them on 11.0 and later. The practical rule: leave the defaults (equivalent to O_DIRECT), and only enable innodb_data_file_buffering if you have an I/O-bound read workload that benefits from the OS page cache as a second-level cache below the buffer pool.

A working baseline for a 64 GB dedicated server:

[mariadb]
default_storage_engine          = InnoDB
innodb_buffer_pool_size         = 48G
innodb_buffer_pool_size_max     = 48G
innodb_log_file_size            = 8G
innodb_log_buffer_size          = 64M
innodb_flush_log_at_trx_commit  = 1
sync_binlog                     = 1
innodb_file_per_table           = ON

log_bin                         = mariadb-bin
binlog_format                   = ROW
log_slave_updates               = ON

slow_query_log                  = ON
slow_query_log_file             = /var/log/mysql/slow.log
long_query_time                 = 0.5
log_slow_verbosity              = query_plan,explain,engine

Replication

MariaDB’s built-in replication is asynchronous: the primary writes to its binary log and does not wait for anyone. It is fast and it guarantees nothing about the replica at commit time. If the primary dies, transactions that had not reached a replica are gone.

Semi-synchronous replication narrows that window. Set rpl_semi_sync_master_enabled=ON on the primary and rpl_semi_sync_slave_enabled=ON on the replica (both default OFF; it is built into the server, nothing to install). The primary then waits for exactly one replica to acknowledge. Be precise about the guarantee: the acknowledgement means the event reached the replica’s relay log, not that it was applied, and whether the relay log is on disk depends on sync_relay_log.

Two defaults deserve attention. rpl_semi_sync_master_timeout is 10000 ms. After that the primary silently reverts to asynchronous replication and logs Semi-sync replication switched OFF, so watch Rpl_semi_sync_master_status. And rpl_semi_sync_master_wait_point defaults to AFTER_COMMIT, which means the primary commits in the storage engine before waiting for the acknowledgement. A reader on the primary can observe a transaction that no replica has yet received. AFTER_SYNC waits before the engine commit and closes that hole.

GTIDs make replicas repointable. Because the position is not a file and offset but the last GTID applied per domain, a replica can be moved to a new primary without working out where the equivalent offset is. The position is recorded in mysql.gtid_slave_pos, and because that table is InnoDB, the position updates in the same transaction as the data: crash-safe, unlike the old relay-log.info file. GTID is used by default from 10.10. Enable gtid_strict_mode to have the server reject the situations that would otherwise let binlogs silently diverge.

Parallel replication addresses the fact that a single SQL thread cannot keep up with a multi-threaded primary. slave_parallel_threads defaults to 0, meaning no worker threads and single-threaded apply, so this is opt-in. slave_parallel_mode defaults to optimistic from 10.5.1.

In-order modes commit in the primary’s order, so parallelism is transparent to the application. Out-of-order apply exists only in GTID mode and only when the application explicitly opts in via replication domains, at which point the application owns conflict avoidance. Note that non-transactional DML and DDL are never applied in parallel with earlier events, because they cannot be rolled back.

Galera cluster

Galera gives synchronous multi-primary replication using certification-based replication. A transaction executes optimistically on one node. At COMMIT, its changes and the primary keys of the affected rows are collected into a write-set and broadcast to every node in a global total order. Each node independently runs the same deterministic certification test (checking the write-set’s primary keys against transactions in the interval since the last one it applied) and therefore reaches the same verdict. Pass, and it commits; fail, and the originating node rolls the transaction back and returns a deadlock error to the client.

Three consequences follow directly, and they are the whole story:

• Deadlocks surface at COMMIT. Because certification happens after the statement has already succeeded locally, your application must handle a deadlock error from COMMIT itself, not just from DML. wsrep_retry_autocommit (default 1) retries autocommitted statements, which does nothing for explicit multi-statement transactions.
• Hot rows destroy throughput. Any row updated concurrently on more than one node is a certification conflict. The counter to watch is a rising wsrep_local_cert_failures. The usual fix is to route all writes to one node and use the others for reads and failover.
• Write scaling does not improve. Every node applies every write-set, so the cluster’s write capacity is that of its slowest node. MariaDB’s own documentation states that cluster performance cannot exceed the slowest node’s, and that even a single node configured as a cluster member is slower than the same server standalone.

The isolation guarantees are weaker than people expect. Galera implements a distributed variant of snapshot isolation, and MariaDB documents two anomalies confirmed by the 2026 Jepsen analysis: lost updates, because there is no first-committer-wins rule for all operation types, which makes the read-modify-write pattern most ORMs use unsafe without SELECT ... FOR UPDATE; and stale reads, because a transaction committed on one node may not yet be visible on another. Set wsrep_sync_wait when a session needs to observe its own or others’ prior writes.

Hard constraints worth checking before you commit to Galera: replication covers InnoDB only; every table needs a primary key (DELETE is unsupported without one); binlog_format must be ROW; LOCK TABLES and GET_LOCK() are unsupported; FLUSH PRIVILEGES is not replicated; auto-increment values are not sequential; the slow and general query logs cannot be written to tables (log_output=FILE); write-sets are capped by wsrep_max_ws_rows (128K rows) and wsrep_max_ws_size (2147483647 bytes) and large transactions are memory-resident, so bulk loads need chunking; and Windows is not supported. wsrep_slave_threads defaults to 1. Raise it, or the applier becomes the bottleneck.

Diagnostics

SHOW ENGINE INNODB STATUS is the first stop for anything InnoDB-shaped. The sections that earn their keep: SEMAPHORES (large numbers of waiting threads indicate I/O or mutex contention), LATEST DETECTED DEADLOCK (the full statements and locks of the last deadlock; this is the only place it is recorded unless you are logging deadlocks), TRANSACTIONS (lock contention and long- running transactions), LOG (current LSN versus the checkpoint, which tells you whether the redo log is under pressure), and BUFFER POOL AND MEMORY. SET GLOBAL innodb_status_output_locks=1 adds per-lock detail. innodb_deadlock_report defaults to full, reporting transactions, waiting locks and blocking locks.

The slow query log is disabled by default and long_query_time defaults to 10 seconds, which finds nothing useful on a healthy OLTP server. Lower it and use the MariaDB-specific filters instead of logging everything:

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_slow_verbosity = 'query_plan,explain,engine';
SET GLOBAL log_slow_filter = 'filesort,filesort_on_disk,tmp_table,tmp_table_on_disk';

log_slow_filter restricts logging to queries exhibiting specific behaviours; log_slow_rate_limit samples one query in N when the log itself becomes an I/O problem; min_examined_row_limit excludes queries that touched few rows. log_queries_not_using_indexes is useful but noisy on its own. Combine it with min_examined_row_limit. From 10.11 these are also available under log_slow_* names (log_slow_query_time, log_slow_min_examined_row_limit). The default log file is ${hostname}-slow.log in the data directory.

performance_schema is off by default in MariaDB, for performance reasons, and cannot be enabled at runtime: it needs performance_schema=ON in the configuration file and a restart. Once on, it provides 80 tables (from 10.5), of which statement digests are usually the reason you turned it on: they normalise queries so you can aggregate by statement shape rather than by literal values. Budget for the memory and the restart before enabling it on a busy server.

Where MariaDB is the wrong tool

Reach for PostgreSQL when the workload leans on features MariaDB does not have or implements more weakly. MariaDB’s JSON is text with functions over it, not a first-class indexable binary type. If JSON documents are central to your schema, that difference is structural, not cosmetic. The same applies to rich type systems and extensions generally: there is no MariaDB equivalent to PostGIS-class geospatial support or the wider extension ecosystem. MariaDB does not support lateral derived tables, and its optimiser, while good, has fewer options for complex analytical SQL.

Galera is not a substitute for a distributed OLTP database either. It is a high-availability mechanism with no write-scaling benefit; scaling writes needs sharding or a different architecture, not more Galera nodes.

MariaDB is the right tool when you want a fast, well-understood row-store with strong replication tooling and straightforward operations, and when you are prepared to treat it as its own database and not as MySQL with the serial numbers filed off.