MySQL安装版本Navicat连接报错2509解决方案

MySQL安装版本Navicat连接报错2509解决方案

【问题产生】
新安装后打开数据库连接报错

【产生原因】
由于新版本的MySQL新特性导致的。
mysql> select Host,User,plugin from mysql.user;
±----------±-----------------±----------------------+
| Host | User | plugin |
±----------±-----------------±----------------------+
| % | root | caching_sha2_password |
| % | vuluser | caching_sha2_password |

查询结果如上，因为认证方式改变导致的。在老版本里，一般使用加密方式为mysql_native_password。所以可以直接修改为老版本的加密方式，或者升级新版本的加密方式。
【解决办法】
电脑Win+R “cmd ”
按照一下步骤运行

Microsoft Windows [版本 10.0.18362.356]
(c) 2019 Microsoft Corporation。保留所有权利。

C:\Users\18332>mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

C:\Users\18332>
C:\Users\18332>mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 8.0.17 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql;
Database changed
mysql> select user,plugin from user where user=’root’;
ERROR 1054 (42S22): Unknown column '’root’' in 'where clause'
mysql> select user,plugin from user where user= 'root’;
    '>
    '> ^C
mysql> select user,plugin from user where user= 'root’;
    '> select user,plugin from user where user=’root’;^C
mysql> ALTER USER ‘test’@’%’ IDENTIFIED BY ‘123456’ PASSWORD EXPIRE NEVER;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%’ IDENTIFIED BY ‘123456’ PASSWORD EXPIRE NEVER' at line 1
mysql> select user，plugin from user where user = 'root';
ERROR 1054 (42S22): Unknown column 'user，plugin' in 'field list'
mysql> select user，plugin from user where user = 'root';
ERROR 1054 (42S22): Unknown column 'user，plugin' in 'field list'
mysql> select user,plugin from user where user='root';
+------+-----------------------+
| user | plugin                |
+------+-----------------------+
| root | caching_sha2_password |
+------+-----------------------+
1 row in set (0.00 sec)

mysql> alter user 'root'@'%' identified with mysql_native_password by 'root';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%'
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%'
mysql> select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges
    -> ^C
mysql> ^Cush privileges
mysql>