MySQL changing authentication type from standard t

Question

I've setup a new MySQL instance on a computer and every time I add a user it sets the Authentication Type to caching_sha2_password.

This happens even if I set the Authentication Type to "Standard", it then changes it when I save the user. I've also changed the default authentication plug in to "mysql_native_password", but it still keeps doing it.

With it using the caching_sha2_password I can't connect to the database from .net core as I get an error stating:

MySqlException: Authentication method 'caching_sha2_password' not supported by any of the available plugins

How do I get it to save users with the Standard authentication type?

Answer 1

Run

mysql> CREATE USER ‘username’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;

Answer 2

I had the same problem today. The only way I found to fix it was:

1. executing the install file
2. select "Reconfigure" over the mysql server
3. In Authentication Method tab, select "Use Legacy Authentication Method"

It should fix your problem.

Answer 3

import mysql.connector

def connect():
    conn = mysql.connector.connect(host='localhost',
    database='mydb',
    user='root_new',
    password='root_new')
    if conn.is_connected():
       print('Connected to MySQL database')

if name == 'main':
   connect()

Output : Connected to MySQL database

Follow the Screenshots                                                                     

                                                        
            

查看更多

0人赞 添加讨论(0) 举报

Answer 4

For those of you that are using MySQL Workbench 8.0 CE and still struggling, the following worked for me:

1. Under Navigator, click on Administration Administration under Navigator preview, then select Options File.
2. Administration - Options File will open. Under the security tab, scroll down until you see "default authentication plugin". Mine was on caching_sha2_password. Changing it to mysql_native_password, clicking apply and restarting MySQL Workbench worked for me.

Administration - Options File preview

I had to delete and re-add the user. It did not automatically change the user's authentication type back to caching_sha2_password, it kept it on standard.

Answer 5

You can also do it with MySQL Workbench:

"Users and Privilegues" -> "Add Account" -> Authentication Type: "Standard"

This user now can login with default login, i.e. for phpMyAdmin.