MySQL 8.0 vs MariaDB 10.4: Authentication, InnoDB, Clone, Optimizer & Backup Highlights

Authentication Changes

MySQL 8.0 switches the default authentication plugin to caching_sha2_password, which may break JDBC compatibility. To revert, run

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

or set default_authentication_plugin=mysql_native_password in my.cnf. MariaDB 10.4 retains mysql_native_password as the default.

The unix_socket authentication plugin lets OS users log into MariaDB/MySQL using their Linux credentials. It was introduced in MariaDB 10.4.6 and supported in MySQL 8.0.18. Example for MySQL 8.0.18:

INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';
CREATE USER 'hechunyang'@'localhost' IDENTIFIED WITH auth_socket;
# OS side
useradd hechunyang
passwd hechunyang
# Login
su - hechunyang
mysql -S /tmp/mysql_hcy.sock -uhechunyang -e "SELECT VERSION();"

For MariaDB 10.4.10, grant the user via:

GRANT ALL ON *.* TO 'hechunyang' IDENTIFIED VIA unix_socket;

InnoDB Enhancements

MySQL 8.0 introduces a new data dictionary, removing MyISAM system tables and storing metadata in mysql.ibd. This enables atomic DDL, meaning schema changes are committed as a single transaction, preventing half‑finished DDL after crashes.

Instant ADD COLUMN allows metadata‑only changes on tables with billions of rows, avoiding table rebuilds. Limitations: the column must be added at the end unless using algorithm=instant (supported in MariaDB 10.4), it cannot be used with ROW_FORMAT=COMPRESSED, and DROP COLUMN still requires a rebuild.

Online DDL can still lock tables in two scenarios: adding/removing primary keys or changing column attributes, and when a concurrent session holds a metadata lock. MariaDB 10.3 adds the DDL FAST FAIL patch to abort conflicting DDL quickly.

Clone Plugin

From MySQL 8.0.17, the clone plugin enables fast cloning of an instance or secondary of an InnoDB group. Installation and verification:

INSTALL PLUGIN CLONE SONAME 'mysql_clone.so';
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'clone';

Create a clone user with backup privileges, set the donor node, and run:

SET GLOBAL clone_valid_donor_list='192.168.137.11:3306';
CLONE INSTANCE FROM [email protected]:3306 IDENTIFIED BY '123456';
SELECT * FROM performance_schema.clone_status;
SELECT * FROM performance_schema.clone_progress;
CHANGE MASTER TO MASTER_HOST='192.168.137.11', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_AUTO_POSITION=1;
START SLAVE;

Note: cloning is only supported for the InnoDB engine and is unavailable in MariaDB 10.4.

General Features

MariaDB 10.3 adds automatic killing of idle transactions via innodb_kill_idle_transaction (default 0 s). It also introduces system versioning tables that store a full history of row changes, enabling point‑in‑time recovery.

Multi‑table UPDATE with ORDER BY and LIMIT is supported in MariaDB 10.3, while MySQL 8.0 rejects such statements.

MariaDB 10.3 resolves the limitation of updating a table using a sub‑query that references the same table.

Optimizer Improvements

MySQL 8.0 adds descending indexes (e.g., KEY a_idx(a DESC, b ASC)), improving mixed‑order ORDER BY queries. This feature is not available in MariaDB 10.4.

Hash Join is introduced in MySQL 8.0.18 for inner joins on unindexed columns, but left/right joins are not supported yet. The execution plan shows <not executable by iterator executor> for such cases.

Explain Analyze (via EXPLAIN ANALYZE SELECT …) provides estimated rows, actual rows, and execution time, though timing may be inaccurate. MariaDB 10.4 offers similar tracing with SET optimizer_trace='enabled=on'; and querying information_schema.optimizer_trace.

Resource Groups

MySQL 8.0 introduces resource groups to isolate CPU usage of slow SQL. Example creation:

CREATE RESOURCE GROUP slowsql_rg TYPE=USER VCPU=3 THREAD_PRIORITY=19 ENABLE;

Identify slow threads and assign them:

SELECT THREAD_ID, PROCESSLIST_INFO FROM performance_schema.threads WHERE PROCESSLIST_INFO REGEXP 'SELECT|INSERT|UPDATE|DELETE|ALTER' AND PROCESSLIST_TIME>10;
SET RESOURCE GROUP slowsql_rg FOR 379;

Disable with ALTER RESOURCE GROUP slowsql_rg DISABLE FORCE;. Not supported in MariaDB 10.4.

Query Rewrite Plugin

Install the plugin and enable it:

INSTALL PLUGIN query_rewrite SONAME 'query_rewrite.so';
SHOW GLOBAL VARIABLES LIKE 'rewriter_enabled';

Define a rewrite rule to replace a generic SELECT * with a specific column list:

INSERT INTO query_rewrite.rewrite_rules(pattern, replacement, pattern_database) VALUES ('SELECT * from sbtest1 limit ?', 'SELECT k,c from sbtest1 limit ?', 'test');
CALL query_rewrite.flush_rewrite_rules();

After execution, the original query is rewritten and a warning notes the transformation.

Backup Tools

Percona XtraBackup 8.0 for MySQL 8.0

# xtrabackup --defaults-file=/etc/my_hechunyang.cnf -S /tmp/mysql_hechunyang.sock \
    --user='root' --password='123456' --slave-info --backup \
    --compress --compress-threads=4 --target-dir=/data/bak/
# xtrabackup --prepare --target-dir=/data/bak/
# xtrabackup --defaults-file=/etc/my_hechunyang.cnf --copy-back --target-dir=/data/bak/
# chown -R mysql:mysql /var/lib/mysql/

Compression requires the qpress tool ( yum install qpress -y).

MariaDB Mariabackup (hot backup)

# mariabackup --defaults-file=/etc/my.cnf -S /tmp/mysql3306.sock \
    --backup --target-dir=/data/bak/ --user=root --password=123456
# mariabackup --prepare --target-dir=/data/bak/
# mariabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/bak/
# chown -R mysql:mysql /var/lib/mysql/

For replica backups, add --slave-info --safe-slave-backup to record the slave POS.