MySQL, MariaDB or Percona Server: Which One Do You Run?

A hosting control panel says "MySQL 10.11". There is no MySQL 10.11. That server is MariaDB, a fork of MySQL that still uses the old name in many menus, packages and file names. A third server, Percona Server for MySQL, also answers to the name. All three speak the same protocol and accept most of the same SQL, so you can use one for years without knowing which one it is.

It matters when something breaks. A query that works on one server fails on another. A dump from one does not import into the other. This post shows how to find out which server you run, where the three differ, and what breaks when you move data between them. I tested everything in Docker with MySQL 8.4.11, Percona Server 8.4.11-11 and MariaDB 11.8.8, plus MariaDB 10.11.19 and 11.4.13 for one check.

One family, three servers

MySQL came out in 1995 from the Swedish company MySQL AB. Sun bought the company in 2008, and Oracle bought Sun in 2010. Oracle now sells a commercial edition next to the free Community Server.

MariaDB is a fork that Michael Widenius, one of the original MySQL authors, started in 2009. At first it was a drop-in replacement. Since MySQL 5.7 and 8.0, the two have moved apart in features, system tables and defaults.

Percona Server for MySQL does not go its own way. Percona takes each MySQL release, adds features and publishes it with the same version number plus a build number. The extras include a thread pool, an audit log plugin and the MyRocks storage engine. The data files stay the same as in MySQL.

Reading the version number

The version number alone often tells you the family, because the numbers went in different directions. MySQL went from 5.7 straight to 8.0 and now uses the year and month. MariaDB jumped from 5.5 to 10.0 in 2014 to show that it no longer followed the MySQL numbers.

So MariaDB 10.11 is not newer than MySQL 8.4. The two lines are separate.

Ask the server, not the client

The most common mistake is mysql --version. It reports the client program on your machine, not the server. My workstation has the MariaDB 10.3 client, and here it talks to a MySQL server:

$ mysql --version
mysql  Ver 15.1 Distrib 10.3.39-MariaDB, for Linux (x86_64) using readline 5.1
$ mysql -h 127.0.0.1 -P 3307 -N -e 'SELECT VERSION(), @@version_comment'
8.4.11	MySQL Community Server - GPL

In the rest of this post, the three servers run on ports 3307 (MySQL), 3308 (Percona) and 3309 (MariaDB), with the login in ~/.my.cnf. VERSION() and the version_comment variable together tell them apart:

$ for port in 3307 3308 3309; do mysql -h 127.0.0.1 -P "$port" -N -e 'SELECT VERSION(), @@version_comment'; done
8.4.11	MySQL Community Server - GPL
8.4.11-11	Percona Server (GPL), Release 11, Revision 57878ff8
11.8.8-MariaDB-ubu2404	mariadb.org binary distribution

MariaDB puts its name in the version string, followed by a build suffix that depends on the package. Percona adds its build number after a dash and puts its name in the comment. MySQL has neither.

Without a database login, ask the server program: mysqld on MySQL and Percona, mariadbd on current MariaDB. The official MariaDB Docker image has no mysql or mysqld names at all. First on the MySQL host, then on the MariaDB host:

$ mysqld --version
/usr/sbin/mysqld  Ver 8.4.11 for Linux on x86_64 (MySQL Community Server - GPL)
$ mariadbd --version
mariadbd  Ver 11.8.8-MariaDB-ubu2404 for debian-linux-gnu on x86_64 (mariadb.org binary distribution)

This shows the program on disk. After a package update without a restart, the running server is still older. Only VERSION() tells you what is running.

A Bash function

For scripts, I wrap the query in a small function. It passes its arguments to the client, so the usual options for host, port and user work.

