I
I
Ivan Melnikov2018-05-31 09:48:37
MySQL
Ivan Melnikov, 2018-05-31 09:48:37

How to change the value of the default_authentication_plugin global variable in MySQL 8.0?

When migrating a database from a MySQL 5.7 server to a MySQL 8.0 server, there was a problem connecting users (newly created on the MySQL 8.0 server) to the database:
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib/mysql/plugin/ caching_sha2_password.so: cannot open shared object file: No such file or directory
I read that you need to set the default_authentication_plugin variable to mysql_native_password.
I wrote in my.ini:
[mysqld]
default-authentication-plugin=mysql_native_password
[mysql]
default-authentication-plugin=mysql_native_password.
Then restarted the server. But the value of the variable has not changed:
5b0f99d3a622b899840322.png
So how do you set the default_authentication_plugin variable to mysql_native_password and solve the problem with user authentication?
ps. Many places write not about a variable, but about the --default-authentication-plugin option. What is it and how to change the value of the option? Is it the same as a variable?
pps In the console, changing the value of the variable also fails:
5b0f9e325cf6a152261855.png

Answer the question

In order to leave comments, you need to log in

2 answer(s)
M
Ma5t3r, 2018-11-02
@Ma5t3r

ALTER USER 'root'@'localhost'
IDENTIFIED WITH mysql_native_password
BY 'password';
https://dev.mysql.com/doc/refman/8.0/en/upgrading-...

K
Konstantin Kandaurov, 2020-09-19
@kandkonst

In Ubuntu 20, it helped to complete /etc/mysql/mysql.conf.d/mysqld.cnf
default_authentication_plugin=mysql_native_password
and restart mysql

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question