Question
caching_sha2_password vs mysql_native_password: choosing a default for MySQL 8.0 legacy client support
Ira Cedar
0 reputation · 26 Dec 2021, 13:25 UTC
74.7K views1
Loading…
0 reputation · 26 Dec 2021, 13:25 UTC
MySQL 8.0 changed the default authentication plugin to caching_sha2_password, which mandates TLS or RSA key exchange during the handshake. Legacy clients and older connectors that do not implement this plugin cannot connect, producing an authentication plugin load failure. The alternative is mysql_native_password, which remains accepted but is deprecated and offers broader compatibility with existing drivers and applications at the cost of a weaker authentication mechanism.
The decision is complicated by version-sensitive server behavior. Early 8.0 releases rely on default_authentication_plugin as the primary switch, while later minor versions introduce authentication_policy to govern plugin and factor rules. Creating individual accounts with IDENTIFIED WITH mysql_native_password is a documented workaround, but it fragments security policy across the user base. The active control variable depends on the deployed 8.0 minor release and should be confirmed against the server version before committing to a policy.
Should the environment standardize on caching_sha2_password and force client-library upgrades, or retain mysql_native_password as a transitional default for legacy compatibility? Which server variable or policy setting controls this behavior in the specific 8.0 minor version in use? How can the trade-off between uniform security policy and broad client support be resolved without per-account exceptions?
35275 reputation · 26 Dec 2021, 14:02 UTC
Use mysql_native_password as a transitional server-wide default only if you have confirmed legacy clients that cannot be upgraded within your maintenance window and you cannot enforce TLS/RSA for those connections. Otherwise, keep caching_sha2_password (the 8.0.4+ default) and upgrade client libraries. The controlling variable depends entirely on your exact 8.0 minor version:
mysqldefault_authentication_plugin (set to mysql_native_password to change the default)authentication_policy (set to mysql_native_password,caching_sha2_password or similar comma-separated list; default_authentication_plugin is ignored)You cannot avoid per-account exceptions and keep a uniform policy if some clients require the legacy plugin while others use the modern one—you must pick one server-wide default or accept mixed per-account settings.
The only global switch is default_authentication_plugin. Valid values:
caching_sha2_password (default)mysql_native_passwordChange it in my.cnf/my.ini and restart:
[mysqld]
default_authentication_plugin=mysql_native_passwordauthentication_policy replaces the older variable. It accepts a comma-separated list that defines both the allowed plugins and their factor order for multifactor authentication. Examples:
authentication_policy=caching_sha2_password — only the strong plugin (default behavior)authentication_policy=mysql_native_password,caching_sha2_password — legacy first, then modernauthentication_policy=*,caching_sha2_password — any first-factor plugin, then caching_sha2_password for second factorIf authentication_policy is set, default_authentication_plugin is ignored. Set it in the config file and restart.
You have three mutually exclusive paths:
CREATE USER 'app'@'%' IDENTIFIED WITH mysql_native_password BY '…'; for legacy apps, leave others on the default. This fragments auditability and is not a uniform policy.There is no fourth option that gives both uniform policy and broad legacy support simultaneously.
SELECT VERSION(); — confirm exact minor release.SHOW VARIABLES LIKE 'default_authentication_plugin'; and SHOW VARIABLES LIKE 'authentication_policy'; — see which variable is active and its current value.SHOW VARIABLES LIKE 'have_ssl'; and SHOW VARIABLES LIKE 'sha256_password_auto_generate_rsa_keys'; — verify TLS and RSA key availability.--ssl-mode=DISABLED and note the exact error.What is the exact output of SELECT VERSION(); on the target server? The recommendation changes at the 8.0.27 boundary.
Use comments to ask for clarification. Post a solution as an answer.
35,275 reputation · 26 Dec 2021, 22:39 UTC
In MySQL 8.0 the global default authentication plugin is still exposed as default_authentication_plugin, but starting with 8.0.23 it is superseded by authentication_policy. The policy variable takes precedence for every account, regardless of how the account was created. To see which rule is in effect on your server, run:
SHOW VARIABLES LIKE 'default_authentication_plugin';
SHOW VARIABLES LIKE 'authentication_policy';
If authentication_policy returns NULL, the server is using the legacy default_authentication_plugin value. Otherwise the policy value controls all accounts. Tip: when you set authentication_policy='caching_sha2_password', even accounts that were created with IDENTIFIED WITH mysql_native_password will be forced to use the stronger plugin on the next login.
For environments where legacy clients still need to connect, enable RSA key exchange on the server (or upgrade the client). Caching_sha2_password can then negotiate over plain TCP, mitigating the TLS requirement while keeping the stronger hash.