# db_server(): name and version of the MySQL-family server you connect to
# Prints "mysql 8.4.11", "percona 8.4.11-11" or "mariadb 11.8.8".
# All arguments go to the client, for example: db_server -h db1 -P 3306
db_server() {
    local client version='' comment=''
    if command -v mariadb >/dev/null 2>&1; then
        client=mariadb
    elif command -v mysql >/dev/null 2>&1; then
        client=mysql
    else
        printf 'db_server: no mysql or mariadb client found\n' >&2
        return 2
    fi
    IFS=$'\t' read -r version comment < <(
        "$client" "$@" --batch --skip-column-names \
            -e 'SELECT VERSION(), @@version_comment'
    )
    [ -n "$version" ] || return 1
    case "$version $comment" in
        *MariaDB*) printf 'mariadb %s\n' "${version%%-*}" ;;
        *Percona*) printf 'percona %s\n' "$version" ;;
        *)         printf 'mysql %s\n' "$version" ;;
    esac
}

--batch --skip-column-names gives one line with a tab between the two values, and read splits it there. The process substitution keeps read in the current shell, so the variables keep their values. If the login fails, the client prints its own error and the function returns 1. For MariaDB, ${version%%-*} cuts off the build suffix. For Percona, the build number stays, because Percona's release notes use it.

$ db_server -h 127.0.0.1 -P 3307
mysql 8.4.11
$ db_server -h 127.0.0.1 -P 3308
percona 8.4.11-11
$ db_server -h 127.0.0.1 -P 3309
mariadb 11.8.8
$ db_server -h 127.0.0.1 -P 3399; echo "exit=$?"
ERROR 2002 (HY000): Can't connect to MySQL server on '127.0.0.1' (115)
exit=1

A script can then choose the right tool. This pick-dump.sh picks the dump program:

. ./db_server.sh
server=$(db_server "$@") || exit 1
case $server in
    mariadb\ *) dump=mariadb-dump ;;
    *)          dump=mysqldump ;;
esac
printf '%s uses %s\n' "$server" "$dump"

$ bash pick-dump.sh -h 127.0.0.1 -P 3307
mysql 8.4.11 uses mysqldump
$ bash pick-dump.sh -h 127.0.0.1 -P 3309
mariadb 11.8.8 uses mariadb-dump

I tested the function on Bash 4.4.20 with the MariaDB 10.3 client and on Bash 5.2.21 with the MariaDB 11.8 client.

Note: the MariaDB 11.8 client checks the server's TLS certificate by default. My MySQL and Percona containers use self-signed certificates, so it stopped with ERROR 2026 (HY000): TLS/SSL error: self-signed certificate in certificate chain. Adding --skip-ssl-verify-server-cert works for a test. On a real network, give the server a proper certificate instead.

Where they differ

Basic SQL is the same on all three. These are the differences I meet most often.

JSON. MySQL and Percona store JSON columns in a binary format. In MariaDB, JSON is another name for LONGTEXT with a validity check. The same CREATE TABLE on MariaDB 11.8:

CREATE TABLE servers (id INT PRIMARY KEY, doc JSON);
SHOW CREATE TABLE servers\G

*************************** 1. row ***************************
       Table: servers
Create Table: CREATE TABLE `servers` (
  `id` int(11) NOT NULL,
  `doc` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`doc`)),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci

MariaDB also has no -> or ->> operator. doc->>'$.name' works on MySQL and fails on MariaDB with error 1064. JSON_VALUE(doc, '$.name') returned web01 on all three servers, so use it in code that must run everywhere.

MariaDB-only SQL. INSERT ... RETURNING, CREATE SEQUENCE, system-versioned tables and the UUID and INET6 column types exist only in MariaDB. On MySQL and Percona, RETURNING and CREATE SEQUENCE both failed with error 1064.

Login plugins. MySQL 8.4 creates users with caching_sha2_password, and it ships mysql_native_password disabled. MySQL 9.0 removed the old plugin completely. MariaDB still uses mysql_native_password and adds unix_socket for local logins:

SELECT PLUGIN_NAME, PLUGIN_STATUS FROM information_schema.PLUGINS
WHERE PLUGIN_NAME IN ('mysql_native_password', 'caching_sha2_password', 'unix_socket');

MySQL 8.4 and Percona 8.4:
+-----------------------+---------------+
| PLUGIN_NAME           | PLUGIN_STATUS |
+-----------------------+---------------+
| caching_sha2_password | ACTIVE        |
| mysql_native_password | DISABLED      |
+-----------------------+---------------+
MariaDB 11.8:
+-----------------------+---------------+
| PLUGIN_NAME           | PLUGIN_STATUS |
+-----------------------+---------------+
| mysql_native_password | ACTIVE        |
| unix_socket           | ACTIVE        |
+-----------------------+---------------+

