For my previous blog post on the topic, I received plenty of questions about why I did not cover the caching_sha2_password plugin. This plugin is the default since MySQL 8, and all user accounts which are created without the option IDENTIFIED WITH, will use this plugin. Locally everything works fine because the plugin is built-in to the MySQL server and client.
If you then connect with such a user account from a remote machine you may get an error similar to:
1 | ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory |
Unlike the situation, mentioned in the other blog post, the reason for this error is not always the --plugin-dir option of the mysql client. More often this means that you did not upgrade MySQL client libraries.
Plugin caching_sha2_password was first introduced in MySQL 5.7. If you somehow still use MySQL 5.6 or an earlier version, you would not be able to connect to MySQL using this plugin.
Some operating systems have MariaDB packages in place of MySQL.
MariaDB server does not support the caching_sha2_password plugin. But MariaDB Connector/C does support this plugin since version 3.0.2. The plugin is not built-in and is provided as a separate library. All clients that use MariaDB Connector/C support authentication with the caching_sha2_password plugin. This includes mysql and mariadb command-line clients.
MariaDB libmysqlclient library does not support the caching_sha2_password plugin. Therefore, if you use a client linked to this library, you would not be able to authenticate with the caching_sha2_password plugin.
Applications that connect to MySQL using different APIs, such as Python, PHP, Go, Java, and ODBC need to use the latest version of the connectors and database drivers. Both MySQL and MariaDB connectors support the caching_sha2_password plugin.
This also applies to Percona Toolkit.
Conclusion
In order to use the caching_sha2_password authentication plugin:
- For MySQL, Percona Server for MySQL, and Percona XtraDB Cluster
- Use client version 5.7 or newer
- No other actions are needed: support is built-in
- For MariaDB
- Use client, linked with MariaDB Connector/C 3.0.2 or newer
- Specify option --plugin-dir if your client cannot find the caching_sha2_password.so library in the default location
I have latest mysql version on db server 8.0.34 and also using latest percona tool kit version on same db server but all percona utilities like pt_kill, pt_archiver and pt_online_schema_change etc are throwing same error as not getting this plugin as oracle has removed it and merged in some other library in mysql8.0. So can you please help how we can use authenticate to percona utilities via caching_sha2_password plugin. Even I tried to authenticate via socket based authentication but no luck.