DB-유

MySQL 8.0 Authentication: caching_sha2_password vs mysql_native_password

· mysql, security, authentication

My first surprise with MySQL 8.0 wasn't a new SQL feature. It was a failed login from an app that worked fine the day before. The default authentication plugin changed from mysql_native_password, around since MySQL 4.1, to caching_sha2_password. New accounts on 8.0 use the new plugin, and any client library that hasn't been updated can't authenticate against them.

This is for 8.0.11 or later (the first GA release). Before you start, write down which client libraries your applications use, and their versions.

What actually differs

mysql_native_password stores a SHA-1 based hash and uses a challenge-response handshake. It is fast and universally supported, but SHA-1 is not something you want protecting credentials in the long term and the scheme has no salt.

caching_sha2_password stores a salted SHA-256 hash with multiple rounds. The first authentication of a user requires a secure channel (TLS or Unix socket) or RSA key exchange so the password can reach the server for a full verification; the server then caches a fast digest in memory, and subsequent logins use a lightweight challenge-response against that cache. The cache is cleared on restart or with FLUSH PRIVILEGES.

Check the server default:

SHOW VARIABLES LIKE 'default_authentication_plugin';

Audit existing accounts

SELECT user, host, plugin FROM mysql.user ORDER BY user, host;

After an upgrade from 5.7, existing accounts keep mysql_native_password; only accounts created after the upgrade get caching_sha2_password. On a fresh 8.0 install, root@localhost uses the new plugin too.

Test each client library

Don't assume. Connect from each application stack with a test user created with the new plugin:

CREATE USER 'authtest'@'%' IDENTIFIED WITH caching_sha2_password BY 'T3st-P@ss';
GRANT USAGE ON *.* TO 'authtest'@'%';

The typical failure text from an outdated client is:

ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded

or, from PHP's mysqlnd and several other connectors:

The server requested authentication method unknown to the client [caching_sha2_password]

At the time of writing, the current MySQL Connector/J 8.0, Connector/Python 8.0, Connector/NET 8.0 and the 8.0 client library (libmysqlclient) support the new plugin. Many distribution-packaged drivers, older PHP builds and some Go and Node.js drivers do not yet.

Option A: keep the new plugin

If every client passes the test above, leave the default alone and make sure remote connections use TLS so the first-login full authentication is protected. MySQL 8.0 creates self-signed certificates automatically and the client library enables TLS by default; verify with:

SHOW SESSION STATUS LIKE 'Ssl_cipher';

Without TLS, the client falls back to RSA key exchange using the server's public key (--get-server-public-key or --server-public-key-path on the command line client), which is also acceptable.

Option B: fall back for one account

For a single application whose driver can't be updated yet, change only that account:

ALTER USER 'legacyapp'@'10.0.1.%' IDENTIFIED WITH mysql_native_password BY 'App-P@ss';

This is the scope I like for a workaround: one user, one driver, one ticket to revisit later.

Option C: change the server default (last resort)

If most of your landscape is old, set the default plugin back in /etc/my.cnf (CentOS) or /etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu):

[mysqld]
default_authentication_plugin = mysql_native_password

Restart the server. Existing accounts do not change; only accounts created after the restart use the old plugin unless IDENTIFIED WITH is specified. With Docker, the same setting is passed as a command argument:

docker run -d --name mysql8 -e MYSQL_ROOT_PASSWORD=R00t-P@ss mysql:8.0 \
  --default-authentication-plugin=mysql_native_password

Confirming which plugin a session used

Log in with each application account and confirm the plugin in use for the current session:

SELECT user, host, plugin FROM mysql.user WHERE user = CURRENT_USER();
SELECT * FROM performance_schema.session_connect_attrs WHERE PROCESSLIST_ID = CONNECTION_ID();

The _client_name and _client_version attributes show which library actually connected, which helps when an application server has several drivers installed.

Odd behavior

  • ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.: the first login for this user is happening over plain TCP without RSA key exchange. Connect with --ssl-mode=REQUIRED, or add --get-server-public-key to the client command.
  • ERROR 1524 (HY000): Plugin 'caching_sha2_password' is not loaded when running ALTER USER on a 5.7 server: you are connected to the wrong server, or a 5.7 replica received the statement through replication. Keep replicas at 8.0 before creating users with the new plugin.
  • After ALTER USER ... IDENTIFIED WITH mysql_native_password, the application still fails: the driver may have cached the earlier failure or uses a connection pool. Restart the application.
  • mysql_upgrade from 5.7 left root@localhost on mysql_native_password; that is expected, not a bug. Switch it deliberately with ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '...' once the local tooling supports it.

I keep the default and put stragglers on mysql_native_password one account at a time. It's the cleaner path, even if it costs a few evenings.