MySQL Authentication

Check

SELECT user,

plugin

FROM user;

Configuration

generated_random_password_length

Check

SELECT @@generated_random_password_length;

or...

SHOW VARIABLES LIKE '%generated_random_password_length%';

or...

SELECT variable_name,

variable_value

FROM performance_schema.global_variables

WHERE variable_name='generated_random_password_length';

Default: 20

+----------------------------------------------+-----------------+

| Variable_name | Value |

+----------------------------------------------+-----------------+

| caching_sha2_password_auto_generate_rsa_keys | ON |

| caching_sha2_password_digest_rounds | 5000 |

| caching_sha2_password_private_key_path | private_key.pem |

| caching_sha2_password_public_key_path | public_key.pem |

| default_password_lifetime | 0 |

| disconnect_on_expired_password | ON |

| generated_random_password_length | 20 |

| mysql_native_password_proxy_users | OFF |

| password_history | 0 |

| password_require_current | OFF |

| password_reuse_interval | 0 |

| report_password | |

| sha256_password_auto_generate_rsa_keys | ON |

| sha256_password_private_key_path | private_key.pem |

| sha256_password_proxy_users | OFF |

| sha256_password_public_key_path | public_key.pem |

+----------------------------------------------+-----------------+

default_authentication_plugin

Check

SELECT @@default_authentication_plugin;

or...

SHOW VARIABLES LIKE '%default_authentication_plugin%';

or...

SELECT variable_name,

variable_value

FROM performance_schema.global_variables

WHERE variable_name='default_authentication_plugin';

Default:
caching_sha2_password
 (default since MySQL 8.0)
mysql_native_password(default for earlier versions)

Change

In the [mysqld] section of your server option file (my.cnf)...

default_authentication_plugin=caching_sha2_password

default_authentication_plugin=mysql_native_password

Starting with MySQL 8.0 the default authentication plugin changed from mysql_native_password to caching_sha2_password. Existing users in an upgraded installation will not change. You can manually change them using something like these examples...

ALTER USER myuser IDENTIFIED WITH caching_sha2_password BY 'mypassword';

ALTER USER myuser IDENTIFIED WITH caching_sha2_password BY RANDOM PASSWORD;

To revert to the old authentication plugin, use this in the [mysqld] section of your server option file (my.cnf)...

default_authentication_plugin=mysql_native_password

You may wish to do this if you have old clients and connectors in use that don't know about caching_sha2_password.These Clients and Connector versions (and later) are caching_sha2_password aware:
  • libmysqlclient library 8.04+ (includes mysql and mysqladmin)
  • libmysqlclient library 5.7.23+ (includes mysql and mysqladmin)
  • MySQL Connector/C++ 1.1.11+ or 8.0.7+
  • MySQL Connector/NET 8.0.10+ (through the classic MySQL protocol)
  • MySQL Connector/Node.js 8.0.9+
  • X DevAPI PHP extension (mysql_xdevapi)