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)

Starting in MySQL 9.0 the mysql_native_password authentication plugin has been removed.

Bibliography