MySQL & MariaDB CLI in 2026: What Changed

The Commands Still Work. Some of the Defaults Don’t.

If you learned MySQL before 2018, most of what you know still applies. The CLI feels the same, the SQL syntax is the same, and mysqlcheck still does what it says on the tin. But a few things changed — and they’ll bite you at the worst moment if you don’t know about them.

Here’s a practical update for anyone running MySQL or MariaDB in 2026, especially if your setup involves Docker or a homelab server you haven’t touched in a few years.

The Fork Story: Oracle MySQL vs MariaDB

When Oracle acquired Sun Microsystems in 2010, the MySQL community got nervous. Michael Widenius (the original “My” in MySQL) forked it immediately and created MariaDB. The intent was a drop-in replacement. For years, it mostly was.

That’s no longer quite true.

MariaDB and MySQL have diverged significantly — different storage engine enhancements, different JSON handling, different replication behavior, different auth defaults. They share SQL syntax, but if you’re migrating a dump from one to the other, test it first.

In practice, most homelabbers and self-hosters run MariaDB. It’s the default in most Linux distros (apt install mariadb-server), it’s in most Docker Compose stacks for apps like Nextcloud or BookStack, and it doesn’t have the Oracle licensing uncertainty hanging over it. If you’re starting fresh and don’t have a specific reason to run MySQL 8, MariaDB is the practical choice.

That said, if you’re running something like PlanetScale, AWS RDS, or a managed cloud database, you’re probably on MySQL 8. The auth gotcha below applies to you.

MySQL 8.0’s Breaking Change: The Auth Trap

MySQL 8.0 changed the default authentication plugin from mysql_native_password to caching_sha2_password. This is more secure. It’s also why old clients, old PHP apps, and half the Docker images you pull will fail to connect with a cryptic error like:

Authentication plugin 'caching_sha2_password' cannot be loaded

Check what auth plugin a user is using:

SELECT user, host, plugin FROM mysql.user;

If you need to switch a user back to the old plugin (for compatibility with legacy clients):

ALTER USER 'youruser'@'%' IDENTIFIED WITH mysql_native_password BY 'yourpassword';
FLUSH PRIVILEGES;

Or set it as the default for new users in my.cnf:

[mysqld]
default_authentication_plugin=mysql_native_password

MariaDB never switched to caching_sha2_password by default, which is one reason apps targeting MariaDB “just work” more often.

Connecting: The Basics and the Docker Reality

Standard connection, same as always:

mysql -u root -p
mysql -u myuser -p mydatabase
mysql -h 127.0.0.1 -P 3306 -u myuser -p mydatabase

The -h 127.0.0.1 vs -h localhost distinction still matters. localhost uses a Unix socket by default; 127.0.0.1 forces TCP. If your socket path is wrong or missing, use TCP.

Docker is how most people run MySQL now. Getting into a running container:

docker exec -it mysql-container mysql -u root -p

Running a SQL script without an interactive shell:

docker exec -i mysql-container mysql -u root -psecretpass mydatabase < init.sql

Note the -i (not -it) — you need stdin attached but not a TTY when piping. The -p with no space before the password is intentional here; it avoids the interactive prompt when scripting.

User Management in 2026: Don’t Use the Old GRANT Syntax

Pre-MySQL 8, this worked:

-- OLD: This auto-created the user if it didn't exist. Don't use this anymore.
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%' IDENTIFIED BY 'password';

MySQL 8 removed the implicit user creation in GRANT. You need to create the user first:

CREATE USER 'myuser'@'%' IDENTIFIED BY 'strongpassword';
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%';
FLUSH PRIVILEGES;

For read-only access (the correct way to create app users):

CREATE USER 'appuser'@'%' IDENTIFIED BY 'apppassword';
GRANT SELECT ON mydb.* TO 'appuser'@'%';
FLUSH PRIVILEGES;

Check what a user can actually do:

SHOW GRANTS FOR 'myuser'@'%';

Remove a specific permission:

REVOKE INSERT ON mydb.* FROM 'myuser'@'%';

Drop a user entirely:

DROP USER 'myuser'@'%';

MariaDB follows the same syntax here. The old implicit-create GRANT was already deprecated before the MySQL/MariaDB split, so both dropped it.

mysqlcheck Still Works, and You Should Still Use It

The old one-liner for optimizing everything:

mysqlcheck -u root -p --all-databases --optimize

Still works. Still useful. Does check, analyze, and optimize in one pass.

One important caveat: behavior differs by storage engine. For InnoDB tables (which is everything you should be running in 2026), OPTIMIZE TABLE rebuilds the table to reclaim space but isn’t the same operation as on MyISAM. It works, but the performance impact is different — InnoDB optimize is heavier. Don’t run it on production during peak hours.

MyISAM still exists, but if you’re creating new tables in MyISAM in 2026, stop. InnoDB has transactions, foreign keys, crash recovery, and row-level locking. MyISAM has none of that. The only reason MyISAM tables exist on your server is if they were created before ~2015 and you haven’t migrated them.

Useful Admin Queries You’ll Actually Use

What’s running right now:

SHOW PROCESSLIST;

Kill a query that’s been running too long (get the ID from SHOW PROCESSLIST):

KILL 42;

Table sizes — find the big ones:

SELECT
  table_schema AS 'Database',
  table_name AS 'Table',
  ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
ORDER BY (data_length + index_length) DESC
LIMIT 20;

Check InnoDB engine status (great for diagnosing lock waits and deadlocks):

SHOW ENGINE INNODB STATUS\G

The \G instead of ; formats the output vertically — much easier to read for large status dumps.

Backup and Restore One-Liners

Dump a single database with compression:

mysqldump -u root -p mydatabase | gzip > mydatabase_$(date +%Y%m%d).sql.gz

Dump all databases:

mysqldump -u root -p --all-databases | gzip > all_databases_$(date +%Y%m%d).sql.gz

Restore from a compressed dump:

gunzip < mydatabase_20260406.sql.gz | mysql -u root -p mydatabase

From Docker:

gunzip < mydatabase_20260406.sql.gz | docker exec -i mysql-container mysql -u root -psecretpass mydatabase

MariaDB-Specific Notes

If you’re on MariaDB, the binary is still called mysql on most distros — but newer versions ship a mariadb binary as well. They’re functionally identical; mariadb is just the non-Oracle-branded alias. Use whichever your scripts expect.

SHOW ENGINE INNODB STATUS works on MariaDB. For replication status, MariaDB uses SHOW REPLICA STATUS (MySQL 8.0.22+ also switched to this from SHOW SLAVE STATUS — both engines finally aligned on the non-antiquated terminology).

MariaDB’s information_schema and performance_schema are slightly different from MySQL’s. If you’re copying queries from MySQL docs verbatim and getting odd results on MariaDB, that’s usually why.

The TL;DR

The fundamentals haven’t changed. mysql -u root -p, CREATE USER, GRANT, mysqldump — all still work exactly how you remember. What changed:

MySQL 8.0: auth plugin is now caching_sha2_password by default. Old clients fail. You know how to fix it now.
User management: create the user first, then GRANT. The old combined syntax is gone.
MariaDB vs MySQL: they’ve diverged. For most homelab use, MariaDB is the default and the practical choice.
Docker: pipe SQL via stdin with -i, not -it.
MyISAM: it’s dead. Don’t create new MyISAM tables.