DB-유

Fixing the Error: Authentication plugin caching_sha2_password cannot be loaded

· mysql, mysql8, authentication

Since 8.0 went GA, this is the question I get most from developers: they install 8.0, create a user, and the app, MySQL Workbench 6.x, Sequel Pro or a PHP script refuses to connect with a message about caching_sha2_password. Nothing is broken on the server. It defaults to a new authentication plugin that most client libraries released before 8.0 don't understand.

What is going on

MySQL 8.0 replaced mysql_native_password (a SHA-1 based challenge-response scheme from the 4.1 days) with caching_sha2_password as the default plugin for new accounts. The new plugin uses SHA-256, requires either TLS or an RSA key exchange for the first authentication of a session, and caches the result in memory afterwards. A client library has to implement that handshake. Anything linked against libmysqlclient 5.7 or earlier, older versions of mysqlnd in PHP, Go's go-sql-driver before 1.4, Connector/J before 8.0, and many GUI tools cannot.

Typical messages:

Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/lib/plugin/caching_sha2_password.so, 2): image not found

SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

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

Confirm which plugin the account uses

SELECT user, host, plugin FROM mysql.user WHERE user = 'shopapp';

If the row shows caching_sha2_password and the client is complaining, that is your problem.

Quick fix: switch the account

Fastest, and no restart:

ALTER USER 'shopapp'@'10.0.1.%' IDENTIFIED WITH mysql_native_password BY 'the-same-or-new-password';

The password must be supplied again because the hash format is different. For an account that authenticates from localhost as well, repeat for each host row. There is no FLUSH PRIVILEGES needed.

Changing the server default

If you create accounts often and your clients aren't ready, set the default in /etc/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
default_authentication_plugin = mysql_native_password

Restart the server. Only accounts created after the change are affected; existing caching_sha2_password accounts stay as they are until you alter them. On 8.0 you can also make the change without editing the file:

SET PERSIST_ONLY default_authentication_plugin = 'mysql_native_password';
RESTART;

RESTART works when the server runs under systemd or the Windows service manager.

Or name the plugin explicitly

Whatever the server default is, you can state the plugin in CREATE USER. I do this in provisioning scripts so they behave the same on 5.7 and 8.0:

CREATE USER 'report'@'10.0.2.%' IDENTIFIED WITH mysql_native_password BY 'report-password';

The real fix: newer clients

A client that speaks the new protocol is the better long-term answer:

  • MySQL Workbench 8.0 and MySQL Shell 8.0 support it natively.
  • Connector/J 8.0.x, Connector/NET 8.0.x, Connector/Python 8.0.x and Connector/ODBC 8.0 all support it.
  • PHP: support in mysqlnd depends on the exact 7.x build; check the changelog of your PHP version, and assume older builds only work with mysql_native_password.
  • Python mysqlclient 1.3.13 or later linked against libmysqlclient 8.0, or PyMySQL 0.9 or later with cryptography installed.
  • Go go-sql-driver/mysql 1.4.0 or later.
  • Node.js: the mysql2 package supports it; the older mysql package does not at the time of writing.

If the connection is not over TLS, the first authentication of each session uses an RSA public key exchange. Some connectors require you to allow that explicitly, for example allowPublicKeyRetrieval=true in Connector/J, otherwise you get Public Key Retrieval is not allowed.

Did it work?

Connect with the client that failed. On the server, check the plugin of an established session:

SELECT t.processlist_user, t.processlist_host, u.plugin
FROM performance_schema.threads t
JOIN mysql.user u ON u.user = t.processlist_user
WHERE t.processlist_id IS NOT NULL;

And confirm the server default:

SHOW VARIABLES LIKE 'default_authentication_plugin';
  • ERROR 1524 (HY000): Plugin 'caching_sha2_password' is not loaded on a 5.7 server – Somebody copied a CREATE USER ... IDENTIFIED WITH caching_sha2_password statement from an 8.0 dump. On 5.7 use mysql_native_password or sha256_password.
  • Public Key Retrieval is not allowed from a Java application – Either enable TLS (useSSL=true) or add allowPublicKeyRetrieval=true to the JDBC URL. The latter is acceptable on a trusted network only, since the key exchange without TLS can be intercepted.
  • Access denied for user 'shopapp'@'10.0.1.23' (using password: YES) right after ALTER USER ... IDENTIFIED WITH – The password was re-entered incorrectly, or the application still caches an old one. Test with the command-line client first.
  • Ubuntu 18.04 sudo mysql works but mysql -u root -p does not – That is a different plugin, auth_socket, unrelated to this error; switch root with ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '...' if you need password login.

I treat mysql_native_password as a temporary workaround. Each time I update an application's driver I check whether it supports the new plugin and move that account forward.