Extra settings. Percona and MariaDB both have server variables that MySQL does not know. SHOW VARIABLES LIKE 'userstat' returns a row on Percona and on MariaDB, and nothing on MySQL. If such a setting stays in my.cnf after a switch to MySQL, the server does not start. It stops with [ERROR] [MY-000067] [Server] unknown variable 'userstat=1'.

Moving between them

MySQL and Percona of the same version can share a data directory. I started Percona 8.4.11-11 on a data directory that MySQL 8.4.11 had created, and all the data was there. The way back also worked, with one extra step. Percona registers its telemetry component in the data dictionary. MySQL cannot load it, so it logged Can't open shared library ... component_percona_telemetry.so at every start. Remove the component on Percona before you switch back:

UNINSTALL COMPONENT 'file://component_percona_telemetry';

After that, MySQL started with no errors.

MySQL 8 and MariaDB cannot share a data directory. MySQL 8 keeps table definitions in a data dictionary inside InnoDB. MariaDB still writes a .frm file for each table. The same table on each server:

$ ls /var/lib/mysql/shop/
orders.ibd
$ ls /var/lib/mysql/shop/
db.opt	orders.frm  orders.ibd

The first listing is from MySQL 8.4, the second from MariaDB 11.8. When I started MariaDB 11.8 on a copy of the MySQL data directory, it stopped at once (timestamps removed):

[ERROR] InnoDB: File ./ib_logfile0 was not found
[ERROR] InnoDB: Plugin initialization aborted with error Generic error
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting

Between MySQL and MariaDB, use a logical dump. A mysqldump file from MySQL 8.4 imported cleanly into MariaDB 11.8, and the JSON column became LONGTEXT with the check. MariaDB 10.11, on port 3310, refused the same file:

$ mysql -h 127.0.0.1 -P 3310 < from-mysql.sql
ERROR 1273 (HY000) at line 22: Unknown collation: 'utf8mb4_0900_ai_ci'

MariaDB 11.4.13 and 11.8.8 accept that collation name, but 10.11.19 does not. The other direction fails in the same way, because MySQL does not know MariaDB's default collation. A dump from MariaDB 11.8, loaded with the MySQL 8.4 client:

$ mysql < from-maria.sql
ERROR 1273 (HY000) at line 23: Unknown collation: 'utf8mb4_uca1400_ai_ci'
$ sed 's/utf8mb4_uca1400_ai_ci/utf8mb4_0900_ai_ci/g' from-maria.sql | mysql
$ mysql -e 'SELECT * FROM blog.notes'
+----+-------+
| id | body  |
+----+-------+
|  1 | hello |
+----+-------+

The two collations follow different versions of the Unicode sort rules (14.0 and 9.0), so a few characters may sort differently. Remove MariaDB-only features such as sequences or INET6 columns before you move, or the import stops there too. Physical backup tools do not help here: Percona XtraBackup works with MySQL and Percona, and mariadb-backup only with MariaDB.

Which one to use

For a new server, I take what the distribution or host supports well and keep it on an LTS line. On Debian, the default-mysql-server package installs MariaDB. Choose MySQL or Percona when an application needs MySQL 8 features such as the binary JSON type. Choose MariaDB for its extra SQL, such as sequences and system-versioned tables. Choose Percona when you want MySQL with a free thread pool and audit log.

If you write software that must run on all three, test on all three. Keep to the common SQL: JSON_VALUE() instead of ->>, AUTO_INCREMENT instead of sequences, and a second query instead of RETURNING.

MariaDB keeps a list of differences between MariaDB and MySQL, and the Percona Server 8.4 documentation lists its extra features.

Related posts

• Store IPv4 and IPv6 together in MySQL with INET6_ATON()
• Match an IPv4 address against a CIDR subnet in MySQL
• ipcalc() in MySQL: network, broadcast and prefix from a netmask