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';

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

| 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';

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

• 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)