Support MySQL client 9.0 that removes mysql_native_password plugin

[yahonda]

Feature Request

I want tiup playground supports to create root user with the caching_sha2_password plugin because MySQL client 9.0 that removes mysql_native_password plugin.

Describe the feature you'd like:
MySQL 9.0.0 removes mysql_native_password plugin.

https://dev.mysql.com/doc/relnotes/mysql/9.0/en/news-9-0-0.html

The mysql_native_password authentication plugin, deprecated in MySQL 8.0, has been removed, and the server now rejects mysql_native authentication requests from older client programs which do not have CLIENT_PLUGIN_AUTH capability. For backward compatibility, mysql_native_password remains available on the client; the client-side built-in authentication plugin has been converted into a dynamically loadable plugin.

Why the featue is needed:
If macOS uses installs mysql client, now it installs MySQL client 9.0.1, then users will get this error.

% brew install mysql-client
% tiup playground v8.1.0
% mysql --comments --host 127.0.0.1 --port 4000 -u root

ERROR 2059 (HY000): Authentication plugin 'mysql_native_password' cannot be loaded: dlopen(/opt/homebrew/Cellar/mysql-client/9.0.1/lib/plugin/mysql_native_password.so, 0x0002): tried: '/opt/homebrew/Cellar/mysql-client/9.0.1/lib/plugin/mysql_native_password.so' (no such file), '/System/Volumes/Preboot/Cryptexes/OS/opt/homebrew/Cellar/mysql-client/9.0.1/lib/plugin/mysql_native_password.so' (no such file), '/opt/homebrew/Cellar/mysql-client/9.0.1/lib/plugin/mysql_native_password.so' (no such file)
~ %

Describe alternatives you've considered:
Workaround is installing [email protected] explicitly as follows.

% brew uninstall mysql-client
% brew install [email protected]
% echo 'export PATH="/opt/homebrew/opt/[email protected]/bin:$PATH"' >> ~/.zshrc
% exec $SHELL -l
% mysql --comments --host 127.0.0.1 --port 4000 -u root

mysql> select host,user,plugin from mysql.user\G
*************************** 1. row ***************************
  host: %
  user: root
plugin: mysql_native_password
1 row in set (0.00 sec)

Teachability, Documentation, Adoption, Migration Strategy:

[yahonda]

It looks like duplicate with pingcap/tidb#54138

[dveeden]

MySQL has removed mysql_native_password only from the server.

Note this from the description and release notes:

For backward compatibility, mysql_native_password remains available on the client; the client-side built-in authentication plugin has been converted into a dynamically loadable plugin.

I think the problem listed here is a platform specific issue on macOS that causes the loading of dynamically loadable plugin to fail. This isn't a TiDB issue. However it would be good if we can start moving to caching_sha2_password instead of relying on a deprecated authentication method.

[kennytm]

This should have been fixed by Homebrew/homebrew-core#201853, but it seems the change is not correctly "bottled" so the prebuilt binaries are still missing the plugin .sos.